explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KAv4

Settings
# exclusive inclusive rows x rows loops node
1. 0.685 2,189.277 ↑ 1.0 1 1

Index Scan using merchant_id on merchants m (cost=0.14..8,154.80 rows=1 width=770) (actual time=2,189.272..2,189.277 rows=1 loops=1)

  • Index Cond: (merchant_id = 266451)
2.          

SubPlan (forIndex Scan)

3. 48.896 2,188.592 ↑ 1.0 1 1

Aggregate (cost=8,144.49..8,144.51 rows=1 width=32) (actual time=2,188.591..2,188.592 rows=1 loops=1)

4. 2.920 2,139.696 ↓ 2,640.0 2,640 1

Subquery Scan on t (cost=8,144.47..8,144.49 rows=1 width=160) (actual time=2,136.195..2,139.696 rows=2,640 loops=1)

5. 2.880 2,136.776 ↓ 2,640.0 2,640 1

Sort (cost=8,144.47..8,144.48 rows=1 width=188) (actual time=2,136.174..2,136.776 rows=2,640 loops=1)

  • Sort Key: (abs(('1547600400'::double precision - date_part('epoch'::text, (inventory.times - '00:00:00'::interval)))))
  • Sort Method: quicksort Memory: 633kB
6.          

CTE parties

7. 0.007 23.274 ↓ 3.0 3 1

GroupAggregate (cost=301.95..301.99 rows=1 width=32) (actual time=23.270..23.274 rows=3 loops=1)

  • Group Key: (to_timestamp(((mp.reserved_ts / 1000))::double precision))
8. 0.015 23.267 ↓ 5.0 5 1

Sort (cost=301.95..301.95 rows=1 width=34) (actual time=23.265..23.267 rows=5 loops=1)

  • Sort Key: (to_timestamp(((mp.reserved_ts / 1000))::double precision))
  • Sort Method: quicksort Memory: 25kB
9. 22.983 23.252 ↓ 5.0 5 1

Bitmap Heap Scan on merchant_parties mp (cost=10.19..301.94 rows=1 width=34) (actual time=22.841..23.252 rows=5 loops=1)

  • Recheck Cond: (merchant_id = m.merchant_id)
  • Filter: ((deleted_ts IS NULL) AND ((reserved_ts)::double precision >= (date_part('epoch'::text, ((to_timestamp(((start_of_restaurant_day('00:00:00'::interval, '1547600400000'::bigint) / 1000))::double precision))::timestamp without time zone - '00:00:00'::interval)) * '1000'::double precision)) AND ((reserved_ts)::double precision <= (date_part('epoch'::text, ((((to_timestamp(((start_of_restaurant_day('00:00:00'::interval, '1547600400000'::bigint) / 1000))::double precision) + '1 day'::interval) - '00:15:00'::interval))::timestamp without time zone - '00:00:00'::interval)) * '1000'::double precision)))
  • Rows Removed by Filter: 5162
  • Heap Blocks: exact=143
10. 0.269 0.269 ↓ 21.4 5,444 1

Bitmap Index Scan on idx_merchant_parties_merchant (cost=0.00..10.19 rows=254 width=0) (actual time=0.269..0.269 rows=5,444 loops=1)

  • Index Cond: (merchant_id = m.merchant_id)
11.          

CTE time_series

12. 5.555 1,177.715 ↓ 2.9 2,880 1

Function Scan on generate_series times (cost=0.53..7,178.03 rows=1,000 width=32) (actual time=23.708..1,177.715 rows=2,880 loops=1)

13.          

SubPlan (forFunction Scan)

14. 11.520 1,143.360 ↓ 0.0 0 2,880

Limit (cost=7.12..7.12 rows=1 width=16) (actual time=0.389..0.397 rows=0 loops=2,880)

15.          

Initplan (forLimit)

16. 2.880 2.880 ↓ 0.0 0 2,880

Seq Scan on merchant_special_days msd (cost=0.00..1.03 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=2,880)

  • Filter: ((merchant_id = m.merchant_id) AND (date = (times.times)::date))
  • Rows Removed by Filter: 2
17. 2.880 2.880 ↓ 0.0 0 2,880

Seq Scan on merchant_special_days msd_1 (cost=0.00..1.03 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=2,880)

  • Filter: ((merchant_id = m.merchant_id) AND (date = (times.times)::date))
  • Rows Removed by Filter: 2
18. 23.040 1,126.080 ↓ 0.0 0 2,880

