explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bX1R

Settings
# exclusive inclusive rows x rows loops node
1. 914.852 84,393.145 ↓ 1.0 3,626,770 1

Unique (cost=4,033,740.97..4,178,580.77 rows=3,620,995 width=93) (actual time=82,120.895..84,393.145 rows=3,626,770 loops=1)

2.          

CTE test

3. 15,244.319 51,713.009 ↓ 1.0 41,324,841 1

Hash Join (cost=459.38..1,927,094.79 rows=41,095,470 width=48) (actual time=6.564..51,713.009 rows=41,324,841 loops=1)

  • Hash Cond: (ue_1.role = r.id)
4. 36,464.981 36,464.981 ↓ 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=2.804..36,464.981 rows=41,324,841 loops=1)

5. 1.816 3.709 ↑ 1.0 13,706 1

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

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

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

7. 880.765 83,478.293 ↓ 1.0 3,626,770 1

Group (cost=2,106,646.18..2,188,118.57 rows=3,620,995 width=93) (actual time=82,120.893..83,478.293 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
8. 5,644.681 82,597.528 ↓ 1.0 3,626,770 1

Sort (cost=2,106,646.18..2,115,698.67 rows=3,620,995 width=93) (actual time=82,120.888..82,597.528 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
9. 5,432.961 76,952.847 ↓ 1.0 3,626,770 1

Hash Join (cost=167.21..1,340,881.81 rows=3,620,995 width=93) (actual time=3,666.205..76,952.847 rows=3,626,770 loops=1)

  • Hash Cond: (ue.exercice_organisme = e.id)
10. 71,519.326 71,519.326 ↓ 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=6.567..71,519.326 rows=41,324,841 loops=1)

11. 0.191 0.560 ↑ 1.0 1,280 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
12. 0.294 0.369 ↑ 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.085..0.369 rows=1,280 loops=1)

  • Recheck Cond: (annee_exercice = 2019)
  • Heap Blocks: exact=12
13. 0.075 0.075 ↑ 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.075..0.075 rows=1,280 loops=1)

  • Index Cond: (annee_exercice = 2019)
Planning time : 0.891 ms
Execution time : 84,573.270 ms