explain.depesz.com

PostgreSQL's explain analyze made readable

Result: THAi

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

Limit (cost=90,164.77..90,164.79 rows=10 width=266) (actual time=423.612..423.618 rows=10 loops=1)

2.          

CTE relevant_holdings

3. 4.845 403.995 ↓ 1.3 3,801 1

Unique (cost=89,236.19..89,483.30 rows=2,949 width=105) (actual time=386.109..403.995 rows=3,801 loops=1)

4. 116.716 399.150 ↓ 1.2 59,097 1

Sort (cost=89,236.19..89,359.75 rows=49,421 width=105) (actual time=386.107..399.150 rows=59,097 loops=1)

  • Sort Key: holdings.investment_fund_id, sec_filings.date DESC
  • Sort Method: external merge Disk: 6728kB
5. 0.000 282.434 ↓ 1.2 59,097 1

Gather (cost=1,597.41..83,700.08 rows=49,421 width=105) (actual time=42.190..282.434 rows=59,097 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 8.082 292.127 ↑ 1.0 11,819 5 / 5

Nested Loop (cost=597.41..77,757.98 rows=12,355 width=105) (actual time=39.097..292.127 rows=11,819 loops=5)

7. 137.412 142.212 ↑ 1.0 11,819 5 / 5

Parallel Bitmap Heap Scan on holdings (cost=596.98..51,599.13 rows=12,355 width=93) (actual time=38.985..142.212 rows=11,819 loops=5)

  • Recheck Cond: (company_id = 176)
  • Heap Blocks: exact=13139
8. 4.800 4.800 ↓ 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=23.999..24.000 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.sec_filing_id)
10. 2.253 423.612 ↑ 294.9 10 1

Sort (cost=681.47..688.84 rows=2,949 width=266) (actual time=423.611..423.612 rows=10 loops=1)

  • Sort Key: ((sum((COALESCE(relevant_holdings.shares, (relevant_holdings.principal / NULLIF(relevant_holdings.value, '0'::numeric))) / '4601080000'::numeric)) * '100'::numeric)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 30kB
11. 10.288 421.359 ↓ 1.3 3,801 1

HashAggregate (cost=566.13..617.74 rows=2,949 width=266) (actual time=415.904..421.359 rows=3,801 loops=1)

  • Group Key: investment_funds.id
12. 3.043 411.071 ↓ 1.3 3,801 1

Hash Join (cost=440.42..507.15 rows=2,949 width=298) (actual time=388.623..411.071 rows=3,801 loops=1)

  • Hash Cond: (relevant_holdings.investment_fund_id = investment_funds.id)
13. 405.563 405.563 ↓ 1.3 3,801 1

CTE Scan on relevant_holdings (cost=0.00..58.98 rows=2,949 width=104) (actual time=386.114..405.563 rows=3,801 loops=1)

14. 1.287 2.465 ↓ 1.0 5,874 1

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

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

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

Planning time : 0.553 ms
Execution time : 428.495 ms