explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8u3O

Settings
# exclusive inclusive rows x rows loops node
1. 875.216 74,708.444 ↓ 1.0 3,626,770 1

Group (cost=4,033,740.97..4,115,213.36 rows=3,620,995 width=93) (actual time=73,363.217..74,708.444 rows=3,626,770 loops=1)

  • Group Key: ue.id, e.code_geo, e.annee_exercice, ue.matricule_unique, ue.valeur_terrain, ue.valeur_batiment, ue.valeur_immeuble, ue.est_terrain_vague
2.          

CTE test

3. 15,077.491 45,174.149 ↓ 1.0 41,324,841 1

Hash Join (cost=459.38..1,927,094.79 rows=41,095,470 width=48) (actual time=3.639..45,174.149 rows=41,324,841 loops=1)

  • Hash Cond: (ue_1.role = r.id)
4. 30,093.048 30,093.048 ↓ 1.0 41,324,841 1

Seq Scan on unite_evaluation ue_1 (cost=0.00..1,407,831.70 rows=41,095,470 width=48) (actual time=0.005..30,093.048 rows=41,324,841 loops=1)

5. 1.831 3.610 ↑ 1.0 13,706 1

Hash (cost=288.06..288.06 rows=13,706 width=8) (actual time=3.610..3.610 rows=13,706 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 664kB
6. 1.779 1.779 ↑ 1.0 13,706 1

Seq Scan on role r (cost=0.00..288.06 rows=13,706 width=8) (actual time=0.007..1.779 rows=13,706 loops=1)

7. 5,226.142 73,833.228 ↓ 1.0 3,626,770 1

Sort (cost=2,106,646.18..2,115,698.67 rows=3,620,995 width=93) (actual time=73,363.211..73,833.228 rows=3,626,770 loops=1)

  • Sort Key: ue.id, e.code_geo, ue.matricule_unique, ue.valeur_terrain, ue.valeur_batiment, ue.valeur_immeuble, ue.est_terrain_vague
  • Sort Method: external merge Disk: 239208kB
8. 5,253.225 68,607.086 ↓ 1.0 3,626,770 1

Hash Join (cost=167.21..1,340,881.81 rows=3,620,995 width=93) (actual time=13,045.030..68,607.086 rows=3,626,770 loops=1)

  • Hash Cond: (ue.exercice_organisme = e.id)
9. 63,353.394 63,353.394 ↓ 1.0 41,324,841 1

CTE Scan on test ue (cost=0.00..821,909.40 rows=41,095,470 width=87) (actual time=3.641..63,353.394 rows=41,324,841 loops=1)

10. 0.182 0.467 ↑ 1.0 1,280 1

Hash (cost=151.21..151.21 rows=1,280 width=14) (actual time=0.467..0.467 rows=1,280 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
11. 0.220 0.285 ↑ 1.0 1,280 1

Bitmap Heap Scan on exercice_organisme e (cost=26.21..151.21 rows=1,280 width=14) (actual time=0.075..0.285 rows=1,280 loops=1)

  • Recheck Cond: (annee_exercice = 2019)
  • Heap Blocks: exact=12
12. 0.065 0.065 ↑ 1.0 1,280 1

Bitmap Index Scan on exercice_organisme_annee_exercice_idx (cost=0.00..25.89 rows=1,280 width=0) (actual time=0.065..0.065 rows=1,280 loops=1)

  • Index Cond: (annee_exercice = 2019)