New Query

[lusterchris]

explain SELECT df4.as_of_date, df4.portfolio_uid, df4.listing_uid, SUM(df4.mkt_val_sod) AS mkt_val_sod, SUM(df4.acc_int_tot_qty) AS acc_int_tot_qty, SUM(df4.crd_emv) AS crd_emv FROM (SELECT as_of_date, df3.portfolio_uid am_portfolio_uid listing_uid, ) SUM(mkt_val_sod) AS mkt_val_sod. SUM(acc_int_tot_qty) AS acc_int tot_qty, SUM(crd_emv) AS crd_emv FROM (SELECT df2.as_of_date, df2._uid AS portfolio_uid, CASE WHEN df2.committee_flag = 'true THEN am.portfolio_uid ELSE df2.portfolio_uid END as am_portfolio_uid,

CASE WHEN df2.committee_flag = 'true' THEN -iso uid ELSE accr_listing_uid END AS listing_uid CASE WHEN accr. listing uid L 0 THEN accruals_usd reclaims usd ELSE value_usd END AS mkt_val_sod, CASE WHEN accr_listing_uid 0 THEN 0 ELSE accruals usd + reclaims usd END AS acc_int_tot_qty, value_usd + accruals_usd t reclaims_usd AS crd_emv FROM (SELECT df1.* CASE WHEN accrual_type_code IN ('IDV', 'IRC') AND held_accr IN ('- true false' S AND CASE WHEN asset_class NOT IN ('Cash', 'Equity') AND income spendable ='false THEN 1 WHEN asset_class NOT IN ('Cash') AND income_spendable = 'true' THEN 1

END = 1 THEN -iso_uid WHEN accrual_type_code IN ( 'NCG', 'NFC", 'NPY', 'NRC') AND held_accr IN ('true', 'false') AND asset_class NOT IN ('Cash') AND income_spendable IN ('true', 'false'). THEN -iso uid WHEN accrual_type_code IN ('non') AND held accr IN C true') AND secondary_instrument_type_code NOT IN: ('FDBT', SWA', 'CRD') AND asset_class NOT IN ('Cash') AND income_spendable IN ('true', 'false") THEN -iso_uid WHEN accrual_type_code IN ('IIN") AND held _accr IN ("false') AND asset_class NOT IN ('Cash') AND income_spendable IN ('true') THEN -iso_uid ELSE listing_uid END AS accr_listing_uid,

CASE WHEN accrual_type_code IN ('IIN') AND held_accr IN ('false') AND asset_class NOT IN ('Cash') AND income_spendable IN ('false') THEN 'true WHEN accrual_type_code IN ('IDV', 'IRC') AND held_accr IN ('true false AND asset_class IN ('Cash ) AND income_spendable IN ('false') Equity') THEN 'true ELSE 'false END As committee_flag

FROM (SELECT dfe.*, ab.portfolio_uid AS _uid, ab.income_spendable, S iso.listing_uid AS iso_uid, l.name AS listing_name, l.cg_symbol, coalesce(l.primary_instrument_type_code, '') AS primary_instrument_type_code, coalesce(l.secondary_instrument_type_code, '') AS secondaryinstrument type_code, coalesce(l.tertiary_instrument_type_code, ') AS tertiary_instrument_type_code, coalesce(l.asset_category, ') AS asset_category, coalesce(l.asset_class, '') AS asset_class, CASE WHEN count(df0.listing_uid) over (partition by df0.as_of_date, ab.portfolio_uid, df0.portfolio_uid, df0.listing_uid) > 1 AND sum(coalesce(df0.value_usd, 0)) over (partition by df0.as_of_date, ab.portfolio_uid, df0.portfolio_uid, dfo.listing_uid) > 0 THEN 'true ELSE 'false END AS held_accr

FROM (SELECT pos.as_ofdate, pos.portfolio uid, pos.listing_uid pos.local_currency, pos.n_shares, pos.value_usd NULL AS accrual_type_code, 0 AS accruals usd, 0 AS reclaims usd 0 AS spendable_accruals usd FROM codex.am_positions pos UNION ALL SELECT accr.as_of_date accr.portfolio uid accr. .listing_uid . accr.local_currency, 0 AS n_shares, 0 AS value_usd, accr.accrual_type_code, accr.accruals_usd AS accruals_usd, accr.reclaims_usd AS reclaims_usd, accr.spendable_accruals_usd AS spendable_accruals_usd FROM codex.portfolio_accruals accr) df0 INNER JOIN onyx_api.account_breakdowns ab ON dfe.portfolio_uid = ab.am_portfolio_uid AND df@.as_of_date <@ ab.effective_during

LEFT JOIN codex.listings I ON l.listing_uid = df0.listing_uid LEFT JOIN codex.currencies iso ON iso.iso_code = df0.local_currency) df1) df2 INNER JOIN onyx_api.account_breakdowns dc ON dc.am_portfolio_uid = df2.portfolio uid AND dc.type = 'AA AND df2.as_of_date <@ dc.effective_during INNER JOIN codex.am_portfolio_history_wide am ON am.aa_portfolio_uid = dc.aa_portfolio-uid AND df2.as_of_date <@ am.effective_during AND am.mr_number ='680 INNER JOIN onyx_api.portfolios p ON p.portfolio_uid = df2._uid AND CASE WHEN p.portfolio_type = 'AM AND CASE WHEN df2.committee_flag = 'true THEN am.portfolio uid ELSE df2.portfolio_uid END != am.portfolio_uid THEN 1 WHEN p.portfolio_type != 'AM AND df2.portfolio_uid = df2.portfolio_uid

THEN 1 END = 1 ) df3 GROUP BY 1, 2, 3, 4) df4 INNER JOIN onyx_api.rollup_dates cal ON df4.as_of_date = cal.as_of_date GROUP BY 1, 2, 3 ORDER BY 1, 2, 3;

[lusterchris]

WITH pos_data AS ( SELECT pos.as_of_date, pos.portfolio_uid, pos.listing_uid, pos.local_currency, pos.n_shares, pos.value_usd, NULL::text AS accrual_type_code, 0::numeric AS accruals_usd, 0::numeric AS reclaims_usd, 0::numeric AS spendable_accruals_usd FROM codex.am_positions pos UNION ALL SELECT accr.as_of_date, accr.portfolio_uid, accr.listing_uid, accr.local_currency, 0::numeric AS n_shares, 0::numeric AS value_usd, accr.accrual_type_code, accr.accruals_usd, accr.reclaims_usd, accr.spendable_accruals_usd FROM codex.portfolio_accruals accr ), aggregated_data AS ( SELECT df1.as_of_date, df1.portfolio_uid, df1.listing_uid, -- Removed incorrect iso_uid reference SUM(CASE WHEN df1.accrual_type_code IN ('IDV', 'IRC') THEN df1.value_usd ELSE df1.accruals_usd + df1.reclaims_usd END) AS mkt_val_sod, SUM(CASE WHEN df1.accrual_type_code IN ('IDV', 'IRC') THEN 0 ELSE df1.accruals_usd + df1.reclaims_usd END) AS acc_int_tot_qty, SUM(df1.value_usd + df1.accruals_usd + df1.reclaims_usd) AS crd_emv FROM pos_data df1 LEFT JOIN onyx_api.account_breakdowns ab ON df1.portfolio_uid = ab.am_portfolio_uid AND df1.as_of_date <@ ab.effective_during LEFT JOIN codex.listings l ON l.listing_uid = df1.listing_uid LEFT JOIN codex.currencies iso ON iso.iso_code = df1.local_currency GROUP BY df1.as_of_date, df1.portfolio_uid, df1.listing_uid ) SELECT df4.as_of_date, df4.portfolio_uid, df4.listing_uid, SUM(df4.mkt_val_sod) AS mkt_val_sod, SUM(df4.acc_int_tot_qty) AS acc_int_tot_qty, SUM(df4.crd_emv) AS crd_emv FROM aggregated_data df4 INNER JOIN onyx_api.rollup_dates cal ON df4.as_of_date = cal.as_of_date GROUP BY df4.as_of_date, df4.portfolio_uid, df4.listing_uid ORDER BY df4.as_of_date, df4.portfolio_uid, df4.listing_uid;

[lusterchris]

Here are the commands to create the necessary indexes for optimizing your query. These indexes will improve join performance and speed up filtering in your query:

1. Index on codex.am_positions

CREATE INDEX idx_am_positions_as_of_date_portfolio_uid_listing_uid 
ON codex.am_positions(as_of_date, portfolio_uid, listing_uid);

2. Index on codex.portfolio_accruals

CREATE INDEX idx_portfolio_accruals_as_of_date_portfolio_uid_listing_uid 
ON codex.portfolio_accruals(as_of_date, portfolio_uid, listing_uid);

3. Index on onyx_api.account_breakdowns

CREATE INDEX idx_account_breakdowns_am_portfolio_uid_effective_during 
ON onyx_api.account_breakdowns(am_portfolio_uid, effective_during);

4. Index on onyx_api.rollup_dates

CREATE INDEX idx_rollup_dates_as_of_date 
ON onyx_api.rollup_dates(as_of_date);

5. Index on codex.listings

CREATE INDEX idx_listings_listing_uid 
ON codex.listings(listing_uid);

6. Index on codex.currencies

CREATE INDEX idx_currencies_iso_code 
ON codex.currencies(iso_code);

Optional: Analyze Tables for Statistics Update

After creating the indexes, run the following command to update the statistics for the query planner:

ANALYZE codex.am_positions;
ANALYZE codex.portfolio_accruals;
ANALYZE onyx_api.account_breakdowns;
ANALYZE onyx_api.rollup_dates;
ANALYZE codex.listings;
ANALYZE codex.currencies;

These indexes should help optimize the execution of your query. Let me know how it performs after applying these changes!