explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rA0r : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #jyov; plan #Yj9t; plan #vrTD; plan #iVmn; plan #BktC; plan #fDhb; plan #LebJ; plan #3ITz; plan #9KnD; plan #dlLn; plan #UOVe

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.101 0.787 ↓ 2.0 2 1

Nested Loop (cost=42.24..48.70 rows=1 width=96) (actual time=0.552..0.787 rows=2 loops=1)

2.          

CTE candidates

3. 0.449 0.449 ↓ 2.0 2 1

Index Scan using travel_cancellation_option_ma_partner_id_site_id_country_fa_idx on travel_cancellation_option_mapping tcom (cost=0.41..41.69 rows=1 width=133) (actual time=0.202..0.449 rows=2 loops=1)

  • Index Cond: ((partner_id = '1'::text) AND (site_id = '1'::text) AND (country = 'JP'::text) AND (fare_class = 'SuperValue'::text) AND ((currency)::text = 'JPY'::text) AND (price_band_1_item_count = 1) AND (price_band_1_price_start < '20000'::numeric) AND (price_band_1_price_end >= '20000'::numeric) AND (price_band_2_item_count = 1) AND (price_band_2_price_start < '10000'::numeric) AND (price_band_2_price_end >= '10000'::numeric))
  • Filter: ((allowed_airports IS NULL) OR ('{HND,OSA}'::text[] <@ allowed_airports))
4. 0.516 0.516 ↓ 2.0 2 1

CTE Scan on candidates (cost=0.00..0.02 rows=1 width=129) (actual time=0.226..0.516 rows=2 loops=1)

  • Filter: (special_airport OR (NOT special_airport))
5. 0.170 0.170 ↑ 1.0 1 2

Index Only Scan using special_airports_mapping_id_code_start_day_start_month_end__idx on special_airports sa (cost=0.55..6.99 rows=1 width=49) (actual time=0.055..0.085 rows=1 loops=2)

  • Index Cond: (mapping_id = candidates.id)
  • Filter: ((('KTD'::text = code) OR ('OKA'::text = code)) AND (((1 >= start_day) AND (1 <= end_day) AND (9 >= start_month) AND (9 <= end_month)) OR ((1 < start_day) AND (1 > end_day) AND (9 < start_month) AND (9 > end_month))) AND ((candidates.special_airport AND (1 >= start_day) AND (1 <= end_day) AND (9 >= start_month) AND (9 <= end_month)) OR ((NOT candidates.special_airport) AND (1 < start_day) AND (1 > end_day) AND (9 < start_month) AND (9 > end_month))))
  • Rows Removed by Filter: 18
  • Heap Fetches: 0
Planning time : 0.786 ms
Execution time : 1.051 ms