explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OtMw

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

Limit (cost=7,873.04..7,873.07 rows=10 width=265) (actual time=247.026..247.032 rows=10 loops=1)

2.          

CTE relevant_filings

3. 0.088 103.340 ↑ 1.2 36 1

Unique (cost=6,313.57..6,316.40 rows=44 width=28) (actual time=103.204..103.340 rows=36 loops=1)

4. 0.642 103.252 ↑ 1.0 546 1

Sort (cost=6,313.57..6,314.98 rows=565 width=28) (actual time=103.203..103.252 rows=546 loops=1)

  • Sort Key: holdings_1.investment_fund_id, sec_filings.date DESC
  • Sort Method: quicksort Memory: 67kB
5. 0.442 102.610 ↑ 1.0 546 1

Nested Loop (cost=21.23..6,287.74 rows=565 width=28) (actual time=90.520..102.610 rows=546 loops=1)

6. 28.781 96.708 ↑ 1.0 546 1

Bitmap Heap Scan on holdings holdings_1 (cost=20.80..1,602.91 rows=565 width=16) (actual time=90.446..96.708 rows=546 loops=1)

  • Recheck Cond: (company_id = 176)
  • Heap Blocks: exact=507
7. 67.927 67.927 ↑ 1.0 549 1

Bitmap Index Scan on index_holdings_on_company_id (cost=0.00..20.66 rows=565 width=0) (actual time=67.927..67.927 rows=549 loops=1)

  • Index Cond: (company_id = 176)
8. 5.460 5.460 ↑ 1.0 1 546

Index Scan using sec_filings_pkey on sec_filings (cost=0.43..8.29 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=546)

  • Index Cond: (id = holdings_1.sec_filing_id)
9. 0.754 247.026 ↑ 2.6 10 1

Sort (cost=1,556.65..1,556.71 rows=26 width=265) (actual time=247.025..247.026 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: 29kB
10. 0.170 246.272 ↓ 1.4 36 1

GroupAggregate (cost=1,554.53..1,556.08 rows=26 width=265) (actual time=246.086..246.272 rows=36 loops=1)

  • Group Key: investment_funds.id
11. 0.045 246.102 ↓ 1.4 37 1

Merge Join (cost=1,554.53..1,555.11 rows=26 width=215) (actual time=246.056..246.102 rows=37 loops=1)

  • Merge Cond: (holdings.investment_fund_id = investment_funds.id)
12. 0.071 223.407 ↓ 1.4 37 1

Sort (cost=1,550.94..1,551.00 rows=26 width=22) (actual time=223.402..223.407 rows=37 loops=1)

  • Sort Key: holdings.investment_fund_id
  • Sort Method: quicksort Memory: 27kB
13. 0.138 223.336 ↓ 1.4 37 1

Nested Loop (cost=32.17..1,550.33 rows=26 width=22) (actual time=148.377..223.336 rows=37 loops=1)

14. 22.159 125.530 ↑ 1.2 36 1

HashAggregate (cost=0.99..1.43 rows=44 width=8) (actual time=125.505..125.530 rows=36 loops=1)

  • Group Key: relevant_filings.filing_id
15. 103.371 103.371 ↑ 1.2 36 1

CTE Scan on relevant_filings (cost=0.00..0.88 rows=44 width=8) (actual time=103.208..103.371 rows=36 loops=1)

16. 0.144 97.668 ↑ 1.0 1 36

Bitmap Heap Scan on holdings (cost=31.18..35.19 rows=1 width=30) (actual time=2.713..2.713 rows=1 loops=36)

  • Recheck Cond: ((sec_filing_id = relevant_filings.filing_id) AND (company_id = 176))
  • Heap Blocks: exact=36
17. 0.216 97.524 ↓ 0.0 0 36

BitmapAnd (cost=31.18..31.18 rows=1 width=0) (actual time=2.709..2.709 rows=0 loops=36)

18. 3.312 3.312 ↓ 2.1 529 36

Bitmap Index Scan on index_holdings_on_sec_filing_id (cost=0.00..10.13 rows=252 width=0) (actual time=0.092..0.092 rows=529 loops=36)

  • Index Cond: (sec_filing_id = relevant_filings.filing_id)
19. 93.996 93.996 ↑ 1.0 549 36

Bitmap Index Scan on index_holdings_on_company_id (cost=0.00..20.66 rows=565 width=0) (actual time=2.611..2.611 rows=549 loops=36)

  • Index Cond: (company_id = 176)
20. 22.613 22.650 ↓ 1.0 45 1

Sort (cost=3.60..3.70 rows=43 width=201) (actual time=22.646..22.650 rows=45 loops=1)

  • Sort Key: investment_funds.id
  • Sort Method: quicksort Memory: 36kB
21. 0.037 0.037 ↓ 1.0 45 1

Seq Scan on investment_funds (cost=0.00..2.43 rows=43 width=201) (actual time=0.025..0.037 rows=45 loops=1)

Planning time : 9.216 ms
Execution time : 288.133 ms