explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HaSvG : WightedHoldingSector_BMK_1Sec_prod

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 367.008 ↑ 451,574.1 13 1

Sort (cost=3,782,141,012.25..3,782,155,688.41 rows=5,870,463 width=288) (actual time=367.007..367.008 rows=13 loops=1)

  • Sort Key: (COALESCE(sum(securities.acctmv), '0'::double precision)) DESC
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=94,874, temp read=23 written=21
2.          

CTE accountholdingdate

3. 0.037 0.297 ↑ 79.0 1 1

GroupAggregate (cost=0.70..29.66 rows=79 width=28) (actual time=0.297..0.297 rows=1 loops=1)

  • Group Key: h.masterentityid, h.sourcecode
  • Buffers: shared hit=289
4. 0.260 0.260 ↓ 3.5 277 1

Index Only Scan using holding_pkey on holding h (cost=0.70..28.28 rows=79 width=28) (actual time=0.049..0.260 rows=277 loops=1)

  • Index Cond: ((effectivedate = '2020-07-31'::date) AND (masterentityid = '01CB0002XX'::text) AND (sourcecode = 'STARDIRECT'::text))
  • Heap Fetches: 5
  • Buffers: shared hit=289
5.          

CTE holdings

6. 0.206 8.687 ↑ 20.3 277 1

Nested Loop Left Join (cost=4.54..157,900.39 rows=5,625 width=87) (actual time=0.389..8.687 rows=277 loops=1)

  • Buffers: shared hit=3,267
7. 0.103 0.725 ↓ 1.9 277 1

Nested Loop (cost=0.70..3,052.48 rows=145 width=51) (actual time=0.328..0.725 rows=277 loops=1)

  • Buffers: shared hit=320
8. 0.299 0.299 ↑ 79.0 1 1

CTE Scan on accountholdingdate m (cost=0.00..1.58 rows=79 width=1,040) (actual time=0.298..0.299 rows=1 loops=1)

  • Buffers: shared hit=289
9. 0.323 0.323 ↓ 39.6 277 1

Index Scan using holding_pkey on holding h_1 (cost=0.70..38.55 rows=7 width=51) (actual time=0.027..0.323 rows=277 loops=1)

  • Index Cond: ((effectivedate = m.effectivedate) AND ((masterentityid)::text = (m.masterentityid)::text) AND ((sourcecode)::text = (m.sourcecode)::text))
  • Buffers: shared hit=31
10. 0.251 7.756 ↑ 39.0 1 277

Hash Left Join (cost=3.84..1,071.34 rows=39 width=40) (actual time=0.017..0.028 rows=1 loops=277)

  • Hash Cond: ((s.issuetypecode)::text = (cr.issuetypecode)::text)
  • Buffers: shared hit=2,947
11. 7.479 7.479 ↑ 39.0 1 277

Index Scan using ix_873765_securityreferencemaster on securityreferencemaster s (cost=0.56..1,067.79 rows=39 width=35) (actual time=0.017..0.027 rows=1 loops=277)

  • Index Cond: ((h_1.primaryissueid)::text = (primaryissueid)::text)
  • Filter: ((_seq = 1) AND (h_1.effectivedate >= _fromdate) AND (h_1.effectivedate <= _todate))
  • Rows Removed by Filter: 18
  • Buffers: shared hit=2,945
12. 0.010 0.026 ↑ 1.0 40 1

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

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

Seq Scan on securityglobalcashrule cr (cost=0.00..2.78 rows=40 width=10) (actual time=0.005..0.016 rows=40 loops=1)

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

CTE benchmark

15. 0.002 0.017 ↑ 14.0 1 1

Nested Loop (cost=0.41..615.70 rows=14 width=28) (actual time=0.015..0.017 rows=1 loops=1)

  • Buffers: shared hit=4
16. 0.001 0.001 ↑ 79.0 1 1

CTE Scan on accountholdingdate a (cost=0.00..1.58 rows=79 width=516) (actual time=0.000..0.001 rows=1 loops=1)

17. 0.014 0.014 ↑ 1.0 1 1

Index Scan using accountbenchmarkrelationship_pkey on accountbenchmarkrelationship abr (cost=0.41..7.76 rows=1 width=28) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (((masterentityid)::text = (a.masterentityid)::text) AND ((segmentcode)::text = 'TF'::text) AND (benchmarkordinalnumber = 14))
  • Buffers: shared hit=4
18.          

CTE bmksources

19. 0.011 0.011 ↑ 33.3 3 1

Function Scan on unnest t (cost=0.00..1.25 rows=100 width=40) (actual time=0.010..0.011 rows=3 loops=1)

