explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oyDu

Settings
# exclusive inclusive rows x rows loops node
1. 14.102 439,645.192 ↓ 1.0 17,198 1

Limit (cost=54,796.04..2,761,063.40 rows=16,981 width=208) (actual time=342.920..439,645.192 rows=17,198 loops=1)

2. 523.406 439,631.090 ↓ 1.0 17,198 1

Result (cost=54,796.04..2,761,063.40 rows=16,981 width=208) (actual time=342.919..439,631.090 rows=17,198 loops=1)

3. 44.793 327.009 ↓ 1.0 17,198 1

Sort (cost=54,796.04..54,838.49 rows=16,981 width=82) (actual time=313.077..327.009 rows=17,198 loops=1)

  • Sort Key: ((SubPlan 6)) DESC NULLS LAST, a1.id
  • Sort Method: quicksort Memory: 3184kB
4. 172.665 282.216 ↓ 1.0 17,198 1

Gather (cost=12.86..53,602.99 rows=16,981 width=82) (actual time=0.734..282.216 rows=17,198 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 48.753 109.539 ↑ 1.2 8,599 2

Nested Loop Left Join (cost=2.86..27,226.59 rows=9,989 width=45) (actual time=0.320..109.539 rows=8,599 loops=2)

6. 25.320 60.782 ↑ 1.2 8,599 2

Merge Left Join (cost=2.44..17,411.34 rows=9,989 width=26) (actual time=0.306..60.782 rows=8,599 loops=2)

  • Merge Cond: (a1.stock_id = a2.id)
7. 8.621 8.621 ↑ 1.2 8,599 2

Parallel Index Scan using buy_side_holding_fields_holder_id_stock_id_report_date_idx on buy_side_holding_fields a1 (cost=0.56..15,450.39 rows=9,989 width=22) (actual time=0.022..8.621 rows=8,599 loops=2)

  • Index Cond: ((holder_id IS NOT NULL) AND (holder_id = 1930))
8. 26.841 26.841 ↑ 1.2 62,196 2

Index Only Scan using stocks_pkey on stocks a2 (cost=0.29..1,680.97 rows=72,512 width=4) (actual time=0.019..26.841 rows=62,196 loops=2)

  • Heap Fetches: 53591
9. 0.004 0.004 ↑ 1.0 1 17,198

Index Scan using stock_fields_pkey on stock_fields a3 (cost=0.42..0.98 rows=1 width=27) (actual time=0.004..0.004 rows=1 loops=17,198)

  • Index Cond: (stock_id = a2.id)
10.          

SubPlan (forGather)

11. 0.002 0.012 ↑ 1.0 1 17,198

Limit (cost=0.57..1.55 rows=1 width=36) (actual time=0.011..0.012 rows=1 loops=17,198)

12. 0.010 0.010 ↑ 2,500.0 1 17,198

Index Scan Backward using price_history_stock_id_price_date_key on price_history (cost=0.57..2,461.07 rows=2,500 width=36) (actual time=0.010..0.010 rows=1 loops=17,198)

  • Index Cond: ((stock_id = a1.stock_id) AND (price_date <= a1.report_date))
13.          

SubPlan (forResult)

14. 34.396 223.574 ↑ 1.0 1 17,198

Limit (cost=0.57..1.55 rows=1 width=36) (actual time=0.012..0.013 rows=1 loops=17,198)

15. 189.178 189.178 ↑ 2,500.0 1 17,198

Index Scan using price_history_stock_id_price_date_key on price_history p (cost=0.57..2,461.07 rows=2,500 width=36) (actual time=0.010..0.011 rows=1 loops=17,198)

  • Index Cond: ((stock_id = a1.stock_id) AND (price_date >= a1.report_date))
16. 60,042.410 418,926.082 ↑ 1.0 1 17,198

Aggregate (cost=86.38..86.39 rows=1 width=32) (actual time=24.359..24.359 rows=1 loops=17,198)

17. 20,568.808 20,568.808 ↓ 35.6 2,459 17,198

Index Scan using buy_side_holding_fields_report_date_holder_id_idx on buy_side_holding_fields h (cost=0.43..35.02 rows=69 width=14) (actual time=0.011..1.196 rows=2,459 loops=17,198)

  • Index Cond: ((report_date = a1.report_date) AND (holder_id = a1.holder_id))
18.          

SubPlan (forAggregate)

19. 42,289.358 338,314.864 ↑ 1.0 1 42,289,358

Limit (cost=0.56..0.74 rows=1 width=10) (actual time=0.008..0.008 rows=1 loops=42,289,358)

20. 296,025.506 296,025.506 ↑ 2,500.0 1 42,289,358

Index Only Scan using price_history_stock_id_price_date_price_usd_split_adjusted_idx1 on price_history p_1 (cost=0.56..436.46 rows=2,500 width=10) (actual time=0.007..0.007 rows=1 loops=42,289,358)

  • Index Cond: ((stock_id = h.stock_id) AND (price_date >= h.report_date))
  • Heap Fetches: 16863852
21. 154.782 154.782 ↑ 1.0 1 17,198

Index Scan using buy_side_holding_fields_holder_id_stock_id_report_date_idx on buy_side_holding_fields (cost=0.56..2.59 rows=1 width=6) (actual time=0.009..0.009 rows=1 loops=17,198)

  • Index Cond: ((holder_id = a1.holder_id) AND (stock_id = a1.stock_id) AND (report_date >= (a1.report_date - '100 days'::interval)) AND (report_date <= (a1.report_date - 1)))
22. 51.594 51.594 ↑ 1.0 1 17,198

Index Scan using buy_side_holding_fields_holder_id_stock_id_report_date_idx on buy_side_holding_fields buy_side_holding_fields_1 (cost=0.56..2.59 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=17,198)

  • Index Cond: ((holder_id = a1.holder_id) AND (stock_id = a1.stock_id) AND (report_date >= (a1.report_date - '100 days'::interval)) AND (report_date <= (a1.report_date - 1)))
23. 51.594 257.970 ↑ 1.0 1 17,198

Limit (cost=0.57..1.55 rows=1 width=36) (actual time=0.014..0.015 rows=1 loops=17,198)

24. 206.376 206.376 ↑ 7,500.0 1 17,198

Index Scan Backward using price_history_stock_id_price_date_key on price_history price_history_1 (cost=0.57..7,362.82 rows=7,500 width=36) (actual time=0.012..0.012 rows=1 loops=17,198)

  • Index Cond: ((stock_id = a1.stock_id) AND (price_date <= CURRENT_DATE))
25. 1,427.434 19,003.790 ↑ 1.0 1 17,198

Subquery Scan on s (cost=53.82..55.32 rows=1 width=8) (actual time=0.744..1.105 rows=1 loops=17,198)

  • Filter: (s.holder_id = a1.holder_id)
  • Rows Removed by Filter: 371
26. 5,382.974 17,576.356 ↓ 7.4 372 17,198

WindowAgg (cost=53.82..54.69 rows=50 width=18) (actual time=0.634..1.022 rows=372 loops=17,198)

27. 4,815.440 12,193.382 ↓ 7.4 372 17,198

Sort (cost=53.82..53.94 rows=50 width=10) (actual time=0.629..0.709 rows=372 loops=17,198)

  • Sort Key: buy_side_holding_fields_2.shares DESC
  • Sort Method: quicksort Memory: 35kB
28. 7,377.942 7,377.942 ↓ 7.4 372 17,198

Index Scan using buy_side_holding_fields_stock_id_report_date_idx on buy_side_holding_fields buy_side_holding_fields_2 (cost=0.43..52.40 rows=50 width=10) (actual time=0.012..0.429 rows=372 loops=17,198)

  • Index Cond: ((stock_id = a1.stock_id) AND (report_date = a1.report_date))
29. 85.990 85.990 ↑ 1.0 1 17,198

Index Scan using buy_side_holding_fields_holder_id_stock_id_report_date_idx on buy_side_holding_fields buy_side_holding_fields_3 (cost=0.56..2.59 rows=1 width=6) (actual time=0.005..0.005 rows=1 loops=17,198)

  • Index Cond: ((holder_id = a1.holder_id) AND (stock_id = a1.stock_id) AND (report_date >= (a1.report_date - '100 days'::interval)) AND (report_date <= (a1.report_date - 1)))
30. 51.888 51.888 ↑ 1.0 1 12,972

Index Scan using buy_side_holding_fields_holder_id_stock_id_report_date_idx on buy_side_holding_fields buy_side_holding_fields_4 (cost=0.56..2.59 rows=1 width=6) (actual time=0.003..0.004 rows=1 loops=12,972)

  • Index Cond: ((holder_id = a1.holder_id) AND (stock_id = a1.stock_id) AND (report_date >= (a1.report_date - '100 days'::interval)) AND (report_date <= (a1.report_date - 1)))
31. 25.005 25.005 ↑ 1.0 1 8,335

Index Scan using buy_side_holding_fields_holder_id_stock_id_report_date_idx on buy_side_holding_fields buy_side_holding_fields_5 (cost=0.56..2.59 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=8,335)

  • Index Cond: ((holder_id = a1.holder_id) AND (stock_id = a1.stock_id) AND (report_date >= (a1.report_date - '100 days'::interval)) AND (report_date <= (a1.report_date - 1)))