explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oebn

Settings
# exclusive inclusive rows x rows loops node
1. 45,717.023 1,643,369.210 ↓ 0.0 0 1

Update on base.calc_data_from_t0 t (cost=28,116,200,176.92..29,178,010,670.43 rows=2,062,334 width=928) (actual time=1,643,369.210..1,643,369.210 rows=0 loops=1)

  • Buffers: shared hit=398334915 read=720762, temp read=3852326 written=3852326
2.          

Initplan (forUpdate)

3. 0.009 0.009 ↑ 1,450.0 1 1

Seq Scan on base.batch_date bd_1 (cost=0.00..24.50 rows=1,450 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Output: bd_1.copy_dt
  • Buffers: shared hit=1
4. 3,336.742 1,597,652.178 ↓ 1.0 2,097,759 1

Merge Join (cost=28,116,200,152.42..29,178,010,645.93 rows=2,062,334 width=928) (actual time=1,460,103.833..1,597,652.178 rows=2,097,759 loops=1)

  • Output: t.copy_dt, t.vcif, t.ave_profit, t.ave_profit_act, t.ave_profit_monthly, t.ave_profit_monthly_act, t.ave_profit_yearly, t.ave_profit_yearly_act, t.ave_loss, t.ave_loss_act, t.ave_loss_monthly, t.ave_loss_monthly_act, t.ave_loss_yearly, t.ave_loss_yearly_act, t.profit_loss_ratio, t.profit_loss_ratio_act, t.profit_loss_ratio_raw_ranking, t.profit_loss_ratio_ranking, t.profit_loss_ratio_monthly, t.profit_loss_ratio_monthly_act, t.profit_loss_ratio_monthly_raw_ranking, t.profit_loss_ratio_monthly_ranking, t.profit_loss_ratio_yearly, t.profit_loss_ratio_yearly_act, t.profit_loss_ratio_yearly_raw_ranking, t.profit_loss_ratio_yearly_ranking, t.profit_factor, t.profit_factor_act, t.profit_factor_raw_ranking, t.profit_factor_ranking, t.profit_factor_monthly, t.profit_factor_monthly_act, t.profit_factor_monthly_raw_ranking, t.profit_factor_monthly_ranking, t.profit_factor_yearly, t.profit_factor_yearly_act, t.profit_factor_yearly_raw_ranking, t.profit_factor_yearly_ranking, t.ave_deflection, t.ave_deflection_act, t.ave_deflection_raw_ranking, t.ave_deflection_ranking, t.ave_deflection_monthly, t.ave_deflection_monthly_act, t.ave_deflection_monthly_raw_ranking, t.ave_deflection_monthly_ranking, t.ave_deflection_yearly, t.ave_deflection_yearly_act, t.ave_deflection_yearly_raw_ranking, t.ave_deflection_yearly_ranking, t.winning_rate, t.winning_rate_raw_ranking, t.winning_rate_ranking, t.winning_rate_monthly, t.winning_rate_monthly_raw_ranking, t.winning_rate_monthly_ranking, t.winning_rate_yearly, t.winning_rate_yearly_raw_ranking, t.winning_rate_yearly_ranking, (a.val * 15.652475842498528), t.stddev_percent_rank, t.stddev_act, t.stddev_monthly, t.stddev_monthly_percent_rank, t.stddev_monthly_act, t.stddev_yearly, t.stddev_yearly_percent_rank, t.stddev_yearly_act, t.annual_return, t.annual_return_act, t.sharpe_ratio, t.sharpe_ratio_act, t.peak, t.peak_act, t.draw_down, t.draw_down_act, t.max_draw_down, t.max_draw_down_act, t.max_profit, t.max_profit_act, t.max_loss, t.max_loss_act, t.ctid, a.*
  • Merge Cond: ((a.vcif)::text = (t.vcif)::text)
  • Buffers: shared hit=362545343 read=686918, temp read=3852326 written=3852326
5. 654.979 1,588,357.827 ↑ 1.0 2,097,759 1

Subquery Scan on a (cost=28,116,200,151.85..29,173,635,312.89 rows=2,132,787 width=126) (actual time=1,460,103.799..1,588,357.827 rows=2,097,759 loops=1)

  • Output: a.val, a.*, a.copy_dt, a.vcif
  • Buffers: shared hit=351918213 read=686707, temp read=3852326 written=3852326
6. 56,191.463 1,587,702.848 ↑ 1.0 2,097,759 1

GroupAggregate (cost=28,116,200,151.85..29,173,613,985.02 rows=2,132,787 width=51) (actual time=1,460,103.794..1,587,702.848 rows=2,097,759 loops=1)

  • Output: max(a_1.copy_dt), a_1.vcif, stddev_samp(a_1.val)
  • Group Key: a_1.vcif
  • Filter: (max(a_1.copy_dt) = $0)
  • Buffers: shared hit=351918213 read=686707, temp read=3852326 written=3852326
7. 1,268,335.288 1,531,511.385 ↑ 483.3 175,015,691 1

Sort (cost=28,116,200,151.85..28,327,677,586.51 rows=84,590,973,867 width=23) (actual time=1,460,103.685..1,531,511.385 rows=175,015,691 loops=1)

  • Output: a_1.vcif, a_1.copy_dt, a_1.val
  • Sort Key: a_1.vcif
  • Sort Method: external merge Disk: 5899024kB
  • Buffers: shared hit=351918212 read=686707, temp read=3852326 written=3852326
8. 28,922.648 263,176.097 ↑ 483.3 175,015,691 1

Nested Loop (cost=0.15..2,354,374,467.56 rows=84,590,973,867 width=23) (actual time=0.139..263,176.097 rows=175,015,691 loops=1)

  • Output: a_1.vcif, a_1.copy_dt, a_1.val
  • Buffers: shared hit=351918212 read=686707
9. 59,237.758 59,237.758 ↑ 1.0 175,015,691 1

Seq Scan on base.performance_each_day_base_from_t0 a_1 (cost=0.00..3,037,039.08 rows=175,015,808 width=23) (actual time=0.130..59,237.758 rows=175,015,691 loops=1)

  • Output: a_1.copy_dt, a_1.prev_dt, a_1.vcif, a_1.val
  • Filter: (a_1.val IS NOT NULL)
  • Buffers: shared hit=1886830 read=686707
10. 175,015.691 175,015.691 ↑ 483.0 1 175,015,691

Index Only Scan using batch_date_pkey on base.batch_date bd (cost=0.15..8.61 rows=483 width=4) (actual time=0.001..0.001 rows=1 loops=175,015,691)

  • Output: bd.copy_dt
  • Index Cond: (bd.copy_dt >= a_1.copy_dt)
  • Heap Fetches: 175015691
  • Buffers: shared hit=350031382
11. 5,957.609 5,957.609 ↑ 1.0 2,097,759 1

Index Scan using calc_data_from_t0_pkey on base.calc_data_from_t0 t (cost=0.57..4,333,781.62 rows=2,113,774 width=829) (actual time=0.021..5,957.609 rows=2,097,759 loops=1)

  • Output: t.copy_dt, t.vcif, t.ave_profit, t.ave_profit_act, t.ave_profit_monthly, t.ave_profit_monthly_act, t.ave_profit_yearly, t.ave_profit_yearly_act, t.ave_loss, t.ave_loss_act, t.ave_loss_monthly, t.ave_loss_monthly_act, t.ave_loss_yearly, t.ave_loss_yearly_act, t.profit_loss_ratio, t.profit_loss_ratio_act, t.profit_loss_ratio_raw_ranking, t.profit_loss_ratio_ranking, t.profit_loss_ratio_monthly, t.profit_loss_ratio_monthly_act, t.profit_loss_ratio_monthly_raw_ranking, t.profit_loss_ratio_monthly_ranking, t.profit_loss_ratio_yearly, t.profit_loss_ratio_yearly_act, t.profit_loss_ratio_yearly_raw_ranking, t.profit_loss_ratio_yearly_ranking, t.profit_factor, t.profit_factor_act, t.profit_factor_raw_ranking, t.profit_factor_ranking, t.profit_factor_monthly, t.profit_factor_monthly_act, t.profit_factor_monthly_raw_ranking, t.profit_factor_monthly_ranking, t.profit_factor_yearly, t.profit_factor_yearly_act, t.profit_factor_yearly_raw_ranking, t.profit_factor_yearly_ranking, t.ave_deflection, t.ave_deflection_act, t.ave_deflection_raw_ranking, t.ave_deflection_ranking, t.ave_deflection_monthly, t.ave_deflection_monthly_act, t.ave_deflection_monthly_raw_ranking, t.ave_deflection_monthly_ranking, t.ave_deflection_yearly, t.ave_deflection_yearly_act, t.ave_deflection_yearly_raw_ranking, t.ave_deflection_yearly_ranking, t.winning_rate, t.winning_rate_raw_ranking, t.winning_rate_ranking, t.winning_rate_monthly, t.winning_rate_monthly_raw_ranking, t.winning_rate_monthly_ranking, t.winning_rate_yearly, t.winning_rate_yearly_raw_ranking, t.winning_rate_yearly_ranking, t.stddev_percent_rank, t.stddev_act, t.stddev_monthly, t.stddev_monthly_percent_rank, t.stddev_monthly_act, t.stddev_yearly, t.stddev_yearly_percent_rank, t.stddev_yearly_act, t.annual_return, t.annual_return_act, t.sharpe_ratio, t.sharpe_ratio_act, t.peak, t.peak_act, t.draw_down, t.draw_down_act, t.max_draw_down, t.max_draw_down_act, t.max_profit, t.max_profit_act, t.max_loss, t.max_loss_act, t.ctid
  • Index Cond: (t.copy_dt = $0)
  • Buffers: shared hit=10627130 read=211