explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A29C

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 607.026 ↑ 1.0 10 1

Limit (cost=141,564.23..141,564.25 rows=10 width=266) (actual time=607.019..607.026 rows=10 loops=1)

2.          

CTE relevant_filings

3. 5.704 338.460 ↓ 1.3 3,801 1

Unique (cost=87,551.22..87,798.32 rows=2,949 width=28) (actual time=325.517..338.460 rows=3,801 loops=1)

4. 40.396 332.756 ↓ 1.2 59,097 1

Sort (cost=87,551.22..87,674.77 rows=49,421 width=28) (actual time=325.514..332.756 rows=59,097 loops=1)

  • Sort Key: holdings_1.investment_fund_id, sec_filings.date DESC
  • Sort Method: quicksort Memory: 6054kB
5. 6.126 292.360 ↓ 1.2 59,097 1

Gather (cost=1,597.41..83,698.15 rows=49,421 width=28) (actual time=36.077..292.360 rows=59,097 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 8.827 286.234 ↑ 1.0 11,819 5 / 5

Nested Loop (cost=597.41..77,756.05 rows=12,355 width=28) (actual time=30.825..286.234 rows=11,819 loops=5)

7. 131.655 135.574 ↑ 1.0 11,819 5 / 5

Parallel Bitmap Heap Scan on holdings holdings_1 (cost=596.98..51,599.13 rows=12,355 width=16) (actual time=30.700..135.574 rows=11,819 loops=5)

  • Recheck Cond: (company_id = 176)
  • Heap Blocks: exact=10622
8. 3.919 3.919 ↓ 1.2 59,097 1 / 5

Bitmap Index Scan on index_holdings_on_company_id (cost=0.00..584.62 rows=49,421 width=0) (actual time=19.597..19.597 rows=59,097 loops=1)

  • Index Cond: (company_id = 176)
9. 141.833 141.833 ↑ 1.0 1 59,097 / 5

Index Scan using sec_filings_pkey on sec_filings (cost=0.43..2.12 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=59,097)

  • Index Cond: (id = holdings_1.sec_filing_id)
10. 2.453 607.019 ↑ 584.1 10 1

Sort (cost=53,765.90..53,780.51 rows=5,841 width=266) (actual time=607.018..607.019 rows=10 loops=1)

  • Sort Key: ((sum((COALESCE(holdings.shares, (holdings.principal / NULLIF(holdings.value, '0'::numeric))) / 4601080000.0)) * '100'::numeric)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 30kB
11. 12.026 604.566 ↑ 1.5 3,801 1

HashAggregate (cost=53,537.46..53,639.68 rows=5,841 width=266) (actual time=600.202..604.566 rows=3,801 loops=1)

  • Group Key: investment_funds.id
12. 3.692 592.540 ↑ 6.4 3,849 1

Hash Join (cost=1,108.25..53,043.26 rows=24,710 width=218) (actual time=383.477..592.540 rows=3,849 loops=1)

  • Hash Cond: (holdings.investment_fund_id = investment_funds.id)
13. 14.359 583.857 ↑ 6.4 3,849 1

Hash Join (cost=667.83..52,537.93 rows=24,710 width=24) (actual time=378.427..583.857 rows=3,849 loops=1)

  • Hash Cond: (holdings.sec_filing_id = relevant_filings.filing_id)
14. 208.026 227.517 ↓ 1.2 59,097 1

Bitmap Heap Scan on holdings (cost=596.98..52,062.45 rows=49,421 width=32) (actual time=35.198..227.517 rows=59,097 loops=1)

  • Recheck Cond: (company_id = 176)
  • Heap Blocks: exact=54775
15. 19.491 19.491 ↓ 1.2 59,097 1

Bitmap Index Scan on index_holdings_on_company_id (cost=0.00..584.62 rows=49,421 width=0) (actual time=19.491..19.491 rows=59,097 loops=1)

  • Index Cond: (company_id = 176)
16. 0.592 341.981 ↓ 19.0 3,801 1

Hash (cost=68.35..68.35 rows=200 width=8) (actual time=341.980..341.981 rows=3,801 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 181kB
17. 1.823 341.389 ↓ 19.0 3,801 1

HashAggregate (cost=66.35..68.35 rows=200 width=8) (actual time=340.788..341.389 rows=3,801 loops=1)

  • Group Key: relevant_filings.filing_id
18. 339.566 339.566 ↓ 1.3 3,801 1

CTE Scan on relevant_filings (cost=0.00..58.98 rows=2,949 width=8) (actual time=325.521..339.566 rows=3,801 loops=1)

19. 1.937 4.991 ↓ 1.0 5,874 1

Hash (cost=367.41..367.41 rows=5,841 width=202) (actual time=4.991..4.991 rows=5,874 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1273kB
20. 3.054 3.054 ↓ 1.0 5,874 1

Seq Scan on investment_funds (cost=0.00..367.41 rows=5,841 width=202) (actual time=0.006..3.054 rows=5,874 loops=1)

Planning time : 4.463 ms
Execution time : 610.245 ms