explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l5V

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 10,095.577 ↑ 1.0 1 1

Result (cost=31,577.00..31,577.01 rows=1 width=0) (actual time=10,095.576..10,095.577 rows=1 loops=1)

  • Output: $30, $31
2.          

CTE initial_items

3. 6.970 6.970 ↓ 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..6.970 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. 199.027 8,182.102 ↓ 57,324.0 57,324 1

Nested Loop (cost=437.66..1,275.84 rows=1 width=235) (actual time=17.576..8,182.102 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 (...)
  • Join Filter: ((ii.spread_date = ss.spread_date) AND (ii.short_base_product = ss.opt_product_id))
6. 46.362 7,868.427 ↓ 57,324.0 57,324 1

Nested Loop (cost=437.24..1,275.25 rows=1 width=247) (actual time=17.555..7,868.427 rows=57,324 loops=1)

  • Output: ii.spread_date, 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_ (...)
  • Join Filter: ((ii.spread_date = ls.spread_date) AND (ii.long_base_product = ls.opt_product_id))
7. 69.994 7,650.093 ↓ 57,324.0 57,324 1

Nested Loop (cost=436.82..1,274.73 rows=1 width=219) (actual time=17.541..7,650.093 rows=57,324 loops=1)

  • Output: ii.spread_date, 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.brea (...)
  • Join Filter: ((ii.spread_date = oss.spread_date) AND (ii.spread_type = oss.spread_type) AND (ii.short_base_product = oss.short_base_product) AND (ii.long_base_product = oss.long_base_product))
8. 1,075.910 7,408.127 ↓ 57,324.0 57,324 1

Nested Loop (cost=436.39..1,274.19 rows=1 width=179) (actual time=17.527..7,408.127 rows=57,324 loops=1)

  • Output: ii.spread_date, 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.p (...)
  • Join Filter: ((ii.spread_date = dist.spread_date) AND (ii.spread_type = dist.spread_type) AND (ii.short_base_product = dist.short_base_product) AND (ii.long_base_product = dist.long_base_product) AND (unddist.period_days = dist. (...)
  • Rows Removed by Join Filter: 2866200
9. 836.859 3,408.693 ↓ 974,508.0 974,508 1

Nested Loop (cost=435.83..1,272.76 rows=1 width=121) (actual time=17.508..3,408.693 rows=974,508 loops=1)

  • Output: ii.spread_date, ii.spread_type, ii.short_base_product, ii.long_base_product, unddist.days_til_earnings, unddist.is_est_earnings, unddist.period_days, unddist.total_obs, unddist.pct_positive, unddist.pct_negative, u (...)
10. 345.192 622.818 ↓ 324,836.0 974,508 1

Nested Loop (cost=435.40..1,250.86 rows=3 width=95) (actual time=17.494..622.818 rows=974,508 loops=1)

  • Output: ii.spread_date, ii.spread_type, ii.short_base_product, ii.long_base_product, unddist.days_til_earnings, unddist.is_est_earnings, unddist.period_days, unddist.total_obs, unddist.pct_positive, unddist.pct_negat (...)
  • Join Filter: ((ii.spread_date = unddist.spread_date) AND (ii.und_product_id = unddist.und_product_id))
11. 9.315 29.222 ↓ 3,821.6 19,108 1

Merge Join (cost=435.11..1,248.00 rows=5 width=58) (actual time=17.476..29.222 rows=19,108 loops=1)

  • Output: ii.spread_date, ii.spread_type, ii.short_base_product, ii.long_base_product, ii.und_product_id, undseas.years_in_sample, undseas.total_obs, undseas.pct_positive, undseas.pct_negative, undseas.avg_return (...)
  • Merge Cond: ((undseas.spread_date = ii.spread_date) AND (undseas.und_product_id = ii.und_product_id) AND (undseas.exp_date = ii.expiration_date))
12. 0.050 0.050 ↑ 251.9 31 1

Index Scan using opt_staging_und_seasonality_pk on public.opt_staging_und_seasonality undseas (cost=0.28..701.43 rows=7,810 width=44) (actual time=0.013..0.050 rows=31 loops=1)

  • Output: undseas.lastupdated, undseas.und_product_id, undseas.spread_date, undseas.exp_date, undseas.years_in_sample, undseas.total_obs, undseas.positive_obs, undseas.negative_obs, undseas.pct_positive, un (...)
13. 6.984 19.857 ↓ 3.6 19,108 1

Sort (cost=434.83..448.11 rows=5,311 width=22) (actual time=17.449..19.857 rows=19,108 loops=1)

  • Output: ii.spread_date, ii.spread_type, ii.short_base_product, ii.long_base_product, ii.und_product_id, ii.expiration_date
  • Sort Key: ii.spread_date, ii.und_product_id, ii.expiration_date
  • Sort Method: quicksort Memory: 2261kB
14. 12.873 12.873 ↓ 3.6 19,108 1

CTE Scan on initial_items ii (cost=0.00..106.22 rows=5,311 width=22) (actual time=0.030..12.873 rows=19,108 loops=1)

  • Output: ii.spread_date, ii.spread_type, ii.short_base_product, ii.long_base_product, ii.und_product_id, ii.expiration_date
15. 248.404 248.404 ↓ 51.0 51 19,108

Index Scan using opt_staging_und_distribution_pk on public.opt_staging_und_distribution unddist (cost=0.29..0.56 rows=1 width=49) (actual time=0.004..0.013 rows=51 loops=19,108)

  • 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, (...)
  • Index Cond: ((unddist.spread_date = undseas.spread_date) AND (unddist.und_product_id = undseas.und_product_id))
16. 1,949.016 1,949.016 ↑ 1.0 1 974,508

Index Scan using opt_spreads_staging_seasonality_pk on public.opt_spreads_staging_seasonality seas (cost=0.43..7.29 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=974,508)

  • 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_down_move
  • Index Cond: ((seas.spread_type = ii.spread_type) AND (seas.spread_date = ii.spread_date) AND (seas.short_base_product = ii.short_base_product) AND (seas.long_base_product = ii.long_base_product))
17. 2,923.524 2,923.524 ↓ 3.0 3 974,508

Index Scan using opt_spreads_staging_distribution_pk on public.opt_spreads_staging_distribution dist (cost=0.56..1.40 rows=1 width=66) (actual time=0.003..0.003 rows=3 loops=974,508)

  • 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_rate, (...)
  • Index Cond: ((dist.spread_type = seas.spread_type) AND (dist.spread_date = seas.spread_date) AND (dist.short_base_product = seas.short_base_product) AND (dist.long_base_product = seas.long_base_product))
18. 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.52 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, oss. (...)
  • Index Cond: ((oss.spread_type = dist.spread_type) AND (oss.spread_date = dist.spread_date) AND (oss.short_base_product = dist.short_base_product) AND (oss.long_base_product = dist.long_base_product))
19. 171.972 171.972 ↑ 1.0 1 57,324

Index Scan using opt_spreads_staging_base_pk on public.opt_spreads_staging_base ls (cost=0.42..0.51 rows=1 width=44) (actual time=0.002..0.003 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.days_to_e (...)
  • Index Cond: ((ls.opt_product_id = dist.long_base_product) AND (ls.spread_date = dist.spread_date))
  • Filter: (NOT ls.mark_to_delete)
20. 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.51 rows=1 width=32) (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_exp, ss (...)
  • Index Cond: ((ss.opt_product_id = dist.short_base_product) AND (ss.spread_date = dist.spread_date))
  • Filter: (NOT ss.mark_to_delete)
21.          

CTE all_update

22. 1,115.224 9,832.802 ↓ 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=17.645..9,832.802 rows=57,324 loops=1)

  • Output: 1
23. 43.504 8,717.578 ↓ 57,324.0 57,324 1

Nested Loop (cost=0.56..8.63 rows=1 width=525) (actual time=17.620..8,717.578 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. 8,330.130 8,330.130 ↓ 57,324.0 57,324 1

CTE Scan on all_new_rows anr (cost=0.00..0.02 rows=1 width=438) (actual time=17.592..8,330.130 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. 343.944 343.944 ↑ 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.005..0.006 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.002 224.146 ↓ 0.0 0 1

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

  • Output: 1
28. 38.990 224.144 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.56..8.62 rows=1 width=288) (actual time=224.144..224.144 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. 13.182 13.182 ↓ 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.017..13.182 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: 111961
31.          

Initplan (for Result)

32. 8.433 9,871.421 ↑ 1.0 1 1

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

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

CTE Scan on all_update u (cost=0.00..0.02 rows=1 width=24) (actual time=17.648..9,862.988 rows=57,324 loops=1)

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

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

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

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

  • Output: i.*
Total runtime : 10,101.304 ms