explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h07r

Settings
# exclusive inclusive rows x rows loops node
1. 12.875 9,380.628 ↑ 22.8 1,755 1

Sort (cost=99,323.27..99,423.20 rows=39,972 width=289) (actual time=9,379.291..9,380.628 rows=1,755 loops=1)

  • Sort Key: load_prop.property_name, (CASE WHEN (pf.floorplan_name IS NOT NULL) THEN 1 ELSE 2 END), (COALESCE(pf.floorplan_name, 'Unknown'::character varying))
  • Sort Method: quicksort Memory: 515kB
2. 183.476 9,367.753 ↑ 22.8 1,755 1

HashAggregate (cost=95,568.56..96,268.07 rows=39,972 width=289) (actual time=9,364.047..9,367.753 rows=1,755 loops=1)

  • Group Key: cusp.cid, cusp.property_id, load_prop.property_name, cusp.property_floorplan_id, pf.floorplan_name
3. 76.395 9,184.277 ↓ 1.0 40,468 1

Hash Left Join (cost=5,834.55..92,620.63 rows=39,972 width=78) (actual time=463.545..9,184.277 rows=40,468 loops=1)

  • Hash Cond: ((cusp.cid = md.cid) AND (cusp.unit_space_id = md.unit_space_id))
4. 85.809 9,105.471 ↓ 1.0 40,468 1

Hash Left Join (cost=5,777.91..92,247.45 rows=39,972 width=74) (actual time=461.115..9,105.471 rows=40,468 loops=1)

  • Hash Cond: ((cusp.cid = mimod.cid) AND (cusp.unit_space_id = mimod.unit_space_id))
5. 71.793 8,932.291 ↓ 1.0 40,468 1

Hash Left Join (cost=4,452.47..90,235.93 rows=39,972 width=58) (actual time=373.643..8,932.291 rows=40,468 loops=1)

  • Hash Cond: ((cusp.cid = pf.cid) AND (cusp.property_floorplan_id = pf.id))
6. 79.762 8,842.020 ↓ 1.0 40,468 1

Hash Join (cost=4,141.33..89,714.84 rows=39,972 width=42) (actual time=355.138..8,842.020 rows=40,468 loops=1)

  • Hash Cond: (cusp.property_id = load_prop.property_id)
7. 160.044 8,762.035 ↓ 1.0 40,468 1

Nested Loop (cost=4,135.77..89,159.67 rows=39,972 width=21) (actual time=354.906..8,762.035 rows=40,468 loops=1)

8. 74.310 467.923 ↓ 1.0 40,468 1

Hash Join (cost=4,135.34..5,123.42 rows=40,045 width=24) (actual time=353.562..467.923 rows=40,468 loops=1)

  • Hash Cond: (cusp.property_unit_id = pu.id)
9. 40.239 40.239 ↑ 1.0 40,468 1

Seq Scan on cached_unit_space_periods_temp cusp (cost=0.00..881.85 rows=40,468 width=32) (actual time=0.009..40.239 rows=40,468 loops=1)

  • Filter: (cid = 3,482)
10. 114.056 353.374 ↓ 1.0 80,515 1

Hash (cost=3,129.49..3,129.49 rows=80,468 width=8) (actual time=353.373..353.374 rows=80,515 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,170kB
11. 239.318 239.318 ↓ 1.0 80,515 1

Seq Scan on property_units pu (cost=0.00..3,129.49 rows=80,468 width=8) (actual time=1.009..239.318 rows=80,515 loops=1)

  • Filter: (cid = 3,482)
  • Rows Removed by Filter: 805
12. 8,134.068 8,134.068 ↑ 1.0 1 40,468

Index Scan using idx_unit_space_logs_id on unit_space_logs usl (cost=0.43..2.10 rows=1 width=9) (actual time=0.201..0.201 rows=1 loops=40,468)

  • Index Cond: (id = cusp.unit_space_log_id)
  • Filter: (cid = 3,482)
13. 0.112 0.223 ↑ 1.0 158 1

Hash (cost=3.58..3.58 rows=158 width=25) (actual time=0.223..0.223 rows=158 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
14. 0.111 0.111 ↑ 1.0 158 1

Seq Scan on load_prop (cost=0.00..3.58 rows=158 width=25) (actual time=0.012..0.111 rows=158 loops=1)

15. 4.179 18.478 ↑ 1.0 4,553 1

Hash (cost=240.91..240.91 rows=4,682 width=24) (actual time=18.478..18.478 rows=4,553 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 324kB
16. 14.299 14.299 ↑ 1.0 4,553 1

Seq Scan on property_floorplans pf (cost=0.00..240.91 rows=4,682 width=24) (actual time=0.858..14.299 rows=4,553 loops=1)

  • Filter: (cid = 3,482)
  • Rows Removed by Filter: 186
17. 45.887 87.371 ↑ 1.0 39,107 1

Hash (cost=738.84..738.84 rows=39,107 width=24) (actual time=87.371..87.371 rows=39,107 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,651kB
18. 41.484 41.484 ↑ 1.0 39,107 1

Seq Scan on move_in_move_out_data_temp mimod (cost=0.00..738.84 rows=39,107 width=24) (actual time=0.013..41.484 rows=39,107 loops=1)

  • Filter: (cid = 3,482)
19. 1.206 2.411 ↑ 1.0 1,696 1

Hash (cost=31.20..31.20 rows=1,696 width=16) (actual time=2.410..2.411 rows=1,696 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 96kB
20. 1.205 1.205 ↑ 1.0 1,696 1

Seq Scan on maintenance_data_temp md (cost=0.00..31.20 rows=1,696 width=16) (actual time=0.014..1.205 rows=1,696 loops=1)

  • Filter: (cid = 3,482)