explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dER8

Settings
# exclusive inclusive rows x rows loops node
1. 1,984.643 14,636.124 ↑ 140.1 1,302 1

GroupAggregate (cost=249,444.03..267,229.69 rows=182,417 width=188) (actual time=11,176.191..14,636.124 rows=1,302 loops=1)

  • Group Key: struct_commune.code_pole, struct_commune.code_uf, rh_fonction_regroupement.libelle_classification_bs, (to_char((cdg_date.jour)::timestamp with time zone, 'YYYY'::text)), struct_commune.lib_pole, struct_commune.lib_ssserv1, struct_commune.lib_uf, rh_contrat_foch.contrat_foch_libelle
  • Filter: ((((sum(rh_f_paie.montant_salaire_brut) - sum(CASE WHEN (rh_type_paie.code_groupe_paie = 'SFR_AMT_ALL_VOL_RET'::text) THEN rh_f_paie.montant_rubrique_paie ELSE '0'::numeric END)) + sum(rh_f_paie.montant_cotisation_patronale)) - (sum(CASE WHEN (rh_type_paie.code_groupe_paie = 'SFR_AMT_ALL_VOL_RET'::text) THEN rh_f_paie.montant_rubrique_paie ELSE '0'::numeric END) * 0.59)) > '0'::numeric)
  • Rows Removed by Filter: 131
2. 11,412.136 12,651.481 ↓ 10.7 1,958,942 1

Sort (cost=249,444.03..249,900.08 rows=182,417 width=187) (actual time=11,175.899..12,651.481 rows=1,958,942 loops=1)

  • Sort Key: struct_commune.code_pole, struct_commune.code_uf, rh_fonction_regroupement.libelle_classification_bs, (to_char((cdg_date.jour)::timestamp with time zone, 'YYYY'::text)), struct_commune.lib_pole, struct_commune.lib_ssserv1, struct_commune.lib_uf, rh_contrat_foch.contrat_foch_libelle
  • Sort Method: external merge Disk: 321536kB
3. 0.000 1,239.345 ↓ 10.7 1,958,942 1

