Notes

qhub:

Incremental Sort (cost=1345229.08..1345229.12 rows=2 width=354) (actual time=3036.862..3036.891 rows=247 loops=1)
" Sort Key: sub.as_of_date, sub.account, (sum(sub.value_usd) OVER (?)) DESC, (sum(sub.value_usd) OVER (?)) DESC, sub.asset_category_code, (sum(sub.value_usd) OVER (?)) DESC, sub.value_usd DESC"
" Presorted Key: sub.as_of_date, sub.account"
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB
Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 75kB Peak Memory: 75kB
-> WindowAgg (cost=1345228.89..1345229.07 rows=2 width=354) (actual time=3034.850..3035.019 rows=247 loops=1)
-> WindowAgg (cost=1345228.89..1345229.03 rows=2 width=322) (actual time=3034.558..3034.848 rows=247 loops=1)
-> WindowAgg (cost=1345228.89..1345228.98 rows=2 width=290) (actual time=3034.385..3034.671 rows=247 loops=1)
-> Incremental Sort (cost=1345228.89..1345228.93 rows=2 width=258) (actual time=3034.381..3034.411 rows=247 loops=1)
" Sort Key: sub.as_of_date, sub.account, sub.investment_division, sub.portfolio, sub.issuer"
Presorted Key: sub.as_of_date
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 36kB Peak Memory: 36kB
Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 67kB Peak Memory: 67kB
-> Subquery Scan on sub (cost=1343703.14..1345228.88 rows=1 width=258) (actual time=3033.131..3033.565 rows=247 loops=1)
-> WindowAgg (cost=1343703.14..1345228.87 rows=1 width=258) (actual time=3033.130..3033.537 rows=247 loops=1)
-> GroupAggregate (cost=1343703.14..1345228.84 rows=1 width=226) (actual time=3032.862..3033.308 rows=247 loops=1)
" Group Key: pos.as_of_date, id.abbreviation, (concat(id.abbreviation, ' ', aa.abbreviation, ' Fund')), l_1.asset_category_code, cat.name, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text ELSE l_1.issuer_short_name END), l.xf_company_id, l.primary_listing_sedol, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text WHEN (l_1.asset_category_code = 3) THEN cat.name ELSE l.name END), l.xf_security_id, l.xf_trading_item_id, l.sedol"
-> Incremental Sort (cost=1343703.14..1345228.74 rows=2 width=226) (actual time=3032.851..3032.898 rows=599 loops=1)
" Sort Key: pos.as_of_date, id.abbreviation, (concat(id.abbreviation, ' ', aa.abbreviation, ' Fund')), l_1.asset_category_code, cat.name, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text ELSE l_1.issuer_short_name END), l.xf_company_id, l.primary_listing_sedol, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text WHEN (l_1.asset_category_code = 3) THEN cat.name ELSE l.name END), l.xf_security_id, l.xf_trading_item_id, l.sedol"
Presorted Key: pos.as_of_date
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 35kB Peak Memory: 35kB
Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 120kB Peak Memory: 120kB
-> Nested Loop Left Join (cost=1342177.60..1345228.65 rows=1 width=226) (actual time=2800.990..3031.247 rows=599 loops=1)
-> Nested Loop (cost=1342177.17..1345224.83 rows=1 width=142) (actual time=2800.965..3029.193 rows=599 loops=1)
-> Nested Loop (cost=1342177.04..1345224.52 rows=2 width=124) (actual time=2800.940..3028.322 rows=599 loops=1)
Join Filter: (am.aa_portfolio_uid = aa.portfolio_uid)
Rows Removed by Join Filter: 293917
-> GroupAggregate (cost=1342177.04..1343912.73 rows=31558 width=194) (actual time=2619.973..2951.015 rows=294516 loops=1)
" Group Key: pos.as_of_date, am.aa_portfolio_uid, id.abbreviation, (CASE WHEN (rp.portfolio_uid IS NOT NULL) THEN concat(rp.number, ' ', id.abbreviation, ' RP', '-', aa_1.abbreviation) ELSE concat(mr.number, ' ', mi.initials, '-', aa_1.abbreviation) END), (CASE WHEN (mr.asset_class = 'EQ'::text) THEN 'Equity'::text WHEN (mr.asset_class = 'FI'::text) THEN 'Fixed Income'::text ELSE 'UNKNOWN'::text END), (COALESCE(rp.portfolio_uid, am.portfolio_uid)), l_1.asset_category_code, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text ELSE l_1.issuer_short_name END), (CASE WHEN (l_1.asset_category_code = 2) THEN NULL::bigint ELSE pos.listing_uid END)"
-> Sort (cost=1342177.04..1342255.94 rows=31558 width=186) (actual time=2619.961..2684.057 rows=296161 loops=1)
" Sort Key: pos.as_of_date, am.aa_portfolio_uid, id.abbreviation, (CASE WHEN (rp.portfolio_uid IS NOT NULL) THEN concat(rp.number, ' ', id.abbreviation, ' RP', '-', aa_1.abbreviation) ELSE concat(mr.number, ' ', mi.initials, '-', aa_1.abbreviation) END), (CASE WHEN (mr.asset_class = 'EQ'::text) THEN 'Equity'::text WHEN (mr.asset_class = 'FI'::text) THEN 'Fixed Income'::text ELSE 'UNKNOWN'::text END), (COALESCE(rp.portfolio_uid, am.portfolio_uid)), l_1.asset_category_code, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text ELSE l_1.issuer_short_name END), (CASE WHEN (l_1.asset_category_code = 2) THEN NULL::bigint ELSE pos.listing_uid END)"
Sort Method: external merge Disk: 32456kB
-> Hash Right Join (cost=1334507.54..1336903.26 rows=31558 width=186) (actual time=1536.809..1748.180 rows=296161 loops=1)
Hash Cond: (am_rp.portfolio_uid = am.portfolio_uid)
Join Filter: ((pos.as_of_date >= am_rp.effective_start) AND (pos.as_of_date <= am_rp.effective_end))
Rows Removed by Join Filter: 485
-> Hash Join (cost=4.72..1705.88 rows=52406 width=28) (actual time=0.060..15.167 rows=52434 loops=1)
Hash Cond: (am_rp.rp_portfolio_uid = rp.portfolio_uid)
-> Seq Scan on am_portfolio_rp_membership_revisions am_rp (cost=0.00..980.57 rows=52406 width=24) (actual time=0.010..6.241 rows=52434 loops=1)
Filter: (archive_timestamp IS NULL)
Rows Removed by Filter: 97
-> Hash (cost=3.21..3.21 rows=121 width=12) (actual time=0.038..0.039 rows=121 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Seq Scan on research_portfolio_revisions rp (cost=0.00..3.21 rows=121 width=12) (actual time=0.005..0.021 rows=121 loops=1)
Filter: (archive_timestamp IS NULL)
-> Hash (cost=1334108.34..1334108.34 rows=31558 width=144) (actual time=1536.200..1536.211 rows=296161 loops=1)
Buckets: 65536 (originally 32768) Batches: 8 (originally 1) Memory Usage: 7681kB
-> Nested Loop (cost=1321528.43..1334108.34 rows=31558 width=144) (actual time=857.954..1447.545 rows=296161 loops=1)
-> Nested Loop (cost=1321528.14..1332857.62 rows=31558 width=147) (actual time=857.940..1317.798 rows=296161 loops=1)
-> Nested Loop (cost=1321527.85..1331546.24 rows=31558 width=140) (actual time=857.917..1185.307 rows=296161 loops=1)
-> Merge Join (cost=1321527.56..1327452.84 rows=31621 width=130) (actual time=857.897..1038.596 rows=296161 loops=1)
Merge Cond: (am.portfolio_uid = pos.portfolio_uid)
-> Index Scan using am_portfolio_revisions_portfolio_uid_unq_when_valid on am_portfolio_revisions am (cost=0.29..5032.34 rows=135920 width=24) (actual time=0.025..25.645 rows=135667 loops=1)
-> Materialize (cost=1321527.26..1321685.42 rows=31631 width=122) (actual time=857.817..943.352 rows=296161 loops=1)
-> Sort (cost=1321527.26..1321606.34 rows=31631 width=122) (actual time=857.815..901.789 rows=296161 loops=1)
Sort Key: pos.portfolio_uid
Sort Method: external merge Disk: 22808kB
-> Hash Join (cost=4241.21..1317108.49 rows=31631 width=122) (actual time=38.080..774.376 rows=296161 loops=1)
Hash Cond: (am_id.id_portfolio_uid = id.portfolio_uid)
-> Hash Join (cost=4240.10..1316593.39 rows=52718 width=126) (actual time=38.060..715.921 rows=434040 loops=1)
Hash Cond: (pos.portfolio_uid = am_id.portfolio_uid)
Join Filter: ((pos.as_of_date >= am_id.effective_start) AND (pos.as_of_date <= am_id.effective_end))
Rows Removed by Join Filter: 44687
-> Append (cost=1.00..1300125.71 rows=371753 width=110) (actual time=0.045..575.691 rows=439891 loops=1)
-> Nested Loop (cost=1.00..1238913.60 rows=370527 width=94) (actual time=0.044..539.008 rows=438821 loops=1)
-> Index Scan using am_position_revisions_p2024_as_of_date_portfolio_uid_listi_idx1 on am_position_revisions_p2024 pos (cost=0.56..1208369.97 rows=855639 width=33) (actual time=0.031..169.766 rows=438821 loops=1)
" Index Cond: (as_of_date = ANY ('{2024-06-01,2024-06-03}'::date[]))"
Filter: (value_usd <> 0.0)
Rows Removed by Filter: 49844
-> Memoize (cost=0.43..0.69 rows=1 width=33) (actual time=0.000..0.001 rows=1 loops=438821)
Cache Key: pos.listing_uid
Cache Mode: logical
Hits: 401426 Misses: 37395 Evictions: 0 Overflows: 0 Memory Usage: 5265kB
-> Index Scan using listing_revisions_listing_uid_unq_when_valid on listing_revisions l_1 (cost=0.42..0.68 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=37395)
Index Cond: (listing_uid = pos.listing_uid)
" -> Subquery Scan on ""*SELECT* 2"" (cost=0.57..59353.34 rows=1226 width=75) (actual time=0.041..4.109 rows=1070 loops=1)"
-> Nested Loop (cost=0.57..59338.01 rows=1226 width=51) (actual time=0.039..3.999 rows=1070 loops=1)
-> Seq Scan on asset_categories cat_1 (cost=0.00..1.09 rows=1 width=20) (actual time=0.008..0.009 rows=1 loops=1)
Filter: (code = 3)
Rows Removed by Filter: 6
-> Index Scan using am_portfolio_rollup_revisions_entry_unq_when_valid on am_portfolio_rollup_revisions pru (cost=0.57..59324.66 rows=1226 width=15) (actual time=0.030..3.875 rows=1070 loops=1)
" Index Cond: (as_of_date = ANY ('{2024-06-01,2024-06-03}'::date[]))"
Filter: (spendable_accruals_usd <> 0.0)
Rows Removed by Filter: 10401
-> Hash (cost=2679.60..2679.60 rows=124760 width=24) (actual time=37.746..37.747 rows=124834 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 7851kB
-> Seq Scan on am_portfolio_id_membership_revisions am_id (cost=0.00..2679.60 rows=124760 width=24) (actual time=0.004..18.230 rows=124834 loops=1)
Filter: (archive_timestamp IS NULL)
Rows Removed by Filter: 381
-> Hash (cost=1.07..1.07 rows=3 width=12) (actual time=0.013..0.014 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on investment_division_revisions id (cost=0.00..1.07 rows=3 width=12) (actual time=0.008..0.010 rows=3 loops=1)
" Filter: ((archive_timestamp IS NULL) AND (abbreviation = ANY ('{CII,CRGI,CWI}'::text[])))"
Rows Removed by Filter: 2
-> Memoize (cost=0.30..0.33 rows=1 width=18) (actual time=0.000..0.000 rows=1 loops=296161)
Cache Key: am.aa_portfolio_uid
Cache Mode: logical
Hits: 290893 Misses: 5268 Evictions: 0 Overflows: 0 Memory Usage: 658kB
-> Index Scan using account_revisions_account_portfolio_uid_unq_when_valid on account_revisions aa_1 (cost=0.29..0.32 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=5268)
Index Cond: (portfolio_uid = am.aa_portfolio_uid)
-> Memoize (cost=0.29..0.31 rows=1 width=23) (actual time=0.000..0.000 rows=1 loops=296161)
Cache Key: am.mr_portfolio_uid
Cache Mode: logical
Hits: 295767 Misses: 394 Evictions: 0 Overflows: 0 Memory Usage: 50kB
-> Index Scan using management_responsibility_revisions_portfolio_uid_unq_when_vali on management_responsibility_revisions mr (cost=0.28..0.30 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=394)
Index Cond: (portfolio_uid = am.mr_portfolio_uid)
-> Memoize (cost=0.29..0.33 rows=1 width=13) (actual time=0.000..0.000 rows=1 loops=296161)
Cache Key: mr.initials_uid
Cache Mode: logical
Hits: 295826 Misses: 335 Evictions: 0 Overflows: 0 Memory Usage: 40kB
-> Index Only Scan using manager_initials_revisions_initials_uid_unq_when_valid on manager_initials_revisions mi (cost=0.28..0.32 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=335)
Index Cond: (initials_uid = mr.initials_uid)
Heap Fetches: 40
-> Materialize (cost=0.00..522.84 rows=1 width=18) (actual time=0.000..0.000 rows=1 loops=294516)
-> Seq Scan on account_revisions aa (cost=0.00..522.84 rows=1 width=18) (actual time=1.222..1.991 rows=1 loops=1)
Filter: ((archive_timestamp IS NULL) AND (abbreviation = 'AMCAP'::text))
Rows Removed by Filter: 16518
-> Index Scan using asset_categories_pkey on asset_categories cat (cost=0.13..0.15 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=599)
Index Cond: (code = l_1.asset_category_code)
-> Index Scan using listing_revisions_listing_uid_unq_when_valid on listing_revisions l (cost=0.42..3.81 rows=1 width=73) (actual time=0.002..0.003 rows=1 loops=599)
Index Cond: (listing_uid = (CASE WHEN (l_1.asset_category_code = 2) THEN NULL::bigint ELSE pos.listing_uid END))
Planning Time: 3.278 ms
Execution Time: 3051.348 ms


onyx:

Incremental Sort (cost=3166968.66..3166968.70 rows=2 width=354) (actual time=10155.385..10155.505 rows=247 loops=1)
" Sort Key: sub.as_of_date, sub.account, (sum(sub.value_usd) OVER (?)) DESC, (sum(sub.value_usd) OVER (?)) DESC, sub.asset_category_code, (sum(sub.value_usd) OVER (?)) DESC, sub.value_usd DESC"
" Presorted Key: sub.as_of_date, sub.account"
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB
Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 75kB Peak Memory: 75kB
-> WindowAgg (cost=3166968.47..3166968.65 rows=2 width=354) (actual time=10153.405..10153.665 rows=247 loops=1)
-> WindowAgg (cost=3166968.47..3166968.61 rows=2 width=322) (actual time=10153.108..10153.493 rows=247 loops=1)
-> WindowAgg (cost=3166968.47..3166968.56 rows=2 width=290) (actual time=10152.935..10153.309 rows=247 loops=1)
-> Incremental Sort (cost=3166968.47..3166968.51 rows=2 width=258) (actual time=10152.930..10153.051 rows=247 loops=1)
" Sort Key: sub.as_of_date, sub.account, sub.investment_division, sub.portfolio, sub.issuer"
Presorted Key: sub.as_of_date
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 36kB Peak Memory: 36kB
Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 67kB Peak Memory: 67kB
-> Subquery Scan on sub (cost=3161592.96..3166968.46 rows=1 width=258) (actual time=10151.689..10152.215 rows=247 loops=1)
-> WindowAgg (cost=3161592.96..3166968.45 rows=1 width=258) (actual time=10151.688..10152.186 rows=247 loops=1)
-> GroupAggregate (cost=3161592.96..3166968.42 rows=1 width=226) (actual time=10151.418..10151.954 rows=247 loops=1)
" Group Key: pos.as_of_date, id.abbreviation, (concat(id.abbreviation, ' ', aa.abbreviation, ' Fund')), l_1.asset_category_code, cat.name, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text ELSE l_1.issuer_short_name END), l.xf_company_id, l.primary_listing_sedol, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text WHEN (l_1.asset_category_code = 3) THEN cat.name ELSE l.name END), l.xf_security_id, l.xf_trading_item_id, l.sedol"
-> Incremental Sort (cost=3161592.96..3166968.32 rows=2 width=226) (actual time=10151.409..10151.548 rows=599 loops=1)
" Sort Key: pos.as_of_date, id.abbreviation, (concat(id.abbreviation, ' ', aa.abbreviation, ' Fund')), l_1.asset_category_code, cat.name, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text ELSE l_1.issuer_short_name END), l.xf_company_id, l.primary_listing_sedol, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text WHEN (l_1.asset_category_code = 3) THEN cat.name ELSE l.name END), l.xf_security_id, l.xf_trading_item_id, l.sedol"
Presorted Key: pos.as_of_date
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 35kB Peak Memory: 35kB
Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 120kB Peak Memory: 120kB
-> Nested Loop Left Join (cost=3156217.67..3166968.23 rows=1 width=226) (actual time=9865.167..10149.936 rows=599 loops=1)
-> Nested Loop (cost=3156217.25..3166967.26 rows=1 width=142) (actual time=9865.150..10148.081 rows=599 loops=1)
-> Nested Loop (cost=3156217.12..3166966.06 rows=8 width=124) (actual time=9865.132..10147.249 rows=599 loops=1)
Join Filter: (am.aa_portfolio_uid = aa.portfolio_uid)
Rows Removed by Join Filter: 293917
-> GroupAggregate (cost=3156217.12..3163234.57 rows=127590 width=194) (actual time=9649.650..10072.302 rows=294516 loops=1)
" Group Key: pos.as_of_date, am.aa_portfolio_uid, id.abbreviation, (CASE WHEN (rp.portfolio_uid IS NOT NULL) THEN concat(rp.number, ' ', id.abbreviation, ' RP', '-', aa_1.abbreviation) ELSE concat(mr.number, ' ', mi.initials, '-', aa_1.abbreviation) END), (CASE WHEN (mr.asset_class = 'EQ'::text) THEN 'Equity'::text WHEN (mr.asset_class = 'FI'::text) THEN 'Fixed Income'::text ELSE 'UNKNOWN'::text END), (COALESCE(rp.portfolio_uid, am.portfolio_uid)), l_1.asset_category_code, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text ELSE l_1.issuer_short_name END), (CASE WHEN (l_1.asset_category_code = 2) THEN NULL::bigint ELSE pos.listing_uid END)"
-> Sort (cost=3156217.12..3156536.09 rows=127590 width=186) (actual time=9649.639..9804.643 rows=296161 loops=1)
" Sort Key: pos.as_of_date, am.aa_portfolio_uid, id.abbreviation, (CASE WHEN (rp.portfolio_uid IS NOT NULL) THEN concat(rp.number, ' ', id.abbreviation, ' RP', '-', aa_1.abbreviation) ELSE concat(mr.number, ' ', mi.initials, '-', aa_1.abbreviation) END), (CASE WHEN (mr.asset_class = 'EQ'::text) THEN 'Equity'::text WHEN (mr.asset_class = 'FI'::text) THEN 'Fixed Income'::text ELSE 'UNKNOWN'::text END), (COALESCE(rp.portfolio_uid, am.portfolio_uid)), l_1.asset_category_code, (CASE WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text ELSE l_1.issuer_short_name END), (CASE WHEN (l_1.asset_category_code = 2) THEN NULL::bigint ELSE pos.listing_uid END)"
Sort Method: external merge Disk: 32472kB
-> Hash Left Join (cost=13416.55..3133619.25 rows=127590 width=186) (actual time=6427.058..8822.571 rows=296161 loops=1)
Hash Cond: (am.portfolio_uid = am_rp.portfolio_uid)
Join Filter: ((pos.as_of_date >= am_rp.effective_start) AND (pos.as_of_date <= am_rp.effective_end))
Rows Removed by Join Filter: 485
-> Hash Join (cost=11055.33..3127808.86 rows=127590 width=144) (actual time=6404.052..8665.382 rows=296161 loops=1)
Hash Cond: (am.mr_portfolio_uid = mr.portfolio_uid)
-> Nested Loop (cost=10884.91..3125884.07 rows=127590 width=140) (actual time=6402.437..8606.003 rows=296161 loops=1)
-> Hash Join (cost=10884.61..3119448.97 rows=127720 width=130) (actual time=6402.409..8459.214 rows=296161 loops=1)
Hash Cond: (pos.portfolio_uid = am.portfolio_uid)
Join Filter: ((pos.as_of_date >= am_id.effective_start) AND (pos.as_of_date <= am_id.effective_end))
Rows Removed by Join Filter: 7797
-> Gather (cost=1000.00..3076428.57 rows=1505701 width=110) (actual time=6269.413..8166.573 rows=439891 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Append (cost=0.00..2924858.47 rows=627376 width=110) (actual time=4177.555..8163.295 rows=146630 loops=3)
-> Nested Loop (cost=0.43..1433206.57 rows=627017 width=94) (actual time=511.627..3956.161 rows=146274 loops=3)
-> Parallel Seq Scan on am_position_revisions_p2024 pos (cost=0.00..1406067.25 rows=713275 width=33) (actual time=511.594..3819.795 rows=146274 loops=3)
" Filter: ((archive_timestamp IS NULL) AND (value_usd <> 0.0) AND (as_of_date = ANY ('{2024-06-01,2024-06-03}'::date[])))"
Rows Removed by Filter: 23194025
-> Memoize (cost=0.43..0.49 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=438821)
Cache Key: pos.listing_uid
Cache Mode: logical
Hits: 33713 Misses: 14354 Evictions: 0 Overflows: 0 Memory Usage: 2026kB
Worker 0: Hits: 180865 Misses: 18215 Evictions: 0 Overflows: 0 Memory Usage: 2549kB
Worker 1: Hits: 165857 Misses: 25817 Evictions: 0 Overflows: 0 Memory Usage: 3611kB
-> Index Scan using listing_revisions_listing_uid_unq_when_valid on listing_revisions l_1 (cost=0.42..0.48 rows=1 width=33) (actual time=0.002..0.003 rows=1 loops=58386)
Index Cond: (listing_uid = pos.listing_uid)
" -> Subquery Scan on ""*SELECT* 2"" (cost=0.00..1488515.01 rows=359 width=75) (actual time=6265.743..6295.217 rows=535 loops=2)"
-> Nested Loop (cost=0.00..1488510.53 rows=359 width=51) (actual time=6265.741..6295.162 rows=535 loops=2)
-> Parallel Seq Scan on am_portfolio_rollup_revisions pru (cost=0.00..1488116.52 rows=359 width=15) (actual time=6265.716..6294.576 rows=535 loops=2)
" Filter: ((archive_timestamp IS NULL) AND (spendable_accruals_usd <> 0.0) AND (as_of_date = ANY ('{2024-06-01,2024-06-03}'::date[])))"
Rows Removed by Filter: 42077802
-> Seq Scan on asset_categories cat_1 (cost=0.00..1.09 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=1070)
Filter: (code = 3)
Rows Removed by Filter: 6
-> Hash (cost=8949.16..8949.16 rows=74836 width=44) (actual time=132.692..132.695 rows=109420 loops=1)
Buckets: 131072 (originally 131072) Batches: 2 (originally 1) Memory Usage: 7169kB
-> Hash Join (cost=4833.73..8949.16 rows=74836 width=44) (actual time=53.751..105.853 rows=109420 loops=1)
Hash Cond: (am.portfolio_uid = am_id.portfolio_uid)
-> Seq Scan on am_portfolio_revisions am (cost=0.00..2857.52 rows=135879 width=24) (actual time=0.005..18.882 rows=135852 loops=1)
Filter: (archive_timestamp IS NULL)
Rows Removed by Filter: 83
-> Hash (cost=3897.78..3897.78 rows=74876 width=20) (actual time=53.490..53.492 rows=109420 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 6589kB
-> Hash Join (cost=1.11..3897.78 rows=74876 width=20) (actual time=0.026..36.357 rows=109420 loops=1)
Hash Cond: (am_id.id_portfolio_uid = id.portfolio_uid)
-> Seq Scan on am_portfolio_id_membership_revisions am_id (cost=0.00..2679.94 rows=124794 width=24) (actual time=0.007..18.101 rows=124834 loops=1)
Filter: (archive_timestamp IS NULL)
Rows Removed by Filter: 381
-> Hash (cost=1.07..1.07 rows=3 width=12) (actual time=0.010..0.011 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on investment_division_revisions id (cost=0.00..1.07 rows=3 width=12) (actual time=0.005..0.006 rows=3 loops=1)
" Filter: ((archive_timestamp IS NULL) AND (abbreviation = ANY ('{CII,CRGI,CWI}'::text[])))"
Rows Removed by Filter: 2
-> Memoize (cost=0.30..0.33 rows=1 width=18) (actual time=0.000..0.000 rows=1 loops=296161)
Cache Key: am.aa_portfolio_uid
Cache Mode: logical
Hits: 290893 Misses: 5268 Evictions: 0 Overflows: 0 Memory Usage: 658kB
-> Index Scan using account_revisions_account_portfolio_uid_unq_when_valid on account_revisions aa_1 (cost=0.29..0.32 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=5268)
Index Cond: (portfolio_uid = am.aa_portfolio_uid)
-> Hash (cost=138.21..138.21 rows=2577 width=20) (actual time=1.601..1.602 rows=2593 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 166kB
-> Hash Join (cost=48.01..138.21 rows=2577 width=20) (actual time=0.402..1.217 rows=2593 loops=1)
Hash Cond: (mr.initials_uid = mi.initials_uid)
-> Seq Scan on management_responsibility_revisions mr (cost=0.00..54.77 rows=2577 width=23) (actual time=0.008..0.373 rows=2593 loops=1)
Filter: (archive_timestamp IS NULL)
-> Hash (cost=29.67..29.67 rows=1467 width=13) (actual time=0.388..0.389 rows=1481 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 86kB
-> Seq Scan on manager_initials_revisions mi (cost=0.00..29.67 rows=1467 width=13) (actual time=0.005..0.197 rows=1481 loops=1)
Filter: (archive_timestamp IS NULL)
Rows Removed by Filter: 1
-> Hash (cost=1706.02..1706.02 rows=52416 width=28) (actual time=22.974..22.975 rows=52434 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 3585kB
-> Hash Join (cost=4.70..1706.02 rows=52416 width=28) (actual time=0.050..15.008 rows=52434 loops=1)
Hash Cond: (am_rp.rp_portfolio_uid = rp.portfolio_uid)
-> Seq Scan on am_portfolio_rp_membership_revisions am_rp (cost=0.00..980.60 rows=52416 width=24) (actual time=0.007..6.071 rows=52434 loops=1)
Filter: (archive_timestamp IS NULL)
Rows Removed by Filter: 77
-> Hash (cost=3.20..3.20 rows=120 width=12) (actual time=0.035..0.035 rows=120 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Seq Scan on research_portfolio_revisions rp (cost=0.00..3.20 rows=120 width=12) (actual time=0.004..0.020 rows=120 loops=1)
Filter: (archive_timestamp IS NULL)
-> Materialize (cost=0.00..541.74 rows=1 width=18) (actual time=0.000..0.000 rows=1 loops=294516)
-> Seq Scan on account_revisions aa (cost=0.00..541.74 rows=1 width=18) (actual time=1.294..2.103 rows=1 loops=1)
Filter: ((archive_timestamp IS NULL) AND (abbreviation = 'AMCAP'::text))
Rows Removed by Filter: 16522
-> Index Scan using asset_categories_pkey on asset_categories cat (cost=0.13..0.15 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=599)
Index Cond: (code = l_1.asset_category_code)
-> Index Scan using listing_revisions_listing_uid_unq_when_valid on listing_revisions l (cost=0.42..0.95 rows=1 width=71) (actual time=0.002..0.002 rows=1 loops=599)
Index Cond: (listing_uid = (CASE WHEN (l_1.asset_category_code = 2) THEN NULL::bigint ELSE pos.listing_uid END))
Planning Time: 3.014 ms
Execution Time: 10165.318 ms


SELECT sub.as_of_date,
aa.abbreviation AS account,
sub.investment_division,
sub.portfolio,
sub.portfolio_type,
sub.portfolio_uid,
sub.asset_category_code,
cat.name AS asset_category,
sub.issuer_short_name AS issuer,
l.xf_company_id,
l.primary_listing_sedol,
CASE
WHEN (sub.asset_category_code = 2) THEN 'Cash'::text
WHEN (sub.asset_category_code = 3) THEN cat.name
ELSE l.name
END AS listing_name,
l.xf_security_id,
l.xf_trading_item_id,
l.sedol AS listing_sedol,
l.bloomberg_id,
l.axioma_id,
l.cg_symbol,
l.listing_uid,
l.issuer_uid,
sub.n_shares,
sub.value_usd
FROM (((( SELECT pl.as_of_date,
am.aa_portfolio_uid,
id.abbreviation AS investment_division,
CASE
WHEN (rp.portfolio_uid IS NOT NULL) THEN concat(rp.number, ' ', id.abbreviation, ' RP', '-', aa_1.abbreviation)
ELSE concat(mr.number, ' ', mi.initials, '-', aa_1.abbreviation)
END AS portfolio,
CASE
WHEN (mr.asset_class = 'EQ'::text) THEN 'Equity'::text
WHEN (mr.asset_class = 'FI'::text) THEN 'Fixed Income'::text
ELSE 'UNKNOWN'::text
END AS portfolio_type,
COALESCE(rp.portfolio_uid, am.portfolio_uid) AS portfolio_uid,
pl.asset_category_code,
pl.issuer_short_name,
pl.listing_uid,
sum(pl.n_shares) AS n_shares,
sum(pl.value_usd) AS value_usd
FROM ((((((( SELECT pos.as_of_date,
pos.portfolio_uid,
l_1.asset_category_code,
CASE
WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text
ELSE l_1.issuer_short_name
END AS issuer_short_name,
CASE
WHEN (l_1.asset_category_code = 2) THEN NULL::bigint
ELSE pos.listing_uid
END AS listing_uid,
CASE
WHEN (l_1.asset_category_code = 2) THEN NULL::numeric
ELSE pos.n_shares
END AS n_shares,
pos.value_usd
FROM (codex.am_position_revisions pos
JOIN codex.listing_revisions l_1 ON (((pos.listing_uid = l_1.listing_uid) AND (l_1.archive_timestamp IS NULL))))
WHERE (pos.archive_timestamp IS NULL)
UNION ALL
SELECT pru.as_of_date,
pru.portfolio_uid,
cat_1.code AS asset_category_code,
cat_1.name AS issuer_short_name,
NULL::bigint AS listing_uid,
NULL::bigint AS n_shares,
pru.spendable_accruals_usd AS value_usd
FROM (codex.am_portfolio_rollup_revisions pru
JOIN codex.asset_categories cat_1 ON ((cat_1.code = 3)))
WHERE (pru.archive_timestamp IS NULL)) pl
JOIN codex.am_portfolio_revisions am ON (((pl.portfolio_uid = am.portfolio_uid) AND (am.archive_timestamp IS NULL))))
JOIN codex.account_revisions aa_1 ON (((am.aa_portfolio_uid = aa_1.portfolio_uid) AND (aa_1.archive_timestamp IS NULL))))
JOIN codex.management_responsibility_revisions mr ON (((am.mr_portfolio_uid = mr.portfolio_uid) AND (mr.archive_timestamp IS NULL))))
JOIN codex.manager_initials_revisions mi ON (((mr.initials_uid = mi.initials_uid) AND (mi.archive_timestamp IS NULL))))
LEFT JOIN (codex.am_portfolio_id_membership_revisions am_id
JOIN codex.investment_division_revisions id ON (((am_id.id_portfolio_uid = id.portfolio_uid) AND (id.archive_timestamp IS NULL)))) ON (((am.portfolio_uid = am_id.portfolio_uid) AND ((pl.as_of_date >= am_id.effective_start) AND (pl.as_of_date <= am_id.effective_end)) AND (am_id.archive_timestamp IS NULL))))
LEFT JOIN (codex.am_portfolio_rp_membership_revisions am_rp
JOIN codex.research_portfolio_revisions rp ON (((am_rp.rp_portfolio_uid = rp.portfolio_uid) AND (rp.archive_timestamp IS NULL)))) ON (((am.portfolio_uid = am_rp.portfolio_uid) AND ((pl.as_of_date >= am_rp.effective_start) AND (pl.as_of_date <= am_rp.effective_end)) AND (am_rp.archive_timestamp IS NULL))))
WHERE (pl.value_usd <> 0.0)
GROUP BY pl.as_of_date, am.aa_portfolio_uid, id.abbreviation,
CASE
WHEN (rp.portfolio_uid IS NOT NULL) THEN concat(rp.number, ' ', id.abbreviation, ' RP', '-', aa_1.abbreviation)
ELSE concat(mr.number, ' ', mi.initials, '-', aa_1.abbreviation)
END,
CASE
WHEN (mr.asset_class = 'EQ'::text) THEN 'Equity'::text
WHEN (mr.asset_class = 'FI'::text) THEN 'Fixed Income'::text
ELSE 'UNKNOWN'::text
END, COALESCE(rp.portfolio_uid, am.portfolio_uid), pl.asset_category_code, pl.issuer_short_name, pl.listing_uid) sub
JOIN codex.account_revisions aa ON (((sub.aa_portfolio_uid = aa.portfolio_uid) AND (aa.archive_timestamp IS NULL))))
JOIN codex.asset_categories cat ON ((sub.asset_category_code = cat.code)))
LEFT JOIN codex.listing_revisions l ON (((sub.listing_uid = l.listing_uid) AND (l.archive_timestamp IS NULL))));
--------------------------------------------------------------------------------------------------

SELECT sub.as_of_date,
aa.abbreviation AS account,
sub.investment_division,
sub.portfolio,
sub.portfolio_type,
sub.portfolio_uid,
sub.asset_category_code,
cat.name AS asset_category,
sub.issuer_short_name AS issuer,
l.xf_company_id,
l.primary_listing_sedol,
CASE
WHEN (sub.asset_category_code = 2) THEN 'Cash'::text
WHEN (sub.asset_category_code = 3) THEN cat.name
ELSE l.name
END AS listing_name,
l.xf_security_id,
l.xf_trading_item_id,
l.sedol AS listing_sedol,
l.bloomberg_id,
l.axioma_id,
l.cg_symbol,
l.listing_uid AS cg_listing_uid,
l.issuer_uid AS cg_issuer_uid,
sub.n_shares,
sub.value_usd
FROM (((( SELECT pl.as_of_date,
am.aa_portfolio_uid,
id.abbreviation AS investment_division,
CASE
WHEN (rp.portfolio_uid IS NOT NULL) THEN concat(rp.number, ' ', id.abbreviation, ' RP', '-', aa_1.abbreviation)
ELSE concat(mr.number, ' ', mi.initials, '-', aa_1.abbreviation)
END AS portfolio,
CASE
WHEN (mr.asset_class = 'EQ'::text) THEN 'Equity'::text
WHEN (mr.asset_class = 'FI'::text) THEN 'Fixed Income'::text
ELSE 'UNKNOWN'::text
END AS portfolio_type,
COALESCE(rp.portfolio_uid, am.portfolio_uid) AS portfolio_uid,
pl.asset_category_code,
pl.issuer_short_name,
pl.listing_uid,
sum(pl.n_shares) AS n_shares,
sum(pl.value_usd) AS value_usd
FROM ((((((( SELECT pos.as_of_date,
pos.portfolio_uid,
l_1.asset_category_code,
CASE
WHEN (l_1.asset_category_code = 2) THEN 'Cash'::text
ELSE l_1.issuer_short_name
END AS issuer_short_name,
CASE
WHEN (l_1.asset_category_code = 2) THEN NULL::bigint
ELSE pos.listing_uid
END AS listing_uid,
CASE
WHEN (l_1.asset_category_code = 2) THEN NULL::numeric
ELSE pos.n_shares
END AS n_shares,
pos.value_usd
FROM (am_position_revisions pos
JOIN listing_revisions l_1 ON (((pos.listing_uid = l_1.listing_uid) AND (l_1.archive_timestamp IS NULL))))
WHERE (pos.archive_timestamp IS NULL)
UNION ALL
SELECT pru.as_of_date,
pru.portfolio_uid,
cat_1.code AS asset_category_code,
cat_1.name AS issuer_short_name,
NULL::bigint AS listing_uid,
NULL::bigint AS n_shares,
pru.spendable_accruals_usd AS value_usd
FROM (am_portfolio_rollup_revisions pru
JOIN asset_categories cat_1 ON ((cat_1.code = 3)))
WHERE (pru.archive_timestamp IS NULL)) pl
JOIN am_portfolio_revisions am ON (((pl.portfolio_uid = am.portfolio_uid) AND (am.archive_timestamp IS NULL))))
JOIN account_revisions aa_1 ON (((am.aa_portfolio_uid = aa_1.portfolio_uid) AND (aa_1.archive_timestamp IS NULL))))
JOIN management_responsibility_revisions mr ON (((am.mr_portfolio_uid = mr.portfolio_uid) AND (mr.archive_timestamp IS NULL))))
JOIN manager_initials_revisions mi ON (((mr.initials_uid = mi.initials_uid) AND (mi.archive_timestamp IS NULL))))
LEFT JOIN (am_portfolio_id_membership_revisions am_id
JOIN investment_division_revisions id ON (((am_id.id_portfolio_uid = id.portfolio_uid) AND (id.archive_timestamp IS NULL)))) ON (((am.portfolio_uid = am_id.portfolio_uid) AND ((pl.as_of_date >= am_id.effective_start) AND (pl.as_of_date <= am_id.effective_end)) AND (am_id.archive_timestamp IS NULL))))
LEFT JOIN (am_portfolio_rp_membership_revisions am_rp
JOIN research_portfolio_revisions rp ON (((am_rp.rp_portfolio_uid = rp.portfolio_uid) AND (rp.archive_timestamp IS NULL)))) ON (((am.portfolio_uid = am_rp.portfolio_uid) AND ((pl.as_of_date >= am_rp.effective_start) AND (pl.as_of_date <= am_rp.effective_end)) AND (am_rp.archive_timestamp IS NULL))))
WHERE (pl.value_usd <> 0.0)
GROUP BY pl.as_of_date, am.aa_portfolio_uid, id.abbreviation,
CASE
WHEN (rp.portfolio_uid IS NOT NULL) THEN concat(rp.number, ' ', id.abbreviation, ' RP', '-', aa_1.abbreviation)
ELSE concat(mr.number, ' ', mi.initials, '-', aa_1.abbreviation)
END,
CASE
WHEN (mr.asset_class = 'EQ'::text) THEN 'Equity'::text
WHEN (mr.asset_class = 'FI'::text) THEN 'Fixed Income'::text
ELSE 'UNKNOWN'::text
END, COALESCE(rp.portfolio_uid, am.portfolio_uid), pl.asset_category_code, pl.issuer_short_name, pl.listing_uid) sub
JOIN account_revisions aa ON (((sub.aa_portfolio_uid = aa.portfolio_uid) AND (aa.archive_timestamp IS NULL))))
JOIN asset_categories cat ON ((sub.asset_category_code = cat.code)))
LEFT JOIN listing_revisions l ON (((sub.listing_uid = l.listing_uid) AND (l.archive_timestamp IS NULL))));
------------------------------------------------------------------------------------------------------------
Main query

EXPLAIN ANALYZE SELECT as_of_date,
account,
investment_division,
portfolio,

asset_category,

issuer,
xf_company_id,
primary_listing_sedol,


listing_name AS listing,
xf_security_id,
xf_trading_item_id,
listing_sedol,
n_shares,
value_usd,
weight

FROM (


SELECT as_of_date,
account,

investment_division,
concat(investment_division, ' ', account, ' Fund') AS portfolio,

asset_category_code,
asset_category,
issuer,
xf_company_id,
primary_listing_sedol,
listing_name,
xf_security_id,
xf_trading_item_id,
listing_sedol,

sum(n_shares) AS n_shares,
sum(value_usd) AS value_usd,
sum(value_usd) / sum(sum(value_usd)) OVER (PARTITION BY as_of_date, account, investment_division) AS weight
FROM codex.positions
WHERE as_of_date IN ('2024-06-01', '2024-06-03')
AND account IN ('AMCAP')

AND investment_division IN ('CII', 'CRGI', 'CWI')

GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13

) sub


ORDER BY as_of_date,
account,

sum(value_usd) OVER (PARTITION BY as_of_date, account, investment_division) DESC,
sum(value_usd) OVER (PARTITION BY as_of_date, account, investment_division, portfolio) DESC,
asset_category_code,
sum(value_usd) OVER (PARTITION BY as_of_date, account, investment_division, portfolio, issuer) DESC,
value_usd DESC;