explain.depesz.com

PostgreSQL's explain analyze made readable

Result: taMG

Settings
# exclusive inclusive rows x rows loops node
1. 1,132.247 11,816.471 ↑ 25.0 12 1

HashAggregate (cost=13,432.73..13,437.98 rows=300 width=68) (actual time=11,816.467..11,816.471 rows=12 loops=1)

  • Group Key: to_char((bi_calendrier.jour)::timestamp with time zone, 'YYYY'::text)
2. 2,362.642 10,684.224 ↓ 189.9 5,047,160 1

Hash Join (cost=10,260.00..13,299.82 rows=26,581 width=35) (actual time=6,593.165..10,684.224 rows=5,047,160 loops=1)

  • Hash Cond: (unite_cdg.id = securite_helper.elt_struct_id)
3. 903.006 8,313.625 ↓ 185.5 5,047,160 1

Hash Join (cost=8,555.79..11,189.99 rows=27,202 width=19) (actual time=6,585.182..8,313.625 rows=5,047,160 loops=1)

  • Hash Cond: (dm_cumul_eff.id_elt_struct = unite_cdg.id)
4. 824.405 7,406.492 ↓ 185.5 5,047,160 1

Hash Join (cost=7,973.92..10,234.09 rows=27,202 width=11) (actual time=6,581.038..7,406.492 rows=5,047,160 loops=1)

  • Hash Cond: ((bi_calendrier.annee = dm_cumul_eff.no_annee) AND (bi_calendrier.mois = dm_cumul_eff.no_mois))
5. 1.153 1.153 ↑ 1.0 300 1

Seq Scan on bi_calendrier (cost=0.00..182.15 rows=300 width=12) (actual time=0.010..1.153 rows=300 loops=1)

  • Filter: (no_jour_mois = 1)
  • Rows Removed by Filter: 8832
6. 907.698 6,580.934 ↓ 185.6 5,047,160 1

Hash (cost=7,566.03..7,566.03 rows=27,193 width=15) (actual time=6,580.934..6,580.934 rows=5,047,160 loops=1)

  • Buckets: 8388608 (originally 32768) Batches: 1 (originally 1) Memory Usage: 303487kB
7. 869.921 5,673.236 ↓ 185.6 5,047,160 1

Hash Join (cost=5.19..7,566.03 rows=27,193 width=15) (actual time=0.115..5,673.236 rows=5,047,160 loops=1)

  • Hash Cond: (dm_cumul_eff.id_statut = statut.id)
8. 1,158.407 4,803.257 ↓ 170.6 5,184,242 1

Nested Loop (cost=0.99..7,175.93 rows=30,392 width=19) (actual time=0.052..4,803.257 rows=5,184,242 loops=1)

9. 0.853 4.450 ↓ 158.3 950 1

Nested Loop (cost=0.56..78.96 rows=6 width=8) (actual time=0.038..4.450 rows=950 loops=1)

10. 1.228 1.697 ↓ 158.3 950 1

Nested Loop (cost=0.28..76.74 rows=6 width=4) (actual time=0.028..1.697 rows=950 loops=1)

  • Join Filter: (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)
  • Rows Removed by Join Filter: 177
11. 0.469 0.469 ↓ 1.0 1,127 1

Index Scan using pk_grade_cat on grade_cat (cost=0.28..53.18 rows=1,126 width=6) (actual time=0.012..0.469 rows=1,127 loops=1)

12. 0.000 0.000 ↑ 1.0 1 1,127

Materialize (cost=0.00..1.04 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1,127)

13. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on type_personnel (cost=0.00..1.04 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)

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

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

  • Index Cond: (id = grade_cat.id_grade)
  • Heap Fetches: 950
15. 3,640.400 3,640.400 ↑ 4.8 5,457 950

Index Scan using dm_cumul_eff_id_grade_idx on dm_cumul_eff (cost=0.43..919.70 rows=26,313 width=23) (actual time=0.003..3.832 rows=5,457 loops=950)

  • Index Cond: (id_grade = grade.id)
16. 0.013 0.058 ↑ 1.0 85 1

Hash (cost=3.14..3.14 rows=85 width=4) (actual time=0.058..0.058 rows=85 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 0.045 0.045 ↑ 1.0 85 1

Seq Scan on statut (cost=0.00..3.14 rows=85 width=4) (actual time=0.010..0.045 rows=85 loops=1)

  • Filter: (cd_statut <> ALL ('{3A,3E,3F,3H,3J,3X,3Y,NA,6T,90}'::text[]))
  • Rows Removed by Filter: 10
18. 0.283 4.127 ↑ 1.0 2,583 1

Hash (cost=549.58..549.58 rows=2,583 width=8) (actual time=4.127..4.127 rows=2,583 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 133kB
19. 0.557 3.844 ↑ 1.0 2,583 1

Hash Join (cost=99.12..549.58 rows=2,583 width=8) (actual time=0.638..3.844 rows=2,583 loops=1)

  • Hash Cond: (struct_commune.id_elt_struct = unite_cdg.id)
20. 2.676 2.676 ↑ 1.0 2,583 1

Seq Scan on struct_commune (cost=0.00..414.95 rows=2,583 width=4) (actual time=0.013..2.676 rows=2,583 loops=1)

  • Filter: is_defaut_unite
  • Rows Removed by Filter: 7812
21. 0.215 0.611 ↑ 1.0 2,583 1

Hash (cost=66.83..66.83 rows=2,583 width=4) (actual time=0.611..0.611 rows=2,583 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 123kB
22. 0.396 0.396 ↑ 1.0 2,583 1

Seq Scan on unite_cdg (cost=0.00..66.83 rows=2,583 width=4) (actual time=0.008..0.396 rows=2,583 loops=1)

23. 0.245 7.957 ↓ 1.5 2,497 1

Hash (cost=1,683.94..1,683.94 rows=1,621 width=4) (actual time=7.957..7.957 rows=2,497 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 120kB
24. 0.707 7.712 ↓ 1.5 2,497 1

HashAggregate (cost=1,667.73..1,683.94 rows=1,621 width=4) (actual time=7.440..7.712 rows=2,497 loops=1)

  • Group Key: securite_helper.elt_struct_id
25. 7.005 7.005 ↑ 1.0 2,497 1

Seq Scan on temp_security_view securite_helper (cost=0.00..1,661.42 rows=2,524 width=4) (actual time=0.015..7.005 rows=2,497 loops=1)

  • Filter: (name = 'cdg'::text)
  • Rows Removed by Filter: 90417
Planning time : 3.417 ms
Execution time : 11,828.124 ms