explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7924 : Optimization for: Optimization for: 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; plan #rA0r; plan #Vf18

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.049 0.756 ↑ 1.0 1 1

Unique (cost=48.71..48.73 rows=1 width=96) (actual time=0.700..0.756 rows=1 loops=1)

2.          

CTE candidates

3. 0.349 0.349 ↓ 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.146..0.349 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.094 0.707 ↓ 2.0 2 1

Sort (cost=7.03..7.03 rows=1 width=96) (actual time=0.681..0.707 rows=2 loops=1)

  • Sort Key: candidates.product_group_option_id, candidates.price_band_1_product_group_option_id, candidates.price_band_2_product_group_option_id
  • Sort Method: quicksort Memory: 25kB
5. 0.092 0.613 ↓ 2.0 2 1

Nested Loop (cost=0.55..7.02 rows=1 width=96) (actual time=0.444..0.613 rows=2 loops=1)

6. 0.415 0.415 ↓ 2.0 2 1

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

  • Filter: (special_airport OR (NOT special_airport))
7. 0.106 0.106 ↑ 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.032..0.053 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