explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yfs7 : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #Wfg3; plan #Bfjj; plan #moJ0; plan #RqCS; plan #tWiUD; plan #vdJC; plan #sgOS5; plan #AEbe; plan #EJsH; plan #0SILf

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.376 7,995.484 ↑ 1.0 1 1

Index Scan using travel_cancellation_option_ma_partner_id_site_id_country_fa_idx on travel_cancellation_option_mapping tcom (cost=43,696.35..43,704.64 rows=1 width=99) (actual time=7,995.465..7,995.484 rows=1 loops=1)

  • Index Cond: ((partner_id = '1'::text) AND (site_id = '1'::text) AND (country = 'JP'::text) AND (fare_class = 'SuperValue'::text) AND ($4 = special_airport) 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 ('{NRT,OKA}'::text[] <@ allowed_airports))
2.          

Initplan (forIndex Scan)

3. 604.933 7,995.108 ↑ 1.0 1 1

Aggregate (cost=43,695.92..43,695.93 rows=1 width=1) (actual time=7,995.091..7,995.108 rows=1 loops=1)

4. 2,370.287 7,390.175 ↓ 27.0 45,288 1

Nested Loop (cost=18,566.79..43,691.73 rows=1,677 width=33) (actual time=363.157..7,390.175 rows=45,288 loops=1)

5. 1,155.678 3,027.216 ↓ 27.0 45,288 1

Nested Loop (cost=18,566.38..42,948.00 rows=1,677 width=66) (actual time=363.064..3,027.216 rows=45,288 loops=1)

6. 0.074 0.074 ↑ 1.0 2 1

Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=40) (actual time=0.036..0.074 rows=2 loops=1)

7. 1,536.010 1,871.464 ↓ 27.0 22,644 2

Bitmap Heap Scan on special_airports sa (cost=18,566.38..21,465.60 rows=839 width=86) (actual time=181.443..935.732 rows=22,644 loops=2)

  • Recheck Cond: ((code = "*VALUES*".column1) AND (start_day <= "*VALUES*".column2) AND (start_month <= "*VALUES*".column3) AND (end_day >= "*VALUES*".column2) AND (end_month >= "*VALUES*".column3))
  • Filter: (NOT is_deleted)
  • Heap Blocks: exact=32113
8. 19.942 335.454 ↓ 0.0 0 2

BitmapAnd (cost=18,566.38..18,566.38 rows=839 width=0) (actual time=167.723..167.727 rows=0 loops=2)

9. 33.916 33.916 ↑ 3.0 22,644 2

Bitmap Index Scan on special_airports_codes (cost=0.00..1,419.92 rows=67,933 width=0) (actual time=16.953..16.958 rows=22,644 loops=2)

  • Index Cond: (code = "*VALUES*".column1)
10. 281.596 281.596 ↓ 27.0 588,744 1

Bitmap Index Scan on special_airports_dates (cost=0.00..17,145.78 rows=21,806 width=0) (actual time=281.588..281.596 rows=588,744 loops=1)

  • Index Cond: ((start_day <= "*VALUES*".column2) AND (start_month <= "*VALUES*".column3) AND (end_day >= "*VALUES*".column2) AND (end_month >= "*VALUES*".column3))
11. 1,992.672 1,992.672 ↑ 1.0 1 45,288

Index Only Scan using travel_cancellation_option_mapping_pkey on travel_cancellation_option_mapping tcom_1 (cost=0.41..0.43 rows=1 width=33) (actual time=0.030..0.044 rows=1 loops=45,288)

  • Index Cond: (id = sa.mapping_id)
  • Heap Fetches: 0
Planning time : 3.808 ms
Execution time : 8,002.051 ms