explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5W7Ug

Settings
# exclusive inclusive rows x rows loops node
1. 5,621.386 12,447.305 ↑ 333.3 22 1

GroupAggregate (cost=1,163.93..1,365.58 rows=7,333 width=72) (actual time=7,277.740..12,447.305 rows=22 loops=1)

  • Group Key: (to_char((bi_calendrier.jour)::timestamp with time zone, 'YYYY'::text)), statut.cd_statut, statut.lb_statut
2. 5,526.203 6,825.919 ↓ 342.7 2,513,160 1

Sort (cost=1,163.93..1,182.26 rows=7,333 width=64) (actual time=4,876.513..6,825.919 rows=2,513,160 loops=1)

  • Sort Key: (to_char((bi_calendrier.jour)::timestamp with time zone, 'YYYY'::text)), statut.cd_statut, statut.lb_statut
  • Sort Method: external merge Disk: 99424kB
3. 1,213.888 1,299.716 ↓ 342.7 2,513,160 1

Hash Join (cost=239.60..693.14 rows=7,333 width=64) (actual time=1.738..1,299.716 rows=2,513,160 loops=1)

  • Hash Cond: (rh_dim_eff_dist.id_statut = statut.id)
4. 13.840 84.143 ↓ 44.9 118,545 1

Nested Loop (cost=21.37..348.41 rows=2,639 width=4) (actual time=0.036..84.143 rows=118,545 loops=1)

5. 0.771 3.803 ↓ 41.3 950 1

Nested Loop (cost=20.95..59.62 rows=23 width=8) (actual time=0.031..3.803 rows=950 loops=1)

6. 0.867 1.132 ↓ 41.3 950 1

Hash Join (cost=20.68..52.20 rows=23 width=4) (actual time=0.025..1.132 rows=950 loops=1)

  • Hash Cond: (CASE WHEN (grade_cat.cd_cat = '5'::text) THEN 1 WHEN (COALESCE(grade_cat.cd_cat, '0'::text) <> '5'::text) THEN 2 ELSE '-1'::integer END = type_personnel.id)
7. 0.256 0.256 ↓ 1.0 1,127 1

Seq Scan on grade_cat (cost=0.00..24.26 rows=1,126 width=6) (actual time=0.010..0.256 rows=1,127 loops=1)

8. 0.003 0.009 ↑ 4.0 1 1

Hash (cost=20.62..20.62 rows=4 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.006 0.006 ↑ 4.0 1 1

Seq Scan on type_personnel (cost=0.00..20.62 rows=4 width=4) (actual time=0.006..0.006 rows=1 loops=1)

  • Filter: (code = 'PNM'::text)
  • Rows Removed by Filter: 2
10. 1.900 1.900 ↑ 1.0 1 950

Index Only Scan using pk_grade on grade (cost=0.28..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=950)

  • Index Cond: (id = grade_cat.id_grade)
  • Heap Fetches: 950
11. 66.500 66.500 ↑ 3.0 125 950

Index Scan using rh_dim_eff_dist_id_grade_idx on rh_dim_eff_dist (cost=0.42..8.76 rows=380 width=8) (actual time=0.002..0.070 rows=125 loops=950)

  • Index Cond: (id_grade = grade.id)
12. 0.328 1.685 ↓ 8.0 2,112 1

Hash (cost=214.93..214.93 rows=264 width=40) (actual time=1.685..1.685 rows=2,112 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 185kB
13. 0.260 1.357 ↓ 8.0 2,112 1

Nested Loop (cost=0.29..214.93 rows=264 width=40) (actual time=0.061..1.357 rows=2,112 loops=1)

14. 0.041 0.041 ↑ 1.0 88 1

Seq Scan on statut (cost=0.00..2.78 rows=88 width=28) (actual time=0.008..0.041 rows=88 loops=1)

  • Filter: (cd_statut <> ALL ('{3F,90,3E,3H,3J,NA,6T}'::text[]))
  • Rows Removed by Filter: 7
15. 0.100 1.056 ↓ 8.0 24 88

Materialize (cost=0.29..208.85 rows=3 width=12) (actual time=0.001..0.012 rows=24 loops=88)

16. 0.018 0.956 ↓ 8.0 24 1

Nested Loop (cost=0.29..208.84 rows=3 width=12) (actual time=0.050..0.956 rows=24 loops=1)

17. 0.035 0.035 ↑ 1.0 301 1

Seq Scan on anneemois_eff (cost=0.00..5.01 rows=301 width=8) (actual time=0.010..0.035 rows=301 loops=1)

18. 0.903 0.903 ↓ 0.0 0 301

Index Scan using bi_calendrier_join1_idx on bi_calendrier (cost=0.29..0.67 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=301)

  • Index Cond: ((((annee * 10000) + (mois * 100)) + no_jour_mois) = (((anneemois_eff.no_annee * 10000) + (anneemois_eff.no_mois * 100)) + 1))
  • Filter: (to_char((jour)::timestamp with time zone, 'YYYY'::text) = ANY ('{2019,2018}'::text[]))
  • Rows Removed by Filter: 1
Planning time : 1.811 ms
Execution time : 12,465.510 ms