explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8JG8

Settings
# exclusive inclusive rows x rows loops node
1. 0.695 256.995 ↓ 84.0 84 1

Nested Loop (cost=2,401.81..2,409.85 rows=1 width=596) (actual time=185.502..256.995 rows=84 loops=1)

2.          

CTE hotels

3. 1.432 152.897 ↓ 86.0 86 1

Unique (cost=2,399.05..2,401.25 rows=1 width=436) (actual time=150.386..152.897 rows=86 loops=1)

4. 8.810 151.465 ↓ 11.1 3,263 1

Sort (cost=2,399.05..2,399.78 rows=294 width=436) (actual time=150.384..151.465 rows=3,263 loops=1)

  • Sort Key: brewed_bedbanks_packages_2000_1903110047.departure_date, brewed_bedbanks_packages_2000_1903110047.stay, ((brewed_bedbanks_packages_2000_1903110047.total_price + brewed_bedbanks_packages_2000_1903110047_1.total_price))
  • Sort Method: quicksort Memory: 1779kB
5. 5.806 142.655 ↓ 11.1 3,263 1

Nested Loop (cost=47.20..2,386.99 rows=294 width=436) (actual time=22.496..142.655 rows=3,263 loops=1)

6. 23.804 23.804 ↓ 4,915.0 4,915 1

Index Scan using ix_brewed_bedbanks_packages_2000_ap_id_1903110047 on brewed_bedbanks_packages_2000_1903110047 (cost=0.56..8.64 rows=1 width=428) (actual time=14.529..23.804 rows=4,915 loops=1)

  • Index Cond: ((ap_id = 'P-000182462'::text) AND ((departure_code)::text = 'ARN'::text) AND (departure_date >= 190418) AND (departure_date <= 190727) AND (stay >= 3) AND (stay <= 6))
  • Filter: ((seats >= 4) AND (total_price >= 0) AND (total_price <= 999999))
  • Rows Removed by Filter: 958
7. 9.830 113.045 ↑ 294.0 1 4,915

Bitmap Heap Scan on brewed_bedbanks_packages_2000_1903110047 brewed_bedbanks_packages_2000_1903110047_1 (cost=46.64..2,373.94 rows=294 width=102) (actual time=0.023..0.023 rows=1 loops=4,915)

  • Recheck Cond: ((flight_key = brewed_bedbanks_packages_2000_1903110047.flight_key) AND (ap_id = 'P-000182462'::text))
  • Filter: CASE WHEN (brewed_bedbanks_packages_2000_1903110047.room_group_code = room_group_code) THEN (brewed_bedbanks_packages_2000_1903110047.available_rooms > 1) ELSE ((brewed_bedbanks_packages_2000_1903110047.available_rooms > 0) AND (available_rooms > 0)) END
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=4915
8. 103.215 103.215 ↑ 588.0 1 4,915

Bitmap Index Scan on ix_brewed_bedbanks_packages_2000_flight_key_1903110047_test (cost=0.00..46.57 rows=588 width=0) (actual time=0.021..0.021 rows=1 loops=4,915)

  • Index Cond: ((flight_key = brewed_bedbanks_packages_2000_1903110047.flight_key) AND (ap_id = 'P-000182462'::text))
9. 153.014 153.014 ↓ 86.0 86 1

CTE Scan on hotels ho (cost=0.00..0.02 rows=1 width=436) (actual time=150.389..153.014 rows=86 loops=1)

10. 103.286 103.286 ↑ 1.0 1 86

Index Scan using flights_all_raw_pkey on flights_all fa (cost=0.56..8.57 rows=1 width=167) (actual time=1.201..1.201 rows=1 loops=86)

  • Index Cond: ((flight_key)::text = ho.flight_key)
  • Filter: (seats >= 4)
  • Rows Removed by Filter: 0