Sort (cost=5.05..5.05 rows=1 width=16) (actual time=0.389..0.391 rows=0 loops=2,880)

  • Sort Key: (CASE WHEN (ms.block_start < '05:00:00'::time without time zone) THEN (('2019-01-17 21:17:14.205406'::timestamp without time zone + (ms.block_start)::interval) + '24:00:00'::interval) ELSE ('2019-01-17 21:17:14.205406'::timestamp without time zone + (ms.block_start)::interval) END) DESC
  • Sort Method: quicksort Memory: 25kB
19. 24.192 1,103.040 ↓ 0.0 0 2,880

Nested Loop (cost=0.00..5.04 rows=1 width=16) (actual time=0.287..0.383 rows=0 loops=2,880)

  • Join Filter: (mss.merchant_shift_id = ms.merchant_shift_id)
  • Rows Removed by Join Filter: 1
20. 106.560 1,048.320 ↓ 2.0 2 2,880

Nested Loop Left Join (cost=0.00..3.38 rows=1 width=16) (actual time=0.073..0.364 rows=2 loops=2,880)

  • Join Filter: (mss.merchant_special_day_id = msd_2.merchant_special_day_id)
  • Rows Removed by Join Filter: 25
  • Filter: (($3 AND (msd_2.date = (times.times)::date)) OR ((NOT $4) AND (mss.merchant_special_day_id IS NULL) AND (date_part('dow'::text, times.times) = (CASE WHEN ((times.times)::time without time zone < '05:00:00'::time without time zone) THEN ((mss.day_of_week + 1) % 7) ELSE mss.day_of_week END)::double precision)))
  • Rows Removed by Filter: 11
21. 457.920 866.880 ↓ 13.0 13 2,880

Nested Loop (cost=0.00..2.29 rows=1 width=28) (actual time=0.010..0.301 rows=13 loops=2,880)

  • Join Filter: (mss.merchant_shift_id = ms_1.merchant_shift_id)
  • Rows Removed by Join Filter: 96
22. 51.840 51.840 ↓ 31.0 31 2,880

Seq Scan on merchant_shift_schedule mss (cost=0.00..1.19 rows=1 width=20) (actual time=0.001..0.018 rows=31 loops=2,880)

  • Filter: (deleted_ts IS NULL)
  • Rows Removed by Filter: 1
23. 357.120 357.120 ↓ 4.0 4 89,280

Seq Scan on merchant_shifts ms_1 (cost=0.00..1.09 rows=1 width=8) (actual time=0.002..0.004 rows=4 loops=89,280)

  • Filter: (merchant_id = m.merchant_id)
  • Rows Removed by Filter: 2
24. 74.880 74.880 ↑ 1.0 2 37,440

Seq Scan on merchant_special_days msd_2 (cost=0.00..1.02 rows=2 width=12) (actual time=0.001..0.002 rows=2 loops=37,440)

25. 30.528 30.528 ↑ 1.0 1 5,088

Seq Scan on merchant_shifts ms (cost=0.00..1.63 rows=1 width=16) (actual time=0.004..0.006 rows=1 loops=5,088)

  • Filter: ((merchant_id = 266451) AND (((block_start >= '05:00:00'::time without time zone) AND ((block_start + block_duration) < '24:00:00'::time without time zone) AND ((times.times)::time without time zone >= block_start) AND ((times.times)::time without time zone <= (block_start + block_duration))) OR ((block_start > '00:00:00'::time without time zone) AND ((block_start + block_duration) < '05:00:00'::time without time zone) AND ((times.times)::time without time zone >= block_start) AND ((times.times)::time without time zone <= (block_start + block_duration))) OR ((block_start >= '05:00:00'::time without time zone) AND ((block_start + block_duration) < '05:00:00'::time without time zone) AND ((times.times)::time without time zone < '24:00:00'::time without time zone) AND ((times.times)::time without time zone >= block_start)) OR ((block_start >= '05:00:00'::time without time zone) AND ((block_start + block_duration) < '05:00:00'::time without time zone) AND ((times.times)::time without time zone >= '00:00:00'::time without time zone) AND ((times.times)::time without time zone <= (block_start + block_duration)))))
  • Rows Removed by Filter: 6
26. 25.920 25.920 ↓ 0.0 0 2,880

CTE Scan on parties p (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=2,880)

  • Filter: (bucket = times.times)
  • Rows Removed by Filter: 3
27. 2.880 2.880 ↓ 0.0 0 2,880

CTE Scan on parties p_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=2,880)

  • Filter: (bucket = times.times)
  • Rows Removed by Filter: 3
28.          

CTE inventory

29. 3.653 2,119.682 ↓ 2,640.0 2,640 1

