explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hjS6

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 2,807.592 ↑ 1.0 1 1

Result (cost=30,643.36..30,643.37 rows=1 width=0) (actual time=2,807.592..2,807.592 rows=1 loops=1)

  • Output: $27, $28
2.          

CTE initial_items

3. 14.531 14.531 ↓ 3.6 19,108 1

Index Scan using opt_spreads_staging_undidx on public.opt_spreads_staging s1 (cost=0.43..30,283.85 rows=5,311 width=22) (actual time=0.028..14.531 rows=19,108 loops=1)

  • Output: s1.spread_type, s1.spread_date, s1.und_product_id, s1.short_base_product, s1.long_base_product, s1.expiration_date
  • Index Cond: ((s1.spread_date = '2019-11-06'::date) AND (s1.join_prodid = 40))
  • Filter: ((NOT s1.mark_to_delete) AND (abs(s1.max_profit_or_loss) >= 0.05::double precision))
  • Rows Removed by Filter: 1761
4.          

CTE all_new_rows

5. 144.597 1,067.635 ↓ 57,324.0 57,324 1

Nested Loop (cost=10.86..342.20 rows=1 width=235) (actual time=0.131..1,067.635 rows=57,324 loops=1)

  • Output: oss.spread_date, oss.spread_type, false, 10, ss.opt_product_id, ls.opt_product_id, oss.und_product_id, oss.expiration_id, oss.und_product_id, oss.expiration_date, ls.atm_price, (100::double precision * ls.opt_ask_iv), ls.opt_iv_rank, (100 (...)
6. 46.376 808.390 ↓ 57,324.0 57,324 1

Nested Loop (cost=10.57..341.81 rows=1 width=202) (actual time=0.116..808.390 rows=57,324 loops=1)

  • Output: oss.spread_date, oss.spread_type, oss.und_product_id, oss.expiration_id, oss.expiration_date, oss.spread_price, oss.credit_or_debit, oss.max_profit_or_loss, oss.breakeven_price, oss.cushion_to_breakeven, ls.opt_product_id, ls.atm_pr (...)
  • Join Filter: (ii.short_base_product = ss.opt_product_id)
7. 66.324 647.366 ↓ 57,324.0 57,324 1

Nested Loop (cost=10.15..341.27 rows=1 width=190) (actual time=0.111..647.366 rows=57,324 loops=1)

  • Output: ii.short_base_product, oss.spread_date, oss.spread_type, oss.und_product_id, oss.expiration_id, oss.expiration_date, oss.spread_price, oss.credit_or_debit, oss.max_profit_or_loss, oss.breakeven_price, oss.cushion_to_breakeven, (...)
  • Join Filter: (ii.long_base_product = ls.opt_product_id)
8. 48.793 466.394 ↓ 57,324.0 57,324 1

Nested Loop (cost=9.73..340.72 rows=1 width=166) (actual time=0.101..466.394 rows=57,324 loops=1)

  • Output: ii.short_base_product, ii.long_base_product, oss.spread_date, oss.spread_type, oss.und_product_id, oss.expiration_id, oss.expiration_date, oss.spread_price, oss.credit_or_debit, oss.max_profit_or_loss, oss.breakeven_pric (...)
  • Join Filter: ((ii.spread_type = oss.spread_type) AND (ii.short_base_product = oss.short_base_product) AND (ii.long_base_product = oss.long_base_product))
9. 35.720 245.629 ↓ 57,324.0 57,324 1

Nested Loop (cost=9.30..340.17 rows=1 width=126) (actual time=0.090..245.629 rows=57,324 loops=1)

  • Output: ii.spread_type, ii.short_base_product, ii.long_base_product, dist.pnl_stock_flat, dist.theo_value, dist.theo_edge_pct, dist.win_rate, dist.sharpe_ratio, dist.forward_atm_price, dist.pnl_avg_return, dist.pnl_avg_up_ (...)
  • Join Filter: ((ii.spread_type = dist.spread_type) AND (ii.short_base_product = dist.short_base_product) AND (ii.long_base_product = dist.long_base_product))
10. 6.481 114.369 ↓ 19,108.0 19,108 1

Nested Loop (cost=8.74..331.76 rows=1 width=64) (actual time=0.072..114.369 rows=19,108 loops=1)

  • Output: ii.spread_type, ii.short_base_product, ii.long_base_product, seas.pnl_avg_return, seas.pnl_avg_up_move, seas.pnl_avg_down_move, seas.spread_type, seas.short_base_product, seas.long_base_product, undseas.years (...)
11. 10.635 31.456 ↓ 707.7 19,108 1

Hash Join (cost=8.31..134.72 rows=27 width=42) (actual time=0.058..31.456 rows=19,108 loops=1)

  • Output: ii.spread_type, ii.short_base_product, ii.long_base_product, undseas.years_in_sample, undseas.total_obs, undseas.pct_positive, undseas.pct_negative, undseas.avg_return, undseas.median_return, undseas.av (...)
  • Hash Cond: (ii.expiration_date = undseas.exp_date)
12. 20.802 20.802 ↓ 3.6 19,108 1

CTE Scan on initial_items ii (cost=0.00..106.22 rows=5,311 width=14) (actual time=0.029..20.802 rows=19,108 loops=1)

  • Output: ii.spread_type, ii.spread_date, ii.und_product_id, ii.short_base_product, ii.long_base_product, ii.expiration_date
13. 0.002 0.019 ↓ 17.0 17 1

Hash (cost=8.30..8.30 rows=1 width=36) (actual time=0.019..0.019 rows=17 loops=1)

  • Output: undseas.years_in_sample, undseas.total_obs, undseas.pct_positive, undseas.pct_negative, undseas.avg_return, undseas.median_return, undseas.avg_up_move, undseas.avg_down_move, undseas.exp_date
  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
14. 0.017 0.017 ↓ 17.0 17 1

Index Scan using opt_staging_und_seasonality_pk on public.opt_staging_und_seasonality undseas (cost=0.28..8.30 rows=1 width=36) (actual time=0.009..0.017 rows=17 loops=1)

  • Output: undseas.years_in_sample, undseas.total_obs, undseas.pct_positive, undseas.pct_negative, undseas.avg_return, undseas.median_return, undseas.avg_up_move, undseas.avg_down_move, undseas.exp_dat (...)
  • Index Cond: ((undseas.spread_date = '2019-11-06'::date) AND (undseas.und_product_id = 40))
15. 76.432 76.432 ↑ 1.0 1 19,108

Index Scan using opt_spreads_staging_seasonality_pk on public.opt_spreads_staging_seasonality seas (cost=0.43..7.29 rows=1 width=22) (actual time=0.003..0.004 rows=1 loops=19,108)

  • Output: seas.spread_type, seas.spread_date, seas.short_base_product, seas.long_base_product, seas.und_product_id, seas.exp_date, seas.years_in_sample, seas.pnl_avg_return, seas.pnl_avg_up_move, seas.pnl_avg_dow (...)
  • Index Cond: ((seas.spread_type = ii.spread_type) AND (seas.spread_date = '2019-11-06'::date) AND (seas.short_base_product = ii.short_base_product) AND (seas.long_base_product = ii.long_base_product))
16. 95.540 95.540 ↓ 3.0 3 19,108

Index Scan using opt_spreads_staging_distribution_pk on public.opt_spreads_staging_distribution dist (cost=0.56..8.40 rows=1 width=62) (actual time=0.004..0.005 rows=3 loops=19,108)

  • Output: dist.lastupdated, dist.spread_type, dist.spread_date, dist.short_base_product, dist.long_base_product, dist.und_product_id, dist.years_in_sample, dist.period_days, dist.theo_edge, dist.theo_edge_pct, dist.win (...)
  • Index Cond: ((dist.spread_type = seas.spread_type) AND (dist.spread_date = '2019-11-06'::date) AND (dist.short_base_product = seas.short_base_product) AND (dist.long_base_product = seas.long_base_product))
17. 171.972 171.972 ↑ 1.0 1 57,324

Index Scan using opt_spreads_staging_pk on public.opt_spreads_staging oss (cost=0.43..0.53 rows=1 width=46) (actual time=0.003..0.003 rows=1 loops=57,324)

  • Output: oss.lastupdated, oss.mark_to_delete, oss.spread_type, oss.spread_date, oss.short_base_product, oss.long_base_product, oss.join_prodid, oss.und_product_id, oss.expiration_date, oss.expiration_id, oss.credit_or_debit (...)
  • Index Cond: ((oss.spread_type = dist.spread_type) AND (oss.spread_date = '2019-11-06'::date) AND (oss.short_base_product = dist.short_base_product) AND (oss.long_base_product = dist.long_base_product))
18. 114.648 114.648 ↑ 1.0 1 57,324

Index Scan using opt_spreads_staging_base_pk on public.opt_spreads_staging_base ls (cost=0.42..0.53 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=57,324)

  • Output: ls.mark_to_delete, ls.opt_product_id, ls.opt_type, ls.spread_date, ls.lastupdated, ls.und_symbol, ls.und_product_id, ls.root_id, ls.expiration_date, ls.expiration_id, ls.contract_type, ls.atm_price, ls.entry_time, ls.day (...)
  • Index Cond: ((ls.opt_product_id = dist.long_base_product) AND (ls.spread_date = '2019-11-06'::date))
  • Filter: (NOT ls.mark_to_delete)
19. 114.648 114.648 ↑ 1.0 1 57,324

Index Scan using opt_spreads_staging_base_pk on public.opt_spreads_staging_base ss (cost=0.42..0.53 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=57,324)

  • Output: ss.mark_to_delete, ss.opt_product_id, ss.opt_type, ss.spread_date, ss.lastupdated, ss.und_symbol, ss.und_product_id, ss.root_id, ss.expiration_date, ss.expiration_id, ss.contract_type, ss.atm_price, ss.entry_time, ss.days_to_e (...)
  • Index Cond: ((ss.opt_product_id = dist.short_base_product) AND (ss.spread_date = '2019-11-06'::date))
  • Filter: (NOT ss.mark_to_delete)
20. 114.648 114.648 ↑ 1.0 1 57,324

Index Scan using opt_staging_und_distribution_pk on public.opt_staging_und_distribution unddist (cost=0.29..0.32 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=57,324)

  • Output: unddist.lastupdated, unddist.und_product_id, unddist.spread_date, unddist.years_in_sample, unddist.period_days, unddist.days_til_earnings, unddist.total_obs, unddist.positive_obs, unddist.negative_obs, unddist.pct_positive, unddist. (...)
  • Index Cond: ((unddist.spread_date = '2019-11-06'::date) AND (unddist.und_product_id = 40) AND (unddist.years_in_sample = dist.years_in_sample) AND (unddist.period_days = dist.period_days))
21.          

CTE all_update

22. 1,016.086 2,550.791 ↓ 57,324.0 57,324 1

Update on public.opt_spreads_staging_full_3 osf (cost=0.56..8.63 rows=1 width=525) (actual time=0.189..2,550.791 rows=57,324 loops=1)

  • Output: 1
23. 43.762 1,534.705 ↓ 57,324.0 57,324 1

Nested Loop (cost=0.56..8.63 rows=1 width=525) (actual time=0.172..1,534.705 rows=57,324 loops=1)

  • Output: osf.spread_date, osf.spread_type, osf.mark_to_delete, anr.update_ct, osf.join_shortoptid, osf.join_longoptid, osf.join_prodid, osf.join_expid, osf.underlying_productid, osf.details_expirationdate, anr.details_atmprice, anr.details_l (...)
24. 1,204.323 1,204.323 ↓ 57,324.0 57,324 1

CTE Scan on all_new_rows anr (cost=0.00..0.02 rows=1 width=438) (actual time=0.148..1,204.323 rows=57,324 loops=1)

  • Output: anr.update_ct, anr.details_atmprice, anr.details_long_iv, anr.details_long_iv_rank, anr.details_short_iv, anr.details_short_iv_rank, anr.details_spreadbid, anr.details_spreadask, anr.details_spreadbidaskspread, anr.details_lon (...)
25. 286.620 286.620 ↑ 1.0 1 57,324

Index Scan using opt_spreads_staging_full_3_pk on public.opt_spreads_staging_full_3 osf (cost=0.56..8.60 rows=1 width=105) (actual time=0.004..0.005 rows=1 loops=57,324)

  • Output: osf.spread_date, osf.spread_type, osf.mark_to_delete, osf.join_shortoptid, osf.join_longoptid, osf.join_prodid, osf.join_expid, osf.underlying_productid, osf.details_expirationdate, osf.details_long_opt_id, osf.details_short_o (...)
  • Index Cond: ((osf.spread_date = anr.spread_date) AND (osf.spread_type = anr.spread_type) AND (osf.join_shortoptid = anr.join_shortoptid) AND (osf.join_longoptid = anr.join_longoptid) AND (osf.intervals_yrs = anr.intervals_yrs))
26.          

CTE all_insert_new

27. 0.001 220.623 ↓ 0.0 0 1

Insert on public.opt_spreads_staging_full_3 (cost=0.56..8.62 rows=1 width=288) (actual time=220.623..220.623 rows=0 loops=1)

  • Output: 1
28. 35.831 220.622 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.56..8.62 rows=1 width=288) (actual time=220.622..220.622 rows=0 loops=1)

  • Output: anr_1.spread_date, anr_1.spread_type, anr_1.mark_to_delete, anr_1.update_ct, anr_1.join_shortoptid, anr_1.join_longoptid, anr_1.join_prodid, anr_1.join_expid, anr_1.underlying_productid, anr_1.details_expirationdate, anr_1.details_a (...)
