explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cv7l

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

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

2. 42.443 422.042 ↓ 0.0 0 1

Merge Join (cost=27,489.71..30,486.71 rows=1 width=249) (actual time=422.042..422.042 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. 34.177 183.995 ↑ 1.1 27,968 1

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

4. 43.554 149.818 ↑ 1.1 27,968 1

GroupAggregate (cost=11,928.35..13,242.95 rows=31,895 width=56) (actual time=89.470..149.818 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. 56.123 106.264 ↑ 1.6 27,968 1

Sort (cost=11,928.35..12,038.98 rows=44,251 width=56) (actual time=89.460..106.264 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. 50.141 50.141 ↑ 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=12.200..50.141 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. 27.975 195.604 ↑ 1.6 27,967 1

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

8. 110.143 167.629 ↑ 1.6 27,967 1

Sort (cost=15,561.35..15,671.98 rows=44,251 width=168) (actual time=147.747..167.629 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. 57.486 57.486 ↑ 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=9.683..57.486 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.802 ms
Execution time : 425.460 ms