explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8JGCr

Settings
# exclusive inclusive rows x rows loops node
1. 5.900 5.969 ↑ 1.0 1 1

CTE Scan on prev_ts prv (cost=11.55..11.57 rows=1 width=33) (actual time=5.968..5.969 rows=1 loops=1)

2.          

CTE next_ts

3. 0.001 5.838 ↑ 1.0 1 1

Limit (cost=0.42..8.02 rows=1 width=12) (actual time=5.837..5.838 rows=1 loops=1)

4. 5.837 5.837 ↑ 1,441.0 1 1

Index Scan using timeslot_location_id_time_status_idx on timeslot ts (cost=0.42..10,951.89 rows=1,441 width=12) (actual time=5.837..5.837 rows=1 loops=1)

  • Index Cond: (location_id = 21)
  • Filter: ((item_category_id = 1) AND (status = ANY ('{PUBLISHED,BOOKED,"MARKED TO DELETE"}'::text[])) AND (now() < ("time" + ('00:01:00'::interval * (duration)::double precision))))
  • Rows Removed by Filter: 9,581
5.          

CTE prev_ts

6. 0.000 5.894 ↑ 1.0 1 1

Limit (cost=0.44..3.23 rows=1 width=16) (actual time=5.894..5.894 rows=1 loops=1)

7.          

Initplan (for Limit)

8. 5.843 5.843 ↑ 1.0 1 1

CTE Scan on next_ts (cost=0.00..0.02 rows=1 width=8) (actual time=5.842..5.843 rows=1 loops=1)

9. 5.893 5.893 ↑ 1,441.0 1 1

Index Scan Backward using timeslot_time_index on timeslot ts_1 (cost=0.42..4,023.37 rows=1,441 width=16) (actual time=5.893..5.893 rows=1 loops=1)

  • Index Cond: ("time" < $1)
  • Filter: ((location_id = 21) AND (item_category_id = 1) AND (status = ANY ('{PUBLISHED,BOOKED,"MARKED TO DELETE"}'::text[])))
  • Rows Removed by Filter: 32
10.          

Initplan (for CTE Scan)

11. 0.068 0.068 ↑ 1.0 1 1

CTE Scan on next_ts nxt (cost=0.00..0.28 rows=1 width=32) (actual time=0.068..0.068 rows=1 loops=1)

12. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on next_ts next_ts_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)