explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jpg : WightedHoldingSector_BMK_335s_QA

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 335,623.322 ↑ 1,617,892.1 13 1

Sort (cost=15,997,733,227.95..15,997,785,809.44 rows=21,032,597 width=288) (actual time=335,623.321..335,623.322 rows=13 loops=1)

  • Sort Key: (COALESCE(sum(securities.acctmv), '0'::double precision)) DESC
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=453,095 read=3,297,252, temp read=39 written=33
2.          

CTE accountholdingdate

3. 0.073 171.250 ↑ 96.0 1 1

GroupAggregate (cost=0.70..402.05 rows=96 width=27) (actual time=171.250..171.250 rows=1 loops=1)

  • Group Key: h.masterentityid, h.sourcecode
  • Buffers: shared hit=146 read=350
4. 171.177 171.177 ↓ 2.9 277 1

Index Only Scan using holding_pkey on holding h (cost=0.70..400.37 rows=96 width=27) (actual time=27.876..171.177 rows=277 loops=1)

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

CTE holdings

6. 0.704 2,565.979 ↑ 33.5 277 1

Nested Loop Left Join (cost=4.54..263,240.84 rows=9,266 width=86) (actual time=176.712..2,565.979 rows=277 loops=1)

  • Buffers: shared hit=939 read=3,400
7. 0.358 172.272 ↓ 2.0 277 1

Nested Loop (cost=0.70..3,704.61 rows=142 width=50) (actual time=171.299..172.272 rows=277 loops=1)

  • Buffers: shared hit=177 read=350
8. 171.254 171.254 ↑ 96.0 1 1

CTE Scan on accountholdingdate m (cost=0.00..1.92 rows=96 width=1,040) (actual time=171.253..171.254 rows=1 loops=1)

  • Buffers: shared hit=146 read=350
9. 0.660 0.660 ↓ 39.6 277 1

