explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WZkl

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 404.084 ↓ 0.0 0 1

Update on avail_update_details details (cost=22,566.23..24,315.99 rows=1 width=216) (actual time=404.084..404.084 rows=0 loops=1)

2. 45.823 404.075 ↓ 0.0 0 1

Merge Join (cost=22,566.23..24,315.99 rows=1 width=216) (actual time=404.075..404.075 rows=0 loops=1)

  • Merge Cond: ((details.date = maxdetail.date) AND (details.building_id = maxdetail.building_id) AND (details.category_id = maxdetail.category_id) AND (details.category_ratetype_id = maxdetail.category_ratetype_id) AND (details.category_occupancy_id = maxdetail.category_occupancy_id) AND (details.board_indicative_id = maxdetail.board_indicative_id) AND (details.applyon = maxdetail.applyon))
  • Join Filter: (details.id <> maxdetail.maxid)
  • Rows Removed by Join Filter: 27968
3. 94.106 129.338 ↑ 1.8 27,968 1

Sort (cost=11,685.41..11,811.89 rows=50,591 width=131) (actual time=111.047..129.338 rows=27,968 loops=1)

  • Sort Key: details.date, details.building_id, details.category_id, details.category_ratetype_id, details.category_occupancy_id, details.board_indicative_id, details.applyon
  • Sort Method: external merge Disk: 3296kB
4. 35.232 35.232 ↑ 1.8 27,968 1

Index Scan using avail_update_details_outdated_index on avail_update_details details (cost=0.42..4,271.09 rows=50,591 width=131) (actual time=0.023..35.232 rows=27,968 loops=1)

  • Index Cond: (outdated = false)
  • Filter: ((NOT outdated) AND (record_type = 5) AND (property_id = 1))
  • Rows Removed by Filter: 26544
5. 27.320 228.914 ↑ 1.2 27,968 1

Materialize (cost=10,880.81..11,044.80 rows=32,797 width=144) (actual time=174.693..228.914 rows=27,968 loops=1)

6. 89.447 201.594 ↑ 1.2 27,968 1

Sort (cost=10,880.81..10,962.80 rows=32,797 width=144) (actual time=174.689..201.594 rows=27,968 loops=1)

  • Sort Key: maxdetail.date, maxdetail.building_id, maxdetail.category_id, maxdetail.category_ratetype_id, maxdetail.category_occupancy_id, maxdetail.board_indicative_id, maxdetail.applyon
  • Sort Method: external merge Disk: 4448kB
7. 33.727 112.147 ↑ 1.2 27,968 1

Subquery Scan on maxdetail (cost=5,409.39..6,065.33 rows=32,797 width=144) (actual time=52.598..112.147 rows=27,968 loops=1)

8. 51.756 78.420 ↑ 1.2 27,968 1

HashAggregate (cost=5,409.39..5,737.36 rows=32,797 width=60) (actual time=52.590..78.420 rows=27,968 loops=1)

  • Group Key: maxdetail_1.date, maxdetail_1.building_id, maxdetail_1.category_id, maxdetail_1.category_ratetype_id, maxdetail_1.category_occupancy_id, maxdetail_1.board_indicative_id, maxdetail_1.applyon, maxdetail_1.record_type
9. 26.664 26.664 ↑ 1.8 27,968 1

Index Scan using avail_update_details_outdated_index on avail_update_details maxdetail_1 (cost=0.42..4,271.09 rows=50,591 width=60) (actual time=0.056..26.664 rows=27,968 loops=1)

  • Index Cond: (outdated = false)
  • Filter: ((NOT outdated) AND (record_type = 5) AND (property_id = 1))
  • Rows Removed by Filter: 26544
Planning time : 0.467 ms
Execution time : 407.213 ms