Vous n'êtes pas identifié(e).
Pages : 1
Bonjour à tous,
Suite à une migration de Postgres 11 vers Postgres 14, je rencontre de nombreuses pertes de performances sur des requêtes assez simples. Et j'ai remarqué que c'est l'utilisation de UNION dans ces requêtes qui posent souci.
J'ai un exemple où j'ai trois requêtes que j'ai combinées avec des UNION : dans Postgres 11, la requête "combinée" s'exécute en 1,2 secondes, et dans Postgres 14, elle s'exécute en 6,5 secondes... Si en revanche je teste chacune de ces 3 requêtes indépendamment, j'ai les mêmes temps d'exécution des deux côtés.
J'ai essayé de refaire les index, de regarder les EXPLAIN ANALYSE (je vois des Nested Loop, des HashAggregate qui prennent plus de temps mais je ne vois pas trop comment analyser ça) et je n'ai pas trouvé de solution.
Est-ce que depuis la version 11, il y a eu des changements dans le moteur de Postgres qui font que les UNION plombent les performances ? (j'ai essayé avec des UNION ALL, ça n'a rien changé)
Pour info, la migration des données de la 11 à la 14 s'est faite avec des pg_dump (format custom) et des pg_restore.
Merci d'avance pour vos retours.
Hors ligne
Bonjour,
Avez-vous effectué un VACUUM ANALYZE de la (ou les) bases après le pg_restore ? Sinon, pouvez-vous donner les plans d'exécution (EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT ...) d'une même requête avec pg 11 et pg14 ?
Julien.
https://rjuju.github.io/
Hors ligne
Bonjour,
Oui j'avais effectué un VACUUM ANALYZE sur toutes les tables concernées mais pas de changement.
Je pose ici les plans d'exécution (je n'avais fait que EXPLAIN ANALYZE) :
- avec Postgres 11 :
PG11_MASTER
HashAggregate (cost=1265959.01..1265959.04 rows=3 width=100) (actual time=1603.467..1603.467 rows=3 loops=1)
Group Key: ('Groupe de réf.'::text), ('Salariés privé'::text), (1), (((((((sum(a.etppres)) + (sum(a.etpnpres))) - ((sum(a.etpprespub)) + (sum(a.etpnprespub)))))::numeric / (((((sum(a.etppres)) + (sum(a.etpnpres))) - ((sum(a.etpprespub)) + (sum(a.etpnprespub)))) + ((sum(a.etpprespub)) + (sum(a.etpnprespub)))))::numeric) * ((sum(a_2.pxx_emplt_sal)))::numeric))
-> Append (cost=421987.61..1265958.98 rows=3 width=100) (actual time=543.885..1603.455 rows=3 loops=1)
-> Nested Loop (cost=421987.61..421987.69 rows=1 width=100) (actual time=543.885..543.885 rows=1 loops=1)
-> Aggregate (cost=178676.99..178677.00 rows=1 width=32) (actual time=251.540..251.540 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Aggregate (cost=4670.34..4670.35 rows=1 width=32) (actual time=12.189..12.189 rows=1 loops=1)
-> Seq Scan on clap (cost=0.00..4491.07 rows=71707 width=5) (actual time=0.003..7.149 rows=71707 loops=1)
InitPlan 3 (returns $5)
-> Subquery Scan on epci_groupe_ref (cost=68634.44..68634.45 rows=1 width=4) (actual time=58.009..58.009 rows=1 loops=1)
-> Sort (cost=68634.44..68634.44 rows=1 width=28) (actual time=58.008..58.008 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_12.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_12.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_12.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 2 (returns $2)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=57.507..57.507 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=57.403..57.571 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=53.667..53.667 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.007..35.660 rows=180402 loops=3)
-> GroupAggregate (cost=1.43..641.78 rows=1 width=28) (actual time=57.999..57.999 rows=1 loops=1)
Group Key: d_6.id_peri
-> Nested Loop (cost=1.43..641.35 rows=41 width=32) (actual time=57.560..57.987 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_6 (cost=0.29..8.31 rows=1 width=24) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..632.63 rows=41 width=15) (actual time=57.547..57.968 rows=72 loops=1)
-> Nested Loop (cost=0.71..465.43 rows=44 width=13) (actual time=0.021..0.194 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_12 (cost=0.42..156.41 rows=41 width=13) (actual time=0.009..0.018 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_12 (cost=0.29..7.53 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_12.insee)
-> Index Scan using cc_emp_pkey on cc_emp a_12 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_12.depcoma) AND (annee = $2))
-> Nested Loop (cost=103387.84..105368.08 rows=412 width=16) (actual time=222.471..251.192 rows=5197 loops=1)
-> Nested Loop (cost=103387.43..104972.47 rows=450 width=6) (actual time=210.267..223.042 rows=5907 loops=1)
-> Nested Loop (cost=103387.14..104838.61 rows=417 width=6) (actual time=210.262..212.017 rows=5196 loops=1)
-> HashAggregate (cost=103382.17..103382.23 rows=6 width=6) (actual time=210.248..210.279 rows=151 loops=1)
Group Key: d.id_peri
-> Sort (cost=103382.08..103382.09 rows=6 width=28) (actual time=210.219..210.224 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_1.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_1.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_1.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 4 (returns $7)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=64.366..64.366 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=64.228..64.404 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=60.316..60.316 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_1 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.008..42.729 rows=180402 loops=3)
-> Finalize GroupAggregate (cost=35023.10..35389.35 rows=6 width=28) (actual time=209.461..210.188 rows=151 loops=1)
Group Key: d.id_peri
Filter: (CASE WHEN (sum(a_1.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_1.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_1.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $5)
Rows Removed by Filter: 1099
-> Gather Merge (cost=35023.10..35319.46 rows=2540 width=32) (actual time=151.437..152.053 rows=1427 loops=1)
Workers Planned: 2
Params Evaluated: $7
Workers Launched: 2
-> Sort (cost=34023.08..34026.25 rows=1270 width=32) (actual time=82.776..82.795 rows=476 loops=3)
Sort Key: d.id_peri
Sort Method: quicksort Memory: 78kB
Worker 0: Sort Method: quicksort Memory: 74kB
Worker 1: Sort Method: quicksort Memory: 77kB
-> Partial HashAggregate (cost=33944.91..33957.61 rows=1270 width=32) (actual time=82.238..82.291 rows=476 loops=3)
Group Key: d.id_peri
-> Nested Loop (cost=2315.84..33914.57 rows=6067 width=32) (actual time=29.602..80.553 rows=11644 loops=3)
-> Hash Join (cost=2315.41..8902.84 rows=6582 width=30) (actual time=29.579..36.019 rows=13052 loops=3)
Hash Cond: (c_1.insee = b_1.depcomn)
-> Hash Join (cost=1051.22..7366.62 rows=6110 width=30) (actual time=21.631..24.851 rows=11650 loops=3)
Hash Cond: (c_1.id_peri = d.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_1 (cost=0.00..5697.74 rows=235274 width=13) (actual time=0.007..8.395 rows=188219 loops=3)
-> Hash (cost=1035.35..1035.35 rows=1270 width=24) (actual time=3.043..3.043 rows=1255 loops=3)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d (cost=0.00..1035.35 rows=1270 width=24) (actual time=2.708..2.886 rows=1255 loops=3)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Hash (cost=774.64..774.64 rows=39164 width=12) (actual time=7.797..7.797 rows=39164 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2195kB
-> Seq Scan on communes_passage b_1 (cost=0.00..774.64 rows=39164 width=12) (actual time=0.011..3.767 rows=39164 loops=3)
-> Index Scan using cc_emp_pkey on cc_emp a_1 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=39157)
Index Cond: ((codgeo = b_1.depcoma) AND (annee = $7))
-> Bitmap Heap Scan on assoc_commune_peri c (cost=4.97..242.03 rows=70 width=13) (actual time=0.006..0.009 rows=34 loops=151)
Recheck Cond: (id_peri = d.id_peri)
Heap Blocks: exact=182
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.95 rows=70 width=0) (actual time=0.005..0.005 rows=34 loops=151)
Index Cond: (id_peri = d.id_peri)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b (cost=0.29..0.31 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5196)
Index Cond: (depcomn = c.insee)
-> Index Scan using clap_pkey on clap a (cost=0.42..0.88 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=5907)
Index Cond: ((insee = b.depcoma) AND (annee = $0))
-> Aggregate (cost=243310.61..243310.62 rows=1 width=16) (actual time=292.327..292.327 rows=1 loops=1)
InitPlan 5 (returns $9)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=56.698..56.698 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=56.564..56.736 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=52.606..52.606 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_2 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.009..35.549 rows=180402 loops=3)
InitPlan 7 (returns $14)
-> Subquery Scan on epci_groupe_ref_1 (cost=68634.44..68634.45 rows=1 width=4) (actual time=56.676..56.676 rows=1 loops=1)
-> Sort (cost=68634.44..68634.44 rows=1 width=28) (actual time=56.674..56.675 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_13.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_13.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_13.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 6 (returns $11)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=56.176..56.176 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=56.040..56.210 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=52.379..52.379 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_3 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.006..35.163 rows=180402 loops=3)
-> GroupAggregate (cost=1.43..641.78 rows=1 width=28) (actual time=56.667..56.667 rows=1 loops=1)
Group Key: d_7.id_peri
-> Nested Loop (cost=1.43..641.35 rows=41 width=32) (actual time=56.223..56.656 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_7 (cost=0.29..8.31 rows=1 width=24) (actual time=0.009..0.010 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..632.63 rows=41 width=15) (actual time=56.213..56.640 rows=72 loops=1)
-> Nested Loop (cost=0.71..465.43 rows=44 width=13) (actual time=0.017..0.183 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_13 (cost=0.42..156.41 rows=41 width=13) (actual time=0.009..0.018 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_13 (cost=0.29..7.53 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_13.insee)
-> Index Scan using cc_emp_pkey on cc_emp a_13 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_13.depcoma) AND (annee = $11))
-> Nested Loop (cost=103387.85..106682.48 rows=414 width=8) (actual time=256.777..292.042 rows=5195 loops=1)
-> Nested Loop (cost=103387.43..104972.47 rows=450 width=6) (actual time=200.055..213.716 rows=5907 loops=1)
-> Nested Loop (cost=103387.14..104838.61 rows=417 width=6) (actual time=200.049..201.914 rows=5196 loops=1)
-> HashAggregate (cost=103382.17..103382.23 rows=6 width=6) (actual time=200.036..200.073 rows=151 loops=1)
Group Key: d_1.id_peri
-> Sort (cost=103382.08..103382.09 rows=6 width=28) (actual time=200.005..200.010 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_3.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_3.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_3.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 8 (returns $16)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=56.038..56.038 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=55.898..56.076 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=52.201..52.201 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_4 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.007..35.098 rows=180402 loops=3)
-> Finalize GroupAggregate (cost=35023.10..35389.35 rows=6 width=28) (actual time=199.241..199.979 rows=151 loops=1)
Group Key: d_1.id_peri
Filter: (CASE WHEN (sum(a_3.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_3.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_3.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $14)
Rows Removed by Filter: 1099
-> Gather Merge (cost=35023.10..35319.46 rows=2540 width=32) (actual time=142.549..143.176 rows=1427 loops=1)
Workers Planned: 2
Params Evaluated: $16
Workers Launched: 2
-> Sort (cost=34023.08..34026.25 rows=1270 width=32) (actual time=82.198..82.218 rows=476 loops=3)
Sort Key: d_1.id_peri
Sort Method: quicksort Memory: 79kB
Worker 0: Sort Method: quicksort Memory: 74kB
Worker 1: Sort Method: quicksort Memory: 76kB
-> Partial HashAggregate (cost=33944.91..33957.61 rows=1270 width=32) (actual time=81.659..81.713 rows=476 loops=3)
Group Key: d_1.id_peri
-> Nested Loop (cost=2315.84..33914.57 rows=6067 width=32) (actual time=29.468..79.998 rows=11644 loops=3)
-> Hash Join (cost=2315.41..8902.84 rows=6582 width=30) (actual time=29.445..35.844 rows=13052 loops=3)
Hash Cond: (c_3.insee = b_3.depcomn)
-> Hash Join (cost=1051.22..7366.62 rows=6110 width=30) (actual time=21.449..24.626 rows=11650 loops=3)
Hash Cond: (c_3.id_peri = d_1.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_3 (cost=0.00..5697.74 rows=235274 width=13) (actual time=0.008..8.164 rows=188219 loops=3)
-> Hash (cost=1035.35..1035.35 rows=1270 width=24) (actual time=3.095..3.095 rows=1255 loops=3)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d_1 (cost=0.00..1035.35 rows=1270 width=24) (actual time=2.785..2.941 rows=1255 loops=3)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Hash (cost=774.64..774.64 rows=39164 width=12) (actual time=7.843..7.843 rows=39164 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2195kB
-> Seq Scan on communes_passage b_3 (cost=0.00..774.64 rows=39164 width=12) (actual time=0.010..3.857 rows=39164 loops=3)
-> Index Scan using cc_emp_pkey on cc_emp a_3 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=39157)
Index Cond: ((codgeo = b_3.depcoma) AND (annee = $16))
-> Bitmap Heap Scan on assoc_commune_peri c_2 (cost=4.97..242.03 rows=70 width=13) (actual time=0.007..0.009 rows=34 loops=151)
Recheck Cond: (id_peri = d_1.id_peri)
Heap Blocks: exact=182
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.95 rows=70 width=0) (actual time=0.005..0.005 rows=34 loops=151)
Index Cond: (id_peri = d_1.id_peri)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_2 (cost=0.29..0.31 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5196)
Index Cond: (depcomn = c_2.insee)
-> Index Scan using cc_emp_pkey on cc_emp a_2 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=5907)
Index Cond: ((codgeo = b_2.depcoma) AND (annee = $9))
-> Nested Loop (cost=421987.61..421987.70 rows=1 width=100) (actual time=532.022..532.022 rows=1 loops=1)
-> Aggregate (cost=178676.99..178677.00 rows=1 width=32) (actual time=241.114..241.114 rows=1 loops=1)
InitPlan 9 (returns $17)
-> Aggregate (cost=4670.34..4670.35 rows=1 width=32) (actual time=10.986..10.986 rows=1 loops=1)
-> Seq Scan on clap clap_1 (cost=0.00..4491.07 rows=71707 width=5) (actual time=0.008..5.958 rows=71707 loops=1)
InitPlan 11 (returns $22)
-> Subquery Scan on epci_groupe_ref_2 (cost=68634.44..68634.45 rows=1 width=4) (actual time=57.425..57.425 rows=1 loops=1)
-> Sort (cost=68634.44..68634.44 rows=1 width=28) (actual time=57.424..57.424 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_14.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_14.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_14.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 10 (returns $19)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=56.935..56.935 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=56.831..56.975 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=53.112..53.112 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_5 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.006..35.579 rows=180402 loops=3)
-> GroupAggregate (cost=1.43..641.78 rows=1 width=28) (actual time=57.417..57.417 rows=1 loops=1)
Group Key: d_8.id_peri
-> Nested Loop (cost=1.43..641.35 rows=41 width=32) (actual time=56.982..57.405 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_8 (cost=0.29..8.31 rows=1 width=24) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..632.63 rows=41 width=15) (actual time=56.968..57.387 rows=72 loops=1)
-> Nested Loop (cost=0.71..465.43 rows=44 width=13) (actual time=0.016..0.183 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_14 (cost=0.42..156.41 rows=41 width=13) (actual time=0.008..0.018 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_14 (cost=0.29..7.53 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_14.insee)
-> Index Scan using cc_emp_pkey on cc_emp a_14 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_14.depcoma) AND (annee = $19))
-> Nested Loop (cost=103387.84..105368.08 rows=412 width=16) (actual time=211.090..240.749 rows=5197 loops=1)
-> Nested Loop (cost=103387.43..104972.47 rows=450 width=6) (actual time=200.089..213.207 rows=5907 loops=1)
-> Nested Loop (cost=103387.14..104838.61 rows=417 width=6) (actual time=200.085..201.845 rows=5196 loops=1)
-> HashAggregate (cost=103382.17..103382.23 rows=6 width=6) (actual time=200.072..200.117 rows=151 loops=1)
Group Key: d_2.id_peri
-> Sort (cost=103382.08..103382.09 rows=6 width=28) (actual time=200.041..200.046 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_5.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_5.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_5.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 12 (returns $24)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=56.505..56.505 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=56.403..56.542 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=52.747..52.747 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_6 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.008..35.229 rows=180402 loops=3)
-> Finalize GroupAggregate (cost=35023.10..35389.35 rows=6 width=28) (actual time=199.286..200.020 rows=151 loops=1)
Group Key: d_2.id_peri
Filter: (CASE WHEN (sum(a_5.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_5.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_5.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $22)
Rows Removed by Filter: 1099
-> Gather Merge (cost=35023.10..35319.46 rows=2540 width=32) (actual time=141.845..142.464 rows=1427 loops=1)
Workers Planned: 2
Params Evaluated: $24
Workers Launched: 2
-> Sort (cost=34023.08..34026.25 rows=1270 width=32) (actual time=81.170..81.188 rows=476 loops=3)
Sort Key: d_2.id_peri
Sort Method: quicksort Memory: 80kB
Worker 0: Sort Method: quicksort Memory: 73kB
Worker 1: Sort Method: quicksort Memory: 76kB
-> Partial HashAggregate (cost=33944.91..33957.61 rows=1270 width=32) (actual time=80.630..80.681 rows=476 loops=3)
Group Key: d_2.id_peri
-> Nested Loop (cost=2315.84..33914.57 rows=6067 width=32) (actual time=28.840..78.913 rows=11644 loops=3)
-> Hash Join (cost=2315.41..8902.84 rows=6582 width=30) (actual time=28.816..35.231 rows=13052 loops=3)
Hash Cond: (c_5.insee = b_5.depcomn)
-> Hash Join (cost=1051.22..7366.62 rows=6110 width=30) (actual time=21.030..24.223 rows=11650 loops=3)
Hash Cond: (c_5.id_peri = d_2.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_5 (cost=0.00..5697.74 rows=235274 width=13) (actual time=0.008..8.136 rows=188219 loops=3)
-> Hash (cost=1035.35..1035.35 rows=1270 width=24) (actual time=2.983..2.983 rows=1255 loops=3)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d_2 (cost=0.00..1035.35 rows=1270 width=24) (actual time=2.679..2.828 rows=1255 loops=3)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Hash (cost=774.64..774.64 rows=39164 width=12) (actual time=7.630..7.630 rows=39164 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2195kB
-> Seq Scan on communes_passage b_5 (cost=0.00..774.64 rows=39164 width=12) (actual time=0.010..3.642 rows=39164 loops=3)
-> Index Scan using cc_emp_pkey on cc_emp a_5 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=39157)
Index Cond: ((codgeo = b_5.depcoma) AND (annee = $24))
-> Bitmap Heap Scan on assoc_commune_peri c_4 (cost=4.97..242.03 rows=70 width=13) (actual time=0.006..0.008 rows=34 loops=151)
Recheck Cond: (id_peri = d_2.id_peri)
Heap Blocks: exact=182
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.95 rows=70 width=0) (actual time=0.004..0.004 rows=34 loops=151)
Index Cond: (id_peri = d_2.id_peri)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_4 (cost=0.29..0.31 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5196)
Index Cond: (depcomn = c_4.insee)
-> Index Scan using clap_pkey on clap a_4 (cost=0.42..0.88 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=5907)
Index Cond: ((insee = b_4.depcoma) AND (annee = $17))
-> Aggregate (cost=243310.61..243310.62 rows=1 width=16) (actual time=290.896..290.896 rows=1 loops=1)
InitPlan 13 (returns $26)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=57.229..57.229 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=57.127..57.267 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=53.327..53.327 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_7 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.008..35.854 rows=180402 loops=3)
InitPlan 15 (returns $31)
-> Subquery Scan on epci_groupe_ref_3 (cost=68634.44..68634.45 rows=1 width=4) (actual time=57.461..57.461 rows=1 loops=1)
-> Sort (cost=68634.44..68634.44 rows=1 width=28) (actual time=57.460..57.460 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_15.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_15.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_15.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 14 (returns $28)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=56.960..56.960 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=56.856..56.996 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=53.131..53.131 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_8 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.006..35.528 rows=180402 loops=3)
-> GroupAggregate (cost=1.43..641.78 rows=1 width=28) (actual time=57.452..57.452 rows=1 loops=1)
Group Key: d_9.id_peri
-> Nested Loop (cost=1.43..641.35 rows=41 width=32) (actual time=57.012..57.439 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_9 (cost=0.29..8.31 rows=1 width=24) (actual time=0.009..0.010 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..632.63 rows=41 width=15) (actual time=57.002..57.422 rows=72 loops=1)
-> Nested Loop (cost=0.71..465.43 rows=44 width=13) (actual time=0.018..0.188 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_15 (cost=0.42..156.41 rows=41 width=13) (actual time=0.010..0.019 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_15 (cost=0.29..7.53 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_15.insee)
-> Index Scan using cc_emp_pkey on cc_emp a_15 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_15.depcoma) AND (annee = $28))
-> Nested Loop (cost=103387.85..106682.48 rows=414 width=8) (actual time=257.004..290.610 rows=5195 loops=1)
-> Nested Loop (cost=103387.43..104972.47 rows=450 width=6) (actual time=199.759..212.947 rows=5907 loops=1)
-> Nested Loop (cost=103387.14..104838.61 rows=417 width=6) (actual time=199.754..201.477 rows=5196 loops=1)
-> HashAggregate (cost=103382.17..103382.23 rows=6 width=6) (actual time=199.742..199.777 rows=151 loops=1)
Group Key: d_3.id_peri
-> Sort (cost=103382.08..103382.09 rows=6 width=28) (actual time=199.709..199.714 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_7.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_7.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_7.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 16 (returns $33)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=55.652..55.652 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=55.548..55.688 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=51.800..51.800 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_9 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.010..34.973 rows=180402 loops=3)
-> Finalize GroupAggregate (cost=35023.10..35389.35 rows=6 width=28) (actual time=198.947..199.682 rows=151 loops=1)
Group Key: d_3.id_peri
Filter: (CASE WHEN (sum(a_7.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_7.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_7.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $31)
Rows Removed by Filter: 1099
-> Gather Merge (cost=35023.10..35319.46 rows=2540 width=32) (actual time=141.470..142.087 rows=1427 loops=1)
Workers Planned: 2
Params Evaluated: $33
Workers Launched: 2
-> Sort (cost=34023.08..34026.25 rows=1270 width=32) (actual time=81.581..81.601 rows=476 loops=3)
Sort Key: d_3.id_peri
Sort Method: quicksort Memory: 79kB
Worker 0: Sort Method: quicksort Memory: 76kB
Worker 1: Sort Method: quicksort Memory: 75kB
-> Partial HashAggregate (cost=33944.91..33957.61 rows=1270 width=32) (actual time=81.049..81.101 rows=476 loops=3)
Group Key: d_3.id_peri
-> Nested Loop (cost=2315.84..33914.57 rows=6067 width=32) (actual time=29.108..79.389 rows=11644 loops=3)
-> Hash Join (cost=2315.41..8902.84 rows=6582 width=30) (actual time=29.083..35.519 rows=13052 loops=3)
Hash Cond: (c_7.insee = b_7.depcomn)
-> Hash Join (cost=1051.22..7366.62 rows=6110 width=30) (actual time=21.116..24.349 rows=11650 loops=3)
Hash Cond: (c_7.id_peri = d_3.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_7 (cost=0.00..5697.74 rows=235274 width=13) (actual time=0.008..8.140 rows=188219 loops=3)
-> Hash (cost=1035.35..1035.35 rows=1270 width=24) (actual time=3.017..3.017 rows=1255 loops=3)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d_3 (cost=0.00..1035.35 rows=1270 width=24) (actual time=2.709..2.861 rows=1255 loops=3)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Hash (cost=774.64..774.64 rows=39164 width=12) (actual time=7.814..7.814 rows=39164 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2195kB
-> Seq Scan on communes_passage b_7 (cost=0.00..774.64 rows=39164 width=12) (actual time=0.011..3.657 rows=39164 loops=3)
-> Index Scan using cc_emp_pkey on cc_emp a_7 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=39157)
Index Cond: ((codgeo = b_7.depcoma) AND (annee = $33))
-> Bitmap Heap Scan on assoc_commune_peri c_6 (cost=4.97..242.03 rows=70 width=13) (actual time=0.006..0.008 rows=34 loops=151)
Recheck Cond: (id_peri = d_3.id_peri)
Heap Blocks: exact=182
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.95 rows=70 width=0) (actual time=0.005..0.005 rows=34 loops=151)
Index Cond: (id_peri = d_3.id_peri)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_6 (cost=0.29..0.31 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5196)
Index Cond: (depcomn = c_6.insee)
-> Index Scan using cc_emp_pkey on cc_emp a_6 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=5907)
Index Cond: ((codgeo = b_6.depcoma) AND (annee = $26))
-> Nested Loop (cost=421983.49..421983.54 rows=1 width=100) (actual time=527.544..527.545 rows=1 loops=1)
-> Aggregate (cost=178672.87..178672.88 rows=1 width=32) (actual time=236.302..236.302 rows=1 loops=1)
InitPlan 17 (returns $34)
-> Aggregate (cost=4670.34..4670.35 rows=1 width=32) (actual time=11.200..11.201 rows=1 loops=1)
-> Seq Scan on clap clap_2 (cost=0.00..4491.07 rows=71707 width=5) (actual time=0.003..6.128 rows=71707 loops=1)
InitPlan 19 (returns $39)
-> Subquery Scan on epci_groupe_ref_4 (cost=68634.44..68634.45 rows=1 width=4) (actual time=56.176..56.177 rows=1 loops=1)
-> Sort (cost=68634.44..68634.44 rows=1 width=28) (actual time=56.175..56.176 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_16.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_16.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_16.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 18 (returns $36)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=55.680..55.680 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=55.576..55.720 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=51.922..51.922 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_10 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.008..34.918 rows=180402 loops=3)
-> GroupAggregate (cost=1.43..641.78 rows=1 width=28) (actual time=56.168..56.168 rows=1 loops=1)
Group Key: d_10.id_peri
-> Nested Loop (cost=1.43..641.35 rows=41 width=32) (actual time=55.725..56.157 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_10 (cost=0.29..8.31 rows=1 width=24) (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..632.63 rows=41 width=15) (actual time=55.714..56.140 rows=72 loops=1)
-> Nested Loop (cost=0.71..465.43 rows=44 width=13) (actual time=0.016..0.189 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_16 (cost=0.42..156.41 rows=41 width=13) (actual time=0.008..0.017 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_16 (cost=0.29..7.53 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_16.insee)
-> Index Scan using cc_emp_pkey on cc_emp a_16 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_16.depcoma) AND (annee = $36))
-> Nested Loop (cost=103387.84..105368.08 rows=412 width=0) (actual time=209.008..236.124 rows=5197 loops=1)
-> Nested Loop (cost=103387.43..104972.47 rows=450 width=6) (actual time=197.785..210.459 rows=5907 loops=1)
-> Nested Loop (cost=103387.14..104838.61 rows=417 width=6) (actual time=197.779..199.408 rows=5196 loops=1)
-> HashAggregate (cost=103382.17..103382.23 rows=6 width=6) (actual time=197.766..197.793 rows=151 loops=1)
Group Key: d_4.id_peri
-> Sort (cost=103382.08..103382.09 rows=6 width=28) (actual time=197.733..197.738 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_9.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_9.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_9.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 20 (returns $41)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=55.874..55.874 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=55.774..55.911 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=52.035..52.035 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_11 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.007..34.981 rows=180402 loops=3)
-> Finalize GroupAggregate (cost=35023.10..35389.35 rows=6 width=28) (actual time=196.974..197.712 rows=151 loops=1)
Group Key: d_4.id_peri
Filter: (CASE WHEN (sum(a_9.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_9.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_9.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $39)
Rows Removed by Filter: 1099
-> Gather Merge (cost=35023.10..35319.46 rows=2540 width=32) (actual time=140.782..141.404 rows=1427 loops=1)
Workers Planned: 2
Params Evaluated: $41
Workers Launched: 2
-> Sort (cost=34023.08..34026.25 rows=1270 width=32) (actual time=80.658..80.677 rows=476 loops=3)
Sort Key: d_4.id_peri
Sort Method: quicksort Memory: 79kB
Worker 0: Sort Method: quicksort Memory: 76kB
Worker 1: Sort Method: quicksort Memory: 74kB
-> Partial HashAggregate (cost=33944.91..33957.61 rows=1270 width=32) (actual time=80.124..80.174 rows=476 loops=3)
Group Key: d_4.id_peri
-> Nested Loop (cost=2315.84..33914.57 rows=6067 width=32) (actual time=28.696..78.442 rows=11644 loops=3)
-> Hash Join (cost=2315.41..8902.84 rows=6582 width=30) (actual time=28.673..35.024 rows=13052 loops=3)
Hash Cond: (c_9.insee = b_9.depcomn)
-> Hash Join (cost=1051.22..7366.62 rows=6110 width=30) (actual time=20.857..24.037 rows=11650 loops=3)
Hash Cond: (c_9.id_peri = d_4.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_9 (cost=0.00..5697.74 rows=235274 width=13) (actual time=0.008..8.029 rows=188219 loops=3)
-> Hash (cost=1035.35..1035.35 rows=1270 width=24) (actual time=3.015..3.015 rows=1255 loops=3)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d_4 (cost=0.00..1035.35 rows=1270 width=24) (actual time=2.697..2.849 rows=1255 loops=3)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Hash (cost=774.64..774.64 rows=39164 width=12) (actual time=7.663..7.663 rows=39164 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2195kB
-> Seq Scan on communes_passage b_9 (cost=0.00..774.64 rows=39164 width=12) (actual time=0.011..3.657 rows=39164 loops=3)
-> Index Scan using cc_emp_pkey on cc_emp a_9 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=39157)
Index Cond: ((codgeo = b_9.depcoma) AND (annee = $41))
-> Bitmap Heap Scan on assoc_commune_peri c_8 (cost=4.97..242.03 rows=70 width=13) (actual time=0.006..0.008 rows=34 loops=151)
Recheck Cond: (id_peri = d_4.id_peri)
Heap Blocks: exact=182
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.95 rows=70 width=0) (actual time=0.004..0.004 rows=34 loops=151)
Index Cond: (id_peri = d_4.id_peri)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_8 (cost=0.29..0.31 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5196)
Index Cond: (depcomn = c_8.insee)
-> Index Only Scan using clap_pkey on clap a_8 (cost=0.42..0.88 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=5907)
Index Cond: ((insee = b_8.depcoma) AND (annee = $34))
Heap Fetches: 5197
-> Aggregate (cost=243310.61..243310.62 rows=1 width=16) (actual time=291.236..291.236 rows=1 loops=1)
InitPlan 21 (returns $43)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=56.602..56.602 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=56.506..56.639 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=52.644..52.644 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_12 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.008..35.319 rows=180402 loops=3)
InitPlan 23 (returns $48)
-> Subquery Scan on epci_groupe_ref_5 (cost=68634.44..68634.45 rows=1 width=4) (actual time=57.707..57.707 rows=1 loops=1)
-> Sort (cost=68634.44..68634.44 rows=1 width=28) (actual time=57.706..57.706 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_17.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_17.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_17.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 22 (returns $45)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=57.211..57.211 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=57.110..57.247 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=53.371..53.372 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_13 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.006..35.692 rows=180402 loops=3)
-> GroupAggregate (cost=1.43..641.78 rows=1 width=28) (actual time=57.695..57.695 rows=1 loops=1)
Group Key: d_11.id_peri
-> Nested Loop (cost=1.43..641.35 rows=41 width=32) (actual time=57.257..57.682 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_11 (cost=0.29..8.31 rows=1 width=24) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..632.63 rows=41 width=15) (actual time=57.246..57.665 rows=72 loops=1)
-> Nested Loop (cost=0.71..465.43 rows=44 width=13) (actual time=0.016..0.184 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_17 (cost=0.42..156.41 rows=41 width=13) (actual time=0.008..0.017 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_17 (cost=0.29..7.53 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_17.insee)
-> Index Scan using cc_emp_pkey on cc_emp a_17 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_17.depcoma) AND (annee = $45))
-> Nested Loop (cost=103387.85..106682.48 rows=414 width=8) (actual time=257.333..290.982 rows=5195 loops=1)
-> Nested Loop (cost=103387.43..104972.47 rows=450 width=6) (actual time=200.711..213.938 rows=5907 loops=1)
-> Nested Loop (cost=103387.14..104838.61 rows=417 width=6) (actual time=200.706..202.446 rows=5196 loops=1)
-> HashAggregate (cost=103382.17..103382.23 rows=6 width=6) (actual time=200.693..200.727 rows=151 loops=1)
Group Key: d_5.id_peri
-> Sort (cost=103382.08..103382.09 rows=6 width=28) (actual time=200.663..200.667 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_11.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_11.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_11.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 24 (returns $50)
-> Finalize Aggregate (cost=67992.64..67992.65 rows=1 width=32) (actual time=56.511..56.511 rows=1 loops=1)
-> Gather (cost=67992.43..67992.64 rows=2 width=32) (actual time=56.407..56.547 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=66992.43..66992.44 rows=1 width=32) (actual time=52.679..52.679 rows=1 loops=3)
-> Parallel Seq Scan on cc_emp cc_emp_14 (cost=0.00..66428.34 rows=225634 width=5) (actual time=0.010..35.338 rows=180402 loops=3)
-> Finalize GroupAggregate (cost=35023.10..35389.35 rows=6 width=28) (actual time=199.912..200.638 rows=151 loops=1)
Group Key: d_5.id_peri
Filter: (CASE WHEN (sum(a_11.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_11.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_11.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $48)
Rows Removed by Filter: 1099
-> Gather Merge (cost=35023.10..35319.46 rows=2540 width=32) (actual time=142.190..142.807 rows=1427 loops=1)
Workers Planned: 2
Params Evaluated: $50
Workers Launched: 2
-> Sort (cost=34023.08..34026.25 rows=1270 width=32) (actual time=81.432..81.451 rows=476 loops=3)
Sort Key: d_5.id_peri
Sort Method: quicksort Memory: 80kB
Worker 0: Sort Method: quicksort Memory: 78kB
Worker 1: Sort Method: quicksort Memory: 71kB
-> Partial HashAggregate (cost=33944.91..33957.61 rows=1270 width=32) (actual time=80.894..80.946 rows=476 loops=3)
Group Key: d_5.id_peri
-> Nested Loop (cost=2315.84..33914.57 rows=6067 width=32) (actual time=28.977..79.227 rows=11644 loops=3)
-> Hash Join (cost=2315.41..8902.84 rows=6582 width=30) (actual time=28.953..35.362 rows=13052 loops=3)
Hash Cond: (c_11.insee = b_11.depcomn)
-> Hash Join (cost=1051.22..7366.62 rows=6110 width=30) (actual time=21.179..24.394 rows=11650 loops=3)
Hash Cond: (c_11.id_peri = d_5.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_11 (cost=0.00..5697.74 rows=235274 width=13) (actual time=0.008..8.078 rows=188219 loops=3)
-> Hash (cost=1035.35..1035.35 rows=1270 width=24) (actual time=3.001..3.001 rows=1255 loops=3)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d_5 (cost=0.00..1035.35 rows=1270 width=24) (actual time=2.682..2.837 rows=1255 loops=3)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Hash (cost=774.64..774.64 rows=39164 width=12) (actual time=7.625..7.625 rows=39164 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2195kB
-> Seq Scan on communes_passage b_11 (cost=0.00..774.64 rows=39164 width=12) (actual time=0.011..3.653 rows=39164 loops=3)
-> Index Scan using cc_emp_pkey on cc_emp a_11 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=39157)
Index Cond: ((codgeo = b_11.depcoma) AND (annee = $50))
-> Bitmap Heap Scan on assoc_commune_peri c_10 (cost=4.97..242.03 rows=70 width=13) (actual time=0.006..0.008 rows=34 loops=151)
Recheck Cond: (id_peri = d_5.id_peri)
Heap Blocks: exact=182
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.95 rows=70 width=0) (actual time=0.005..0.005 rows=34 loops=151)
Index Cond: (id_peri = d_5.id_peri)
-> Index Scan using communes_passage_depcomn_idx on communes_passage b_10 (cost=0.29..0.31 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5196)
Index Cond: (depcomn = c_10.insee)
-> Index Scan using cc_emp_pkey on cc_emp a_10 (cost=0.42..3.80 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=5907)
Index Cond: ((codgeo = b_10.depcoma) AND (annee = $43))
Planning Time: 12.646 ms
Execution Time: 1607.139 ms- et avec Postgres 14 :
PG14_MASTER2022
HashAggregate (cost=514424.12..514424.15 rows=3 width=100) (actual time=6258.408..6261.373 rows=3 loops=1)
Group Key: ('Groupe de réf.'::text), ('Salariés privé'::text), (1), (((((((sum(a.etppres)) + (sum(a.etpnpres))) - ((sum(a.etpprespub)) + (sum(a.etpnprespub)))))::numeric / (((((sum(a.etppres)) + (sum(a.etpnpres))) - ((sum(a.etpprespub)) + (sum(a.etpnprespub)))) + ((sum(a.etpprespub)) + (sum(a.etpnprespub)))))::numeric) * ((sum(a_2.pxx_emplt_sal)))::numeric))
Batches: 1 Memory Usage: 32kB
-> Append (cost=171534.46..514424.09 rows=3 width=100) (actual time=4065.513..6261.358 rows=3 loops=1)
-> Nested Loop (cost=171534.46..171534.54 rows=1 width=100) (actual time=4065.513..4066.524 rows=1 loops=1)
-> Aggregate (cost=78866.45..78866.46 rows=1 width=32) (actual time=3476.945..3477.450 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Aggregate (cost=2371.29..2371.30 rows=1 width=32) (actual time=8.357..8.358 rows=1 loops=1)
-> Index Only Scan using clap_pkey on clap (cost=0.42..2192.02 rows=71707 width=5) (actual time=0.010..4.183 rows=71707 loops=1)
Heap Fetches: 0
InitPlan 3 (returns $5)
-> Subquery Scan on epci_groupe_ref (cost=15366.78..15366.80 rows=1 width=4) (actual time=85.835..85.900 rows=1 loops=1)
-> Sort (cost=15366.78..15366.79 rows=1 width=28) (actual time=85.834..85.898 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_12.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_12.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_12.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 2 (returns $2)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.422..85.484 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=74.278..85.474 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.674..68.675 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.027..12.135 rows=180402 loops=3)
Heap Fetches: 0
-> GroupAggregate (cost=1.43..497.32 rows=1 width=28) (actual time=85.826..85.829 rows=1 loops=1)
Group Key: d_6.id_peri
-> Nested Loop (cost=1.43..497.10 rows=41 width=32) (actual time=85.478..85.819 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_6 (cost=0.29..8.31 rows=1 width=24) (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..488.38 rows=41 width=15) (actual time=85.466..85.802 rows=72 loops=1)
-> Nested Loop (cost=0.71..320.85 rows=45 width=13) (actual time=0.015..0.144 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_12 (cost=0.42..155.83 rows=41 width=13) (actual time=0.008..0.018 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Only Scan using communes_passage_pkey on communes_passage b_12 (cost=0.29..4.01 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_12.insee)
Heap Fetches: 0
-> Index Scan using cc_emp_pkey on cc_emp a_12 (cost=0.42..3.72 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_12.depcoma) AND (annee = $2))
-> Nested Loop (cost=59180.21..61124.19 rows=416 width=16) (actual time=3454.762..3477.093 rows=5197 loops=1)
-> Nested Loop (cost=59179.79..60736.78 rows=455 width=6) (actual time=3446.388..3456.077 rows=5907 loops=1)
-> Nested Loop (cost=59179.50..60603.93 rows=417 width=6) (actual time=3446.374..3447.925 rows=5196 loops=1)
-> HashAggregate (cost=59174.54..59174.60 rows=6 width=6) (actual time=3446.355..3446.810 rows=151 loops=1)
Group Key: d.id_peri
Batches: 1 Memory Usage: 48kB
-> Sort (cost=59174.45..59174.47 rows=6 width=28) (actual time=3446.319..3446.759 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_1.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_1.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_1.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 4 (returns $7)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=3055.356..3055.389 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=3055.350..3055.385 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=1090.997..1090.997 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_1 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.043..15.110 rows=180402 loops=3)
Heap Fetches: 0
-> Finalize GroupAggregate (cost=44128.90..44304.92 rows=6 width=28) (actual time=3445.607..3446.684 rows=151 loops=1)
Group Key: d.id_peri
Filter: (CASE WHEN (sum(a_1.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_1.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_1.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $5)
Rows Removed by Filter: 1099
-> Gather Merge (cost=44128.90..44273.45 rows=1257 width=32) (actual time=3359.754..3360.578 rows=2434 loops=1)
Workers Planned: 1
Params Evaluated: $7
Workers Launched: 1
-> Sort (cost=43128.89..43132.03 rows=1257 width=32) (actual time=293.060..293.134 rows=1217 loops=2)
Sort Key: d.id_peri
Sort Method: quicksort Memory: 183kB
Worker 0: Sort Method: quicksort Memory: 179kB
-> Partial HashAggregate (cost=43051.61..43064.18 rows=1257 width=32) (actual time=292.010..292.102 rows=1217 loops=2)
Group Key: d.id_peri
Batches: 1 Memory Usage: 321kB
Worker 0: Batches: 1 Memory Usage: 321kB
-> Nested Loop (cost=7126.76..43008.67 rows=8589 width=32) (actual time=238.206..290.077 rows=17466 loops=2)
-> Parallel Hash Join (cost=7126.33..8296.27 rows=9324 width=30) (actual time=238.185..242.507 rows=19578 loops=2)
Hash Cond: (b_1.depcomn = c_1.insee)
-> Parallel Index Only Scan using communes_passage_pkey on communes_passage b_1 (cost=0.29..1044.98 rows=23038 width=12) (actual time=0.020..1.346 rows=19582 loops=2)
Heap Fetches: 108
-> Parallel Hash (cost=7050.45..7050.45 rows=6047 width=30) (actual time=238.101..238.103 rows=17476 loops=2)
Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3552kB
-> Hash Join (cost=992.06..7050.45 rows=6047 width=30) (actual time=135.318..138.193 rows=17476 loops=2)
Hash Cond: (c_1.id_peri = d.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_1 (cost=0.00..5440.74 rows=235274 width=13) (actual time=0.011..9.868 rows=282329 loops=2)
-> Hash (cost=976.35..976.35 rows=1257 width=24) (actual time=112.959..112.960 rows=1255 loops=2)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d (cost=0.00..976.35 rows=1257 width=24) (actual time=112.719..112.828 rows=1255 loops=2)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Index Scan using cc_emp_pkey on cc_emp a_1 (cost=0.42..3.72 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=39157)
Index Cond: ((codgeo = b_1.depcoma) AND (annee = $7))
-> Bitmap Heap Scan on assoc_commune_peri c (cost=4.96..237.53 rows=69 width=13) (actual time=0.004..0.005 rows=34 loops=151)
Recheck Cond: (id_peri = d.id_peri)
Heap Blocks: exact=178
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.94 rows=69 width=0) (actual time=0.003..0.003 rows=34 loops=151)
Index Cond: (id_peri = d.id_peri)
-> Index Only Scan using communes_passage_pkey on communes_passage b (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5196)
Index Cond: (depcomn = c.insee)
Heap Fetches: 23
-> Index Scan using clap_pkey on clap a (cost=0.42..0.85 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=5907)
Index Cond: ((insee = b.depcoma) AND (annee = $0))
-> Aggregate (cost=92668.00..92668.01 rows=1 width=16) (actual time=588.556..589.061 rows=1 loops=1)
InitPlan 5 (returns $9)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.387..85.421 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=73.460..85.410 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.319..68.320 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_2 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.024..11.780 rows=180402 loops=3)
Heap Fetches: 0
InitPlan 7 (returns $14)
-> Subquery Scan on epci_groupe_ref_1 (cost=15366.78..15366.80 rows=1 width=4) (actual time=85.829..85.862 rows=1 loops=1)
-> Sort (cost=15366.78..15366.79 rows=1 width=28) (actual time=85.828..85.860 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_13.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_13.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_13.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 6 (returns $11)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.412..85.441 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=73.490..85.430 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.370..68.371 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_3 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.027..11.907 rows=180402 loops=3)
Heap Fetches: 0
-> GroupAggregate (cost=1.43..497.32 rows=1 width=28) (actual time=85.821..85.823 rows=1 loops=1)
Group Key: d_7.id_peri
-> Nested Loop (cost=1.43..497.10 rows=41 width=32) (actual time=85.471..85.813 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_7 (cost=0.29..8.31 rows=1 width=24) (actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..488.38 rows=41 width=15) (actual time=85.456..85.793 rows=72 loops=1)
-> Nested Loop (cost=0.71..320.85 rows=45 width=13) (actual time=0.018..0.146 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_13 (cost=0.42..155.83 rows=41 width=13) (actual time=0.006..0.015 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Only Scan using communes_passage_pkey on communes_passage b_13 (cost=0.29..4.01 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_13.insee)
Heap Fetches: 0
-> Index Scan using cc_emp_pkey on cc_emp a_13 (cost=0.42..3.72 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_13.depcoma) AND (annee = $11))
-> Nested Loop (cost=59180.22..62430.71 rows=419 width=8) (actual time=562.000..588.670 rows=5195 loops=1)
-> Nested Loop (cost=59179.79..60736.78 rows=455 width=6) (actual time=476.587..486.871 rows=5907 loops=1)
-> Nested Loop (cost=59179.50..60603.93 rows=417 width=6) (actual time=476.578..478.219 rows=5196 loops=1)
-> HashAggregate (cost=59174.54..59174.60 rows=6 width=6) (actual time=476.558..477.018 rows=151 loops=1)
Group Key: d_1.id_peri
Batches: 1 Memory Usage: 48kB
-> Sort (cost=59174.45..59174.47 rows=6 width=28) (actual time=476.524..476.963 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_3.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_3.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_3.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 8 (returns $16)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.503..85.536 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=74.401..85.524 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.724..68.725 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_4 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.043..11.918 rows=180402 loops=3)
Heap Fetches: 0
-> Finalize GroupAggregate (cost=44128.90..44304.92 rows=6 width=28) (actual time=475.890..476.899 rows=151 loops=1)
Group Key: d_1.id_peri
Filter: (CASE WHEN (sum(a_3.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_3.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_3.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $14)
Rows Removed by Filter: 1099
-> Gather Merge (cost=44128.90..44273.45 rows=1257 width=32) (actual time=390.039..390.824 rows=2426 loops=1)
Workers Planned: 1
Params Evaluated: $16
Workers Launched: 1
-> Sort (cost=43128.89..43132.03 rows=1257 width=32) (actual time=293.226..293.295 rows=1213 loops=2)
Sort Key: d_1.id_peri
Sort Method: quicksort Memory: 179kB
Worker 0: Sort Method: quicksort Memory: 182kB
-> Partial HashAggregate (cost=43051.61..43064.18 rows=1257 width=32) (actual time=292.161..292.250 rows=1213 loops=2)
Group Key: d_1.id_peri
Batches: 1 Memory Usage: 321kB
Worker 0: Batches: 1 Memory Usage: 321kB
-> Nested Loop (cost=7126.76..43008.67 rows=8589 width=32) (actual time=239.216..290.179 rows=17466 loops=2)
-> Parallel Hash Join (cost=7126.33..8296.27 rows=9324 width=30) (actual time=239.195..243.357 rows=19578 loops=2)
Hash Cond: (b_3.depcomn = c_3.insee)
-> Parallel Index Only Scan using communes_passage_pkey on communes_passage b_3 (cost=0.29..1044.98 rows=23038 width=12) (actual time=0.018..1.326 rows=19582 loops=2)
Heap Fetches: 108
-> Parallel Hash (cost=7050.45..7050.45 rows=6047 width=30) (actual time=239.112..239.114 rows=17476 loops=2)
Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3552kB
-> Hash Join (cost=992.06..7050.45 rows=6047 width=30) (actual time=134.008..136.757 rows=17476 loops=2)
Hash Cond: (c_3.id_peri = d_1.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_3 (cost=0.00..5440.74 rows=235274 width=13) (actual time=0.005..9.276 rows=282329 loops=2)
-> Hash (cost=976.35..976.35 rows=1257 width=24) (actual time=113.406..113.407 rows=1255 loops=2)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d_1 (cost=0.00..976.35 rows=1257 width=24) (actual time=113.178..113.276 rows=1255 loops=2)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Index Scan using cc_emp_pkey on cc_emp a_3 (cost=0.42..3.72 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=39157)
Index Cond: ((codgeo = b_3.depcoma) AND (annee = $16))
-> Bitmap Heap Scan on assoc_commune_peri c_2 (cost=4.96..237.53 rows=69 width=13) (actual time=0.004..0.006 rows=34 loops=151)
Recheck Cond: (id_peri = d_1.id_peri)
Heap Blocks: exact=178
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.94 rows=69 width=0) (actual time=0.003..0.003 rows=34 loops=151)
Index Cond: (id_peri = d_1.id_peri)
-> Index Only Scan using communes_passage_pkey on communes_passage b_2 (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5196)
Index Cond: (depcomn = c_2.insee)
Heap Fetches: 23
-> Index Scan using cc_emp_pkey on cc_emp a_2 (cost=0.42..3.72 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=5907)
Index Cond: ((codgeo = b_2.depcoma) AND (annee = $9))
-> Nested Loop (cost=171534.46..171534.54 rows=1 width=100) (actual time=1097.312..1098.295 rows=1 loops=1)
-> Aggregate (cost=78866.45..78866.46 rows=1 width=32) (actual time=507.596..508.070 rows=1 loops=1)
InitPlan 9 (returns $17)
-> Aggregate (cost=2371.29..2371.30 rows=1 width=32) (actual time=8.366..8.367 rows=1 loops=1)
-> Index Only Scan using clap_pkey on clap clap_1 (cost=0.42..2192.02 rows=71707 width=5) (actual time=0.012..4.169 rows=71707 loops=1)
Heap Fetches: 0
InitPlan 11 (returns $22)
-> Subquery Scan on epci_groupe_ref_2 (cost=15366.78..15366.80 rows=1 width=4) (actual time=86.070..86.105 rows=1 loops=1)
-> Sort (cost=15366.78..15366.79 rows=1 width=28) (actual time=86.068..86.103 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_14.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_14.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_14.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 10 (returns $19)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.631..85.663 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=74.420..85.651 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.844..68.845 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_5 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.027..12.176 rows=180402 loops=3)
Heap Fetches: 0
-> GroupAggregate (cost=1.43..497.32 rows=1 width=28) (actual time=86.061..86.064 rows=1 loops=1)
Group Key: d_8.id_peri
-> Nested Loop (cost=1.43..497.10 rows=41 width=32) (actual time=85.679..86.052 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_8 (cost=0.29..8.31 rows=1 width=24) (actual time=0.008..0.009 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..488.38 rows=41 width=15) (actual time=85.670..86.036 rows=72 loops=1)
-> Nested Loop (cost=0.71..320.85 rows=45 width=13) (actual time=0.014..0.155 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_14 (cost=0.42..155.83 rows=41 width=13) (actual time=0.007..0.017 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Only Scan using communes_passage_pkey on communes_passage b_14 (cost=0.29..4.01 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_14.insee)
Heap Fetches: 0
-> Index Scan using cc_emp_pkey on cc_emp a_14 (cost=0.42..3.72 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_14.depcoma) AND (annee = $19))
-> Nested Loop (cost=59180.21..61124.19 rows=416 width=16) (actual time=485.571..507.743 rows=5197 loops=1)
-> Nested Loop (cost=59179.79..60736.78 rows=455 width=6) (actual time=477.188..486.946 rows=5907 loops=1)
-> Nested Loop (cost=59179.50..60603.93 rows=417 width=6) (actual time=477.178..478.723 rows=5196 loops=1)
-> HashAggregate (cost=59174.54..59174.60 rows=6 width=6) (actual time=477.158..477.611 rows=151 loops=1)
Group Key: d_2.id_peri
Batches: 1 Memory Usage: 48kB
-> Sort (cost=59174.45..59174.47 rows=6 width=28) (actual time=477.124..477.562 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_5.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_5.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_5.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 12 (returns $24)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.275..85.311 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=74.692..85.297 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.784..68.784 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_6 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.021..11.976 rows=180402 loops=3)
Heap Fetches: 0
-> Finalize GroupAggregate (cost=44128.90..44304.92 rows=6 width=28) (actual time=476.435..477.490 rows=151 loops=1)
Group Key: d_2.id_peri
Filter: (CASE WHEN (sum(a_5.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_5.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_5.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $22)
Rows Removed by Filter: 1099
-> Gather Merge (cost=44128.90..44273.45 rows=1257 width=32) (actual time=390.340..391.150 rows=2434 loops=1)
Workers Planned: 1
Params Evaluated: $24
Workers Launched: 1
-> Sort (cost=43128.89..43132.03 rows=1257 width=32) (actual time=293.773..293.845 rows=1217 loops=2)
Sort Key: d_2.id_peri
Sort Method: quicksort Memory: 180kB
Worker 0: Sort Method: quicksort Memory: 182kB
-> Partial HashAggregate (cost=43051.61..43064.18 rows=1257 width=32) (actual time=292.719..292.807 rows=1217 loops=2)
Group Key: d_2.id_peri
Batches: 1 Memory Usage: 321kB
Worker 0: Batches: 1 Memory Usage: 321kB
-> Nested Loop (cost=7126.76..43008.67 rows=8589 width=32) (actual time=239.436..290.755 rows=17466 loops=2)
-> Parallel Hash Join (cost=7126.33..8296.27 rows=9324 width=30) (actual time=239.418..243.610 rows=19578 loops=2)
Hash Cond: (b_5.depcomn = c_5.insee)
-> Parallel Index Only Scan using communes_passage_pkey on communes_passage b_5 (cost=0.29..1044.98 rows=23038 width=12) (actual time=0.023..1.333 rows=19582 loops=2)
Heap Fetches: 108
-> Parallel Hash (cost=7050.45..7050.45 rows=6047 width=30) (actual time=239.369..239.370 rows=17476 loops=2)
Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3552kB
-> Hash Join (cost=992.06..7050.45 rows=6047 width=30) (actual time=134.240..137.078 rows=17476 loops=2)
Hash Cond: (c_5.id_peri = d_2.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_5 (cost=0.00..5440.74 rows=235274 width=13) (actual time=0.007..9.374 rows=282329 loops=2)
-> Hash (cost=976.35..976.35 rows=1257 width=24) (actual time=113.440..113.441 rows=1255 loops=2)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d_2 (cost=0.00..976.35 rows=1257 width=24) (actual time=113.210..113.308 rows=1255 loops=2)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Index Scan using cc_emp_pkey on cc_emp a_5 (cost=0.42..3.72 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=39157)
Index Cond: ((codgeo = b_5.depcoma) AND (annee = $24))
-> Bitmap Heap Scan on assoc_commune_peri c_4 (cost=4.96..237.53 rows=69 width=13) (actual time=0.004..0.005 rows=34 loops=151)
Recheck Cond: (id_peri = d_2.id_peri)
Heap Blocks: exact=178
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.94 rows=69 width=0) (actual time=0.003..0.003 rows=34 loops=151)
Index Cond: (id_peri = d_2.id_peri)
-> Index Only Scan using communes_passage_pkey on communes_passage b_4 (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5196)
Index Cond: (depcomn = c_4.insee)
Heap Fetches: 23
-> Index Scan using clap_pkey on clap a_4 (cost=0.42..0.85 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=5907)
Index Cond: ((insee = b_4.depcoma) AND (annee = $17))
-> Aggregate (cost=92668.00..92668.01 rows=1 width=16) (actual time=589.704..590.212 rows=1 loops=1)
InitPlan 13 (returns $26)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.311..85.344 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=75.696..85.334 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.926..68.927 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_7 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.029..12.163 rows=180402 loops=3)
Heap Fetches: 0
InitPlan 15 (returns $31)
-> Subquery Scan on epci_groupe_ref_3 (cost=15366.78..15366.80 rows=1 width=4) (actual time=85.761..85.797 rows=1 loops=1)
-> Sort (cost=15366.78..15366.79 rows=1 width=28) (actual time=85.760..85.795 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_15.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_15.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_15.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 14 (returns $28)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.344..85.376 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=73.722..85.366 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.434..68.435 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_8 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.024..12.031 rows=180402 loops=3)
Heap Fetches: 0
-> GroupAggregate (cost=1.43..497.32 rows=1 width=28) (actual time=85.754..85.756 rows=1 loops=1)
Group Key: d_9.id_peri
-> Nested Loop (cost=1.43..497.10 rows=41 width=32) (actual time=85.402..85.746 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_9 (cost=0.29..8.31 rows=1 width=24) (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..488.38 rows=41 width=15) (actual time=85.390..85.727 rows=72 loops=1)
-> Nested Loop (cost=0.71..320.85 rows=45 width=13) (actual time=0.016..0.146 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_15 (cost=0.42..155.83 rows=41 width=13) (actual time=0.006..0.015 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Only Scan using communes_passage_pkey on communes_passage b_15 (cost=0.29..4.01 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_15.insee)
Heap Fetches: 0
-> Index Scan using cc_emp_pkey on cc_emp a_15 (cost=0.42..3.72 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_15.depcoma) AND (annee = $28))
-> Nested Loop (cost=59180.22..62430.71 rows=419 width=8) (actual time=563.142..589.832 rows=5195 loops=1)
-> Nested Loop (cost=59179.79..60736.78 rows=455 width=6) (actual time=477.797..488.022 rows=5907 loops=1)
-> Nested Loop (cost=59179.50..60603.93 rows=417 width=6) (actual time=477.788..479.450 rows=5196 loops=1)
-> HashAggregate (cost=59174.54..59174.60 rows=6 width=6) (actual time=477.768..478.228 rows=151 loops=1)
Group Key: d_3.id_peri
Batches: 1 Memory Usage: 48kB
-> Sort (cost=59174.45..59174.47 rows=6 width=28) (actual time=477.730..478.175 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_7.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_7.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_7.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 16 (returns $33)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.267..85.300 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=74.885..85.288 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.753..68.753 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_9 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.028..12.155 rows=180402 loops=3)
Heap Fetches: 0
-> Finalize GroupAggregate (cost=44128.90..44304.92 rows=6 width=28) (actual time=477.063..478.104 rows=151 loops=1)
Group Key: d_3.id_peri
Filter: (CASE WHEN (sum(a_7.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_7.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_7.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $31)
Rows Removed by Filter: 1099
-> Gather Merge (cost=44128.90..44273.45 rows=1257 width=32) (actual time=391.283..392.092 rows=2434 loops=1)
Workers Planned: 1
Params Evaluated: $33
Workers Launched: 1
-> Sort (cost=43128.89..43132.03 rows=1257 width=32) (actual time=294.822..294.901 rows=1217 loops=2)
Sort Key: d_3.id_peri
Sort Method: quicksort Memory: 179kB
Worker 0: Sort Method: quicksort Memory: 183kB
-> Partial HashAggregate (cost=43051.61..43064.18 rows=1257 width=32) (actual time=293.765..293.856 rows=1217 loops=2)
Group Key: d_3.id_peri
Batches: 1 Memory Usage: 321kB
Worker 0: Batches: 1 Memory Usage: 321kB
-> Nested Loop (cost=7126.76..43008.67 rows=8589 width=32) (actual time=239.815..291.692 rows=17466 loops=2)
-> Parallel Hash Join (cost=7126.33..8296.27 rows=9324 width=30) (actual time=239.794..243.988 rows=19578 loops=2)
Hash Cond: (b_7.depcomn = c_7.insee)
-> Parallel Index Only Scan using communes_passage_pkey on communes_passage b_7 (cost=0.29..1044.98 rows=23038 width=12) (actual time=0.017..1.338 rows=19582 loops=2)
Heap Fetches: 108
-> Parallel Hash (cost=7050.45..7050.45 rows=6047 width=30) (actual time=239.725..239.727 rows=17476 loops=2)
Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3552kB
-> Hash Join (cost=992.06..7050.45 rows=6047 width=30) (actual time=134.612..137.421 rows=17476 loops=2)
Hash Cond: (c_7.id_peri = d_3.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_7 (cost=0.00..5440.74 rows=235274 width=13) (actual time=0.009..9.190 rows=282329 loops=2)
-> Hash (cost=976.35..976.35 rows=1257 width=24) (actual time=113.668..113.668 rows=1255 loops=2)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d_3 (cost=0.00..976.35 rows=1257 width=24) (actual time=113.436..113.536 rows=1255 loops=2)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Index Scan using cc_emp_pkey on cc_emp a_7 (cost=0.42..3.72 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=39157)
Index Cond: ((codgeo = b_7.depcoma) AND (annee = $33))
-> Bitmap Heap Scan on assoc_commune_peri c_6 (cost=4.96..237.53 rows=69 width=13) (actual time=0.005..0.006 rows=34 loops=151)
Recheck Cond: (id_peri = d_3.id_peri)
Heap Blocks: exact=178
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.94 rows=69 width=0) (actual time=0.003..0.003 rows=34 loops=151)
Index Cond: (id_peri = d_3.id_peri)
-> Index Only Scan using communes_passage_pkey on communes_passage b_6 (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5196)
Index Cond: (depcomn = c_6.insee)
Heap Fetches: 23
-> Index Scan using cc_emp_pkey on cc_emp a_6 (cost=0.42..3.72 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=5907)
Index Cond: ((codgeo = b_6.depcoma) AND (annee = $26))
-> Nested Loop (cost=171354.91..171354.97 rows=1 width=100) (actual time=1095.564..1096.534 rows=1 loops=1)
-> Aggregate (cost=78686.91..78686.92 rows=1 width=32) (actual time=506.716..507.185 rows=1 loops=1)
InitPlan 17 (returns $34)
-> Aggregate (cost=2371.29..2371.30 rows=1 width=32) (actual time=8.250..8.250 rows=1 loops=1)
-> Index Only Scan using clap_pkey on clap clap_2 (cost=0.42..2192.02 rows=71707 width=5) (actual time=0.011..4.066 rows=71707 loops=1)
Heap Fetches: 0
InitPlan 19 (returns $39)
-> Subquery Scan on epci_groupe_ref_4 (cost=15366.78..15366.80 rows=1 width=4) (actual time=86.077..86.110 rows=1 loops=1)
-> Sort (cost=15366.78..15366.79 rows=1 width=28) (actual time=86.076..86.108 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_16.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_16.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_16.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 18 (returns $36)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.650..85.680 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=74.387..85.670 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.763..68.764 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_10 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.025..12.108 rows=180402 loops=3)
Heap Fetches: 0
-> GroupAggregate (cost=1.43..497.32 rows=1 width=28) (actual time=86.065..86.067 rows=1 loops=1)
Group Key: d_10.id_peri
-> Nested Loop (cost=1.43..497.10 rows=41 width=32) (actual time=85.714..86.057 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_10 (cost=0.29..8.31 rows=1 width=24) (actual time=0.009..0.011 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..488.38 rows=41 width=15) (actual time=85.703..86.040 rows=72 loops=1)
-> Nested Loop (cost=0.71..320.85 rows=45 width=13) (actual time=0.022..0.150 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_16 (cost=0.42..155.83 rows=41 width=13) (actual time=0.013..0.022 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Only Scan using communes_passage_pkey on communes_passage b_16 (cost=0.29..4.01 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_16.insee)
Heap Fetches: 0
-> Index Scan using cc_emp_pkey on cc_emp a_16 (cost=0.42..3.72 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_16.depcoma) AND (annee = $36))
-> Nested Loop (cost=59180.21..60948.81 rows=416 width=0) (actual time=487.001..506.923 rows=5197 loops=1)
-> Nested Loop (cost=59179.79..60736.78 rows=455 width=6) (actual time=478.734..488.273 rows=5907 loops=1)
-> Nested Loop (cost=59179.50..60603.93 rows=417 width=6) (actual time=478.723..480.246 rows=5196 loops=1)
-> HashAggregate (cost=59174.54..59174.60 rows=6 width=6) (actual time=478.702..479.151 rows=151 loops=1)
Group Key: d_4.id_peri
Batches: 1 Memory Usage: 48kB
-> Sort (cost=59174.45..59174.47 rows=6 width=28) (actual time=478.671..479.107 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_9.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_9.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_9.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 20 (returns $41)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.158..85.191 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=74.392..85.180 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.503..68.504 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_11 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.031..11.912 rows=180402 loops=3)
Heap Fetches: 0
-> Finalize GroupAggregate (cost=44128.90..44304.92 rows=6 width=28) (actual time=478.024..479.034 rows=151 loops=1)
Group Key: d_4.id_peri
Filter: (CASE WHEN (sum(a_9.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_9.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_9.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $39)
Rows Removed by Filter: 1099
-> Gather Merge (cost=44128.90..44273.45 rows=1257 width=32) (actual time=391.929..392.712 rows=2434 loops=1)
Workers Planned: 1
Params Evaluated: $41
Workers Launched: 1
-> Sort (cost=43128.89..43132.03 rows=1257 width=32) (actual time=295.290..295.361 rows=1217 loops=2)
Sort Key: d_4.id_peri
Sort Method: quicksort Memory: 183kB
Worker 0: Sort Method: quicksort Memory: 179kB
-> Partial HashAggregate (cost=43051.61..43064.18 rows=1257 width=32) (actual time=294.227..294.319 rows=1217 loops=2)
Group Key: d_4.id_peri
Batches: 1 Memory Usage: 321kB
Worker 0: Batches: 1 Memory Usage: 321kB
-> Nested Loop (cost=7126.76..43008.67 rows=8589 width=32) (actual time=239.644..292.165 rows=17466 loops=2)
-> Parallel Hash Join (cost=7126.33..8296.27 rows=9324 width=30) (actual time=239.620..244.021 rows=19578 loops=2)
Hash Cond: (b_9.depcomn = c_9.insee)
-> Parallel Index Only Scan using communes_passage_pkey on communes_passage b_9 (cost=0.29..1044.98 rows=23038 width=12) (actual time=0.018..1.340 rows=19582 loops=2)
Heap Fetches: 108
-> Parallel Hash (cost=7050.45..7050.45 rows=6047 width=30) (actual time=239.533..239.535 rows=17476 loops=2)
Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3552kB
-> Hash Join (cost=992.06..7050.45 rows=6047 width=30) (actual time=134.663..137.485 rows=17476 loops=2)
Hash Cond: (c_9.id_peri = d_4.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_9 (cost=0.00..5440.74 rows=235274 width=13) (actual time=0.007..9.446 rows=282329 loops=2)
-> Hash (cost=976.35..976.35 rows=1257 width=24) (actual time=113.572..113.572 rows=1255 loops=2)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d_4 (cost=0.00..976.35 rows=1257 width=24) (actual time=113.339..113.438 rows=1255 loops=2)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Index Scan using cc_emp_pkey on cc_emp a_9 (cost=0.42..3.72 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=39157)
Index Cond: ((codgeo = b_9.depcoma) AND (annee = $41))
-> Bitmap Heap Scan on assoc_commune_peri c_8 (cost=4.96..237.53 rows=69 width=13) (actual time=0.004..0.005 rows=34 loops=151)
Recheck Cond: (id_peri = d_4.id_peri)
Heap Blocks: exact=178
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.94 rows=69 width=0) (actual time=0.003..0.003 rows=34 loops=151)
Index Cond: (id_peri = d_4.id_peri)
-> Index Only Scan using communes_passage_pkey on communes_passage b_8 (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5196)
Index Cond: (depcomn = c_8.insee)
Heap Fetches: 23
-> Index Only Scan using clap_pkey on clap a_8 (cost=0.42..0.47 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=5907)
Index Cond: ((insee = b_8.depcoma) AND (annee = $34))
Heap Fetches: 0
-> Aggregate (cost=92668.00..92668.01 rows=1 width=16) (actual time=588.838..589.339 rows=1 loops=1)
InitPlan 21 (returns $43)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.209..85.243 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=73.222..85.231 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.067..68.068 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_12 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.027..11.837 rows=180402 loops=3)
Heap Fetches: 0
InitPlan 23 (returns $48)
-> Subquery Scan on epci_groupe_ref_5 (cost=15366.78..15366.80 rows=1 width=4) (actual time=85.853..85.888 rows=1 loops=1)
-> Sort (cost=15366.78..15366.79 rows=1 width=28) (actual time=85.852..85.885 rows=1 loops=1)
Sort Key: (CASE WHEN (sum(a_17.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_17.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_17.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 25kB
InitPlan 22 (returns $45)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.410..85.440 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=73.692..85.430 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.397..68.397 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_13 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.024..11.921 rows=180402 loops=3)
Heap Fetches: 0
-> GroupAggregate (cost=1.43..497.32 rows=1 width=28) (actual time=85.843..85.846 rows=1 loops=1)
Group Key: d_11.id_peri
-> Nested Loop (cost=1.43..497.10 rows=41 width=32) (actual time=85.463..85.834 rows=72 loops=1)
-> Index Scan using peri_pkey on peri d_11 (cost=0.29..8.31 rows=1 width=24) (actual time=0.008..0.009 rows=1 loops=1)
Index Cond: (id_peri = '200066918'::text)
Filter: (id_type_peri = 3)
-> Nested Loop (cost=1.14..488.38 rows=41 width=15) (actual time=85.453..85.818 rows=72 loops=1)
-> Nested Loop (cost=0.71..320.85 rows=45 width=13) (actual time=0.015..0.159 rows=72 loops=1)
-> Index Scan using assoc_commune_peri_id_peri_idx on assoc_commune_peri c_17 (cost=0.42..155.83 rows=41 width=13) (actual time=0.006..0.015 rows=72 loops=1)
Index Cond: (id_peri = '200066918'::text)
-> Index Only Scan using communes_passage_pkey on communes_passage b_17 (cost=0.29..4.01 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=72)
Index Cond: (depcomn = c_17.insee)
Heap Fetches: 0
-> Index Scan using cc_emp_pkey on cc_emp a_17 (cost=0.42..3.72 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=72)
Index Cond: ((codgeo = b_17.depcoma) AND (annee = $45))
-> Nested Loop (cost=59180.22..62430.71 rows=419 width=8) (actual time=562.288..588.999 rows=5195 loops=1)
-> Nested Loop (cost=59179.79..60736.78 rows=455 width=6) (actual time=477.051..487.195 rows=5907 loops=1)
-> Nested Loop (cost=59179.50..60603.93 rows=417 width=6) (actual time=477.043..478.687 rows=5196 loops=1)
-> HashAggregate (cost=59174.54..59174.60 rows=6 width=6) (actual time=477.020..477.477 rows=151 loops=1)
Group Key: d_5.id_peri
Batches: 1 Memory Usage: 48kB
-> Sort (cost=59174.45..59174.47 rows=6 width=28) (actual time=476.982..477.418 rows=151 loops=1)
Sort Key: (CASE WHEN (sum(a_11.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_11.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_11.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 38kB
InitPlan 24 (returns $50)
-> Finalize Aggregate (cost=14869.44..14869.45 rows=1 width=32) (actual time=85.242..85.272 rows=1 loops=1)
-> Gather (cost=14869.23..14869.44 rows=2 width=32) (actual time=74.911..85.261 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13869.23..13869.24 rows=1 width=32) (actual time=68.708..68.709 rows=1 loops=3)
-> Parallel Index Only Scan using cc_emp_pkey on cc_emp cc_emp_14 (cost=0.42..13305.47 rows=225502 width=5) (actual time=0.023..12.033 rows=180402 loops=3)
Heap Fetches: 0
-> Finalize GroupAggregate (cost=44128.90..44304.92 rows=6 width=28) (actual time=476.266..477.355 rows=151 loops=1)
Group Key: d_5.id_peri
Filter: (CASE WHEN (sum(a_11.pxx_emplt) >= '150000'::double precision) THEN 1 WHEN (sum(a_11.pxx_emplt) >= '50000'::double precision) THEN 2 WHEN (sum(a_11.pxx_emplt) >= '20000'::double precision) THEN 3 ELSE 4 END = $48)
Rows Removed by Filter: 1099
-> Gather Merge (cost=44128.90..44273.45 rows=1257 width=32) (actual time=390.386..391.217 rows=2434 loops=1)
Workers Planned: 1
Params Evaluated: $50
Workers Launched: 1
-> Sort (cost=43128.89..43132.03 rows=1257 width=32) (actual time=293.705..293.777 rows=1217 loops=2)
Sort Key: d_5.id_peri
Sort Method: quicksort Memory: 179kB
Worker 0: Sort Method: quicksort Memory: 183kB
-> Partial HashAggregate (cost=43051.61..43064.18 rows=1257 width=32) (actual time=292.636..292.726 rows=1217 loops=2)
Group Key: d_5.id_peri
Batches: 1 Memory Usage: 321kB
Worker 0: Batches: 1 Memory Usage: 321kB
-> Nested Loop (cost=7126.76..43008.67 rows=8589 width=32) (actual time=238.986..290.667 rows=17466 loops=2)
-> Parallel Hash Join (cost=7126.33..8296.27 rows=9324 width=30) (actual time=238.966..243.149 rows=19578 loops=2)
Hash Cond: (b_11.depcomn = c_11.insee)
-> Parallel Index Only Scan using communes_passage_pkey on communes_passage b_11 (cost=0.29..1044.98 rows=23038 width=12) (actual time=0.019..1.322 rows=19582 loops=2)
Heap Fetches: 108
-> Parallel Hash (cost=7050.45..7050.45 rows=6047 width=30) (actual time=238.920..238.922 rows=17476 loops=2)
Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3552kB
-> Hash Join (cost=992.06..7050.45 rows=6047 width=30) (actual time=133.989..136.754 rows=17476 loops=2)
Hash Cond: (c_11.id_peri = d_5.id_peri)
-> Parallel Seq Scan on assoc_commune_peri c_11 (cost=0.00..5440.74 rows=235274 width=13) (actual time=0.006..9.106 rows=282329 loops=2)
-> Hash (cost=976.35..976.35 rows=1257 width=24) (actual time=113.317..113.317 rows=1255 loops=2)
Buckets: 2048 Batches: 1 Memory Usage: 99kB
-> Seq Scan on peri d_5 (cost=0.00..976.35 rows=1257 width=24) (actual time=113.086..113.186 rows=1255 loops=2)
Filter: (id_type_peri = 3)
Rows Removed by Filter: 47653
-> Index Scan using cc_emp_pkey on cc_emp a_11 (cost=0.42..3.72 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=39157)
Index Cond: ((codgeo = b_11.depcoma) AND (annee = $50))
-> Bitmap Heap Scan on assoc_commune_peri c_10 (cost=4.96..237.53 rows=69 width=13) (actual time=0.004..0.006 rows=34 loops=151)
Recheck Cond: (id_peri = d_5.id_peri)
Heap Blocks: exact=178
-> Bitmap Index Scan on assoc_commune_peri_id_peri_idx (cost=0.00..4.94 rows=69 width=0) (actual time=0.003..0.003 rows=34 loops=151)
Index Cond: (id_peri = d_5.id_peri)
-> Index Only Scan using communes_passage_pkey on communes_passage b_10 (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5196)
Index Cond: (depcomn = c_10.insee)
Heap Fetches: 23
-> Index Scan using cc_emp_pkey on cc_emp a_10 (cost=0.42..3.72 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=5907)
Index Cond: ((codgeo = b_10.depcoma) AND (annee = $43))
Planning Time: 10.341 ms
JIT:
Functions: 1002
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 50.123 ms, Inlining 1032.225 ms, Optimization 2998.037 ms, Emission 2337.827 ms, Total 6418.212 ms
Execution Time: 6304.033 msEn espérant que ça éclaire (désolé pour les pavés de code mais je n'ai pas trouvé de quoi mettre des pièces jointes)...
Dernière modification par Maxime31 (22/08/2022 14:28:36)
Hors ligne
Apparemment la majorité du temps est passé sur le JIT en version 14:
JIT:
Functions: 1002
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 50.123 ms, Inlining 1032.225 ms, Optimization 2998.037 ms, Emission 2337.827 ms, Total 6418.212 ms
Vous pouvez essayer un "set jit=off;" avant d'effectuer la requête pour confirmer si vous retrouvez les même performances qu'en version 11. Si c'est le cas, vous pouvez voir pour augmenter les différents couts (cf https://www.postgresql.org/docs/14/jit- … tion.html), voire désactiver jit globalement si vous n'arrivez pas à un comportement acceptable.
Julien.
https://rjuju.github.io/
Hors ligne
Merci ! C'était bien à cause de ça.
C'est bizarre parce que dans la config par défaut, il est sur "on" mais le "jit_above_cost" est égal à 100000 donc il aurait pas dû l'utiliser sur ma requête qui est plutôt courte non ?
Autre question, si je veux le désactiver globalement, il me suffit de mettre "jit=off" dans le fichier de conf ?
Dernière modification par Maxime31 (23/08/2022 09:29:24)
Hors ligne
Le cout de votre requête est 514424.15, soit plus de 5 fois au dessus de votre limite de 100000. Malheureusement, le JIT est connu pour causer de genre de problème, le mécanisme de cout actuel (simplement prendre en compte le cout de la requête) n'étant pas entièrement adapté pour quelquechose comme le JIT (dont le cout dépend principalement du nombre de fonctions à compiler).
Autre question, si je veux le désactiver globalement, il me suffit de mettre "jit=off" dans le fichier de conf ?
Oui, puis de recharcher la configuration.
Julien.
https://rjuju.github.io/
Hors ligne
Ah oui d'accord, au temps pour moi, j'ai confondu coût et temps. Qu'est-ce qu'il serait plutôt conseillé de mettre comme valeur pour le paramètre "jit_above_cost" si je décide de ne pas le désactiver complètement ?
Hors ligne
J'imagine au moins 10 fois la valeur actuelle, mais comme indiqué il est généralement assez compliqué de le configurer correctement, le cout de la requête n'étant pas vraiment en lien avec le cout du JIT.
Quel type d'applicatif s'agit-il ? Si c'est purement de l'OLTP à priori le JIT devrait être inutile et vous devriez le désactiver. Si vous avez un mix OLTP / OLAP vous pouvez l'activer sur une partie des requêtes uniquement, par exemple sur un role en particulier ou une base en particulier.
Julien.
https://rjuju.github.io/
Hors ligne
D'accord, en fait j'ai un entrepôt de données avec une base de données principale sur laquelle je m'appuie pour faire, dans la majorité des cas, des requêtes SELECT afin d'alimenter ensuite des outils web type tableau de bord pour de la consultation donc a priori je pourrais désactiver le JIT.
Le JIT permet de faire un travail d'optimisation sur des requêtes complexes mais il ne permet pas de "fluidifier" un affichage des retours de la requête, si ? Par exemple, je peux être confronté à des utilisateurs qui veulent afficher une table issue de ma base Postgres avec des centaines de milliers d'enregistrements dans QGIS, le JIT n'intervient pas à ce niveau là si je comprends bien, puisque la requête serait basique mais ce sont plutôt les résultats qui seraient volumineux ? Quels outils de Postgres-Postgis peuvent m'aider sur ce genre de problématique et réduire les temps de chargement de données volumineuses ?
Hors ligne
Je me permets de relancer le sujet si quelqu'un a des pistes suite à la question du message précédent...
Hors ligne
Par exemple, je peux être confronté à des utilisateurs qui veulent afficher une table issue de ma base Postgres avec des centaines de milliers d'enregistrements dans QGIS, le JIT n'intervient pas à ce niveau là si je comprends bien, puisque la requête serait basique mais ce sont plutôt les résultats qui seraient volumineux ?
À priori oui le JIT ne devrait pas se déclencher car un simple "SELECT * FROM table" ne devrait pas être avoir un cout trop élevé, du moins s'il s'agit bien d'une table et non d'une vue complexe.
Quels outils de Postgres-Postgis peuvent m'aider sur ce genre de problématique et réduire les temps de chargement de données volumineuses ?
S'il s'agit de simples "SELECT * FROM table", il n'y a généralement pas grand chose à faire le goulet d'étrangelement étant généralement du côté du réseau voire des disques si les données ne tiennent pas en cache.
Julien.
https://rjuju.github.io/
Hors ligne
Pages : 1