Index Scan using holding_pkey on holding h_1 (cost=0.70..38.50 rows=7 width=50) (actual time=0.040..0.660 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. 1.098 2,393.003 ↑ 65.0 1 277

Hash Left Join (cost=3.85..1,830.89 rows=65 width=40) (actual time=5.018..8.639 rows=1 loops=277)

  • Hash Cond: ((s.issuetypecode)::text = (cr.issuetypecode)::text)
  • Buffers: shared hit=762 read=3,050
11. 2,390.510 2,390.510 ↑ 65.0 1 277

Index Scan using ix_873765_securityreferencemaster on securityreferencemaster s (cost=0.56..1,827.16 rows=65 width=35) (actual time=5.009..8.630 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=761 read=3,049
12. 0.009 1.395 ↑ 1.0 40 1

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

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

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

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

CTE benchmark

15. 0.006 4.027 ↑ 17.0 1 1

Nested Loop (cost=0.41..736.24 rows=17 width=28) (actual time=4.024..4.027 rows=1 loops=1)

  • Buffers: shared read=4
16. 0.002 0.002 ↑ 96.0 1 1

CTE Scan on accountholdingdate a (cost=0.00..1.92 rows=96 width=516) (actual time=0.001..0.002 rows=1 loops=1)

17. 4.019 4.019 ↑ 1.0 1 1

Index Scan using accountbenchmarkrelationship_pkey on accountbenchmarkrelationship abr (cost=0.41..7.64 rows=1 width=28) (actual time=4.017..4.019 rows=1 loops=1)

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

CTE bmksources

19. 0.019 0.019 ↑ 33.3 3 1

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

20.          

CTE bmksource

21. 0.730 280.660 ↑ 54,764.0 1 1

GroupAggregate (cost=170,342.81..171,438.09 rows=54,764 width=26) (actual time=280.659..280.660 rows=1 loops=1)

  • Group Key: h_2.accountbenchmarkid, h_2.effectivedate
  • Buffers: shared hit=5,480 read=5,484
22. 1.653 279.930 ↑ 9.7 5,666 1

Sort (cost=170,342.81..170,479.72 rows=54,764 width=26) (actual time=279.599..279.930 rows=5,666 loops=1)

  • Sort Key: h_2.accountbenchmarkid
  • Sort Method: quicksort Memory: 635kB
  • Buffers: shared hit=5,480 read=5,484
23. 1.354 278.277 ↑ 9.7 5,666 1

Hash Join (cost=3.82..166,032.62 rows=54,764 width=26) (actual time=51.265..278.277 rows=5,666 loops=1)

  • Hash Cond: ((h_2.sourcecode)::text = s_1.sourcecode)
  • Buffers: shared hit=5,480 read=5,484
24. 0.675 276.897 ↑ 9.7 5,666 1

Nested Loop (cost=0.57..165,276.37 rows=54,764 width=25) (actual time=51.223..276.897 rows=5,666 loops=1)

  • Buffers: shared hit=5,480 read=5,484
25. 4.030 4.030 ↑ 17.0 1 1

CTE Scan on benchmark b (cost=0.00..0.34 rows=17 width=516) (actual time=4.026..4.030 rows=1 loops=1)

  • Buffers: shared read=4
26. 272.192 272.192 ↓ 1.8 5,666 1

Index Only Scan using benchmarkconstituents_pkey on benchmarkconstituents h_2 (cost=0.57..9,689.91 rows=3,221 width=25) (actual time=47.187..272.192 rows=5,666 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 0.022 0.022 ↑ 33.3 3 1

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

29.          

CTE bmkholdingdate

30. 0.042 280.708 ↑ 27,382.0 1 1

Hash Join (cost=3.25..1,577.71 rows=27,382 width=556) (actual time=280.706..280.708 rows=1 loops=1)

  • Hash Cond: (b_1.sortorder = bs.sortorder)
  • Buffers: shared hit=5,483 read=5,484
31. 280.662 280.662 ↑ 54,764.0 1 1

CTE Scan on bmksource b_1 (cost=0.00..1,095.28 rows=54,764 width=532) (actual time=280.661..280.662 rows=1 loops=1)

  • Buffers: shared hit=5,480 read=5,484
32. 0.003 0.004 ↑ 33.3 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 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.001..0.001 rows=3 loops=1)

34.          

CTE bmkholdings

35. 10.463 41,745.605 ↑ 541.3 5,666 1

Hash Left Join (cost=4.41..9,379,085.75 rows=3,067,128 width=140) (actual time=286.582..41,745.605 rows=5,666 loops=1)

  • Hash Cond: ((s_2.issuetypecode)::text = (cr_1.issuetypecode)::text)
  • Buffers: shared hit=32,056 read=84,826
36. 38.723 41,735.116 ↑ 541.3 5,666 1

Nested Loop Left Join (cost=1.13..9,350,552.29 rows=3,067,128 width=59) (actual time=286.548..41,735.116 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: 116,934
  • Buffers: shared hit=32,054 read=84,826
37. 4.329 294.931 ↑ 8.3 5,666 1

Nested Loop (cost=0.57..8,351,144.79 rows=46,953 width=56) (actual time=280.749..294.931 rows=5,666 loops=1)

  • Buffers: shared hit=5,555 read=5,484
38. 280.710 280.710 ↑ 27,382.0 1 1

CTE Scan on bmkholdingdate b_2 (cost=0.00..547.64 rows=27,382 width=556) (actual time=280.708..280.710 rows=1 loops=1)

  • Buffers: shared hit=5,483 read=5,484
39. 9.892 9.892 ↓ 24.0 5,666 1

Index Scan using benchmarkconstituents_pkey on benchmarkconstituents c (cost=0.57..302.61 rows=236 width=48) (actual time=0.036..9.892 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
40. 41,401.462 41,401.462 ↑ 26.7 22 5,666

Index Scan using ix_873765_securityreferencemaster on securityreferencemaster s_2 (cost=0.56..12.47 rows=588 width=35) (actual time=1.392..7.307 rows=22 loops=5,666)

  • Index Cond: ((c.cadisissueid)::text = (primaryissueid)::text)
  • Filter: (_seq = 1)
  • Buffers: shared hit=26,499 read=79,342
41. 0.008 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
42. 0.018 0.018 ↑ 1.0 40 1

Seq Scan on securityglobalcashrule cr_1 (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
43.          

CTE sectors

44. 2.354 335,585.075 ↑ 47.7 5,763 1

Unique (cost=13,940,594.10..13,947,465.20 rows=274,844 width=1,740) (actual time=335,582.374..335,585.075 rows=5,763 loops=1)

  • Buffers: shared hit=453,086 read=3,297,252
45. 22.685 335,582.721 ↑ 46.2 5,943 1

Sort (cost=13,940,594.10..13,941,281.21 rows=274,844 width=1,740) (actual time=335,582.373..335,582.721 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=453,086 read=3,297,252
46. 2.250 335,560.036 ↑ 46.2 5,943 1

Append (cost=0.57..13,500,545.35 rows=274,844 width=1,740) (actual time=181.696..335,560.036 rows=5,943 loops=1)

  • Buffers: shared hit=453,086 read=3,297,252
47. 1.091 3,420.542 ↑ 917.1 277 1

Nested Loop (cost=0.57..507,207.86 rows=254,032 width=734) (actual time=181.695..3,420.542 rows=277 loops=1)

  • Buffers: shared hit=1,549 read=4,235
48. 2,566.568 2,566.568 ↑ 33.5 277 1

CTE Scan on holdings h_3 (cost=0.00..185.32 rows=9,266 width=524) (actual time=176.714..2,566.568 rows=277 loops=1)

  • Buffers: shared hit=939 read=3,400
49. 852.883 852.883 ↑ 27.0 1 277

Index Scan using securityclassificationmaster_scdindex on securityclassificationmaster ascm (cost=0.57..54.45 rows=27 width=36) (actual time=3.067..3.079 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=610 read=835
50. 12,121.634 332,137.244 ↑ 3.7 5,666 1

Merge Join (cost=2,813,459.51..12,990,589.05 rows=20,812 width=734) (actual time=41,833.481..332,137.244 rows=5,666 loops=1)

  • Merge Cond: (((bscm.sourcecode)::text = c_1.sectorsource) AND ((bscm.primaryissueid)::text = (c_1.cadisissueid)::text) AND ((bscm.sectorclassificationschemacode)::text = c_1.sectorscheme))
  • Join Filter: ((c_1.effectivedate >= bscm._fromdate) AND (c_1.effectivedate <= bscm._todate))
  • Rows Removed by Join Filter: 153,840
  • Buffers: shared hit=451,537 read=3,293,017
51. 278,233.214 278,233.214 ↑ 1.0 74,701,005 1

Index Scan using securityclassificationmaster_scdindex on securityclassificationmaster bscm (cost=0.57..9,573,421.81 rows=75,007,943 width=49) (actual time=3.317..278,233.214 rows=74,701,005 loops=1)

  • Buffers: shared hit=419,481 read=3,208,191
52. 12.189 41,782.396 ↑ 19.3 159,291 1

Materialize (cost=2,813,458.94..2,828,794.58 rows=3,067,128 width=588) (actual time=41,766.657..41,782.396 rows=159,291 loops=1)

  • Buffers: shared hit=32,056 read=84,826
53. 10.884 41,770.207 ↑ 541.3 5,666 1

Sort (cost=2,813,458.94..2,821,126.76 rows=3,067,128 width=588) (actual time=41,766.649..41,770.207 rows=5,666 loops=1)

  • Sort Key: c_1.sectorsource, c_1.cadisissueid, c_1.sectorscheme
  • Sort Method: quicksort Memory: 635kB
  • Buffers: shared hit=32,056 read=84,826
54. 41,759.323 41,759.323 ↑ 541.3 5,666 1

CTE Scan on bmkholdings c_1 (cost=0.00..61,342.56 rows=3,067,128 width=588) (actual time=286.585..41,759.323 rows=5,666 loops=1)

  • Buffers: shared hit=32,056 read=84,826
55.          

CTE securities

56. 1.641 335,610.829 ↑ 707,810.8 5,943 1

Unique (cost=15,577,066,927.16..15,734,811,402.34 rows=4,206,519,338 width=1,344) (actual time=335,608.834..335,610.829 rows=5,943 loops=1)

  • Buffers: shared hit=453,092 read=3,297,252, temp read=39 written=33
57. 4.457 335,609.188 ↑ 707,810.8 5,943 1

Sort (cost=15,577,066,927.16..15,587,583,225.51 rows=4,206,519,338 width=1,344) (actual time=335,608.833..335,609.188 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=453,092 read=3,297,252, temp read=39 written=33
58. 0.527 335,604.731 ↑ 707,810.8 5,943 1

Append (cost=31,878,217.16..153,082,028.20 rows=4,206,519,338 width=1,344) (actual time=335,588.803..335,604.731 rows=5,943 loops=1)

  • Buffers: shared hit=453,089 read=3,297,252, temp read=39 written=33
59. 0.054 335,588.933 ↑ 45,741.5 277 1

Result (cost=31,878,217.16..32,511,737.16 rows=12,670,400 width=1,344) (actual time=335,588.803..335,588.933 rows=277 loops=1)

  • Buffers: shared hit=453,089 read=3,297,252, temp read=39 written=33
60. 0.063 335,588.879 ↑ 45,741.5 277 1

Unique (cost=31,878,217.16..32,353,357.16 rows=12,670,400 width=1,340) (actual time=335,588.801..335,588.879 rows=277 loops=1)

  • Buffers: shared hit=453,089 read=3,297,252, temp read=39 written=33
61. 0.782 335,588.816 ↑ 45,741.5 277 1

Sort (cost=31,878,217.16..31,909,893.16 rows=12,670,400 width=1,340) (actual time=335,588.800..335,588.816 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=453,089 read=3,297,252, temp read=39 written=33
62. 0.029 335,588.034 ↑ 45,741.5 277 1

Append (cost=1,575.74..761,417.33 rows=12,670,400 width=1,340) (actual time=335,582.721..335,588.034 rows=277 loops=1)

  • Buffers: shared hit=453,086 read=3,297,252, temp read=39 written=33
63. 1.513 335,587.964 ↑ 46,073.8 275 1

Hash Join (cost=1,575.74..634,458.06 rows=12,670,308 width=2,308) (actual time=335,582.720..335,587.964 rows=275 loops=1)

  • Hash Cond: ((sa.id)::text = (h_4.primaryissueid)::text)
  • Buffers: shared hit=453,086 read=3,297,252, temp read=39 written=33
64. 335,586.224 335,586.224 ↑ 47.7 5,763 1

CTE Scan on sectors sa (cost=0.00..5,496.88 rows=274,844 width=1,740) (actual time=335,582.376..335,586.224 rows=5,763 loops=1)

  • Buffers: shared hit=453,086 read=3,297,252
65. 0.148 0.227 ↑ 33.5 275 1

Hash (cost=208.48..208.48 rows=9,220 width=1,088) (actual time=0.227..0.227 rows=275 loops=1)

  • Buckets: 4,096 Batches: 4 Memory Usage: 38kB
66. 0.079 0.079 ↑ 33.5 275 1

CTE Scan on holdings h_4 (cost=0.00..208.48 rows=9,220 width=1,088) (actual time=0.003..0.079 rows=275 loops=1)

  • Filter: ((issueclass)::text <> 'CASH'::text)
  • Rows Removed by Filter: 2
67. 0.041 0.041 ↑ 46.0 2 1

CTE Scan on holdings (cost=0.00..255.28 rows=92 width=1,340) (actual time=0.024..0.041 rows=2 loops=1)

  • Filter: (((issueclass)::text = 'CASH'::text) OR (((issueclass)::text = 'EQUITY'::text) AND (accruedincomenetbaseamount <> '0'::double precision)))
  • Rows Removed by Filter: 275
68. 0.755 14.709 ↑ 740,175.4 5,666 1

Subquery Scan on *SELECT* 3 (cost=5,161,661.21..120,501,088.96 rows=4,193,833,602 width=2,312) (actual time=7.575..14.709 rows=5,666 loops=1)

69. 5.016 13.954 ↑ 740,175.4 5,666 1

Merge Join (cost=5,161,661.21..68,078,168.94 rows=4,193,833,602 width=2,308) (actual time=7.574..13.954 rows=5,666 loops=1)

  • Merge Cond: ((sb.id)::text = (c_2.cadisissueid)::text)
70. 3.406 4.344 ↑ 47.7 5,763 1

Sort (cost=445,545.64..446,232.75 rows=274,844 width=1,740) (actual time=3.992..4.344 rows=5,763 loops=1)

  • Sort Key: sb.id
  • Sort Method: quicksort Memory: 648kB
71. 0.938 0.938 ↑ 47.7 5,763 1

CTE Scan on sectors sb (cost=0.00..5,496.88 rows=274,844 width=1,740) (actual time=0.001..0.938 rows=5,763 loops=1)

72. 0.647 4.594 ↑ 538.6 5,666 1

Materialize (cost=4,716,115.57..4,731,374.53 rows=3,051,792 width=1,080) (actual time=3.575..4.594 rows=5,666 loops=1)

73. 2.970 3.947 ↑ 538.6 5,666 1

Sort (cost=4,716,115.57..4,723,745.05 rows=3,051,792 width=1,080) (actual time=3.573..3.947 rows=5,666 loops=1)

  • Sort Key: c_2.cadisissueid
  • Sort Method: quicksort Memory: 635kB
74. 0.977 0.977 ↑ 538.6 5,666 1

CTE Scan on bmkholdings c_2 (cost=0.00..69,010.38 rows=3,051,792 width=1,080) (actual time=0.002..0.977 rows=5,666 loops=1)

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

Subquery Scan on *SELECT* 4 (cost=0.00..69,202.08 rows=15,336 width=1,344) (actual time=0.562..0.562 rows=0 loops=1)

76. 0.561 0.561 ↓ 0.0 0 1

CTE Scan on bmkholdings c_3 (cost=0.00..69,010.38 rows=15,336 width=1,340) (actual time=0.561..0.561 rows=0 loops=1)

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

CTE totals

78. 1.006 4.235 ↑ 1.0 1 1

Aggregate (cost=115,679,281.80..115,679,281.81 rows=1 width=24) (actual time=4.235..4.235 rows=1 loops=1)

79. 3.229 3.229 ↑ 707,810.8 5,943 1

CTE Scan on securities securities_1 (cost=0.00..84,130,386.76 rows=4,206,519,338 width=24) (actual time=0.000..3.229 rows=5,943 loops=1)

80. 2.512 335,623.293 ↑ 1,617,892.1 13 1

GroupAggregate (cost=111,300,021.06..112,509,395.39 rows=21,032,597 width=288) (actual time=335,620.433..335,623.293 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=453,092 read=3,297,252, temp read=39 written=33
81. 5.510 335,620.781 ↑ 3,539.1 5,943 1

Sort (cost=111,300,021.06..111,352,602.55 rows=21,032,597 width=296) (actual time=335,620.418..335,620.781 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=453,092 read=3,297,252, temp read=39 written=33
82. 1.634 335,615.271 ↑ 3,539.1 5,943 1

Hash Join (cost=0.03..100,115,160.28 rows=21,032,597 width=296) (actual time=335,613.096..335,615.271 rows=5,943 loops=1)

  • Hash Cond: (securities.effectivedate = totals.datetot)
  • Buffers: shared hit=453,092 read=3,297,252, temp read=39 written=33
83. 335,609.397 335,609.397 ↑ 707,810.8 5,943 1

CTE Scan on securities (cost=0.00..84,130,386.76 rows=4,206,519,338 width=280) (actual time=335,608.835..335,609.397 rows=5,943 loops=1)

  • Buffers: shared hit=453,092 read=3,297,252, temp read=39 written=33
84. 0.003 4.240 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
85. 4.237 4.237 ↑ 1.0 1 1

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

Planning time : 87.604 ms
Execution time : 335,635.233 ms