explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pTL2

Settings
# exclusive inclusive rows x rows loops node
1. 0.378 37,526.462 ↓ 86.0 86 1

Nested Loop (cost=12,188.90..12,196.95 rows=1 width=596) (actual time=37,513.902..37,526.462 rows=86 loops=1)

2.          

CTE hotels

3. 1.380 37,516.195 ↓ 86.0 86 1

Unique (cost=12,186.14..12,188.35 rows=1 width=436) (actual time=37,513.841..37,516.195 rows=86 loops=1)

4. 17.563 37,514.815 ↓ 11.1 3,263 1

Sort (cost=12,186.14..12,186.88 rows=294 width=436) (actual time=37,513.838..37,514.815 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. 24.162 37,497.252 ↓ 11.1 3,263 1

Nested Loop (cost=9,834.30..12,174.09 rows=294 width=436) (actual time=1,354.670..37,497.252 rows=3,263 loops=1)

6. 2,561.845 2,561.845 ↓ 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=0.539..2,561.845 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. 127.790 34,911.245 ↑ 294.0 1 4,915

Bitmap Heap Scan on brewed_bedbanks_packages_2000_1903110047 brewed_bedbanks_packages_2000_1903110047_1 (cost=9,833.74..12,161.04 rows=294 width=102) (actual time=7.103..7.103 rows=1 loops=4,915)

  • Recheck Cond: ((ap_id = 'P-000182462'::text) AND (flight_key = brewed_bedbanks_packages_2000_1903110047.flight_key))
  • 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. 1,125.535 34,783.455 ↓ 0.0 0 4,915

BitmapAnd (cost=9,833.74..9,833.74 rows=588 width=0) (actual time=7.077..7.077 rows=0 loops=4,915)

9. 33,461.320 33,461.320 ↑ 1.3 91,920 4,915

Bitmap Index Scan on ix_brewed_bedbanks_packages_2000_ap_id_1903110047 (cost=0.00..3,213.95 rows=117,519 width=0) (actual time=6.808..6.808 rows=91,920 loops=4,915)

  • Index Cond: (ap_id = 'P-000182462'::text)
10. 196.600 196.600 ↑ 39,173.0 3 4,915

Bitmap Index Scan on ix_brewed_bedbanks_packages_2000_flight_key_1903110047 (cost=0.00..6,590.08 rows=117,519 width=0) (actual time=0.040..0.040 rows=3 loops=4,915)

  • Index Cond: (flight_key = brewed_bedbanks_packages_2000_1903110047.flight_key)
11. 37,516.280 37,516.280 ↓ 86.0 86 1

CTE Scan on hotels ho (cost=0.00..0.02 rows=1 width=436) (actual time=37,513.844..37,516.280 rows=86 loops=1)

12. 9.804 9.804 ↑ 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=0.114..0.114 rows=1 loops=86)

  • Index Cond: ((flight_key)::text = ho.flight_key)
  • Filter: (seats >= 4)