explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Zho

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 74,781.616 ↓ 130.0 130 1

Unique (cost=199.03..199.05 rows=1 width=37) (actual time=74,781.575..74,781.616 rows=130 loops=1)

2.          

CTE times

3. 11.789 11.789 ↓ 8.9 8,880 1

Function Scan on generate_series i (cost=0.00..40.00 rows=1,000 width=52) (actual time=1.012..11.789 rows=8,880 loops=1)

4. 0.167 74,781.584 ↓ 130.0 130 1

Sort (cost=159.03..159.03 rows=1 width=37) (actual time=74,781.574..74,781.584 rows=130 loops=1)

  • Sort Key: times."time", paa.rate_association_id, par.id, par.reserved_by_day, par.reservation_start, par.reservation_end, paa.quantity
  • Sort Method: quicksort Memory: 35kB
5. 54,485.194 74,781.417 ↓ 130.0 130 1

Nested Loop Left Join (cost=16.16..159.02 rows=1 width=37) (actual time=21.663..74,781.417 rows=130 loops=1)

  • Join Filter: ((timezone('America/New_York'::text, timezone('America/Denver'::text, (par.reservation_start)::timestamp without time zone)) <= times."time") AND (timezone('America/New_York'::text, timezone('America/Denver'::text, (par.reservation_end)::timestamp without time zone)) > times."time"))
  • Rows Removed by Join Filter: 37,053,752
  • Filter: (1 = CASE WHEN ((times."time" >= timezone('America/New_York'::text, timezone('America/Denver'::text, (par.reservation_start)::timestamp without time zone))) AND (times."time" <= timezone('America/New_York'::text, timezone('America/Denver'::text, (par.reservation_end)::timestamp without time zone)))) THEN 1 ELSE 0 END)
  • Rows Removed by Filter: 175,192
6. 23.873 134.423 ↓ 175,320.0 175,320 1

Nested Loop (cost=15.87..101.01 rows=1 width=20) (actual time=1.382..134.423 rows=175,320 loops=1)

7. 0.068 0.870 ↓ 20.0 20 1

Nested Loop (cost=15.87..21.02 rows=1 width=12) (actual time=0.219..0.870 rows=20 loops=1)

8. 0.127 0.262 ↓ 20.0 20 1

Bitmap Heap Scan on property_amenity_availabilities paa (cost=15.45..16.51 rows=1 width=12) (actual time=0.147..0.262 rows=20 loops=1)

  • Recheck Cond: ((property_id = 218,135) AND (rate_association_id = ANY ('{1711209,1711219,1711227,1711228,1711229,1711231,1711239,1711241,1711243,1711244,1711247,1711249,1711251,1711253,1711257,1711258,1721250,1721251,1721252,1721253}'::integer[])))
  • Filter: (cid = 1,926)
  • Heap Blocks: exact=8
9. 0.003 0.135 ↓ 0.0 0 1

BitmapAnd (cost=15.45..15.45 rows=1 width=0) (actual time=0.133..0.135 rows=0 loops=1)

10. 0.074 0.074 ↑ 1.0 36 1

Bitmap Index Scan on idx_property_amenity_availabilities_property_id (cost=0.00..1.54 rows=36 width=0) (actual time=0.073..0.074 rows=36 loops=1)

  • Index Cond: (property_id = 218,135)
11. 0.058 0.058 ↑ 1.0 20 1

Bitmap Index Scan on idx_property_amenity_availabilities_property_amenity_id (cost=0.00..13.65 rows=20 width=0) (actual time=0.058..0.058 rows=20 loops=1)

  • Index Cond: (rate_association_id = ANY ('{1711209,1711219,1711227,1711228,1711229,1711231,1711239,1711241,1711243,1711244,1711247,1711249,1711251,1711253,1711257,1711258,1721250,1721251,1721252,1721253}'::integer[]))
12. 0.540 0.540 ↑ 1.0 1 20

Index Scan using pk_rate_associations on rate_associations ra (cost=0.42..2.47 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=20)

  • Index Cond: ((cid = 1,926) AND (id = paa.rate_association_id))
  • Filter: ((ar_cascade_reference_id = 218,135) AND (ar_origin_id = 4) AND (ar_cascade_id = 1))
13. 109.680 109.680 ↓ 26.3 8,766 20

CTE Scan on times (cost=0.00..70.00 rows=333 width=8) (actual time=0.098..5.484 rows=8,766 loops=20)

  • Filter: ("time" >= ((now() - '1 day'::interval) + '01:00:00'::interval))
  • Rows Removed by Filter: 114
14. 20,161.800 20,161.800 ↓ 3.6 211 175,320

Index Scan using idx_property_amenity_reservations_property_amenity_id on property_add_on_reservations par (cost=0.29..53.73 rows=59 width=29) (actual time=0.004..0.115 rows=211 loops=175,320)

  • Index Cond: (rate_association_id = paa.rate_association_id)
  • Filter: ((amenity_reservation_status_type_id <> ALL ('{4,2}'::integer[])) AND (cid = 1,926) AND (cid = paa.cid))
  • Rows Removed by Filter: 27
Planning time : 3.012 ms
Execution time : 74,782.266 ms