20.          

CTE bmksource

21. 1.302 8.160 ↑ 42,171.0 1 1

HashAggregate (cost=120,867.17..121,288.88 rows=42,171 width=26) (actual time=8.012..8.160 rows=1 loops=1)

  • Group Key: h_2.accountbenchmarkid, h_2.effectivedate
  • Buffers: shared hit=76
22. 1.182 6.858 ↑ 7.4 5,666 1

Hash Join (cost=3.82..120,550.89 rows=42,171 width=26) (actual time=0.081..6.858 rows=5,666 loops=1)

  • Hash Cond: ((h_2.sourcecode)::text = s_1.sourcecode)
  • Buffers: shared hit=76
23. 0.628 5.659 ↑ 7.4 5,666 1

Nested Loop (cost=0.57..119,967.79 rows=42,171 width=25) (actual time=0.057..5.659 rows=5,666 loops=1)

  • Buffers: shared hit=76
24. 0.020 0.020 ↑ 14.0 1 1

CTE Scan on benchmark b (cost=0.00..0.28 rows=14 width=516) (actual time=0.018..0.020 rows=1 loops=1)

  • Buffers: shared hit=4
25. 5.011 5.011 ↓ 1.9 5,666 1

Index Only Scan using benchmarkconstituents_pkey on benchmarkconstituents h_2 (cost=0.57..8,538.99 rows=3,012 width=25) (actual time=0.035..5.011 rows=5,666 loops=1)

  • Index Cond: ((effectivedate = '2020-07-31'::date) AND (accountbenchmarkid = (b.benchmarkid)::text))
  • Heap Fetches: 5,666
  • Buffers: shared hit=72
26. 0.004 0.017 ↑ 33.3 3 1

