explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hIud

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 471.670 ↓ 0.0 0 1

Update on avail_update_details details (cost=27,489.71..30,486.71 rows=1 width=249) (actual time=471.670..471.670 rows=0 loops=1)

2. 40.418 471.667 ↓ 0.0 0 1

Merge Join (cost=27,489.71..30,486.71 rows=1 width=249) (actual time=471.667..471.667 rows=0 loops=1)

  • Merge Cond: ((maxdetail.date = details.date) AND (maxdetail.category_id = details.category_id) AND (maxdetail.category_ratetype_id = details.category_ratetype_id) AND (maxdetail.category_occupancy_id = details.category_occupancy_id) AND (maxdetail.board_indicative_id = details.board_indicative_id) AND (maxdetail.applyon = details.applyon))
  • Join Filter: (details.id <> maxdetail.maxid)
  • Rows Removed by Join Filter: 20876
3. 36.573 231.749 ↑ 1.1 27,968 1

Subquery Scan on maxdetail (cost=11,928.35..13,561.90 rows=31,895 width=136) (actual time=138.369..231.749 rows=27,968 loops=1)

4. 37.437 195.176 ↑ 1.1 27,968 1

GroupAggregate (cost=11,928.35..13,242.95 rows=31,895 width=56) (actual time=138.347..195.176 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
5. 80.368 157.739 ↑ 1.6 27,968 1

Sort (cost=11,928.35..12,038.98 rows=44,251 width=56) (actual time=138.337..157.739 rows=27,968 loops=1)

  • Sort Key: maxdetail_1.date, maxdetail_1.category_id, maxdetail_1.category_ratetype_id, maxdetail_1.category_occupancy_id, maxdetail_1.board_indicative_id, maxdetail_1.applyon
  • Sort Method: external merge Disk: 1976kB
6. 77.371 77.371 ↑ 1.6 27,968 1

Index Scan using avail_update_details_outdated_index on avail_update_details maxdetail_1 (cost=0.42..8,513.63 rows=44,251 width=56) (actual time=18.783..77.371 rows=27,968 loops=1)

  • Index Cond: (outdated = false)
  • Filter: ((NOT outdated) AND ((avail_update_type)::text = 'App\Models\AvailUpdate'::text) AND (building_id = 1) AND (salesclose = 0) AND (minimumstay = 0) AND (daysrelease = 0) AND (checkinallowed = 0))
  • Rows Removed by Filter: 55080
7. 28.359 199.500 ↑ 1.6 27,967 1

Materialize (cost=15,561.35..15,782.61 rows=44,251 width=168) (actual time=145.780..199.500 rows=27,967 loops=1)

8. 107.008 171.141 ↑ 1.6 27,967 1

Sort (cost=15,561.35..15,671.98 rows=44,251 width=168) (actual time=145.776..171.141 rows=27,967 loops=1)

  • Sort Key: details.date, details.category_id, details.category_ratetype_id, details.category_occupancy_id, details.board_indicative_id, details.applyon
  • Sort Method: external merge Disk: 4120kB
9. 64.133 64.133 ↑ 1.6 27,968 1

Index Scan using avail_update_details_outdated_index on avail_update_details details (cost=0.42..8,513.63 rows=44,251 width=168) (actual time=8.681..64.133 rows=27,968 loops=1)

  • Index Cond: (outdated = false)
  • Filter: ((NOT outdated) AND ((avail_update_type)::text = 'App\Models\AvailUpdate'::text) AND (building_id = 1) AND (salesclose = 0) AND (minimumstay = 0) AND (daysrelease = 0) AND (checkinallowed = 0))
  • Rows Removed by Filter: 55080
Planning time : 0.599 ms
Execution time : 475.975 ms