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;
