explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uvI7

Settings
# exclusive inclusive rows x rows loops node
1. 4,631.903 4,631.903 ↓ 63.0 63 1

CTE Scan on paired_call_put (cost=33,685.30..33,685.32 rows=1 width=36) (actual time=4,606.272..4,631.903 rows=63 loops=1)

  • Output: paired_call_put.und_product_id, paired_call_put.exp_id, paired_call_put.call_product_id, paired_call_put.call_iv_id, paired_call_put.put_product_id, paired_call_put.put_iv_id
2.          

CTE exp_dates

3. 0.000 0.000 ↑ 1.0 1 1

Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

  • Output: "*VALUES*".column1
4.          

CTE opt_exps

5. 0.011 0.043 ↑ 1.0 1 1

Nested Loop (cost=0.43..8.48 rows=1 width=20) (actual time=0.034..0.043 rows=1 loops=1)

  • Output: x.und_product_id, x.root_id, x.expiration_date, x.row_id
  • Join Filter: (x.expiration_date = e.exp_date)
  • Rows Removed by Join Filter: 15
6. 0.032 0.032 ↓ 16.0 16 1

Index Scan using option_expirations_idx on public.option_expirations x (cost=0.43..8.45 rows=1 width=20) (actual time=0.017..0.032 rows=16 loops=1)

  • Output: x.und_product_id, x.root_id, x.root_effective_date, x.expiration_date, x.contract_type, x.expiration_cycle, x.date_added, x.current_as_of_date, x.row_id, x.is_active, x.is_primary
  • Index Cond: ((x.und_product_id = 6699) AND (x.current_as_of_date >= ('now'::cstring)::date))
7. 0.000 0.000 ↑ 1.0 1 16

CTE Scan on exp_dates e (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=16)

  • Output: e.exp_date
8.          

CTE paired_call_put

9. 0.040 4,631.870 ↓ 63.0 63 1

Nested Loop (cost=33,319.30..33,676.80 rows=1 width=36) (actual time=4,606.267..4,631.870 rows=63 loops=1)

  • Output: u.und_product_id, u.exp_id, u.product_id, u.today_row_id, u2.product_id, u2.today_row_id
10. 0.009 4,631.515 ↓ 63.0 63 1

Nested Loop (cost=33,318.72..33,668.35 rows=1 width=28) (actual time=4,606.248..4,631.515 rows=63 loops=1)

  • Output: u.und_product_id, u.exp_id, u.product_id, u.today_row_id, u.paired_option
11. 0.044 0.044 ↑ 1.0 1 1

CTE Scan on opt_exps x_1 (cost=0.00..0.02 rows=1 width=12) (actual time=0.034..0.044 rows=1 loops=1)

  • Output: x_1.und_product_id, x_1.root_id, x_1.expiration_date, x_1.row_id
12. 113.700 4,631.462 ↓ 63.0 63 1

Bitmap Heap Scan on public.rtp_opt_data_update u (cost=33,318.72..33,668.32 rows=1 width=28) (actual time=4,606.210..4,631.462 rows=63 loops=1)

  • Output: u.product_id, u.entry_date, u.entry_time, u.opt_type, u.strike, u.today_row_id, u.prev_row_id, u.und_product_id, u.expiration_date, u.days_to_exp, u.exp_id, u.paired_option
  • Recheck Cond: ((u.und_product_id = x_1.und_product_id) AND (u.opt_type = 0) AND (u.entry_date = ('now'::cstring)::date))
  • Rows Removed by Index Recheck: 101683
  • Filter: (x_1.row_id = u.exp_id)
  • Rows Removed by Filter: 631
13. 44.149 4,517.762 ↓ 0.0 0 1

BitmapAnd (cost=33,318.72..33,318.72 rows=87 width=0) (actual time=4,517.762..4,517.762 rows=0 loops=1)

14. 100.019 100.019 ↓ 8.7 914,476 1

Bitmap Index Scan on und_product_id2_idx (cost=0.00..5,025.14 rows=105,657 width=0) (actual time=100.019..100.019 rows=914,476 loops=1)

  • Index Cond: ((u.und_product_id = x_1.und_product_id) AND (u.opt_type = 0))
15. 4,373.594 4,373.594 ↓ 54.4 42,293,393 1

Bitmap Index Scan on rtp_opt_data_update_date2_idx (cost=0.00..28,196.55 rows=777,596 width=0) (actual time=4,373.594..4,373.594 rows=42,293,393 loops=1)

  • Index Cond: (u.entry_date = ('now'::cstring)::date)
16. 0.315 0.315 ↑ 1.0 1 63

Index Scan using rtp_opt_data_update_pkey on public.rtp_opt_data_update u2 (cost=0.58..8.44 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=63)

  • Output: u2.product_id, u2.entry_date, u2.entry_time, u2.opt_type, u2.strike, u2.today_row_id, u2.prev_row_id, u2.und_product_id, u2.expiration_date, u2.days_to_exp, u2.exp_id, u2.paired_option
  • Index Cond: ((u2.product_id = u.paired_option) AND (u2.entry_date = ('now'::cstring)::date))