Thread
-
Strange "actual time" in simple CTE
Jean-Christophe BOGGIO <postgresql@thefreecat.org> — 2023-12-02T16:50:02Z
Hello, I am trying to optimize a complex query and while doing some explains, I stumbled upon this : CTE cfg -> Result (cost=2.02..2.03 rows=1 width=25) (actual time=7167.478..7167.481 rows=1 loops=1) Buffers: shared hit=2 InitPlan 1 (returns $0) -> Limit (cost=0.00..1.01 rows=1 width=1) (actual time=0.058..0.058 rows=1 loops=1) Buffers: shared hit=1 -> Seq Scan on config (cost=0.00..1.01 rows=1 width=1) (actual time=0.024..0.024 rows=1 loops=1) Buffers: shared hit=1 InitPlan 2 (returns $1) -> Limit (cost=0.00..1.01 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1) Buffers: shared hit=1 -> Seq Scan on config config_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1) Buffers: shared hit=1 The CTE query is this: WITH cfg AS ( SELECT (SELECT multidevise FROM config LIMIT 1) AS p_multidevise ,(SELECT monnaie FROM config LIMIT 1) AS p_defaultdevise ,:datedu::DATE AS p_datedu ,:dateau::DATE AS p_dateau ) Table config table only has one row. :datedu and :dateau are named params. How can this take 7 seconds? I am creating this CTE at the start of the query and CROSS JOIN it all along the query. Is it a bad practice to do so? Are these 7 seconds an artefact? Also, when that cfg CTE is being used, sometimes it uses close to nothing: -> CTE Scan on cfg cfg_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1) And sometimes it takes 7 seconds ?! -> CTE Scan on cfg cfg_7 (cost=0.00..0.02 rows=1 width=16) (actual time=7167.481..7167.482 rows=1 loops=1) This really looks like an artefact (maybe in relation to the JIT compiler?) Thanks for your enlightenments. JC Here's the full EXPLAIN PLAN: Sort (cost=3837999522.01..3838152992.01 rows=61388000 width=1454) (actual time=117437.996..117438.093 rows=492 loops=1) Sort Key: s.nom, cl.name, a.nom Sort Method: quicksort Memory: 251kB Buffers: shared hit=71920 CTE cfg -> Result (cost=2.02..2.03 rows=1 width=25) (actual time=7167.478..7167.481 rows=1 loops=1) Buffers: shared hit=2 InitPlan 1 (returns $0) -> Limit (cost=0.00..1.01 rows=1 width=1) (actual time=0.058..0.058 rows=1 loops=1) Buffers: shared hit=1 -> Seq Scan on config (cost=0.00..1.01 rows=1 width=1) (actual time=0.024..0.024 rows=1 loops=1) Buffers: shared hit=1 InitPlan 2 (returns $1) -> Limit (cost=0.00..1.01 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1) Buffers: shared hit=1 -> Seq Scan on config config_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1) Buffers: shared hit=1 CTE daz_adinroy -> HashAggregate (cost=209985.73..241521.32 rows=3153559 width=12) (never executed) Group Key: ra.idad, ra.idoeu, (COALESCE(ra.controllingsoc, o.idsociete)) -> Append (cost=786.61..186334.04 rows=3153559 width=12) (never executed) -> HashAggregate (cost=786.61..806.47 rows=1986 width=12) (never executed) Group Key: ra.idad, ra.idoeu, COALESCE(ra.controllingsoc, o.idsociete) -> Nested Loop (cost=0.43..771.72 rows=1986 width=12) (never executed) -> Seq Scan on royaltiesad ra (cost=0.00..50.86 rows=1986 width=12) (never executed) -> Memoize (cost=0.43..5.34 rows=1 width=8) (never executed) Cache Key: ra.idoeu Cache Mode: logical -> Index Scan using oeu_pkey on oeu o (cost=0.42..5.33 rows=1 width=8) (never executed) Index Cond: (idoeu = ra.idoeu) -> HashAggregate (cost=106708.45..138224.18 rows=3151573 width=12) (never executed) Group Key: ra_1.idad, o_1.idoeu, COALESCE(ra_1.controllingsoc, a_1.idsociete, o_1.idsociete) -> Hash Join (cost=14973.25..83071.65 rows=3151573 width=12) (never executed) Hash Cond: (ra_1.idagreement = a_1.idagreement) -> Hash Join (cost=14923.91..80302.56 rows=1033462 width=24) (never executed) Hash Cond: (og.idoeu = o_1.idoeu) -> Hash Join (cost=264.24..62930.01 rows=1033462 width=20) (never executed) Hash Cond: (og.idgroupe = g.idgroupe) -> Seq Scan on oegroupes og (cost=0.00..45363.20 rows=1858120 width=8) (never executed) -> Hash (cost=248.34..248.34 rows=1272 width=20) (never executed) -> Hash Join (cost=85.46..248.34 rows=1272 width=20) (never executed) Hash Cond: (ra_1.idagreement = g.idagreement) -> Seq Scan on royaltiesad ra_1 (cost=0.00..50.86 rows=1986 width=12) (never executed) -> Hash (cost=56.87..56.87 rows=2287 width=8) (never executed) -> Seq Scan on groupes g (cost=0.00..56.87 rows=2287 width=8) (never executed) -> Hash (cost=11666.52..11666.52 rows=239452 width=8) (never executed) -> Seq Scan on oeu o_1 (cost=0.00..11666.52 rows=239452 width=8) (never executed) -> Hash (cost=34.71..34.71 rows=1171 width=8) (never executed) -> Seq Scan on agreements a_1 (cost=0.00..34.71 rows=1171 width=8) (never executed) CTE currentsoldes2 -> Subquery Scan on currentsoldes (cost=1197.97..1301.70 rows=23 width=27) (actual time=17.699..18.476 rows=1767 loops=1) Filter: (currentsoldes.rang = 1) Buffers: shared hit=304 -> HashAggregate (cost=1197.97..1244.07 rows=4610 width=39) (actual time=17.695..18.209 rows=1767 loops=1) Group Key: soldes_2.idad, soldes_2.idsociete, rank() OVER (?), soldes_2.newbalance, soldes_2.postponed_gross_master, COALESCE(soldes_2.laststatementnet, '0'::double precision) Batches: 1 Memory Usage: 473kB Buffers: shared hit=304 -> WindowAgg (cost=997.16..1117.65 rows=5355 width=39) (actual time=10.749..16.554 rows=1767 loops=1) Run Condition: (rank() OVER (?) <= 1) Buffers: shared hit=304 -> Sort (cost=997.16..1010.55 rows=5355 width=31) (actual time=10.712..11.828 rows=10412 loops=1) Sort Key: soldes_2.idad, soldes_2.idsociete, (COALESCE(soldes_2.date_closingperiod, '1900-01-01'::date)) DESC Sort Method: quicksort Memory: 1117kB Buffers: shared hit=304 -> Nested Loop (cost=0.00..665.50 rows=5355 width=31) (actual time=0.017..5.694 rows=10412 loops=1) Join Filter: ((soldes_2.date_closingperiod < cfg_3.p_datedu) OR (soldes_2.date_closingperiod IS NULL)) Rows Removed by Join Filter: 5654 Buffers: shared hit=304 -> CTE Scan on cfg cfg_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.003 rows=1 loops=1) -> Seq Scan on soldes soldes_2 (cost=0.00..464.66 rows=16066 width=31) (actual time=0.009..2.751 rows=16066 loops=1) Buffers: shared hit=304 CTE detailcalcul -> Append (cost=68592.14..497586.31 rows=2597561 width=248) (actual time=1639.872..11346.625 rows=2598337 loops=1) Buffers: shared hit=55228 " -> Subquery Scan on ""*SELECT* 1_1"" (cost=68592.14..484569.77 rows=2597528 width=236) (actual time=1639.870..11004.550 rows=2598255 loops=1)" Buffers: shared hit=55217 -> Hash Join (cost=68592.14..445606.85 rows=2597528 width=228) (actual time=1639.867..10540.052 rows=2598255 loops=1) Hash Cond: (q.idzdroits = d.idzdroits) Buffers: shared hit=55217 -> Seq Scan on zquoteparts q (cost=0.00..68558.26 rows=2597528 width=28) (actual time=40.897..766.174 rows=2598255 loops=1) Filter: (selectedqp > '0'::double precision) Rows Removed by Filter: 103467 Buffers: shared hit=34784 -> Hash (cost=55214.69..55214.69 rows=1070196 width=45) (actual time=1595.683..1595.686 rows=1070196 loops=1) Buckets: 2097152 Batches: 1 Memory Usage: 75956kB Buffers: shared hit=20433 -> Hash Left Join (cost=1.02..55214.69 rows=1070196 width=45) (actual time=0.069..1275.813 rows=1070196 loops=1) Hash Cond: ((upper((d.devise)::text) = upper((p_1.code)::text)) AND (upper((CASE WHEN cfg_4.p_multidevise THEN d.devise ELSE cfg_4.p_defaultdevise END)::text) = upper((p_1.codedest)::text))) Buffers: shared hit=20433 -> Nested Loop (cost=0.00..41835.94 rows=1070196 width=37) (actual time=0.030..359.845 rows=1070196 loops=1) Buffers: shared hit=20432 -> CTE Scan on cfg cfg_4 (cost=0.00..0.02 rows=1 width=17) (actual time=0.003..0.020 rows=1 loops=1) -> Seq Scan on zdroits d (cost=0.00..31133.96 rows=1070196 width=20) (actual time=0.016..120.020 rows=1070196 loops=1) Buffers: shared hit=20432 -> Hash (cost=1.01..1.01 rows=1 width=16) (actual time=0.023..0.023 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on parites p_1 (cost=0.00..1.01 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1) Buffers: shared hit=1 " -> Subquery Scan on ""*SELECT* 2_1"" (cost=26.90..28.74 rows=33 width=232) (actual time=0.322..0.395 rows=82 loops=1)" Buffers: shared hit=11 -> Nested Loop (cost=26.90..27.91 rows=33 width=64) (actual time=0.319..0.356 rows=82 loops=1) Buffers: shared hit=11 -> CTE Scan on cfg cfg_5 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=1) -> HashAggregate (cost=26.90..27.23 rows=33 width=24) (actual time=0.314..0.334 rows=82 loops=1) Group Key: ce.idsociete, ce.idad Batches: 1 Memory Usage: 32kB Buffers: shared hit=11 -> Nested Loop (cost=0.00..26.57 rows=33 width=17) (actual time=0.183..0.262 rows=151 loops=1) Join Filter: ((ce.datecredit >= cfg_6.p_datedu) AND (ce.datecredit <= cfg_6.p_dateau)) Rows Removed by Join Filter: 738 Buffers: shared hit=11 -> CTE Scan on cfg cfg_6 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1) -> Seq Scan on creditsex ce (cost=0.00..22.11 rows=296 width=21) (actual time=0.031..0.180 rows=889 loops=1) Filter: (COALESCE(idcredextype, 0) < 1000) Buffers: shared hit=11 CTE result1 -> WindowAgg (cost=3780661766.78..3780662817.80 rows=32339 width=259) (actual time=117198.780..117199.680 rows=742 loops=1) Buffers: shared hit=58377 -> HashAggregate (cost=3780661766.78..3780662090.17 rows=32339 width=251) (actual time=117198.771..117199.261 rows=742 loops=1) " Group Key: ""*SELECT* 1_2"".idad, ((""*SELECT* 1_2"".collecte)::numeric), ((""*SELECT* 1_2"".collectepondere)::numeric), ((""*SELECT* 1_2"".droits)::numeric), ((""*SELECT* 1_2"".droitsmaster)::numeric), ((""*SELECT* 1_2"".droitsdep)::numeric), ((""*SELECT* 1_2"".droitsdrm)::numeric), ((""*SELECT* 1_2"".credex)::double precision), ((""*SELECT* 1_2"".avances)::double precision), ""*SELECT* 1_2"".idsoc, ""*SELECT* 1_2"".devise, (false), (false), (false)" Batches: 1 Memory Usage: 1705kB Buffers: shared hit=58377 -> Append (cost=118530.92..3780660634.92 rows=32339 width=251) (actual time=21679.748..117196.878 rows=742 loops=1) Buffers: shared hit=58377 " -> Subquery Scan on ""*SELECT* 1_2"" (cost=118530.92..118778.04 rows=16 width=235) (actual time=21679.746..21681.827 rows=532 loops=1)" Buffers: shared hit=58193 -> Nested Loop (cost=118530.92..118777.56 rows=16 width=59) (actual time=21679.743..21681.564 rows=532 loops=1) Buffers: shared hit=58193 -> Subquery Scan on dc (cost=118530.63..118532.03 rows=31 width=56) (actual time=21679.708..21680.034 rows=540 loops=1) Filter: (NOT (hashed SubPlan 7)) Rows Removed by Filter: 2 Buffers: shared hit=56573 -> HashAggregate (cost=118529.62..118530.24 rows=62 width=56) (actual time=21639.945..21640.121 rows=542 loops=1) Group Key: a_3.idad, a_3.forcedecomptesoc, cfg_7.p_defaultdevise, (0), (0), (0), (0), (0), (0), (0), (0) Batches: 1 Memory Usage: 129kB Buffers: shared hit=56572 -> Append (cost=58445.12..118527.92 rows=62 width=56) (actual time=20816.522..21639.564 rows=556 loops=1) Buffers: shared hit=56572 -> Nested Loop (cost=58445.12..60081.10 rows=51 width=56) (actual time=20816.521..20826.515 rows=20 loops=1) Buffers: shared hit=56268 -> CTE Scan on cfg cfg_7 (cost=0.00..0.02 rows=1 width=16) (actual time=7167.481..7167.482 rows=1 loops=1) Buffers: shared hit=2 -> Seq Scan on ad a_3 (cost=58445.12..60080.57 rows=51 width=8) (actual time=13649.036..13659.022 rows=20 loops=1) Filter: ((forcedecomptesoc IS NOT NULL) AND (NOT (hashed SubPlan 8))) Rows Removed by Filter: 47779 Buffers: shared hit=56266 SubPlan 8 -> CTE Scan on detailcalcul (cost=0.00..51951.22 rows=2597561 width=4) (actual time=1639.877..13071.959 rows=2598337 loops=1) Buffers: shared hit=55228 -> Nested Loop (cost=58445.12..58445.88 rows=11 width=56) (actual time=811.132..812.981 rows=536 loops=1) Buffers: shared hit=304 -> CTE Scan on cfg cfg_8 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.002 rows=1 loops=1) -> CTE Scan on currentsoldes2 cs (cost=58445.12..58445.75 rows=11 width=8) (actual time=811.127..812.898 rows=536 loops=1) Filter: ((NOT (hashed SubPlan 9)) AND ((newbalance > '0'::double precision) OR (laststatementnet <> '0'::double precision))) Rows Removed by Filter: 1231 Buffers: shared hit=304 SubPlan 9 -> CTE Scan on detailcalcul detailcalcul_1 (cost=0.00..51951.22 rows=2597561 width=4) (actual time=39.106..363.440 rows=2598337 loops=1) SubPlan 7 -> Seq Scan on deceasedadlinks (cost=0.00..1.01 rows=1 width=4) (actual time=38.904..38.906 rows=1 loops=1) Buffers: shared hit=1 -> Index Scan using ad_pkey on ad a_2 (cost=0.29..7.92 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=540) Index Cond: (idad = dc.idad) Filter: (NOT COALESCE(isgroupead, false)) Rows Removed by Filter: 0 Buffers: shared hit=1620 -> Subquery Scan on p_2 (cost=63302.48..3628482823.36 rows=31024 width=251) (actual time=13645.266..93288.857 rows=209 loops=1) Buffers: shared hit=180 -> HashAggregate (cost=63302.48..64078.08 rows=31024 width=248) (actual time=13273.998..13275.661 rows=209 loops=1) Group Key: a_4.idad, dc_1.idsoc, dc_1.devise Batches: 1 Memory Usage: 2065kB Buffers: shared hit=180 -> Hash Join (cost=432.55..62449.32 rows=31024 width=248) (actual time=7.477..3304.431 rows=19190470 loops=1) Hash Cond: (dc_1.idad = gl.idadmembre) Buffers: shared hit=180 -> CTE Scan on detailcalcul dc_1 (cost=0.00..51951.22 rows=2597561 width=248) (actual time=0.001..349.551 rows=2598337 loops=1) -> Hash (cost=432.41..432.41 rows=11 width=8) (actual time=7.463..7.465 rows=9850 loops=1) Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 513kB Buffers: shared hit=180 -> Nested Loop (cost=0.30..432.41 rows=11 width=8) (actual time=0.029..5.794 rows=9850 loops=1) Buffers: shared hit=180 -> Seq Scan on groupesadlink gl (cost=0.00..152.50 rows=9850 width=8) (actual time=0.011..1.113 rows=9850 loops=1) Buffers: shared hit=54 -> Memoize (cost=0.30..0.79 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=9850) Cache Key: gl.idadgroupe Cache Mode: logical Hits: 9808 Misses: 42 Evictions: 0 Overflows: 0 Memory Usage: 5kB Buffers: shared hit=126 -> Index Scan using ad_pkey on ad a_4 (cost=0.29..0.78 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=42) Index Cond: (idad = gl.idadgroupe) Filter: isgroupead Buffers: shared hit=126 SubPlan 10 -> Aggregate (cost=58477.59..58477.60 rows=1 width=8) (actual time=188.655..188.655 rows=1 loops=209) -> CTE Scan on detailcalcul tdc (cost=0.00..58445.12 rows=12988 width=8) (actual time=188.651..188.651 rows=0 loops=209) Filter: (idad = p_2.idad) Rows Removed by Filter: 2598337 SubPlan 11 -> Aggregate (cost=58477.59..58477.60 rows=1 width=8) (actual time=194.170..194.170 rows=1 loops=209) -> CTE Scan on detailcalcul tdc_1 (cost=0.00..58445.12 rows=12988 width=8) (actual time=194.166..194.166 rows=0 loops=209) Filter: (idad = p_2.idad) Rows Removed by Filter: 2598337 -> Subquery Scan on p_3 (cost=133539.19..152058548.58 rows=1299 width=251) (actual time=2225.769..2225.777 rows=1 loops=1) Buffers: shared hit=4 -> GroupAggregate (cost=133539.19..133711.31 rows=1299 width=297) (actual time=1864.488..1864.493 rows=1 loops=1) Group Key: a_5.idad, dc_2.idsoc, dc_2.devise, dal.is_heir Buffers: shared hit=4 -> Sort (cost=133539.19..133542.44 rows=1299 width=257) (actual time=1864.454..1864.458 rows=1 loops=1) Sort Key: a_5.idad, dc_2.idsoc, dc_2.devise, dal.is_heir Sort Method: quicksort Memory: 25kB Buffers: shared hit=4 -> Hash Join (cost=123393.48..133472.01 rows=1299 width=257) (actual time=1862.196..1864.432 rows=1 loops=1) Hash Cond: (dc_2.idad = dal.idaddeceased) Buffers: shared hit=4 -> HashAggregate (cost=123384.15..129878.05 rows=259756 width=248) (actual time=1861.461..1864.170 rows=1275 loops=1) Group Key: dc_2.idad, dc_2.idsoc, dc_2.devise Batches: 1 Memory Usage: 16401kB -> CTE Scan on detailcalcul dc_2 (cost=0.00..51951.22 rows=2597561 width=248) (actual time=0.001..294.781 rows=2598337 loops=1) -> Hash (cost=9.32..9.32 rows=1 width=17) (actual time=0.085..0.087 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=4 -> Nested Loop (cost=0.29..9.32 rows=1 width=17) (actual time=0.080..0.082 rows=1 loops=1) Buffers: shared hit=4 -> Seq Scan on deceasedadlinks dal (cost=0.00..1.01 rows=1 width=17) (actual time=0.031..0.032 rows=1 loops=1) Buffers: shared hit=1 -> Index Only Scan using ad_pkey on ad a_5 (cost=0.29..8.31 rows=1 width=4) (actual time=0.029..0.029 rows=1 loops=1) Index Cond: (idad = dal.idadalive) Heap Fetches: 1 Buffers: shared hit=3 SubPlan 12 -> Aggregate (cost=58477.59..58477.60 rows=1 width=8) (actual time=176.146..176.147 rows=1 loops=1) -> CTE Scan on detailcalcul tdc_2 (cost=0.00..58445.12 rows=12988 width=8) (actual time=176.130..176.130 rows=0 loops=1) Filter: (idad = p_3.idad) Rows Removed by Filter: 2598337 SubPlan 13 -> Aggregate (cost=58477.59..58477.60 rows=1 width=8) (actual time=185.099..185.100 rows=1 loops=1) -> CTE Scan on detailcalcul tdc_3 (cost=0.00..58445.12 rows=12988 width=8) (actual time=185.084..185.084 rows=0 loops=1) Filter: (idad = p_3.idad) Rows Removed by Filter: 2598337 CTE allannuaires -> HashAggregate (cost=4874.10..4876.86 rows=276 width=40) (actual time=5.365..5.479 rows=513 loops=1) Group Key: r_1.idad, (NULL::integer), (array_agg(DISTINCT laa.idannuaire)) Batches: 1 Memory Usage: 105kB Buffers: shared hit=3233 -> Append (cost=3069.41..4872.03 rows=276 width=40) (actual time=1.434..5.119 rows=513 loops=1) Buffers: shared hit=3233 -> GroupAggregate (cost=3069.41..4023.14 rows=200 width=40) (actual time=1.434..4.505 rows=509 loops=1) Group Key: r_1.idad, NULL::integer Buffers: shared hit=3215 -> Merge Join (cost=3069.41..3769.62 rows=33469 width=16) (actual time=1.380..3.651 rows=532 loops=1) Merge Cond: (laa.idacteur = r_1.idad) Buffers: shared hit=3215 -> Index Scan using liensacteursannuaire_idacteur on liensacteursannuaire laa (cost=0.28..188.46 rows=3997 width=12) (actual time=0.012..1.606 rows=3960 loops=1) Buffers: shared hit=3215 -> Sort (cost=3069.13..3149.98 rows=32339 width=4) (actual time=1.325..1.384 rows=747 loops=1) Sort Key: r_1.idad Sort Method: quicksort Memory: 25kB -> CTE Scan on result1 r_1 (cost=0.00..646.78 rows=32339 width=4) (actual time=0.002..1.223 rows=742 loops=1) -> GroupAggregate (cost=835.43..844.75 rows=76 width=40) (actual time=0.467..0.544 rows=4 loops=1) Group Key: NULL::integer, s_1.idsociete Buffers: shared hit=18 -> Sort (cost=835.43..837.52 rows=837 width=16) (actual time=0.445..0.470 rows=289 loops=1) Sort Key: s_1.idsociete Sort Method: quicksort Memory: 40kB Buffers: shared hit=18 -> Hash Join (cost=18.66..794.79 rows=837 width=16) (actual time=0.133..0.398 rows=289 loops=1) Hash Cond: (r_2.idsoc = s_1.idsociete) Buffers: shared hit=18 -> CTE Scan on result1 r_2 (cost=0.00..646.78 rows=32339 width=4) (actual time=0.000..0.100 rows=742 loops=1) -> Hash (cost=18.60..18.60 rows=5 width=12) (actual time=0.121..0.123 rows=8 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=18 -> Nested Loop (cost=0.29..18.60 rows=5 width=12) (actual time=0.074..0.116 rows=8 loops=1) Buffers: shared hit=18 -> Seq Scan on societes s_1 (cost=0.00..2.76 rows=76 width=8) (actual time=0.007..0.016 rows=77 loops=1) Buffers: shared hit=2 -> Memoize (cost=0.29..2.36 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=77) Cache Key: s_1.idactorsolorealm Cache Mode: logical Hits: 71 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB Buffers: shared hit=16 -> Index Scan using liensacteursannuaire_idacteur on liensacteursannuaire laa_1 (cost=0.28..2.35 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=6) Index Cond: (idacteur = s_1.idactorsolorealm) Buffers: shared hit=16 -> Nested Loop Left Join (cost=4941.07..9833311.11 rows=61388000 width=1454) (actual time=117307.683..117434.084 rows=492 loops=1) Buffers: shared hit=71920 -> Hash Left Join (cost=4940.78..11230.83 rows=61388 width=526) (actual time=117307.188..117323.931 rows=492 loops=1) Hash Cond: ((a.idad = ap.idad) AND (s.idsociete = ap.idsociete)) Buffers: shared hit=66286 -> Hash Left Join (cost=3589.51..9557.27 rows=61388 width=518) (actual time=117289.523..117305.951 rows=492 loops=1) Hash Cond: (a.idclient = cl.idclient) Buffers: shared hit=65963 -> Hash Left Join (cost=3588.44..9394.89 rows=61388 width=505) (actual time=117289.502..117305.645 rows=492 loops=1) Hash Cond: (r.idsoc = aa2.idsociete) Buffers: shared hit=65962 -> Hash Left Join (cost=3579.47..7103.89 rows=44484 width=473) (actual time=117289.389..117305.239 rows=492 loops=1) Hash Cond: (r.idad = aa.idad) Buffers: shared hit=65962 -> Hash Left Join (cost=3570.50..5441.27 rows=32235 width=441) (actual time=117283.600..117299.104 rows=492 loops=1) Hash Cond: ((a.idad = x.idad) AND (s.idsociete = x.idsociete)) Buffers: shared hit=62729 -> Hash Left Join (cost=2268.46..3816.86 rows=32235 width=419) (actual time=117262.207..117277.319 rows=490 loops=1) Hash Cond: ((a.idad = sl.idad) AND (s.idsociete = sl.idsociete)) Buffers: shared hit=62425 -> Hash Left Join (cost=2267.65..3574.29 rows=32235 width=385) (actual time=117261.493..117276.208 rows=489 loops=1) Hash Cond: (r.idsoc = s.idsociete) Buffers: shared hit=62425 -> Hash Left Join (cost=2263.94..3484.17 rows=32235 width=362) (actual time=117261.404..117275.751 rows=489 loops=1) Hash Cond: (idannuaire_main(a.*) = ann.idannuaire) Buffers: shared hit=62423 -> Hash Join (cost=2111.50..2843.18 rows=32235 width=1596) (actual time=117259.217..117260.097 rows=489 loops=1) Hash Cond: (r.idad = a.idad) Buffers: shared hit=59415 -> CTE Scan on result1 r (cost=0.00..646.78 rows=32339 width=259) (actual time=117198.783..117198.965 rows=742 loops=1) Buffers: shared hit=58377 -> Hash (cost=1515.96..1515.96 rows=47643 width=1337) (actual time=60.315..60.316 rows=47648 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 10979kB Buffers: shared hit=1038 -> Seq Scan on ad a (cost=0.00..1515.96 rows=47643 width=1337) (actual time=0.047..46.338 rows=47648 loops=1) Filter: calculatestatements Rows Removed by Filter: 151 Buffers: shared hit=1038 -> Hash (cost=103.31..103.31 rows=3931 width=34) (actual time=1.792..1.792 rows=3937 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 232kB Buffers: shared hit=64 -> Seq Scan on annuaire ann (cost=0.00..103.31 rows=3931 width=34) (actual time=0.016..0.927 rows=3937 loops=1) Buffers: shared hit=64 -> Hash (cost=2.76..2.76 rows=76 width=23) (actual time=0.079..0.080 rows=77 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 13kB Buffers: shared hit=2 -> Seq Scan on societes s (cost=0.00..2.76 rows=76 width=23) (actual time=0.025..0.052 rows=77 loops=1) Buffers: shared hit=2 -> Hash (cost=0.46..0.46 rows=23 width=42) (actual time=0.703..0.703 rows=1767 loops=1) Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 116kB -> CTE Scan on currentsoldes2 sl (cost=0.00..0.46 rows=23 width=42) (actual time=0.003..0.254 rows=1767 loops=1) -> Hash (cost=1301.70..1301.70 rows=23 width=30) (actual time=21.382..21.385 rows=2041 loops=1) Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 104kB Buffers: shared hit=304 -> Subquery Scan on x (cost=1197.97..1301.70 rows=23 width=30) (actual time=20.156..20.915 rows=2041 loops=1) Filter: (x.rang = 1) Buffers: shared hit=304 -> HashAggregate (cost=1197.97..1244.07 rows=4610 width=50) (actual time=20.154..20.632 rows=2041 loops=1) Group Key: soldes.idad, soldes.idsociete, rank() OVER (?), soldes.newbalance, soldes.grossamountearnedexternally, soldes.date_closingperiod Batches: 1 Memory Usage: 473kB Buffers: shared hit=304 -> WindowAgg (cost=997.16..1117.65 rows=5355 width=50) (actual time=13.213..19.195 rows=2041 loops=1) Run Condition: (rank() OVER (?) <= 1) Buffers: shared hit=304 -> Sort (cost=997.16..1010.55 rows=5355 width=42) (actual time=13.200..14.167 rows=14598 loops=1) Sort Key: soldes.idad, soldes.idsociete, (COALESCE(soldes.date_closingperiod, '1900-01-01'::date)) DESC Sort Method: quicksort Memory: 1411kB Buffers: shared hit=304 -> Nested Loop (cost=0.00..665.50 rows=5355 width=42) (actual time=0.014..5.814 rows=14598 loops=1) Join Filter: ((soldes.date_closingperiod <= cfg.p_dateau) OR (soldes.date_closingperiod IS NULL)) Rows Removed by Join Filter: 1468 Buffers: shared hit=304 -> CTE Scan on cfg (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1) -> Seq Scan on soldes (cost=0.00..464.66 rows=16066 width=38) (actual time=0.008..2.592 rows=16066 loops=1) Buffers: shared hit=304 -> Hash (cost=5.52..5.52 rows=276 width=36) (actual time=5.779..5.780 rows=509 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 42kB Buffers: shared hit=3233 -> CTE Scan on allannuaires aa (cost=0.00..5.52 rows=276 width=36) (actual time=5.368..5.665 rows=513 loops=1) Buffers: shared hit=3233 -> Hash (cost=5.52..5.52 rows=276 width=36) (actual time=0.105..0.106 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> CTE Scan on allannuaires aa2 (cost=0.00..5.52 rows=276 width=36) (actual time=0.002..0.065 rows=513 loops=1) -> Hash (cost=1.03..1.03 rows=3 width=17) (actual time=0.012..0.014 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on clients cl (cost=0.00..1.03 rows=3 width=17) (actual time=0.006..0.007 rows=3 loops=1) Buffers: shared hit=1 -> Hash (cost=1351.26..1351.26 rows=1 width=16) (actual time=17.652..17.658 rows=190 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 17kB Buffers: shared hit=323 -> Subquery Scan on ap (cost=1351.23..1351.26 rows=1 width=16) (actual time=17.500..17.618 rows=190 loops=1) Buffers: shared hit=323 -> GroupAggregate (cost=1351.23..1351.25 rows=1 width=16) (actual time=17.498..17.593 rows=190 loops=1) Group Key: p.idad, p.idsociete Buffers: shared hit=323 -> Sort (cost=1351.23..1351.23 rows=1 width=16) (actual time=17.491..17.510 rows=190 loops=1) Sort Key: p.idad, p.idsociete Sort Method: quicksort Memory: 35kB Buffers: shared hit=323 -> Nested Loop (cost=1246.18..1351.22 rows=1 width=16) (actual time=16.185..17.447 rows=190 loops=1) Join Filter: (p.dateinperiod <= cfg_1.p_dateau) Rows Removed by Join Filter: 111 Buffers: shared hit=323 -> Hash Join (cost=1246.18..1351.19 rows=1 width=20) (actual time=16.174..17.303 rows=301 loops=1) Hash Cond: ((x_1.idad = p.idad) AND (x_1.idsociete = p.idsociete)) Join Filter: (p.dateinperiod > x_1.date_closingperiod) Rows Removed by Join Filter: 1380 Buffers: shared hit=323 -> Subquery Scan on x_1 (cost=1184.58..1288.31 rows=23 width=12) (actual time=15.506..16.172 rows=1922 loops=1) Filter: (x_1.rang = 1) Buffers: shared hit=304 -> HashAggregate (cost=1184.58..1230.68 rows=4610 width=32) (actual time=15.504..15.928 rows=1922 loops=1) Group Key: soldes_1.idad, soldes_1.idsociete, rank() OVER (?), soldes_1.newbalance, soldes_1.date_closingperiod Batches: 1 Memory Usage: 473kB Buffers: shared hit=304 -> WindowAgg (cost=997.16..1117.65 rows=5355 width=32) (actual time=9.371..14.611 rows=1923 loops=1) Run Condition: (rank() OVER (?) <= 1) Buffers: shared hit=304 -> Sort (cost=997.16..1010.55 rows=5355 width=24) (actual time=9.360..10.177 rows=12988 loops=1) Sort Key: soldes_1.idad, soldes_1.idsociete, (COALESCE(soldes_1.date_closingperiod, '1900-01-01'::date)) DESC Sort Method: quicksort Memory: 1298kB Buffers: shared hit=304 -> Nested Loop (cost=0.00..665.50 rows=5355 width=24) (actual time=0.008..4.374 rows=12988 loops=1) Join Filter: ((soldes_1.date_closingperiod < cfg_2.p_dateau) OR (soldes_1.date_closingperiod IS NULL)) Rows Removed by Join Filter: 3078 Buffers: shared hit=304 -> CTE Scan on cfg cfg_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1) -> Seq Scan on soldes soldes_1 (cost=0.00..464.66 rows=16066 width=20) (actual time=0.003..1.879 rows=16066 loops=1) Buffers: shared hit=304 -> Hash (cost=36.04..36.04 rows=1704 width=20) (actual time=0.643..0.644 rows=1704 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 103kB Buffers: shared hit=19 -> Seq Scan on payments p (cost=0.00..36.04 rows=1704 width=20) (actual time=0.009..0.248 rows=1704 loops=1) Buffers: shared hit=19 -> CTE Scan on cfg cfg_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=301) -> Function Scan on calculate_net cn (cost=0.29..10.29 rows=1000 width=168) (actual time=0.211..0.211 rows=1 loops=492) Buffers: shared hit=5634 Planning: Buffers: shared hit=106 Planning Time: 15.825 ms JIT: Functions: 458 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 90.276 ms, Inlining 44.404 ms, Optimization 4297.544 ms, Emission 2940.759 ms, Total 7372.983 ms Execution Time: 117642.499 ms And this is the query: EXPLAIN(ANALYZE, BUFFERS) WITH cfg AS ( SELECT (SELECT multidevise FROM config LIMIT 1) AS p_multidevise ,(SELECT monnaie FROM config LIMIT 1) AS p_defaultdevise ,:datedu::DATE AS p_datedu ,:dateau::DATE AS p_dateau ) , daz_adinroy AS ( SELECT DISTINCT idad, idoeu, COALESCE(ra.controllingsoc, o.idsociete) AS idsociete FROM royaltiesad ra JOIN oeu o USING(idoeu) UNION SELECT DISTINCT ra.idad, o.idoeu, COALESCE(ra.controllingsoc, a.idsociete, o.idsociete) AS idsociete FROM royaltiesad ra JOIN agreements a using(idagreement) JOIN groupes g USING(idagreement) JOIN oegroupes og USING(idgroupe) JOIN oeu o ON og.idoeu=o.idoeu ) , daz_allad AS ( SELECT idad, idoeu, COALESCE(a.stmt_idsociete_forced, idsociete) AS idsociete FROM daz_adinroy JOIN ad a USING(idad) WHERE NULLIF(specialsplit,0) IS NULL UNION SELECT ca.idad, air.idoeu, COALESCE(a.stmt_idsociete_forced, idsociete) AS idsociete FROM daz_adinroy air JOIN ad a USING(idad) JOIN copyrightad ca ON ca.idoeu=air.idoeu AND ca.iscontrolled and a.idad=ca.idad AND ( (specialsplit=1 AND ca.role IN ('A','C','CA','AC','AD','AR','I')) OR (specialsplit=2 AND ca.role IN ('E','CE','SE','ES')) OR (specialsplit=3) ) WHERE specialsplit>0 ) ,payablead AS ( SELECT DISTINCT idad FROM royaltiesad ) ,currentsoldes AS ( SELECT DISTINCT idad,idsociete,rank() OVER (PARTITION BY idad,idsociete ORDER BY COALESCE(date_closingperiod,'1900-01-01') DESC ) AS rang, newbalance, postponed_gross_master, COALESCE(laststatementnet,0) AS laststatementnet FROM soldes CROSS JOIN cfg -- attention : < et pas <= WHERE date_closingperiod<p_datedu OR date_closingperiod IS NULL ) ,currentsoldes2 AS ( SELECT idad,idsociete,newbalance, postponed_gross_master, laststatementnet FROM currentsoldes WHERE rang=1 ) ,detailcalcul AS ( SELECT q.idad ,ARRONDIS4(coalesce(p.taux,1)*d.montant) AS collecte ,ARRONDIS4(coalesce(p.taux,1)*d.montant*q.baseredevance/100) AS collectepondere ,CASE WHEN d.typedroits BETWEEN 1 AND 20 THEN ARRONDIS4(coalesce(p.taux,1)*(q.selectedqp/100)*d.montant*q.baseredevance/100) ELSE NULL END AS droits ,CASE WHEN d.typedroits BETWEEN 21 AND 22 THEN ARRONDIS4(coalesce(p.taux,1)*(q.selectedqp/100)*d.montant*q.baseredevance/100) ELSE NULL END AS droitsmaster ,CASE WHEN d.typedroits IN (1,9) THEN ARRONDIS4(coalesce(p.taux,1)*(q.selectedqp/100)*d.montant*q.baseredevance/100) ELSE NULL END AS droitsDEP ,CASE WHEN d.typedroits IN (2,10) THEN ARRONDIS4(coalesce(p.taux,1)*(q.selectedqp/100)*d.montant*q.baseredevance/100) ELSE NULL END AS droitsDRM ,0 AS credex ,0 AS avances ,q.idsoc ,CAST(CASE WHEN p_multidevise THEN d.devise ELSE p_defaultdevise END AS VARCHAR(4)) AS devise FROM zDroits d CROSS JOIN cfg LEFT JOIN parites p ON UPPER(p.code)=UPPER(d.devise) AND UPPER(p.codedest)=UPPER(CASE WHEN p_multidevise THEN d.devise ELSE p_defaultdevise END) JOIN zQuoteParts q USING(idzdroits) WHERE q.selectedqp>0 UNION ALL -- Ajouter les crédits exceptionnels pour faire apparaître les décomptes pour ceux qui n'ont pas de droits, juste des crédits ex SELECT cr.idad, 0, 0, 0, 0, 0, 0, cr.credex, cr.avances, cr.idsociete, p_defaultdevise AS devise FROM ( SELECT idsociete ,idad ,COALESCE(sum(CASE WHEN NOT COALESCE(isnet,FALSE) THEN montant ELSE 0 END),0) as credex ,COALESCE(sum(CASE WHEN isnet THEN montant ELSE 0 END),0) as avances FROM creditsex ce CROSS JOIN cfg WHERE datecredit BETWEEN p_datedu AND p_dateau AND COALESCE(ce.idcredextype,0)<1000 GROUP BY idsociete,idad ) cr CROSS JOIN cfg ) , detailcalcul2 AS ( -- forcer un décompte sur la société XXX SELECT a.idad, a.forcedecomptesoc AS idsoc, p_defaultdevise AS devise, 0 AS collecte, 0 AS collectepondere, 0 AS droits, 0 AS droitsmaster, 0 AS droitsDEP, 0 AS droitsDRM, 0 AS credex, 0 AS avances FROM ad a CROSS JOIN cfg WHERE a.forcedecomptesoc IS NOT NULL AND a.idad NOT IN (SELECT idad FROM detailcalcul) -- UNION les AD qui ont un solde brut > 0 ou net <> 0 UNION SELECT cs.idad, cs.idsociete, p_defaultdevise AS devise, 0 AS collecte, 0 AS collectepondere, 0 AS droits, 0 AS droitsmaster, 0 AS droitsDEP, 0 AS droitsDRM, 0 AS credex, 0 AS avances FROM currentsoldes2 cs CROSS JOIN cfg WHERE (cs.newbalance>0 OR cs.laststatementnet<>0) AND cs.idad NOT IN (SELECT idad FROM detailcalcul) -- UNION detailcalcul pour daz in [0,2] -- &union1 -- UNION daz_allad pour daz in [1,2] -- &union2 ) , detailscalcul2groupe_pre AS ( SELECT a.idad ,dc.idsoc ,dc.devise ,SUM(dc.collecte) AS collecte ,SUM(dc.collectepondere) AS collectepondere ,SUM(dc.droits) AS droits ,SUM(dc.droitsmaster) AS droitsmaster ,SUM(dc.droitsDEP) AS droitsDEP ,SUM(dc.droitsDRM) AS droitsDRM ,SUM(dc.credex) AS credex ,SUM(dc.avances) AS avances FROM detailcalcul dc JOIN groupesadlink gl ON gl.idadmembre=dc.idad JOIN ad a ON a.idad=gl.idadgroupe WHERE a.isgroupead GROUP BY a.idad,idsoc, dc.devise ) , detailscalcul2groupe AS ( SELECT idad ,idsoc ,devise ,collecte ,collectepondere ,droits ,droitsmaster ,droitsDEP ,droitsDRM ,credex + COALESCE((SELECT SUM(credex) FROM detailcalcul tdc WHERE tdc.idad=p.idad),0) AS credex ,avances + COALESCE((SELECT SUM(avances) FROM detailcalcul tdc WHERE tdc.idad=p.idad),0) AS avances FROM detailscalcul2groupe_pre p ) ,detailscalcul2heritiers_pre1 AS ( SELECT dc.idad ,dc.idsoc ,dc.devise ,SUM(dc.collecte) AS collecte ,SUM(dc.collectepondere) AS collectepondere ,SUM(dc.droits) as droits ,SUM(dc.droitsmaster) as droitsmaster ,SUM(dc.droitsDEP) as droitsDEP ,SUM(dc.droitsDRM) as droitsDRM ,SUM(dc.credex) AS credex ,SUM(dc.avances) AS avances FROM detailcalcul dc GROUP BY dc.idad, dc.idsoc, dc.devise -- &union2 ) ,detailscalcul2heritiers_pre2 AS ( SELECT a.idad ,dc.idsoc ,dc.devise ,dal.is_heir ,SUM(dc.collecte) AS collecte ,SUM(dc.collectepondere) AS collectepondere ,SUM( ARRONDIS4(dc.droits * COALESCE(dal.share,0)/100) ) as droits ,SUM( ARRONDIS4(dc.droitsmaster * COALESCE(dal.share,0)/100) ) as droitsmaster ,SUM( ARRONDIS4(dc.droitsDEP * COALESCE(dal.share,0)/100) ) as droitsDEP ,SUM( ARRONDIS4(dc.droitsDRM * COALESCE(dal.share,0)/100) ) as droitsDRM ,SUM( ARRONDIS4(dc.credex * COALESCE(dal.share,0)/100) ) AS credex ,SUM( ARRONDIS4(dc.avances * COALESCE(dal.share,0)/100) ) AS avances FROM detailscalcul2heritiers_pre1 dc JOIN deceasedadlinks dal ON dal.idaddeceased=dc.idad JOIN ad a on a.idad=dal.idadalive GROUP BY a.idad, idsoc, dc.devise, dal.is_heir ) ,detailscalcul2heritiers AS ( SELECT idad ,idsoc ,devise ,collecte ,collectepondere ,droits ,droitsmaster ,droitsDEP ,droitsDRM ,credex + COALESCE((SELECT SUM(credex) FROM detailcalcul tdc WHERE tdc.idad=p.idad),0) AS credex ,avances + COALESCE((SELECT SUM(avances) FROM detailcalcul tdc WHERE tdc.idad=p.idad),0) AS avances ,is_heir FROM detailscalcul2heritiers_pre2 p ) , result1 AS ( SELECT z.*, row_number() OVER() AS position FROM ( SELECT dc.idad,dc.collecte, dc.collectepondere, dc.droits, dc.droitsmaster, dc.droitsDEP, dc.droitsDRM, dc.credex, dc.avances, dc.idsoc, dc.devise, false AS isgroup, false AS isheritier, false AS cotisationsheritier FROM detailcalcul2 dc JOIN ad a USING(idad) WHERE NOT COALESCE(a.isgroupead,FALSE) AND NOT idad IN (SELECT idadalive FROM deceasedadlinks) UNION SELECT dc.idad,dc.collecte, dc.collectepondere, dc.droits, dc.droitsmaster, dc.droitsDEP, dc.droitsDRM, dc.credex, dc.avances, dc.idsoc, dc.devise, true AS isgroup, false AS isheritier, false AS cotisationsheritier FROM detailscalcul2groupe dc UNION SELECT dc.idad,dc.collecte, dc.collectepondere, dc.droits, dc.droitsmaster, dc.droitsDEP, dc.droitsDRM, dc.credex, dc.avances, dc.idsoc, dc.devise, false AS isgroup, true AS isheritier, is_heir AS cotisationsheritier FROM detailscalcul2heritiers dc ) z ) ,lastsoldes AS ( SELECT idad,idsociete,newbalance, date_closingperiod, GrossAmountEarnedExternally FROM ( SELECT DISTINCT idad,idsociete,rank() OVER (PARTITION BY idad,idsociete ORDER BY COALESCE(date_closingperiod,'1900-01-01') DESC ) AS rang, newbalance, GrossAmountEarnedExternally, date_closingperiod FROM soldes CROSS JOIN cfg -- attention : <= et pas < WHERE date_closingperiod<=p_dateau OR date_closingperiod IS NULL ) x WHERE x.rang=1 ) ,lastsoldesforpayments AS ( SELECT idad,idsociete,newbalance, date_closingperiod FROM ( SELECT DISTINCT idad,idsociete,rank() OVER (PARTITION BY idad,idsociete ORDER BY COALESCE(date_closingperiod,'1900-01-01') DESC ) AS rang, newbalance, date_closingperiod FROM soldes CROSS JOIN cfg -- attention : < et pas <= car si la période a déjà été clôturée, on ne voit aucun paiement WHERE date_closingperiod<p_dateau OR date_closingperiod IS NULL ) x WHERE rang=1 ) ,apayments AS ( SELECT p.idad,p.idsociete,SUM(amount) AS totpayments FROM payments p CROSS JOIN cfg LEFT JOIN lastsoldesforpayments ls ON ls.idad=p.idad AND ls.idsociete=p.idsociete WHERE p.dateinperiod>date_closingperiod AND p.dateinperiod<=p_dateau GROUP BY p.idad,p.idsociete ) ,allannuaires AS ( SELECT r.idad, NULL::INT AS idsociete, array_agg(DISTINCT idannuaire) AS idannuaires FROM result1 r JOIN LiensActeursAnnuaire laa ON laa.idacteur=r.idad GROUP BY 1,2 UNION SELECT NULL::INT AS idad, s.idsociete, array_agg(DISTINCT idannuaire) AS idannuaires FROM result1 r JOIN societes s ON r.idsoc=s.idsociete JOIN LiensActeursAnnuaire laa ON laa.idacteur=s.idactorsolorealm GROUP BY 1,2 ) SELECT r.idad ,r.collecte ,r.collectepondere ,ARRONDIS(r.droits) AS droits ,ARRONDIS(r.droitsmaster) AS droitsmaster ,ARRONDIS(r.droitsDEP) AS droitsDEP ,ARRONDIS(r.droitsDRM) AS droitsDRM ,r.credex ,r.avances ,ARRONDIS(COALESCE(sl.newbalance,0)+r.droits+r.credex) AS montantdu ,ARRONDIS(COALESCE(sl.postponed_gross_master,0)+r.droitsmaster) AS montantdumaster ,r.idsoc ,r.devise ,isgroup ,r.isheritier ,(a.nom || COALESCE(' (' || NULLIF(TRIM(a.libelledecompte),'') || ')', ''))::VARCHAR(100) AS nomad ,NULLIF(TRIM(COALESCE(ann.email,a.email)),'')::VARCHAR(200) AS email ,NULLIF(TRIM(COALESCE(ann.email,a.email)),'') IS NOT NULL AS hasemail ,COALESCE(s.nom,'Société indéfinie')::VARCHAR(30) AS nomsociete ,sl.newbalance ,sl.postponed_gross_master ,cn.* ,r.position -- ,CASE WHEN ARRONDIS(COALESCE(sl.newbalance,0)+r.droits+r.credex) >= cn.paymentthreshold THEN ARRONDIS(COALESCE(sl.newbalance,0)+r.droits+r.credex) ELSE 0 END AS apayer -- ,CASE WHEN ARRONDIS(COALESCE(sl.newbalance,0)+r.droits+r.credex) < cn.paymentthreshold THEN ARRONDIS(COALESCE(sl.newbalance,0)+r.droits+r.credex) ELSE 0 END AS areporter ,CASE WHEN ARRONDIS( cn.netpayable + COALESCE(sl.laststatementnet,0) - COALESCE(ap.totpayments,0) ) >= cn.paymentthreshold THEN ARRONDIS( cn.netpayable + COALESCE(sl.laststatementnet,0) - COALESCE(ap.totpayments,0) ) ELSE 0 END AS apayer ,CASE WHEN ARRONDIS( cn.netpayable + COALESCE(sl.laststatementnet,0) - COALESCE(ap.totpayments,0) ) < cn.paymentthreshold THEN ARRONDIS(COALESCE(sl.newbalance,0)+r.droits+r.credex) ELSE 0 END AS areporter ,lc.date_closingperiod ,a.idclient ,cl.name AS clientname ,-ap.totpayments AS totpayments ,COALESCE(sl.laststatementnet,0) - COALESCE(ap.totpayments,0) AS openingbalance ,COALESCE(sl.laststatementnet,0) AS laststatementnet ,a.ispayable ,r.cotisationsheritier ,s.idrealm ,ann.idannuaire ,COALESCE(ann.wantsenglish,FALSE) AS wantsenglish ,aa.idannuaires ,aa2.idannuaires AS idannuaires2 ,COALESCE(ann.disablenotifications,FALSE) AS DisableNotifications ,ann.iscompany FROM result1 r JOIN ad a USING(idad) LEFT JOIN allannuaires aa USING(idad) LEFT JOIN allannuaires aa2 ON r.idsoc=aa2.idsociete LEFT JOIN annuaire ann ON a.idannuaire_main=ann.idannuaire LEFT JOIN societes s ON s.idsociete=r.idsoc LEFT JOIN currentsoldes2 sl ON sl.idad=a.idad AND sl.idsociete=s.idsociete LEFT JOIN lastsoldes lc ON lc.idad=a.idad AND lc.idsociete=s.idsociete LEFT JOIN calculate_net(a.idad,s.idsociete,ARRONDIS(COALESCE(sl.newbalance,0)+r.droits+r.credex),r.droitsDEP,r.droitsDRM,ARRONDIS(COALESCE(sl.postponed_gross_master,0)+r.droitsmaster),lc.GrossAmountEarnedExternally,r.cotisationsheritier) cn ON TRUE LEFT JOIN clients cl ON cl.idclient=a.idclient LEFT JOIN apayments ap ON ap.idad=a.idad AND ap.idsociete=s.idsociete WHERE a.calculatestatements ORDER BY s.nom,cl.name,a.nom; -
Re: Strange "actual time" in simple CTE
Jeff Janes <jeff.janes@gmail.com> — 2023-12-04T03:15:27Z
On Sat, Dec 2, 2023 at 11:50 AM Jean-Christophe Boggio < postgresql@thefreecat.org> wrote: > Hello, > > I am trying to optimize a complex query and while doing some explains, I > stumbled upon this : > > CTE cfg > -> Result (cost=2.02..2.03 rows=1 width=25) (actual > time=7167.478..7167.481 rows=1 loops=1) > ... > How can this take 7 seconds? > > This really looks like an artefact (maybe in relation to the JIT compiler?) > > Exactly. The time taking to do the JIT compilations gets measured in non-intuitive places in the plan. I'm guessing that that is what is going on here, especially since the time separately reported at the end of the plan for JIT so closely matches this mysterious time. Just turn JIT off, I doubt it doing you any good anyway. Cheers, Jeff