explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ciy1 : WightedHoldingSector_20Sec_Prod

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 25,971.436 ↑ 1.0 10 1

Limit (cost=2,898,129.33..2,898,129.36 rows=10 width=312) (actual time=25,971.433..25,971.436 rows=10 loops=1)

  • Buffers: shared hit=201,660 read=1,292,271
2.          

CTE accountholdingdate

3. 0.069 115.643 ↑ 6.0 1 1

GroupAggregate (cost=0.70..40.23 rows=6 width=27) (actual time=115.642..115.643 rows=1 loops=1)

  • Group Key: h.masterentityid, h.sourcecode
  • Buffers: shared hit=258 read=233
4. 115.574 115.574 ↓ 48.2 289 1

Index Only Scan using holding_pkey on holding h (cost=0.70..40.12 rows=6 width=27) (actual time=39.772..115.574 rows=289 loops=1)

  • Index Cond: ((effectivedate = '2020-08-31'::date) AND (masterentityid = '09TBBUYBXX'::text) AND (sourcecode = 'BNYIRPWS'::text))
  • Heap Fetches: 289
  • Buffers: shared hit=258 read=233
5.          

CTE holdings

6. 1,677.510 25,967.722 ↑ 824.2 289 1

Hash Join (cost=76,456.99..2,601,719.76 rows=238,202 width=610) (actual time=4,007.635..25,967.722 rows=289 loops=1)

  • Hash Cond: ((issuermaster.primaryissueid)::text = (holding.primaryissueid)::text)
  • Join Filter: ((holding.effectivedate >= issuermaster._fromdate) AND (holding.effectivedate <= issuermaster._todate))
  • Rows Removed by Join Filter: 13,196
  • Buffers: shared hit=201,648 read=1,292,271
7. 20,329.444 20,329.444 ↑ 2.6 13,007,110 1

Seq Scan on issuermaster (cost=0.00..1,819,968.68 rows=33,627,068 width=48) (actual time=0.844..20,329.444 rows=13,007,110 loops=1)

  • Buffers: shared hit=200,024 read=1,283,674
8. 0.445 3,960.768 ↑ 9.3 289 1