Sort (cost=664.38..664.39 rows=1 width=146) (actual time=2,118.920..2,119.682 rows=2,640 loops=1)

  • Sort Key: series.times
  • Sort Method: quicksort Memory: 633kB
30. 12.960 2,116.029 ↓ 2,640.0 2,640 1

Hash Left Join (cost=64.21..664.37 rows=1 width=146) (actual time=1,564.892..2,116.029 rows=2,640 loops=1)

  • Hash Cond: (mrt_1.reservation_type_id = mri15.reservation_type_id)
  • Join Filter: (((mri15.block_start)::time without time zone <= (series.times)::time without time zone) AND ((mri15.block_end)::time without time zone > (series.times)::time without time zone) AND ((mri15.expires_ts IS NULL) OR ((mri15.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))) AND ((((mri15.day_of_week)::double precision = date_part('dow'::text, series.times)) AND ((series.times)::time without time zone > '05:00:00'::time without time zone)) OR (((((mri15.day_of_week + 1) % 7))::double precision = date_part('dow'::text, series.times)) AND ((series.times)::time without time zone <= '05:00:00'::time without time zone))))
  • Rows Removed by Join Filter: 1320
31. 55.221 2,103.041 ↓ 2,640.0 2,640 1

Nested Loop Left Join (cost=61.52..77.67 rows=1 width=351) (actual time=1,564.842..2,103.041 rows=2,640 loops=1)

  • Join Filter: (((mri12.block_start)::time without time zone <= (series.times)::time without time zone) AND ((mri12.block_end)::time without time zone > (series.times)::time without time zone) AND ((mri12.expires_ts IS NULL) OR ((mri12.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))) AND ((((mri12.day_of_week)::double precision = date_part('dow'::text, series.times)) AND ((series.times)::time without time zone > '05:00:00'::time without time zone)) OR (((((mri12.day_of_week + 1) % 7))::double precision = date_part('dow'::text, series.times)) AND ((series.times)::time without time zone <= '05:00:00'::time without time zone))))
  • Rows Removed by Join Filter: 81776
32. 44.210 1,955.420 ↓ 2,640.0 2,640 1

Nested Loop Left Join (cost=61.52..73.91 rows=1 width=333) (actual time=1,564.807..1,955.420 rows=2,640 loops=1)

  • Join Filter: (((mri10.expires_ts IS NULL) OR ((mri10.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))) AND ((((mri10.day_of_week)::double precision = date_part('dow'::text, series.times)) AND ((series.times)::time without time zone > '05:00:00'::time without time zone)) OR (((((mri10.day_of_week + 1) % 7))::double precision = date_part('dow'::text, series.times)) AND ((series.times)::time without time zone <= '05:00:00'::time without time zone))))
  • Rows Removed by Join Filter: 36960
33. 19.691 1,845.210 ↓ 2,640.0 2,640 1

Nested Loop Left Join (cost=61.52..71.08 rows=1 width=315) (actual time=1,564.778..1,845.210 rows=2,640 loops=1)

  • Join Filter: (((mri8.block_start)::time without time zone <= (series.times)::time without time zone) AND ((mri8.block_end)::time without time zone > (series.times)::time without time zone) AND ((mri8.expires_ts IS NULL) OR ((mri8.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))))
  • Rows Removed by Join Filter: 12576
34. 74.105 1,759.519 ↓ 2,640.0 2,640 1

Nested Loop Left Join (cost=61.52..68.53 rows=1 width=297) (actual time=1,564.759..1,759.519 rows=2,640 loops=1)

  • Join Filter: (((mri7.block_start)::time without time zone <= (series.times)::time without time zone) AND ((mri7.block_end)::time without time zone > (series.times)::time without time zone) AND ((mri7.expires_ts IS NULL) OR ((mri7.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))) AND ((((mri7.day_of_year)::double precision = date_part('day'::text, series.times)) AND ((mri7.month_of_year)::double precision = date_part('month'::text, series.times)) AND ((mri7.year)::double precision = date_part('year'::text, series.times)) AND ((series.times)::time without time zone > '05:00:00'::time without time zone)) OR (((mri7.day_of_year)::double precision = date_part('day'::text, (series.times - '1 day'::interval))) AND ((mri7.month_of_year)::double precision = date_part('month'::text, (series.times - '1 day'::interval))) AND ((mri7.year)::double precision = date_part('year'::text, (series.times - '1 day'::interval))) AND ((series.times)::time without time zone <= '05:00:00'::time without time zone))))
  • Rows Removed by Join Filter: 81840
35. 2.165 1,566.614 ↓ 2,640.0 2,640 1

