explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t8TJ

Settings
# exclusive inclusive rows x rows loops node
1. 5,161.561 11,972.260 ↑ 333.3 22 1

GroupAggregate (cost=4,077.45..4,279.11 rows=7,333 width=72) (actual time=6,993.219..11,972.260 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,614.583 6,810.699 ↓ 342.7 2,513,160 1

Sort (cost=4,077.45..4,095.78 rows=7,333 width=64) (actual time=4,789.653..6,810.699 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: 99440kB
3. 1,151.763 1,196.116 ↓ 342.7 2,513,160 1

Hash Join (cost=278.14..3,606.67 rows=7,333 width=64) (actual time=3.158..1,196.116 rows=2,513,160 loops=1)

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

Hash Join (cost=59.91..3,261.94 rows=2,639 width=4) (actual time=1.623..42.827 rows=118,545 loops=1)

  • Hash Cond: (rh_dim_eff_dist.id_grade = grade.id)
5. 14.509 14.509 ↑ 1.0 131,933 1

Seq Scan on rh_dim_eff_dist (cost=0.00..2,680.33 rows=131,933 width=8) (actual time=0.010..14.509 rows=131,933 loops=1)

6. 0.118 1.607 ↓ 41.3 950 1

Hash (cost=59.62..59.62 rows=23 width=8) (actual time=1.607..1.607 rows=950 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
7. 0.018 1.489 ↓ 41.3 950 1

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

8. 0.399 0.521 ↓ 41.3 950 1

Hash Join (cost=20.68..52.20 rows=23 width=4) (actual time=0.018..0.521 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)
9. 0.114 0.114 ↓ 1.0 1,127 1

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

10. 0.002 0.008 ↑ 4.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 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
12. 0.950 0.950 ↑ 1.0 1 950

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

  • Index Cond: (id = grade_cat.id_grade)
  • Heap Fetches: 950
13. 0.299 1.526 ↓ 8.0 2,112 1

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 185kB
14. 0.300 1.227 ↓ 8.0 2,112 1

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

15. 0.047 0.047 ↑ 1.0 88 1

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

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

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

17. 0.195 0.831 ↓ 8.0 24 1

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

18. 0.034 0.034 ↑ 1.0 301 1

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

19. 0.602 0.602 ↓ 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.002..0.002 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 : 0.962 ms
Execution time : 11,990.899 ms