Hash (cost=76,423.56..76,423.56 rows=2,675 width=109) (actual time=3,960.768..3,960.768 rows=289 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 68kB
  • Buffers: shared hit=1,624 read=8,597
9. 0.446 3,960.323 ↑ 9.3 289 1

Hash Left Join (cost=4.54..76,423.56 rows=2,675 width=109) (actual time=117.621..3,960.323 rows=289 loops=1)

  • Hash Cond: ((securityreferencemaster.issuetypecode)::text = (securityglobalcashrule.issuetypecode)::text)
  • Buffers: shared hit=1,624 read=8,597
10. 1.151 3,959.850 ↑ 9.3 289 1

Nested Loop (cost=1.26..76,402.08 rows=2,675 width=104) (actual time=117.582..3,959.850 rows=289 loops=1)

  • Buffers: shared hit=1,622 read=8,597
11. 0.387 116.733 ↓ 7.0 289 1

Nested Loop (cost=0.70..231.54 rows=41 width=50) (actual time=115.700..116.733 rows=289 loops=1)

  • Buffers: shared hit=328 read=233
12. 115.647 115.647 ↑ 6.0 1 1

CTE Scan on accountholdingdate m (cost=0.00..0.12 rows=6 width=1,040) (actual time=115.645..115.647 rows=1 loops=1)

  • Buffers: shared hit=258 read=233
13. 0.699 0.699 ↓ 41.3 289 1

Index Scan using holding_pkey on holding (cost=0.70..38.50 rows=7 width=50) (actual time=0.047..0.699 rows=289 loops=1)

  • Index Cond: ((effectivedate = m.effectivedate) AND ((masterentityid)::text = (m.masterentityid)::text) AND ((sourcecode)::text = (m.sourcecode)::text))
  • Buffers: shared hit=70
14. 3,841.966 3,841.966 ↑ 65.0 1 289

Index Scan using ix_873765_securityreferencemaster on securityreferencemaster (cost=0.56..1,857.17 rows=65 width=70) (actual time=3.257..13.294 rows=1 loops=289)

  • Index Cond: ((primaryissueid)::text = (holding.primaryissueid)::text)
  • Filter: ((_seq = 1) AND (holding.effectivedate >= _fromdate) AND (holding.effectivedate <= _todate))
  • Rows Removed by Filter: 49
  • Buffers: shared hit=1,294 read=8,364
15. 0.009 0.027 ↑ 1.0 40 1

Hash (cost=2.78..2.78 rows=40 width=10) (actual time=0.027..0.027 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=2
16. 0.018 0.018 ↑ 1.0 40 1

Seq Scan on securityglobalcashrule (cost=0.00..2.78 rows=40 width=10) (actual time=0.006..0.018 rows=40 loops=1)

  • Filter: ((_seq = 1) AND ((schemecode)::text = 'GLOBAL'::text))
  • Rows Removed by Filter: 12
  • Buffers: shared hit=2
17.          

CTE securities

18. 0.083 25,970.780 ↑ 710.3 337 1

Unique (cost=277,443.25..282,829.32 rows=239,381 width=1,148) (actual time=25,970.678..25,970.780 rows=337 loops=1)

  • Buffers: shared hit=201,657 read=1,292,271
19. 1.342 25,970.697 ↑ 710.3 337 1

Sort (cost=277,443.25..278,041.70 rows=239,381 width=1,148) (actual time=25,970.677..25,970.697 rows=337 loops=1)

  • Sort Key: holdings.masterentityid, holdings.effectivedate, holdings.issueclass, holdings.primaryissueid, holdings.level1code, holdings.level1name, (CASE WHEN ((holdings.issueclass)::text = 'EQUITY'::text) THEN holdings.marketvaluebaseamount ELSE (holdings.marketvaluebaseamount + holdings.accruedincomenetbaseamount) END), (0)
  • Sort Method: quicksort Memory: 72kB
  • Buffers: shared hit=201,657 read=1,292,271
20. 0.211 25,969.355 ↑ 710.3 337 1

Append (cost=0.00..15,500.82 rows=239,381 width=1,148) (actual time=4,007.642..25,969.355 rows=337 loops=1)

  • Buffers: shared hit=201,648 read=1,292,271
21. 25,969.075 25,969.075 ↑ 823.0 288 1

CTE Scan on holdings (cost=0.00..6,544.60 rows=237,011 width=2,116) (actual time=4,007.641..25,969.075 rows=288 loops=1)

  • Filter: ((issueclass)::text <> 'CASH'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=201,648 read=1,292,271
22. 0.069 0.069 ↑ 48.4 49 1

CTE Scan on holdings holdings_1 (cost=0.00..6,562.41 rows=2,370 width=1,148) (actual time=0.007..0.069 rows=49 loops=1)

  • Filter: (((issueclass)::text = 'CASH'::text) OR (((issueclass)::text = 'EQUITY'::text) AND (accruedincomenetbaseamount <> '0'::double precision)))
  • Rows Removed by Filter: 240
23.          

CTE totals

24. 0.039 0.231 ↑ 1.0 1 1

Aggregate (cost=7,172.45..7,172.46 rows=1 width=24) (actual time=0.231..0.231 rows=1 loops=1)

25. 0.192 0.192 ↑ 827.0 288 1

CTE Scan on securities securities_1 (cost=0.00..5,386.07 rows=238,184 width=20) (actual time=0.001..0.192 rows=288 loops=1)

  • Filter: ((level1name)::text <> 'Cash'::text)
  • Rows Removed by Filter: 49
26. 0.118 25,971.432 ↑ 119.1 10 1

Sort (cost=6,367.56..6,370.54 rows=1,191 width=312) (actual time=25,971.432..25,971.432 rows=10 loops=1)

  • Sort Key: (COALESCE(sum(securities.acctmv), '0'::double precision)) DESC
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=201,660 read=1,292,271
27. 0.265 25,971.314 ↑ 4.1 288 1

HashAggregate (cost=6,320.98..6,341.82 rows=1,191 width=312) (actual time=25,971.207..25,971.314 rows=288 loops=1)

  • Group Key: securities.level1code, securities.level1name
  • Buffers: shared hit=201,657 read=1,292,271
28. 0.079 25,971.049 ↑ 4.1 288 1

Hash Join (cost=0.03..6,291.21 rows=1,191 width=100) (actual time=25,970.938..25,971.049 rows=288 loops=1)

  • Hash Cond: (securities.effectivedate = totals.datetot)
  • Buffers: shared hit=201,657 read=1,292,271
29. 25,970.733 25,970.733 ↑ 827.0 288 1

CTE Scan on securities (cost=0.00..5,386.07 rows=238,184 width=84) (actual time=25,970.683..25,970.733 rows=288 loops=1)

  • Filter: ((level1name)::text <> 'Cash'::text)
  • Rows Removed by Filter: 49
  • Buffers: shared hit=201,657 read=1,292,271
30. 0.004 0.237 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=24) (actual time=0.237..0.237 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.233 0.233 ↑ 1.0 1 1

CTE Scan on totals (cost=0.00..0.02 rows=1 width=24) (actual time=0.233..0.233 rows=1 loops=1)

Planning time : 72.125 ms
Execution time : 25,971.742 ms