Hash Right Join (cost=61.52..64.17 rows=1 width=279) (actual time=1,564.722..1,566.614 rows=2,640 loops=1)

  • Hash Cond: ((mri11.block_time)::time without time zone = (series.times)::time without time zone)
  • Join Filter: (((mri11.expires_ts IS NULL) OR ((mri11.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))) AND ((((mri11.day_of_week)::double precision = date_part('dow'::text, series.times)) AND ((series.times)::time without time zone > '05:00:00'::time without time zone)) OR (((((mri11.day_of_week + 1) % 7))::double precision = date_part('dow'::text, series.times)) AND ((series.times)::time without time zone <= '05:00:00'::time without time zone))))
  • Rows Removed by Join Filter: 406
36. 0.024 0.024 ↓ 1.7 31 1

Seq Scan on merchant_reservation_inventory mri11 (cost=0.00..2.46 rows=18 width=35) (actual time=0.007..0.024 rows=31 loops=1)

  • Filter: ((reservation_type_id IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 6
37. 1.402 1,564.425 ↓ 2,640.0 2,640 1

Hash (cost=61.51..61.51 rows=1 width=261) (actual time=1,564.425..1,564.425 rows=2,640 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 364kB
38. 2.067 1,563.023 ↓ 2,640.0 2,640 1

Hash Right Join (cost=58.88..61.51 rows=1 width=261) (actual time=1,561.833..1,563.023 rows=2,640 loops=1)

  • Hash Cond: (mri13.reservation_type_id = mrt_1.reservation_type_id)
  • Join Filter: (((mri13.block_start)::time without time zone <= (series.times)::time without time zone) AND ((mri13.block_end)::time without time zone > (series.times)::time without time zone) AND ((mri13.expires_ts IS NULL) OR ((mri13.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))) AND ((((mri13.day_of_year)::double precision = date_part('day'::text, series.times)) AND ((mri13.month_of_year)::double precision = date_part('month'::text, series.times)) AND ((mri13.year)::double precision = date_part('year'::text, series.times)) AND ((series.times)::time without time zone > '05:00:00'::time without time zone)) OR (((mri13.day_of_year)::double precision = date_part('day'::text, (series.times - '1 day'::interval))) AND ((mri13.month_of_year)::double precision = date_part('month'::text, (series.times - '1 day'::interval))) AND ((mri13.year)::double precision = date_part('year'::text, (series.times - '1 day'::interval))) AND ((series.times)::time without time zone <= '05:00:00'::time without time zone))))
  • Rows Removed by Join Filter: 1320
39. 0.023 0.023 ↓ 1.8 32 1

Seq Scan on merchant_reservation_inventory mri13 (cost=0.00..2.46 rows=18 width=56) (actual time=0.007..0.023 rows=32 loops=1)

  • Filter: (merchant_id = m.merchant_id)
  • Rows Removed by Filter: 5
40. 1.270 1,560.933 ↓ 2,640.0 2,640 1

Hash (cost=58.86..58.86 rows=1 width=243) (actual time=1,560.933..1,560.933 rows=2,640 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 364kB
41. 1.110 1,559.663 ↓ 2,640.0 2,640 1

Hash Right Join (cost=56.28..58.86 rows=1 width=243) (actual time=1,558.571..1,559.663 rows=2,640 loops=1)

  • Hash Cond: (mri14.reservation_type_id = mrt_1.reservation_type_id)
  • Join Filter: (((mri14.expires_ts IS NULL) OR ((mri14.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))) AND ((((mri14.day_of_year)::double precision = date_part('day'::text, series.times)) AND ((mri14.month_of_year)::double precision = date_part('month'::text, series.times)) AND ((mri14.year)::double precision = date_part('year'::text, series.times)) AND ((series.times)::time without time zone > '05:00:00'::time without time zone)) OR (((mri14.day_of_year)::double precision = date_part('day'::text, (series.times - '1 day'::interval))) AND ((mri14.month_of_year)::double precision = date_part('month'::text, (series.times - '1 day'::interval))) AND ((mri14.year)::double precision = date_part('year'::text, (series.times - '1 day'::interval))) AND ((series.times)::time without time zone <= '05:00:00'::time without time zone))))
42. 0.018 0.018 ↓ 2.7 24 1

Seq Scan on merchant_reservation_inventory mri14 (cost=0.00..2.46 rows=9 width=46) (actual time=0.006..0.018 rows=24 loops=1)

  • Filter: ((block_start IS NULL) AND (block_end IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 13
43. 2.352 1,558.535 ↓ 2,640.0 2,640 1

Hash (cost=56.27..56.27 rows=1 width=225) (actual time=1,558.534..1,558.535 rows=2,640 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 364kB
44. 41.358 1,556.183 ↓ 2,640.0 2,640 1

Nested Loop Left Join (cost=44.98..56.27 rows=1 width=225) (actual time=1,215.920..1,556.183 rows=2,640 loops=1)

  • Join Filter: ((mri5.reservation_type_id = mrt_1.reservation_type_id) AND ((mri5.block_time)::time without time zone = (series.times)::time without time zone) AND ((mri5.expires_ts IS NULL) OR ((mri5.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))))
  • Rows Removed by Join Filter: 68640
45. 54.446 1,417.145 ↓ 2,640.0 2,640 1

Nested Loop Left Join (cost=44.98..53.28 rows=1 width=207) (actual time=1,215.897..1,417.145 rows=2,640 loops=1)

  • Join Filter: (((mri3.expires_ts IS NULL) OR ((mri3.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))) AND ((((mri3.day_of_year)::double precision = date_part('day'::text, series.times)) AND ((mri3.month_of_year)::double precision = date_part('month'::text, series.times)) AND ((mri3.year)::double precision = date_part('year'::text, series.times)) AND ((series.times)::time without time zone > '05:00:00'::time without time zone)) OR (((mri3.day_of_year)::double precision = date_part('day'::text, (series.times - '1 day'::interval))) AND ((mri3.month_of_year)::double precision = date_part('month'::text, (series.times - '1 day'::interval))) AND ((mri3.year)::double precision = date_part('year'::text, (series.times - '1 day'::interval))) AND ((series.times)::time without time zone <= '05:00:00'::time without time zone))))
  • Rows Removed by Join Filter: 36960
46. 13.204 1,286.139 ↓ 2,640.0 2,640 1

Nested Loop Left Join (cost=44.98..50.23 rows=1 width=189) (actual time=1,215.862..1,286.139 rows=2,640 loops=1)

  • Join Filter: (((mri.block_time)::time without time zone = (series.times)::time without time zone) AND ((mri.expires_ts IS NULL) OR ((mri.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))))
  • Rows Removed by Join Filter: 23760
47. 1.931 1,217.495 ↓ 2,640.0 2,640 1

Hash Right Join (cost=44.98..47.67 rows=1 width=171) (actual time=1,215.838..1,217.495 rows=2,640 loops=1)

  • Hash Cond: ((mri4.block_time)::time without time zone = (series.times)::time without time zone)
  • Join Filter: (((mri4.expires_ts IS NULL) OR ((mri4.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))) AND ((((mri4.day_of_year)::double precision = date_part('day'::text, series.times)) AND ((mri4.month_of_year)::double precision = date_part('month'::text, series.times)) AND ((mri4.year)::double precision = date_part('year'::text, series.times)) AND ((series.times)::time without time zone > '05:00:00'::time without time zone)) OR (((mri4.day_of_year)::double precision = date_part('day'::text, (series.times - '1 day'::interval))) AND ((mri4.month_of_year)::double precision = date_part('month'::text, (series.times - '1 day'::interval))) AND ((mri4.year)::double precision = date_part('year'::text, (series.times - '1 day'::interval))) AND ((series.times)::time without time zone <= '05:00:00'::time without time zone))))
  • Rows Removed by Join Filter: 406
48. 0.030 0.030 ↓ 1.7 31 1

Seq Scan on merchant_reservation_inventory mri4 (cost=0.00..2.46 rows=18 width=43) (actual time=0.012..0.030 rows=31 loops=1)

  • Filter: ((reservation_type_id IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 6
49. 1.212 1,215.534 ↓ 2,640.0 2,640 1

Hash (cost=44.97..44.97 rows=1 width=153) (actual time=1,215.534..1,215.534 rows=2,640 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 364kB
50. 9.811 1,214.322 ↓ 2,640.0 2,640 1

Hash Right Join (cost=42.41..44.97 rows=1 width=153) (actual time=1,204.566..1,214.322 rows=2,640 loops=1)

  • Hash Cond: (mri6.reservation_type_id = mrt_1.reservation_type_id)
  • Join Filter: (((mri6.block_start)::time without time zone <= (series.times)::time without time zone) AND ((mri6.block_end)::time without time zone > (series.times)::time without time zone) AND ((mri6.expires_ts IS NULL) OR ((mri6.expires_ts)::double precision > (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))))
  • Rows Removed by Join Filter: 1216
51. 0.026 0.026 ↓ 1.7 26 1

Seq Scan on merchant_reservation_inventory mri6 (cost=0.00..2.46 rows=15 width=44) (actual time=0.009..0.026 rows=26 loops=1)

  • Filter: ((day_of_week IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 11
52. 2.057 1,204.485 ↓ 2,640.0 2,640 1

Hash (cost=42.40..42.40 rows=1 width=135) (actual time=1,204.485..1,204.485 rows=2,640 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 362kB
53. 13.696 1,202.428 ↓ 2,640.0 2,640 1

Nested Loop (cost=1.25..42.40 rows=1 width=135) (actual time=35.101..1,202.428 rows=2,640 loops=1)

  • Join Filter: (srt.reservation_type_id = mrt_1.reservation_type_id)
  • Rows Removed by Join Filter: 6600
54. 2.363 1,183.452 ↓ 2,640.0 2,640 1

Hash Join (cost=1.25..41.31 rows=1 width=49) (actual time=35.093..1,183.452 rows=2,640 loops=1)

  • Hash Cond: (series.merchant_shift_id = srt.merchant_shift_id)
55. 1,181.073 1,181.073 ↓ 288.0 2,880 1

CTE Scan on time_series series (cost=0.00..40.00 rows=10 width=32) (actual time=23.710..1,181.073 rows=2,880 loops=1)

  • Filter: ((((times)::time without time zone >= '00:00:00'::time without time zone) AND ((times)::time without time zone < '05:00:00'::time without time zone)) OR (((times)::time without time zone >= '05:00:00'::time without time zone) AND ((times)::time without time zone <= '23:59:00'::time without time zone)))
56. 0.006 0.016 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=25) (actual time=0.016..0.016 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.010 0.010 ↑ 1.0 11 1

Seq Scan on shift_reservation_types srt (cost=0.00..1.11 rows=11 width=25) (actual time=0.006..0.010 rows=11 loops=1)

58. 5.280 5.280 ↑ 1.0 4 2,640

Seq Scan on merchant_reservation_types mrt_1 (cost=0.00..1.04 rows=4 width=86) (actual time=0.002..0.002 rows=4 loops=2,640)

59. 55.440 55.440 ↓ 3.0 9 2,640

Seq Scan on merchant_reservation_inventory mri (cost=0.00..2.46 rows=3 width=31) (actual time=0.005..0.021 rows=9 loops=2,640)

  • Filter: ((reservation_type_id IS NULL) AND (day_of_week IS NULL) AND (day_of_year IS NULL) AND (month_of_year IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 28
60. 76.560 76.560 ↓ 2.0 14 2,640

Seq Scan on merchant_reservation_inventory mri3 (cost=0.00..2.46 rows=7 width=38) (actual time=0.009..0.029 rows=14 loops=2,640)

  • Filter: ((reservation_type_id IS NULL) AND (block_time IS NULL) AND (block_start IS NULL) AND (block_end IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 23
61. 97.680 97.680 ↓ 1.7 26 2,640

Seq Scan on merchant_reservation_inventory mri5 (cost=0.00..2.46 rows=15 width=39) (actual time=0.006..0.037 rows=26 loops=2,640)

  • Filter: ((day_of_week IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 11
62. 118.800 118.800 ↓ 1.7 31 2,640

Seq Scan on merchant_reservation_inventory mri7 (cost=0.00..2.46 rows=18 width=48) (actual time=0.003..0.045 rows=31 loops=2,640)

  • Filter: ((reservation_type_id IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 6
63. 66.000 66.000 ↓ 2.5 5 2,640

Seq Scan on merchant_reservation_inventory mri8 (cost=0.00..2.46 rows=2 width=36) (actual time=0.017..0.025 rows=5 loops=2,640)

  • Filter: ((reservation_type_id IS NULL) AND (day_of_week IS NULL) AND (day_of_year IS NULL) AND (month_of_year IS NULL) AND (block_time IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 32
64. 66.000 66.000 ↓ 2.0 14 2,640

Seq Scan on merchant_reservation_inventory mri10 (cost=0.00..2.46 rows=7 width=30) (actual time=0.006..0.025 rows=14 loops=2,640)

  • Filter: ((reservation_type_id IS NULL) AND (block_time IS NULL) AND (block_start IS NULL) AND (block_end IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 23
65. 92.400 92.400 ↓ 1.7 31 2,640

Seq Scan on merchant_reservation_inventory mri12 (cost=0.00..2.46 rows=18 width=40) (actual time=0.005..0.035 rows=31 loops=2,640)

  • Filter: ((reservation_type_id IS NULL) AND (merchant_id = m.merchant_id))
  • Rows Removed by Filter: 6
66. 0.010 0.028 ↑ 18.0 1 1

Hash (cost=2.46..2.46 rows=18 width=48) (actual time=0.027..0.028 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 0.018 0.018 ↓ 1.8 32 1

Seq Scan on merchant_reservation_inventory mri15 (cost=0.00..2.46 rows=18 width=48) (actual time=0.004..0.018 rows=32 loops=1)

  • Filter: (merchant_id = m.merchant_id)
  • Rows Removed by Filter: 5
68.          

SubPlan (forHash Left Join)

69. 0.000 0.000 ↓ 0.0 0

CTE Scan on good g (cost=583.90..583.94 rows=1 width=0) (never executed)

  • Filter: (conflict IS FALSE)
70.          

CTE rt

71. 0.000 0.000 ↓ 0.0 0

Unique (cost=322.51..322.52 rows=2 width=32) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Sort (cost=322.51..322.52 rows=2 width=32) (never executed)

  • Sort Key: ((SubPlan 10))
73. 0.000 0.000 ↓ 0.0 0

Append (cost=134.49..322.50 rows=2 width=32) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=134.49..140.88 rows=1 width=32) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=134.36..140.37 rows=2 width=16) (never executed)

  • Hash Cond: (mtr.merchant_table_id = mrt.mrt_id)
76. 0.000 0.000 ↓ 0.0 0

Seq Scan on merchant_table_rooms mtr (cost=126.00..131.54 rows=175 width=16) (never executed)

  • Filter: ((deleted_ts IS NULL) AND ((NOT (hashed SubPlan 11)) OR (hashed SubPlan 12)))
77.          

SubPlan (forSeq Scan)

78. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.21..62.99 rows=4 width=8) (never executed)

79. 0.000 0.000 ↓ 0.0 0

Seq Scan on merchant_shift_exception_groups mseg_2 (cost=0.00..29.25 rows=4 width=8) (never executed)

  • Filter: ((is_reservable IS FALSE) AND (shift_reservation_type_id = srt.shift_reservation_type_id))
80. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on exception_group_tables egt (cost=1.21..8.36 rows=7 width=16) (never executed)

  • Recheck Cond: (exception_group_id = mseg_2.exception_group_id)
81. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on exception_group_tables_unique (cost=0.00..1.21 rows=7 width=0) (never executed)

  • Index Cond: (exception_group_id = mseg_2.exception_group_id)
82. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.21..62.99 rows=4 width=8) (never executed)

83. 0.000 0.000 ↓ 0.0 0

Seq Scan on merchant_shift_exception_groups mseg_3 (cost=0.00..29.25 rows=4 width=8) (never executed)

  • Filter: ((is_reservable IS TRUE) AND (shift_reservation_type_id = srt.shift_reservation_type_id))
84. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on exception_group_tables egt_1 (cost=1.21..8.36 rows=7 width=16) (never executed)

  • Recheck Cond: (exception_group_id = mseg_3.exception_group_id)
85. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on exception_group_tables_unique (cost=0.00..1.21 rows=7 width=0) (never executed)

  • Index Cond: (exception_group_id = mseg_3.exception_group_id)
86. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.32..8.32 rows=3 width=8) (never executed)

87. 0.000 0.000 ↓ 0.0 0

Seq Scan on merchant_room_tables mrt (cost=0.00..8.32 rows=3 width=8) (never executed)

  • Filter: ((deleted_ts IS NULL) AND (min_capacity <= 2) AND (max_capacity >= 2))
88. 0.000 0.000 ↓ 0.0 0

Index Scan using merchant_shift_rooms_pkey on merchant_shift_rooms msr (cost=0.14..0.22 rows=1 width=8) (never executed)

  • Index Cond: ((merchant_room_id = mtr.merchant_room_id) AND (shift_reservation_type_id = srt.shift_reservation_type_id))
  • Filter: is_reservable
89.          

SubPlan (forNested Loop)

90. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=8) (never executed)

91. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2 (cost=181.59..181.61 rows=1 width=32) (never executed)

92. 0.000 0.000 ↓ 0.0 0

Group (cost=181.59..181.60 rows=1 width=40) (never executed)

  • Group Key: mtc.merchant_table_combination_id
93. 0.000 0.000 ↓ 0.0 0

Sort (cost=181.59..181.60 rows=1 width=40) (never executed)

  • Sort Key: mtc.merchant_table_combination_id
94. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=127.16..181.58 rows=1 width=40) (never executed)

  • Join Filter: (mcr.merchant_table_combination_id = mtc.merchant_table_combination_id)
95. 0.000 0.000 ↓ 0.0 0

Seq Scan on merchant_table_combinations mtc (cost=126.00..153.00 rows=1 width=40) (never executed)

  • Filter: ((deleted_ts IS NULL) AND (min_capacity <= 2) AND (max_capacity >= 2) AND ((NOT (hashed SubPlan 8)) OR (hashed SubPlan 9)))
96.          

SubPlan (forSeq Scan)

97. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.21..62.99 rows=4 width=8) (never executed)

98. 0.000 0.000 ↓ 0.0 0

Seq Scan on merchant_shift_exception_groups mseg (cost=0.00..29.25 rows=4 width=8) (never executed)

  • Filter: ((is_reservable IS FALSE) AND (shift_reservation_type_id = srt.shift_reservation_type_id))
99. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on exception_group_combos egc (cost=1.21..8.36 rows=7 width=16) (never executed)

  • Recheck Cond: (exception_group_id = mseg.exception_group_id)
100. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on exception_group_combos_unique (cost=0.00..1.21 rows=7 width=0) (never executed)

  • Index Cond: (exception_group_id = mseg.exception_group_id)
101. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.21..62.99 rows=4 width=8) (never executed)

102. 0.000 0.000 ↓ 0.0 0

Seq Scan on merchant_shift_exception_groups mseg_1 (cost=0.00..29.25 rows=4 width=8) (never executed)

  • Filter: ((is_reservable IS TRUE) AND (shift_reservation_type_id = srt.shift_reservation_type_id))
103. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on exception_group_combos egc_1 (cost=1.21..8.36 rows=7 width=16) (never executed)

  • Recheck Cond: (exception_group_id = mseg_1.exception_group_id)
104. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on exception_group_combos_unique (cost=0.00..1.21 rows=7 width=0) (never executed)

  • Index Cond: (exception_group_id = mseg_1.exception_group_id)
105. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.16..28.41 rows=14 width=8) (never executed)

  • Hash Cond: (mcr.merchant_room_id = msr_1.merchant_room_id)
106. 0.000 0.000 ↓ 0.0 0

Seq Scan on merchant_combo_rooms mcr (cost=0.00..23.60 rows=1,360 width=16) (never executed)

107. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.14..1.14 rows=2 width=8) (never executed)

108. 0.000 0.000 ↓ 0.0 0

Seq Scan on merchant_shift_rooms msr_1 (cost=0.00..1.14 rows=2 width=8) (never executed)

  • Filter: (is_reservable AND (shift_reservation_type_id = srt.shift_reservation_type_id))
109.          

CTE good

110. 0.000 0.000 ↓ 0.0 0

CTE Scan on rt (cost=0.00..261.38 rows=2 width=33) (never executed)

111.          

SubPlan (forCTE Scan)

112. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=37.03..1,254.26 rows=13 width=0) (never executed)

113.          

Initplan (forNested Loop)

114. 0.000 0.000 ↓ 0.0 0

Seq Scan on merchant_shift_turn_times mstt (cost=0.00..34.50 rows=1 width=2) (never executed)

  • Filter: ((min_party_size <= 2) AND ((max_party_size >= 2) OR (max_party_size IS NULL)) AND (shift_reservation_type_id = srt.shift_reservation_type_id))
115. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=2.25..408.41 rows=238 width=24) (never executed)

  • Hash Cond: (ts.mrt_id = unnest.unnest)
116. 0.000 0.000 ↓ 0.0 0

Seq Scan on table_status ts (cost=0.00..402.76 rows=284 width=32) (never executed)

  • Filter: ((done_ts IS NULL) AND (abs(((assigned_ts)::double precision - (date_part('epoch'::text, (series.times - '00:00:00'::interval)) * '1000'::double precision))) < (((COALESCE(($20)::integer, 90) * 60) * 1000))::double precision))
117. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.00..1.00 rows=100 width=8) (never executed)

118. 0.000 0.000 ↓ 0.0 0

Function Scan on unnest (cost=0.00..1.00 rows=100 width=8) (never executed)

119. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_merchant_parties_local on merchant_parties mp_1 (cost=0.28..3.40 rows=1 width=24) (never executed)

  • Index Cond: (local_id = ts.party_local_id)
  • Filter: ((reserved_ts >= start_of_restaurant_day('00:00:00'::interval, ts.assigned_ts)) AND (reserved_ts < (start_of_restaurant_day('00:00:00'::interval, ts.assigned_ts) + 86400000)))
120. 2,133.896 2,133.896 ↓ 2,640.0 2,640 1

CTE Scan on inventory (cost=0.00..0.06 rows=1 width=188) (actual time=2,118.941..2,133.896 rows=2,640 loops=1)