explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CoNS : Optimization for: plan #get5

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 79.034 2,274.819 ↓ 6.0 293,562 1

Unique (cost=40,401.53..42,003.55 rows=49,293 width=101) (actual time=2,166.374..2,274.819 rows=293,562 loops=1)

2. 682.949 2,195.785 ↓ 6.0 293,562 1

Sort (cost=40,401.53..40,524.76 rows=49,293 width=101) (actual time=2,166.372..2,195.785 rows=293,562 loops=1)

  • Sort Key: e.code_geo, ue.id, ue.matricule_unique, cub.code, cza.code, ue.valeur_terrain, ue.valeur_batiment, ue.valeur_immeuble, cii.code, cinr.code, ue.est_terrain_vague, (sum(lue.montant_repartition))
  • Sort Method: external sort Disk: 22872kB
3. 162.212 1,512.836 ↓ 6.0 293,562 1

GroupAggregate (cost=32,135.61..33,860.86 rows=49,293 width=101) (actual time=1,219.532..1,512.836 rows=293,562 loops=1)

  • Group Key: e.code_geo, e.annee_exercice, ue.id, cub.code, cza.code, cii.code, cinr.code
4. 631.610 1,350.624 ↓ 7.1 352,115 1

Sort (cost=32,135.61..32,258.84 rows=49,293 width=77) (actual time=1,219.518..1,350.624 rows=352,115 loops=1)

  • Sort Key: e.code_geo, ue.id, cub.code, cza.code, cii.code, cinr.code
  • Sort Method: external merge Disk: 28248kB
5. 66.373 719.014 ↓ 7.1 352,115 1

Hash Left Join (cost=19,136.56..26,102.44 rows=49,293 width=77) (actual time=259.547..719.014 rows=352,115 loops=1)

  • Hash Cond: (ue.code_zonage_agricole = cza.id)
6. 70.514 652.274 ↓ 7.1 352,115 1

Hash Left Join (cost=19,077.26..25,421.08 rows=49,293 width=77) (actual time=259.174..652.274 rows=352,115 loops=1)

  • Hash Cond: (ue.code_utilisation = cub.id)
7. 56.112 581.393 ↓ 7.1 352,115 1

Hash Left Join (cost=19,017.95..24,739.71 rows=49,293 width=77) (actual time=258.801..581.393 rows=352,115 loops=1)

  • Hash Cond: (ue.code_categorie_immeuble_industriel = cii.id)
8. 52.516 524.900 ↓ 7.1 352,115 1

Hash Left Join (cost=18,958.65..24,058.09 rows=49,293 width=77) (actual time=258.413..524.900 rows=352,115 loops=1)

  • Hash Cond: (ue.code_categorie_immeuble_non_residentiel = cinr.id)
9. 197.164 471.951 ↓ 7.1 352,115 1

Hash Right Join (cost=18,899.34..23,376.47 rows=49,293 width=77) (actual time=257.967..471.951 rows=352,115 loops=1)

  • Hash Cond: (lue.unite_evaluation = ue.id)
10. 17.111 17.111 ↓ 1.0 112,855 1

Seq Scan on loi_unite_evaluation lue (cost=0.00..2,088.22 rows=110,622 width=24) (actual time=0.007..17.111 rows=112,855 loops=1)

11. 103.987 257.676 ↓ 6.0 293,562 1

Hash (cost=17,705.18..17,705.18 rows=49,293 width=69) (actual time=257.676..257.676 rows=293,562 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 16 (originally 2) Memory Usage: 3585kB
12. 92.221 153.689 ↓ 6.0 293,562 1

Nested Loop (cost=67.63..17,705.18 rows=49,293 width=69) (actual time=0.424..153.689 rows=293,562 loops=1)

13. 0.569 1.120 ↓ 10.9 1,284 1

Hash Join (cost=67.21..196.91 rows=118 width=14) (actual time=0.394..1.120 rows=1,284 loops=1)

  • Hash Cond: (e.id = r.exercice_organisme)
14. 0.174 0.233 ↑ 1.0 1,287 1

Bitmap Heap Scan on exercice_organisme e (cost=26.26..148.35 rows=1,287 width=14) (actual time=0.067..0.233 rows=1,287 loops=1)

  • Recheck Cond: (annee_exercice = 2019)
  • Heap Blocks: exact=9
15. 0.059 0.059 ↓ 1.0 1,339 1

Bitmap Index Scan on exercice_organisme_annee_exercice_idx1 (cost=0.00..25.94 rows=1,287 width=0) (actual time=0.059..0.059 rows=1,339 loops=1)

  • Index Cond: (annee_exercice = 2019)
16. 0.146 0.318 ↑ 1.0 1,331 1

Hash (cost=24.31..24.31 rows=1,331 width=8) (actual time=0.318..0.318 rows=1,331 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
17. 0.172 0.172 ↑ 1.0 1,331 1

Seq Scan on role r (cost=0.00..24.31 rows=1,331 width=8) (actual time=0.012..0.172 rows=1,331 loops=1)

18. 60.348 60.348 ↑ 15.2 229 1,284

Index Scan using unite_evaluation_role_idx1 on unite_evaluation ue (cost=0.42..113.63 rows=3,475 width=63) (actual time=0.002..0.047 rows=229 loops=1,284)

  • Index Cond: (role = r.id)
19. 0.195 0.433 ↑ 1.0 1,658 1

Hash (cost=38.58..38.58 rows=1,658 width=8) (actual time=0.433..0.433 rows=1,658 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 87kB
20. 0.238 0.238 ↑ 1.0 1,658 1

Seq Scan on domaine_valeur cinr (cost=0.00..38.58 rows=1,658 width=8) (actual time=0.007..0.238 rows=1,658 loops=1)

21. 0.202 0.381 ↑ 1.0 1,658 1

Hash (cost=38.58..38.58 rows=1,658 width=8) (actual time=0.381..0.381 rows=1,658 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 87kB
22. 0.179 0.179 ↑ 1.0 1,658 1

Seq Scan on domaine_valeur cii (cost=0.00..38.58 rows=1,658 width=8) (actual time=0.004..0.179 rows=1,658 loops=1)

23. 0.190 0.367 ↑ 1.0 1,658 1

Hash (cost=38.58..38.58 rows=1,658 width=8) (actual time=0.367..0.367 rows=1,658 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 87kB
24. 0.177 0.177 ↑ 1.0 1,658 1

Seq Scan on domaine_valeur cub (cost=0.00..38.58 rows=1,658 width=8) (actual time=0.003..0.177 rows=1,658 loops=1)

25. 0.191 0.367 ↑ 1.0 1,658 1

Hash (cost=38.58..38.58 rows=1,658 width=8) (actual time=0.367..0.367 rows=1,658 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 87kB
26. 0.176 0.176 ↑ 1.0 1,658 1

Seq Scan on domaine_valeur cza (cost=0.00..38.58 rows=1,658 width=8) (actual time=0.003..0.176 rows=1,658 loops=1)