29. 12.819 12.819 ↓ 57,324.0 57,324 1

CTE Scan on all_new_rows anr_1 (cost=0.00..0.02 rows=1 width=288) (actual time=0.014..12.819 rows=57,324 loops=1)

  • Output: anr_1.spread_date, anr_1.spread_type, anr_1.mark_to_delete, anr_1.update_ct, anr_1.join_shortoptid, anr_1.join_longoptid, anr_1.join_prodid, anr_1.join_expid, anr_1.underlying_productid, anr_1.details_expirationdate, anr_1.det (...)
30. 171.972 171.972 ↑ 1.0 1 57,324

Index Only Scan using opt_spreads_staging_full_3_pk on public.opt_spreads_staging_full_3 osfx (cost=0.56..8.58 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=57,324)

  • Output: osfx.spread_date, osfx.spread_type, osfx.join_shortoptid, osfx.join_longoptid, osfx.intervals_yrs
  • Index Cond: ((osfx.spread_date = anr_1.spread_date) AND (osfx.spread_type = anr_1.spread_type) AND (osfx.join_shortoptid = anr_1.join_shortoptid) AND (osfx.join_longoptid = anr_1.join_longoptid) AND (osfx.intervals_yrs = anr_1.interva (...)
  • Heap Fetches: 111607
31.          

Initplan (for Result)

32. 8.219 2,586.959 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=24) (actual time=2,586.959..2,586.959 rows=1 loops=1)

  • Output: count(u.*)
33. 2,578.740 2,578.740 ↓ 57,324.0 57,324 1

CTE Scan on all_update u (cost=0.00..0.02 rows=1 width=24) (actual time=0.191..2,578.740 rows=57,324 loops=1)

  • Output: u.*
34. 0.004 220.629 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=24) (actual time=220.629..220.629 rows=1 loops=1)

  • Output: count(i.*)
35. 220.625 220.625 ↓ 0.0 0 1

CTE Scan on all_insert_new i (cost=0.00..0.02 rows=1 width=24) (actual time=220.625..220.625 rows=0 loops=1)

  • Output: i.*
Total runtime : 2,814.538 ms