Hash (cost=2.00..2.00 rows=100 width=40) (actual time=0.017..0.017 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.013 0.013 ↑ 33.3 3 1

CTE Scan on bmksources s_1 (cost=0.00..2.00 rows=100 width=40) (actual time=0.011..0.013 rows=3 loops=1)

28.          

CTE bmkholdingdate

29. 0.009 8.177 ↑ 21,086.0 1 1

Hash Join (cost=3.25..1,215.67 rows=21,086 width=556) (actual time=8.027..8.177 rows=1 loops=1)

  • Hash Cond: (b_1.sortorder = bs.sortorder)
  • Buffers: shared hit=76
30. 8.162 8.162 ↑ 42,171.0 1 1

CTE Scan on bmksource b_1 (cost=0.00..843.42 rows=42,171 width=532) (actual time=8.013..8.162 rows=1 loops=1)

  • Buffers: shared hit=76
31. 0.005 0.006 ↑ 33.3 3 1

Hash (cost=2.00..2.00 rows=100 width=40) (actual time=0.006..0.006 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 0.001 0.001 ↑ 33.3 3 1

CTE Scan on bmksources bs (cost=0.00..2.00 rows=100 width=40) (actual time=0.000..0.001 rows=3 loops=1)

33.          

CTE bmkholdings

34. 1.951 201.029 ↑ 255.6 5,666 1

Hash Left Join (cost=4.41..7,927,050.23 rows=1,448,212 width=140) (actual time=8.143..201.029 rows=5,666 loops=1)

  • Hash Cond: ((s_2.issuetypecode)::text = (cr_1.issuetypecode)::text)
  • Buffers: shared hit=59,878
35. 14.288 199.053 ↑ 255.6 5,666 1

Nested Loop Left Join (cost=1.13..7,913,321.46 rows=1,448,212 width=59) (actual time=8.106..199.053 rows=5,666 loops=1)

  • Join Filter: ((b_2.effectivedate >= s_2._fromdate) AND (b_2.effectivedate <= s_2._todate))
  • Rows Removed by Join Filter: 117,087
  • Buffers: shared hit=59,876
36. 1.382 14.785 ↑ 6.6 5,666 1

Nested Loop (cost=0.57..7,430,292.60 rows=37,303 width=56) (actual time=8.071..14.785 rows=5,666 loops=1)

  • Buffers: shared hit=148
37. 8.180 8.180 ↑ 21,086.0 1 1

CTE Scan on bmkholdingdate b_2 (cost=0.00..421.72 rows=21,086 width=556) (actual time=8.030..8.180 rows=1 loops=1)

  • Buffers: shared hit=76
38. 5.223 5.223 ↓ 26.5 5,666 1

Index Scan using benchmarkconstituents_pkey on benchmarkconstituents c (cost=0.57..350.22 rows=214 width=48) (actual time=0.036..5.223 rows=5,666 loops=1)

  • Index Cond: ((effectivedate = b_2.effectivedate) AND ((accountbenchmarkid)::text = (b_2.accountbenchmarkid)::text) AND ((sourcecode)::text = b_2.sourcecode))
  • Buffers: shared hit=72
39. 169.980 169.980 ↑ 15.9 22 5,666

Index Scan using ix_873765_securityreferencemaster on securityreferencemaster s_2 (cost=0.56..7.70 rows=350 width=35) (actual time=0.010..0.030 rows=22 loops=5,666)

  • Index Cond: ((c.cadisissueid)::text = (primaryissueid)::text)
  • Filter: (_seq = 1)
  • Buffers: shared hit=59,728
40. 0.008 0.025 ↑ 1.0 40 1

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

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

Seq Scan on securityglobalcashrule cr_1 (cost=0.00..2.78 rows=40 width=10) (actual time=0.005..0.017 rows=40 loops=1)

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

CTE sectors

43. 2.346 328.837 ↑ 28.2 5,763 1

Unique (cost=11,582,333.84..11,586,392.02 rows=162,327 width=1,740) (actual time=326.132..328.837 rows=5,763 loops=1)

  • Buffers: shared hit=94,874
44. 17.107 326.491 ↑ 27.3 5,943 1

Sort (cost=11,582,333.84..11,582,739.66 rows=162,327 width=1,740) (actual time=326.130..326.491 rows=5,943 loops=1)

  • Sort Key: h_3.primaryissueid, ascm.sectorclassificationlevel2desc, ascm.sectorclassificationlevel2desc, (''::text), (''::text), (''::text), (''::text), (''::text), (''::text)
  • Sort Method: quicksort Memory: 662kB
  • Buffers: shared hit=94,874
45. 0.881 309.384 ↑ 27.3 5,943 1

Append (cost=0.57..11,323,047.62 rows=162,327 width=1,740) (actual time=0.412..309.384 rows=5,943 loops=1)

  • Buffers: shared hit=94,874
46. 0.356 13.100 ↑ 550.4 277 1

Nested Loop (cost=0.57..313,723.54 rows=152,463 width=734) (actual time=0.412..13.100 rows=277 loops=1)

  • Buffers: shared hit=4,717
47. 8.866 8.866 ↑ 20.3 277 1

CTE Scan on holdings h_3 (cost=0.00..112.50 rows=5,625 width=524) (actual time=0.390..8.866 rows=277 loops=1)

  • Buffers: shared hit=3,267
48. 3.878 3.878 ↑ 27.0 1 277

Index Scan using securityclassificationmaster_scdindex on securityclassificationmaster ascm (cost=0.57..55.48 rows=27 width=36) (actual time=0.014..0.014 rows=1 loops=277)

  • Index Cond: (((sourcecode)::text = 'EAGLE'::text) AND ((primaryissueid)::text = (h_3.primaryissueid)::text) AND ((sectorclassificationschemacode)::text = 'IVZ_FI'::text) AND (h_3.effectivedate >= _fromdate) AND (h_3.effectivedate <= _todate))
  • Buffers: shared hit=1,450
49. 5.521 295.403 ↑ 1.7 5,666 1

Nested Loop (cost=0.57..11,007,700.81 rows=9,864 width=734) (actual time=8.168..295.403 rows=5,666 loops=1)

  • Buffers: shared hit=90,157
50. 204.892 204.892 ↑ 255.6 5,666 1

CTE Scan on bmkholdings c_1 (cost=0.00..28,964.24 rows=1,448,212 width=588) (actual time=8.144..204.892 rows=5,666 loops=1)

  • Buffers: shared hit=59,878
51. 84.990 84.990 ↑ 6.0 1 5,666

Index Scan using securityclassificationmaster_scdindex on securityclassificationmaster bscm (cost=0.57..7.52 rows=6 width=49) (actual time=0.015..0.015 rows=1 loops=5,666)

  • Index Cond: (((sourcecode)::text = c_1.sectorsource) AND ((primaryissueid)::text = (c_1.cadisissueid)::text) AND ((sectorclassificationschemacode)::text = c_1.sectorscheme) AND (c_1.effectivedate >= _fromdate) AND (c_1.effectivedate <= _todate))
  • Buffers: shared hit=30,279
52.          

CTE securities

53. 1.647 354.430 ↑ 197,558.9 5,943 1

Unique (cost=3,651,674,146.24..3,695,702,615.59 rows=1,174,092,516 width=1,344) (actual time=352.428..354.430 rows=5,943 loops=1)

  • Buffers: shared hit=94,874, temp read=23 written=21
54. 4.458 352.783 ↑ 197,558.9 5,943 1

Sort (cost=3,651,674,146.24..3,654,609,377.53 rows=1,174,092,516 width=1,344) (actual time=352.427..352.783 rows=5,943 loops=1)

  • Sort Key: h_4.masterentityid, h_4.effectivedate, h_4.issueclass, sa.id, sa.level1code, sa.level1name, sa.level2code, sa.level2name, sa.level3code, sa.level3name, sa.level4code, sa.level4name, (CASE WHEN ((h_4.issueclass)::text = 'EQUITY'::text) THEN h_4.marketvaluebaseamount ELSE (h_4.marketvaluebaseamount + h_4.accruedincomenetbaseamount) END), (((0))::double precision)
  • Sort Method: quicksort Memory: 1,028kB
  • Buffers: shared hit=94,874, temp read=23 written=21
55. 0.523 348.325 ↑ 197,558.9 5,943 1

Append (cost=8,770,504.65..43,678,707.39 rows=1,174,092,516 width=1,344) (actual time=332.300..348.325 rows=5,943 loops=1)

  • Buffers: shared hit=94,874, temp read=23 written=21
56. 0.060 332.442 ↑ 16,399.9 277 1

Result (cost=8,770,504.65..8,997,643.50 rows=4,542,777 width=1,344) (actual time=332.299..332.442 rows=277 loops=1)

  • Buffers: shared hit=94,874, temp read=23 written=21
57. 0.069 332.382 ↑ 16,399.9 277 1

Unique (cost=8,770,504.65..8,940,858.79 rows=4,542,777 width=1,340) (actual time=332.297..332.382 rows=277 loops=1)

  • Buffers: shared hit=94,874, temp read=23 written=21
58. 0.884 332.313 ↑ 16,399.9 277 1

Sort (cost=8,770,504.65..8,781,861.59 rows=4,542,777 width=1,340) (actual time=332.296..332.313 rows=277 loops=1)

  • Sort Key: h_4.masterentityid, h_4.effectivedate, h_4.issueclass, sa.id, sa.level1code, sa.level1name, sa.level2code, sa.level2name, sa.level3code, sa.level3name, sa.level4code, sa.level4name, (CASE WHEN ((h_4.issueclass)::text = 'EQUITY'::text) THEN h_4.marketvaluebaseamount ELSE (h_4.marketvaluebaseamount + h_4.accruedincomenetbaseamount) END), (0)
  • Sort Method: quicksort Memory: 63kB
  • Buffers: shared hit=94,874, temp read=23 written=21
59. 0.029 331.429 ↑ 16,399.9 277 1

Append (cost=956.52..302,789.34 rows=4,542,777 width=1,340) (actual time=326.403..331.429 rows=277 loops=1)

  • Buffers: shared hit=94,874, temp read=23 written=21
60. 1.204 331.362 ↑ 16,519.0 275 1

Hash Join (cost=956.52..257,206.60 rows=4,542,721 width=2,308) (actual time=326.403..331.362 rows=275 loops=1)

  • Hash Cond: ((sa.id)::text = (h_4.primaryissueid)::text)
  • Buffers: shared hit=94,874, temp read=23 written=21
61. 329.955 329.955 ↑ 28.2 5,763 1

CTE Scan on sectors sa (cost=0.00..3,246.54 rows=162,327 width=1,740) (actual time=326.134..329.955 rows=5,763 loops=1)

  • Buffers: shared hit=94,874
62. 0.144 0.203 ↑ 20.4 275 1

Hash (cost=126.56..126.56 rows=5,597 width=1,088) (actual time=0.203..0.203 rows=275 loops=1)

  • Buckets: 4,096 Batches: 2 Memory Usage: 45kB
  • Buffers: temp written=1
63. 0.059 0.059 ↑ 20.4 275 1

CTE Scan on holdings h_4 (cost=0.00..126.56 rows=5,597 width=1,088) (actual time=0.003..0.059 rows=275 loops=1)

  • Filter: ((issueclass)::text <> 'CASH'::text)
  • Rows Removed by Filter: 2
64. 0.038 0.038 ↑ 28.0 2 1

CTE Scan on holdings (cost=0.00..154.97 rows=56 width=1,340) (actual time=0.022..0.038 rows=2 loops=1)

  • Filter: (((issueclass)::text = 'CASH'::text) OR (((issueclass)::text = 'EQUITY'::text) AND (accruedincomenetbaseamount <> '0'::double precision)))
  • Rows Removed by Filter: 275
65. 0.785 14.839 ↑ 206,414.1 5,666 1

Subquery Scan on *SELECT* 3 (cost=2,481,555.85..34,648,388.60 rows=1,169,542,498 width=2,312) (actual time=7.592..14.839 rows=5,666 loops=1)

66. 5.065 14.054 ↑ 206,414.1 5,666 1

Merge Join (cost=2,481,555.85..20,029,107.38 rows=1,169,542,498 width=2,308) (actual time=7.591..14.054 rows=5,666 loops=1)

  • Merge Cond: ((sb.id)::text = (c_2.cadisissueid)::text)
67. 3.450 4.403 ↑ 28.2 5,763 1

Sort (cost=262,532.76..262,938.58 rows=162,327 width=1,740) (actual time=4.050..4.403 rows=5,763 loops=1)

  • Sort Key: sb.id
  • Sort Method: quicksort Memory: 648kB
68. 0.953 0.953 ↑ 28.2 5,763 1

CTE Scan on sectors sb (cost=0.00..3,246.54 rows=162,327 width=1,740) (actual time=0.001..0.953 rows=5,763 loops=1)

69. 0.702 4.586 ↑ 254.3 5,666 1

Materialize (cost=2,219,023.09..2,226,227.94 rows=1,440,971 width=1,080) (actual time=3.535..4.586 rows=5,666 loops=1)

70. 2.915 3.884 ↑ 254.3 5,666 1

Sort (cost=2,219,023.09..2,222,625.52 rows=1,440,971 width=1,080) (actual time=3.532..3.884 rows=5,666 loops=1)

  • Sort Key: c_2.cadisissueid
  • Sort Method: quicksort Memory: 635kB
71. 0.969 0.969 ↑ 254.3 5,666 1

CTE Scan on bmkholdings c_2 (cost=0.00..32,584.77 rows=1,440,971 width=1,080) (actual time=0.002..0.969 rows=5,666 loops=1)

  • Filter: ((issueclass)::text <> 'CASH'::text)
72. 0.001 0.521 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0.00..32,675.28 rows=7,241 width=1,344) (actual time=0.520..0.521 rows=0 loops=1)

73. 0.520 0.520 ↓ 0.0 0 1

CTE Scan on bmkholdings c_3 (cost=0.00..32,584.77 rows=7,241 width=1,340) (actual time=0.520..0.520 rows=0 loops=1)

  • Filter: ((issueclass)::text = 'CASH'::text)
  • Rows Removed by Filter: 5,666
74.          

CTE totals

75. 1.036 4.339 ↑ 1.0 1 1

Aggregate (cost=32,287,544.19..32,287,544.20 rows=1 width=24) (actual time=4.339..4.339 rows=1 loops=1)

76. 3.303 3.303 ↑ 197,558.9 5,943 1

CTE Scan on securities securities_1 (cost=0.00..23,481,850.32 rows=1,174,092,516 width=24) (actual time=0.000..3.303 rows=5,943 loops=1)

77. 2.542 366.991 ↑ 451,574.1 13 1

GroupAggregate (cost=31,011,197.48..31,348,749.10 rows=5,870,463 width=288) (actual time=364.094..366.991 rows=13 loops=1)

  • Group Key: securities.level1code, securities.level1name, securities.level2code, securities.level2name, securities.level3code, securities.level3name, securities.level4code, securities.level4name
  • Buffers: shared hit=94,874, temp read=23 written=21
78. 5.508 364.449 ↑ 987.8 5,943 1

Sort (cost=31,011,197.48..31,025,873.64 rows=5,870,463 width=296) (actual time=364.080..364.449 rows=5,943 loops=1)

  • Sort Key: securities.level1code, securities.level1name, securities.level2code, securities.level2name, securities.level3code, securities.level3name, securities.level4code, securities.level4name
  • Sort Method: quicksort Memory: 1,028kB
  • Buffers: shared hit=94,874, temp read=23 written=21
79. 1.629 358.941 ↑ 987.8 5,943 1

Hash Join (cost=0.03..27,943,401.92 rows=5,870,463 width=296) (actual time=356.793..358.941 rows=5,943 loops=1)

  • Hash Cond: (securities.effectivedate = totals.datetot)
  • Buffers: shared hit=94,874, temp read=23 written=21
80. 352.965 352.965 ↑ 197,558.9 5,943 1

CTE Scan on securities (cost=0.00..23,481,850.32 rows=1,174,092,516 width=280) (actual time=352.430..352.965 rows=5,943 loops=1)

  • Buffers: shared hit=94,874, temp read=23 written=21
81. 0.005 4.347 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
82. 4.342 4.342 ↑ 1.0 1 1

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

Planning time : 3.177 ms
Execution time : 369.051 ms