Gather (cost=1,690.29..233,503.63 rows=182,417 width=187) (actual time=4.965..1,239.345 rows=1,958,942 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
4. 456.059 1,732.886 ↓ 8.9 326,490 6 / 6

Hash Join (cost=690.29..213,532.26 rows=36,483 width=187) (actual time=12.930..1,732.886 rows=326,490 loops=6)

  • Hash Cond: (unite_cdg.id = securite_helper.elt_struct_id)
5. 132.794 1,273.712 ↓ 4.7 326,490 6 / 6

Hash Join (cost=563.92..212,729.81 rows=69,286 width=171) (actual time=9.535..1,273.712 rows=326,490 loops=6)

  • Hash Cond: (rh_f_paie.rh_type_paie_id = rh_type_paie.id)
6. 145.224 1,139.512 ↓ 4.7 326,490 6 / 6

Hash Join (cost=523.97..211,737.18 rows=69,286 width=157) (actual time=8.091..1,139.512 rows=326,490 loops=6)

  • Hash Cond: (rh_f_paie.elt_struct_id = unite_cdg.id)
7. 119.826 990.649 ↓ 4.7 326,490 6 / 6

Hash Join (cost=46.62..210,307.15 rows=69,286 width=66) (actual time=4.410..990.649 rows=326,490 loops=6)

  • Hash Cond: (rh_f_paie.rh_contrat_foch_id = rh_contrat_foch.id)
8. 134.998 870.668 ↓ 5.0 356,878 6 / 6

Hash Join (cost=44.99..209,342.44 rows=72,057 width=38) (actual time=4.062..870.668 rows=356,878 loops=6)

  • Hash Cond: (rh_f_paie.rh_fonction_id = rh_fonction.id)
9. 462.136 734.546 ↓ 5.1 453,297 6 / 6

Hash Join (cost=16.04..208,257.62 rows=89,414 width=33) (actual time=2.892..734.546 rows=453,297 loops=6)

  • Hash Cond: ((rh_f_paie.annee = cdg_date.annee) AND (rh_f_paie.mois = cdg_date.mois))
10. 269.603 269.603 ↑ 1.2 681,400 6 / 6

Parallel Seq Scan on rh_f_paie (cost=0.00..180,811.81 rows=816,481 width=37) (actual time=0.039..269.603 rows=681,400 loops=6)

11. 0.113 2.807 ↓ 4.4 145 6 / 6

Hash (cost=15.55..15.55 rows=33 width=12) (actual time=2.807..2.807 rows=145 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
12. 2.694 2.694 ↓ 4.4 145 6 / 6

Seq Scan on cdg_date (cost=0.00..15.55 rows=33 width=12) (actual time=0.795..2.694 rows=145 loops=6)

  • Filter: ((to_char((jour)::timestamp with time zone, 'YYYY'::text) >= to_char((('now'::cstring)::date + '-1 years -1 mons'::interval), 'YYYY'::text)) AND (to_char((jour)::timestamp with time zone, 'MM'::text) <= to_char((('now'::cstring)::date + '-1 mons'::interval), 'MM'::text)))
  • Rows Removed by Filter: 156
13. 0.148 1.124 ↑ 1.0 274 6 / 6

Hash (cost=25.52..25.52 rows=274 width=13) (actual time=1.124..1.124 rows=274 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
14. 0.344 0.976 ↑ 1.0 274 6 / 6

Hash Right Join (cost=10.68..25.52 rows=274 width=13) (actual time=0.524..0.976 rows=274 loops=6)

  • Hash Cond: (rh_fonction_regroupement.rh_fonction_id = rh_fonction.id)
15. 0.208 0.208 ↑ 1.0 314 6 / 6

Seq Scan on rh_fonction_regroupement (cost=0.00..11.14 rows=314 width=13) (actual time=0.048..0.208 rows=314 loops=6)

16. 0.131 0.424 ↑ 1.0 274 6 / 6

Hash (cost=7.25..7.25 rows=274 width=4) (actual time=0.424..0.424 rows=274 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
17. 0.293 0.293 ↑ 1.0 274 6 / 6

Seq Scan on rh_fonction (cost=0.00..7.25 rows=274 width=4) (actual time=0.077..0.293 rows=274 loops=6)

  • Filter: (is_pm_pnm = 'PNM'::text)
  • Rows Removed by Filter: 66
18. 0.022 0.155 ↑ 1.2 20 6 / 6

Hash (cost=1.32..1.32 rows=25 width=40) (actual time=0.155..0.155 rows=20 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.133 0.133 ↑ 1.2 20 6 / 6

Seq Scan on rh_contrat_foch (cost=0.00..1.32 rows=25 width=40) (actual time=0.121..0.133 rows=20 loops=6)

  • Filter: ((contrat_foch_code)::text <> 'JNSUP'::text)
  • Rows Removed by Filter: 6
20. 0.511 3.639 ↑ 1.0 640 6 / 6

Hash (cost=469.34..469.34 rows=640 width=91) (actual time=3.639..3.639 rows=640 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 62kB
21. 0.811 3.128 ↑ 1.0 640 6 / 6

Hash Join (cost=40.65..469.34 rows=640 width=91) (actual time=1.131..3.128 rows=640 loops=6)

  • Hash Cond: (struct_commune.id_elt_struct = unite_cdg.id)
22. 1.274 1.588 ↑ 1.0 640 6 / 6

Bitmap Heap Scan on struct_commune (cost=17.25..437.14 rows=640 width=87) (actual time=0.363..1.588 rows=640 loops=6)

  • Recheck Cond: (code_hie = 'PMSIPILOT_14'::text)
  • Heap Blocks: exact=45
23. 0.314 0.314 ↑ 1.0 640 6 / 6

Bitmap Index Scan on idx_struct_commune_code_hie (cost=0.00..17.09 rows=640 width=0) (actual time=0.314..0.314 rows=640 loops=6)

  • Index Cond: (code_hie = 'PMSIPILOT_14'::text)
24. 0.292 0.729 ↑ 1.0 640 6 / 6

Hash (cost=15.40..15.40 rows=640 width=4) (actual time=0.729..0.729 rows=640 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
25. 0.437 0.437 ↑ 1.0 640 6 / 6

Seq Scan on unite_cdg (cost=0.00..15.40 rows=640 width=4) (actual time=0.053..0.437 rows=640 loops=6)

26. 0.538 1.406 ↓ 1.0 1,025 6 / 6

Hash (cost=27.20..27.20 rows=1,020 width=22) (actual time=1.406..1.406 rows=1,025 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
27. 0.868 0.868 ↓ 1.0 1,025 6 / 6

Seq Scan on rh_type_paie (cost=0.00..27.20 rows=1,020 width=22) (actual time=0.048..0.868 rows=1,025 loops=6)

28. 0.133 3.115 ↓ 1.6 337 6 / 6

Hash (cost=123.68..123.68 rows=216 width=4) (actual time=3.115..3.115 rows=337 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
29. 0.365 2.982 ↓ 1.6 337 6 / 6

HashAggregate (cost=121.52..123.68 rows=216 width=4) (actual time=2.882..2.982 rows=337 loops=6)

  • Group Key: securite_helper.elt_struct_id
30. 2.617 2.617 ↑ 1.0 337 6 / 6

Seq Scan on temp_security_view securite_helper (cost=0.00..120.68 rows=337 width=4) (actual time=0.132..2.617 rows=337 loops=6)

  • Filter: (user_id = 'RHgestion'::text)
  • Rows Removed by Filter: 6357