explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uIo0

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

Limit (cost=430,953.65..430,953.67 rows=10 width=68) (actual time=421.413..421.420 rows=10 loops=1)

2.          

CTE relevant_filings

3. 5.762 314.934 ↓ 1.3 3,801 1

Unique (cost=87,550.94..87,798.05 rows=2,949 width=36) (actual time=302.176..314.934 rows=3,801 loops=1)

4. 35.510 309.172 ↓ 1.2 59,097 1

Sort (cost=87,550.94..87,674.50 rows=49,421 width=36) (actual time=302.174..309.172 rows=59,097 loops=1)

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

Gather (cost=1,597.41..83,697.88 rows=49,421 width=36) (actual time=35.421..273.662 rows=59,097 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 7.980 268.887 ↑ 1.0 11,819 5 / 5

Nested Loop (cost=597.41..77,755.78 rows=12,355 width=36) (actual time=31.264..268.887 rows=11,819 loops=5)

7. 126.914 130.894 ↑ 1.0 11,819 5 / 5

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

  • Recheck Cond: (company_id = 176)
  • Heap Blocks: exact=9731
8. 3.980 3.980 ↓ 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.900..19.900 rows=59,097 loops=1)

  • Index Cond: (company_id = 176)
9. 130.013 130.013 ↑ 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.011..0.011 rows=1 loops=59,097)

  • Index Cond: (id = holdings_1.sec_filing_id)
10. 0.904 421.413 ↑ 584.1 10 1

Sort (cost=343,155.60..343,170.20 rows=5,841 width=68) (actual time=421.412..421.413 rows=10 loops=1)

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

HashAggregate (cost=342,941.76..343,029.38 rows=5,841 width=68) (actual time=418.356..420.509 rows=3,801 loops=1)

  • Group Key: investment_funds.id
12. 1.251 413.867 ↑ 5,000.9 3,801 1

Hash Join (cost=5,023.61..105,338.76 rows=19,008,240 width=52) (actual time=365.163..413.867 rows=3,801 loops=1)

  • Hash Cond: (holdings.company_id = companies.id)
13. 2.295 368.939 ↑ 5,000.9 3,801 1

Hash Join (cost=507.34..50,924.86 rows=19,008,240 width=60) (actual time=321.137..368.939 rows=3,801 loops=1)

  • Hash Cond: (holdings.investment_fund_id = investment_funds.id)
14. 2.654 363.157 ↑ 5,000.9 3,801 1

Nested Loop (cost=66.92..551.60 rows=19,008,240 width=32) (actual time=317.585..363.157 rows=3,801 loops=1)

15. 2.421 318.692 ↓ 19.0 3,801 1

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

  • Group Key: relevant_filings.holding_id
16. 316.271 316.271 ↓ 1.3 3,801 1

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

17. 41.811 41.811 ↑ 1.0 1 3,801

Index Scan using holdings_pkey on holdings (cost=0.56..2.78 rows=1 width=40) (actual time=0.011..0.011 rows=1 loops=3,801)

  • Index Cond: (id = relevant_filings.holding_id)
18. 1.173 3.487 ↓ 1.0 5,874 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 476kB
19. 2.314 2.314 ↓ 1.0 5,874 1

Seq Scan on investment_funds (cost=0.00..367.41 rows=5,841 width=36) (actual time=0.012..2.314 rows=5,874 loops=1)

20. 17.148 43.677 ↑ 1.1 72,733 1

Hash (cost=3,490.41..3,490.41 rows=82,069 width=8) (actual time=43.677..43.677 rows=72,733 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3866kB
21. 26.529 26.529 ↑ 1.1 72,733 1

Index Only Scan using companies_pkey on companies (cost=0.42..3,490.41 rows=82,069 width=8) (actual time=0.020..26.529 rows=72,733 loops=1)

  • Heap Fetches: 2196
Planning time : 7.571 ms
Execution time : 424.202 ms