Re: BUG #17292: view definition containing multiple joins on partitioned table returns invalid explain plan

Here is a tar file with more information.

It includes scripts to create all objects plus a README with description of
what is included

reiner

On Fri, Nov 19, 2021 at 11:09 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17292
> Logged by: reiner peterke
> Email address: zedaardv(at)drizzle(dot)com
> PostgreSQL version: 14.1
> Operating system: centos 8
> Description:
>
> This has been observer on centos8, ubuntu, and Mac OS
>
> there is a view defintion, provided below.
>
> It has 51 joins on the same partitioned table.
> the table has 219 partitions
> when an index is build on the partitioned table, executing the explain plan
> takes hours (over 5 on my mac) and returns an incomplete explain plan
>
> I will follow up this report with a mail with and attached tar file with
> all
> the necessary information for creating the situation
>
> view definition
> CREATE OR REPLACE VIEW bmk.vw_ftrucf_index
> AS
> SELECT s.source_id,
> s.src_name,
> m.index_id,
> d.as_of_date,
> dp.as_of_date AS prior_date,
> ica_mk_usd.currency_id AS mv_currency_id,
> c_usd.code AS mv_currency_code,
> a_mk.attribute_id AS mv_attribute_id,
> a_mk.code AS mv_attribute_code,
> ica_mk_usd.nvalue AS mv,
> c_usd.currency_id AS return_currency_id,
> c_usd.code AS return_currency_code,
> ( SELECT c.attribute_id
> FROM bmk.attributes c
> WHERE c.code::text = 'PI'::text AND c.attribute_type_id =
> 1::numeric) AS pi_attribute_id,
> 'PI'::text AS pi_attribute_code,
> ica_pi_usd.nvalue / p_ica_pi_usd.nvalue - 1::numeric AS pr,
> p_ica_pi_usd.nvalue AS p_wi_t0,
> ica_pi_usd.nvalue AS p_wi_t1,
> ( SELECT c.attribute_id
> FROM bmk.attributes c
> WHERE c.code::text = 'GI'::text AND c.attribute_type_id =
> 1::numeric) AS gi_attribute_id,
> 'GI'::text AS gi_attribute_code,
> ica_gi_usd.nvalue / p_ica_gi_usd.nvalue - 1::numeric AS gr,
> p_ica_gi_usd.nvalue AS g_wi_t0,
> ica_gi_usd.nvalue AS g_wi_t1,
> ( SELECT c.attribute_id
> FROM bmk.attributes c
> WHERE c.code::text = 'NI'::text AND c.attribute_type_id =
> 1::numeric) AS ni_attribute_id,
> 'NI'::text AS ni_attribute_code,
> ica_ni_usd.nvalue / p_ica_ni_usd.nvalue - 1::numeric AS nr,
> p_ica_ni_usd.nvalue AS n_wi_t0,
> ica_ni_usd.nvalue AS n_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgrjpy_attribute_id,
> 'HGI'::text AS hgrjpy_attribute_code,
> ica_hgi_jpy.nvalue / p_ica_hgi_jpy.nvalue - 1::numeric AS hgrjpy,
> p_ica_hgi_jpy.nvalue AS hgrjpy_wi_t0,
> ica_hgi_jpy.nvalue AS hgrjpy_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgrnzd_attribute_id,
> 'HGI'::text AS hgrnzd_attribute_code,
> ica_hgi_nzd.nvalue / p_ica_hgi_nzd.nvalue - 1::numeric AS hgrnzd,
> p_ica_hgi_nzd.nvalue AS hgrnzd_wi_t0,
> ica_hgi_nzd.nvalue AS hgrnzd_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnrgbp_attribute_id,
> 'HNI'::text AS hnrgbp_attribute_code,
> ica_hni_gbp.nvalue / p_ica_hni_gbp.nvalue - 1::numeric AS hnrgbp,
> p_ica_hni_gbp.nvalue AS hnrgbp_wi_t0,
> ica_hni_gbp.nvalue AS hnrgbp_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnrnzd_attribute_id,
> 'HNI'::text AS hnrnzd_attribute_code,
> ica_hni_nzd.nvalue / p_ica_hni_nzd.nvalue - 1::numeric AS hnrnzd,
> p_ica_hni_nzd.nvalue AS hnrnzd_wi_t0,
> ica_hni_nzd.nvalue AS hnrnzd_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgreur_attribute_id,
> 'HGI'::text AS hgreur_attribute_code,
> ica_hgi_eur.nvalue / p_ica_hgi_eur.nvalue - 1::numeric AS hgreur,
> p_ica_hgi_eur.nvalue AS hgreur_wi_t0,
> ica_hgi_eur.nvalue AS hgreur_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgrgbp_attribute_id,
> 'HGI'::text AS hgrgbp_attribute_code,
> ica_hgi_gbp.nvalue / p_ica_hgi_gbp.nvalue - 1::numeric AS hgrgbp,
> p_ica_hgi_gbp.nvalue AS hgrgbp_wi_t0,
> ica_hgi_gbp.nvalue AS hgrgbp_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgrhkd_attribute_id,
> 'HGI'::text AS hgrhkd_attribute_code,
> ica_hgi_hkd.nvalue / p_ica_hgi_hkd.nvalue - 1::numeric AS hgrhkd,
> p_ica_hgi_hkd.nvalue AS hgrhkd_wi_t0,
> ica_hgi_hkd.nvalue AS hgrhkd_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgrchf_attribute_id,
> 'HGI'::text AS hgrchf_attribute_code,
> ica_hgi_chf.nvalue / p_ica_hgi_chf.nvalue - 1::numeric AS hgrchf,
> p_ica_hgi_chf.nvalue AS hgrchf_wi_t0,
> ica_hgi_chf.nvalue AS hgrchf_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgrzar_attribute_id,
> 'HGI'::text AS hgrzar_attribute_code,
> ica_hgi_zar.nvalue / p_ica_hgi_zar.nvalue - 1::numeric AS hgrzar,
> p_ica_hgi_zar.nvalue AS hgrzar_wi_t0,
> ica_hgi_zar.nvalue AS hgrzar_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnreur_attribute_id,
> 'HNI'::text AS hnreur_attribute_code,
> ica_hni_eur.nvalue / p_ica_hni_eur.nvalue - 1::numeric AS hnreur,
> p_ica_hni_eur.nvalue AS hnreur_wi_t0,
> ica_hni_eur.nvalue AS hnreur_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgrusd_attribute_id,
> 'HGI'::text AS hgrusd_attribute_code,
> ica_hgi_usd.nvalue / p_ica_hgi_usd.nvalue - 1::numeric AS hgrusd,
> p_ica_hgi_usd.nvalue AS hgrusd_wi_t0,
> ica_hgi_usd.nvalue AS hgrusd_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgrcad_attribute_id,
> 'HGI'::text AS hgrcad_attribute_code,
> ica_hgi_cad.nvalue / p_ica_hgi_cad.nvalue - 1::numeric AS hgrcad,
> p_ica_hgi_cad.nvalue AS hgrcad_wi_t0,
> ica_hgi_cad.nvalue AS hgrcad_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnrcad_attribute_id,
> 'HNI'::text AS hnrcad_attribute_code,
> ica_hni_cad.nvalue / p_ica_hni_cad.nvalue - 1::numeric AS hnrcad,
> p_ica_hni_cad.nvalue AS hnrcad_wi_t0,
> ica_hni_cad.nvalue AS hnrcad_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnrhkd_attribute_id,
> 'HNI'::text AS hnrhkd_attribute_code,
> ica_hni_hkd.nvalue / p_ica_hni_hkd.nvalue - 1::numeric AS hnrhkd,
> p_ica_hni_hkd.nvalue AS hnrhkd_wi_t0,
> ica_hni_hkd.nvalue AS hnrhkd_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnrsgd_attribute_id,
> 'HNI'::text AS hnrsgd_attribute_code,
> ica_hni_sgd.nvalue / p_ica_hni_sgd.nvalue - 1::numeric AS hnrsgd,
> p_ica_hni_sgd.nvalue AS hnrsgd_wi_t0,
> ica_hni_sgd.nvalue AS hnrsgd_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnrusd_attribute_id,
> 'HNI'::text AS hnrusd_attribute_code,
> ica_hni_usd.nvalue / p_ica_hni_usd.nvalue - 1::numeric AS hnrusd,
> p_ica_hni_usd.nvalue AS hnrusd_wi_t0,
> ica_hni_usd.nvalue AS hnrusd_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgrsgd_attribute_id,
> 'HGI'::text AS hgrsgd_attribute_code,
> ica_hgi_sgd.nvalue / p_ica_hgi_sgd.nvalue - 1::numeric AS hgrsgd,
> p_ica_hgi_sgd.nvalue AS hgrsgd_wi_t0,
> ica_hgi_sgd.nvalue AS hgrsgd_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnraud_attribute_id,
> 'HNI'::text AS hnraud_attribute_code,
> ica_hni_aud.nvalue / p_ica_hni_aud.nvalue - 1::numeric AS hnraud,
> p_ica_hni_aud.nvalue AS hnraud_wi_t0,
> ica_hni_aud.nvalue AS hnraud_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnrjpy_attribute_id,
> 'HNI'::text AS hnrjpy_attribute_code,
> ica_hni_jpy.nvalue / p_ica_hni_jpy.nvalue - 1::numeric AS hnrjpy,
> p_ica_hni_jpy.nvalue AS hnrjpy_wi_t0,
> ica_hni_jpy.nvalue AS hnrjpy_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HGI'::text AND a.attribute_type_id =
> 1::numeric) AS hgraud_attribute_id,
> 'HGI'::text AS hgraud_attribute_code,
> ica_hgi_aud.nvalue / p_ica_hgi_aud.nvalue - 1::numeric AS hgraud,
> p_ica_hgi_aud.nvalue AS hgraud_wi_t0,
> ica_hgi_aud.nvalue AS hgraud_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnrchf_attribute_id,
> 'HNI'::text AS hnrchf_attribute_code,
> ica_hni_chf.nvalue / p_ica_hni_chf.nvalue - 1::numeric AS hnrchf,
> p_ica_hni_chf.nvalue AS hnrchf_wi_t0,
> ica_hni_chf.nvalue AS hnrchf_wi_t1,
> ( SELECT a.attribute_id
> FROM bmk.attributes a
> WHERE a.code::text = 'HNI'::text AND a.attribute_type_id =
> 1::numeric) AS hnrzar_attribute_id,
> 'HNI'::text AS hnrzar_attribute_code,
> ica_hni_zar.nvalue / p_ica_hni_zar.nvalue - 1::numeric AS hnrzar,
> p_ica_hni_zar.nvalue AS hnrzar_wi_t0,
> ica_hni_zar.nvalue AS hnrzar_wi_t1
> FROM bmk.sources s
> JOIN bmk.dates d ON d.as_of_date = d.as_of_date
> JOIN bmk.dates dp ON d.as_of_date > dp.as_of_date
> JOIN bmk.index_main m ON s.source_id = m.source_id AND d.as_of_date =
> m.as_of_date
> JOIN bmk.attribute_types att_idx ON att_idx.att_typ_name::text =
> 'INDEX'::text
> JOIN bmk.currencies c_eur ON c_eur.code::text = 'EUR'::text
> JOIN bmk.currencies c_zar ON c_zar.code::text = 'ZAR'::text
> JOIN bmk.currencies c_nzd ON c_nzd.code::text = 'NZD'::text
> JOIN bmk.currencies c_cny ON c_cny.code::text = 'CNY'::text
> JOIN bmk.currencies c_gbp ON c_gbp.code::text = 'GBP'::text
> JOIN bmk.currencies c_jpy ON c_jpy.code::text = 'JPY'::text
> JOIN bmk.currencies c_sgd ON c_sgd.code::text = 'SGD'::text
> JOIN bmk.currencies c_chf ON c_chf.code::text = 'CHF'::text
> JOIN bmk.currencies c_hkd ON c_hkd.code::text = 'HKD'::text
> JOIN bmk.currencies c_loc ON c_loc.code::text = 'LOC'::text
> JOIN bmk.currencies c_usd ON c_usd.code::text = 'USD'::text
> JOIN bmk.currencies c_aud ON c_aud.code::text = 'AUD'::text
> JOIN bmk.currencies c_cad ON c_cad.code::text = 'CAD'::text
> JOIN bmk.attributes a_gi ON a_gi.code::text = 'GI'::text AND
> a_gi.attribute_type_id = att_idx.attribute_type_id
> JOIN bmk.attributes a_ni ON a_ni.code::text = 'NI'::text AND
> a_ni.attribute_type_id = att_idx.attribute_type_id
> JOIN bmk.attributes a_hgi ON a_hgi.code::text = 'HGI'::text AND
> a_hgi.attribute_type_id = att_idx.attribute_type_id
> JOIN bmk.attributes a_mk ON a_mk.code::text = 'MK'::text AND
> a_mk.attribute_type_id = att_idx.attribute_type_id
> JOIN bmk.attributes a_hni ON a_hni.code::text = 'HNI'::text AND
> a_hni.attribute_type_id = att_idx.attribute_type_id
> JOIN bmk.attributes a_pi ON a_pi.code::text = 'PI'::text AND
> a_pi.attribute_type_id = att_idx.attribute_type_id
> LEFT JOIN bmk.index_curr_analytics ica_gi_usd ON ica_gi_usd.as_of_date
> = d.as_of_date AND ica_gi_usd.source_id = s.source_id AND
> ica_gi_usd.index_id = m.index_id AND ica_gi_usd.attribute_id =
> a_gi.attribute_id AND ica_gi_usd.currency_id = c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_gi_usd ON
> p_ica_gi_usd.as_of_date = dp.as_of_date AND p_ica_gi_usd.source_id =
> s.source_id AND p_ica_gi_usd.index_id = m.index_id AND
> p_ica_gi_usd.attribute_id = a_gi.attribute_id AND p_ica_gi_usd.currency_id
> =
> c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_pi_usd ON ica_pi_usd.as_of_date
> = d.as_of_date AND ica_pi_usd.source_id = s.source_id AND
> ica_pi_usd.index_id = m.index_id AND ica_pi_usd.attribute_id =
> a_pi.attribute_id AND ica_pi_usd.currency_id = c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_pi_usd ON
> p_ica_pi_usd.as_of_date = dp.as_of_date AND p_ica_pi_usd.source_id =
> s.source_id AND p_ica_pi_usd.index_id = m.index_id AND
> p_ica_pi_usd.attribute_id = a_pi.attribute_id AND p_ica_pi_usd.currency_id
> =
> c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_ni_usd ON ica_ni_usd.as_of_date
> = d.as_of_date AND ica_ni_usd.source_id = s.source_id AND
> ica_ni_usd.index_id = m.index_id AND ica_ni_usd.attribute_id =
> a_ni.attribute_id AND ica_ni_usd.currency_id = c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_ni_usd ON
> p_ica_ni_usd.as_of_date = dp.as_of_date AND p_ica_ni_usd.source_id =
> s.source_id AND p_ica_ni_usd.index_id = m.index_id AND
> p_ica_ni_usd.attribute_id = a_ni.attribute_id AND p_ica_ni_usd.currency_id
> =
> c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_mk_usd ON ica_mk_usd.as_of_date
> = d.as_of_date AND ica_mk_usd.source_id = s.source_id AND
> ica_mk_usd.index_id = m.index_id AND ica_mk_usd.attribute_id =
> a_mk.attribute_id AND ica_mk_usd.currency_id = c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_cad ON
> ica_hni_cad.as_of_date = d.as_of_date AND ica_hni_cad.source_id =
> s.source_id AND ica_hni_cad.index_id = m.index_id AND
> ica_hni_cad.attribute_id = a_hni.attribute_id AND ica_hni_cad.currency_id =
> c_cad.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_cad ON
> p_ica_hni_cad.as_of_date = dp.as_of_date AND p_ica_hni_cad.source_id =
> s.source_id AND p_ica_hni_cad.index_id = m.index_id AND
> p_ica_hni_cad.attribute_id = a_hni.attribute_id AND
> p_ica_hni_cad.currency_id = c_cad.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_chf ON
> ica_hni_chf.as_of_date = d.as_of_date AND ica_hni_chf.source_id =
> s.source_id AND ica_hni_chf.index_id = m.index_id AND
> ica_hni_chf.attribute_id = a_hni.attribute_id AND ica_hni_chf.currency_id =
> c_chf.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_chf ON
> p_ica_hni_chf.as_of_date = dp.as_of_date AND p_ica_hni_chf.source_id =
> s.source_id AND p_ica_hni_chf.index_id = m.index_id AND
> p_ica_hni_chf.attribute_id = a_hni.attribute_id AND
> p_ica_hni_chf.currency_id = c_chf.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_eur ON
> ica_hni_eur.as_of_date = d.as_of_date AND ica_hni_eur.source_id =
> s.source_id AND ica_hni_eur.index_id = m.index_id AND
> ica_hni_eur.attribute_id = a_hni.attribute_id AND ica_hni_eur.currency_id =
> c_eur.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_eur ON
> p_ica_hni_eur.as_of_date = dp.as_of_date AND p_ica_hni_eur.source_id =
> s.source_id AND p_ica_hni_eur.index_id = m.index_id AND
> p_ica_hni_eur.attribute_id = a_hni.attribute_id AND
> p_ica_hni_eur.currency_id = c_eur.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_gbp ON
> ica_hni_gbp.as_of_date = d.as_of_date AND ica_hni_gbp.source_id =
> s.source_id AND ica_hni_gbp.index_id = m.index_id AND
> ica_hni_gbp.attribute_id = a_hni.attribute_id AND ica_hni_gbp.currency_id =
> c_gbp.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_gbp ON
> p_ica_hni_gbp.as_of_date = dp.as_of_date AND p_ica_hni_gbp.source_id =
> s.source_id AND p_ica_hni_gbp.index_id = m.index_id AND
> p_ica_hni_gbp.attribute_id = a_hni.attribute_id AND
> p_ica_hni_gbp.currency_id = c_gbp.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_sgd ON
> ica_hni_sgd.as_of_date = d.as_of_date AND ica_hni_sgd.source_id =
> s.source_id AND ica_hni_sgd.index_id = m.index_id AND
> ica_hni_sgd.attribute_id = a_hni.attribute_id AND ica_hni_sgd.currency_id =
> c_sgd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_sgd ON
> p_ica_hni_sgd.as_of_date = dp.as_of_date AND p_ica_hni_sgd.source_id =
> s.source_id AND p_ica_hni_sgd.index_id = m.index_id AND
> p_ica_hni_sgd.attribute_id = a_hni.attribute_id AND
> p_ica_hni_sgd.currency_id = c_sgd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_usd ON
> ica_hni_usd.as_of_date = d.as_of_date AND ica_hni_usd.source_id =
> s.source_id AND ica_hni_usd.index_id = m.index_id AND
> ica_hni_usd.attribute_id = a_hni.attribute_id AND ica_hni_usd.currency_id =
> c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_usd ON
> p_ica_hni_usd.as_of_date = dp.as_of_date AND p_ica_hni_usd.source_id =
> s.source_id AND p_ica_hni_usd.index_id = m.index_id AND
> p_ica_hni_usd.attribute_id = a_hni.attribute_id AND
> p_ica_hni_usd.currency_id = c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_aud ON
> ica_hni_aud.as_of_date = d.as_of_date AND ica_hni_aud.source_id =
> s.source_id AND ica_hni_aud.index_id = m.index_id AND
> ica_hni_aud.attribute_id = a_hni.attribute_id AND ica_hni_aud.currency_id =
> c_aud.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_aud ON
> p_ica_hni_aud.as_of_date = dp.as_of_date AND p_ica_hni_aud.source_id =
> s.source_id AND p_ica_hni_aud.index_id = m.index_id AND
> p_ica_hni_aud.attribute_id = a_hni.attribute_id AND
> p_ica_hni_aud.currency_id = c_aud.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_jpy ON
> ica_hni_jpy.as_of_date = d.as_of_date AND ica_hni_jpy.source_id =
> s.source_id AND ica_hni_jpy.index_id = m.index_id AND
> ica_hni_jpy.attribute_id = a_hni.attribute_id AND ica_hni_jpy.currency_id =
> c_jpy.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_jpy ON
> p_ica_hni_jpy.as_of_date = dp.as_of_date AND p_ica_hni_jpy.source_id =
> s.source_id AND p_ica_hni_jpy.index_id = m.index_id AND
> p_ica_hni_jpy.attribute_id = a_hni.attribute_id AND
> p_ica_hni_jpy.currency_id = c_jpy.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_zar ON
> ica_hni_zar.as_of_date = d.as_of_date AND ica_hni_zar.source_id =
> s.source_id AND ica_hni_zar.index_id = m.index_id AND
> ica_hni_zar.attribute_id = a_hni.attribute_id AND ica_hni_zar.currency_id =
> c_zar.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_zar ON
> p_ica_hni_zar.as_of_date = dp.as_of_date AND p_ica_hni_zar.source_id =
> s.source_id AND p_ica_hni_zar.index_id = m.index_id AND
> p_ica_hni_zar.attribute_id = a_hni.attribute_id AND
> p_ica_hni_zar.currency_id = c_zar.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_hkd ON
> ica_hni_hkd.as_of_date = d.as_of_date AND ica_hni_hkd.source_id =
> s.source_id AND ica_hni_hkd.index_id = m.index_id AND
> ica_hni_hkd.attribute_id = a_hni.attribute_id AND ica_hni_hkd.currency_id =
> c_hkd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_hkd ON
> p_ica_hni_hkd.as_of_date = dp.as_of_date AND p_ica_hni_hkd.source_id =
> s.source_id AND p_ica_hni_hkd.index_id = m.index_id AND
> p_ica_hni_hkd.attribute_id = a_hni.attribute_id AND
> p_ica_hni_hkd.currency_id = c_hkd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hni_nzd ON
> ica_hni_nzd.as_of_date = d.as_of_date AND ica_hni_nzd.source_id =
> s.source_id AND ica_hni_nzd.index_id = m.index_id AND
> ica_hni_nzd.attribute_id = a_hni.attribute_id AND ica_hni_nzd.currency_id =
> c_nzd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hni_nzd ON
> p_ica_hni_nzd.as_of_date = dp.as_of_date AND p_ica_hni_nzd.source_id =
> s.source_id AND p_ica_hni_nzd.index_id = m.index_id AND
> p_ica_hni_nzd.attribute_id = a_hni.attribute_id AND
> p_ica_hni_nzd.currency_id = c_nzd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_cad ON
> ica_hgi_cad.as_of_date = d.as_of_date AND ica_hgi_cad.source_id =
> s.source_id AND ica_hgi_cad.index_id = m.index_id AND
> ica_hgi_cad.attribute_id = a_hgi.attribute_id AND ica_hgi_cad.currency_id =
> c_cad.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_cad ON
> p_ica_hgi_cad.as_of_date = dp.as_of_date AND p_ica_hgi_cad.source_id =
> s.source_id AND p_ica_hgi_cad.index_id = m.index_id AND
> p_ica_hgi_cad.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_cad.currency_id = c_cad.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_chf ON
> ica_hgi_chf.as_of_date = d.as_of_date AND ica_hgi_chf.source_id =
> s.source_id AND ica_hgi_chf.index_id = m.index_id AND
> ica_hgi_chf.attribute_id = a_hgi.attribute_id AND ica_hgi_chf.currency_id =
> c_chf.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_chf ON
> p_ica_hgi_chf.as_of_date = dp.as_of_date AND p_ica_hgi_chf.source_id =
> s.source_id AND p_ica_hgi_chf.index_id = m.index_id AND
> p_ica_hgi_chf.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_chf.currency_id = c_chf.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_eur ON
> ica_hgi_eur.as_of_date = d.as_of_date AND ica_hgi_eur.source_id =
> s.source_id AND ica_hgi_eur.index_id = m.index_id AND
> ica_hgi_eur.attribute_id = a_hgi.attribute_id AND ica_hgi_eur.currency_id =
> c_eur.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_eur ON
> p_ica_hgi_eur.as_of_date = dp.as_of_date AND p_ica_hgi_eur.source_id =
> s.source_id AND p_ica_hgi_eur.index_id = m.index_id AND
> p_ica_hgi_eur.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_eur.currency_id = c_eur.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_gbp ON
> ica_hgi_gbp.as_of_date = d.as_of_date AND ica_hgi_gbp.source_id =
> s.source_id AND ica_hgi_gbp.index_id = m.index_id AND
> ica_hgi_gbp.attribute_id = a_hgi.attribute_id AND ica_hgi_gbp.currency_id =
> c_gbp.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_gbp ON
> p_ica_hgi_gbp.as_of_date = dp.as_of_date AND p_ica_hgi_gbp.source_id =
> s.source_id AND p_ica_hgi_gbp.index_id = m.index_id AND
> p_ica_hgi_gbp.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_gbp.currency_id = c_gbp.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_sgd ON
> ica_hgi_sgd.as_of_date = d.as_of_date AND ica_hgi_sgd.source_id =
> s.source_id AND ica_hgi_sgd.index_id = m.index_id AND
> ica_hgi_sgd.attribute_id = a_hgi.attribute_id AND ica_hgi_sgd.currency_id =
> c_sgd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_sgd ON
> p_ica_hgi_sgd.as_of_date = dp.as_of_date AND p_ica_hgi_sgd.source_id =
> s.source_id AND p_ica_hgi_sgd.index_id = m.index_id AND
> p_ica_hgi_sgd.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_sgd.currency_id = c_sgd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_usd ON
> ica_hgi_usd.as_of_date = d.as_of_date AND ica_hgi_usd.source_id =
> s.source_id AND ica_hgi_usd.index_id = m.index_id AND
> ica_hgi_usd.attribute_id = a_hgi.attribute_id AND ica_hgi_usd.currency_id =
> c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_usd ON
> p_ica_hgi_usd.as_of_date = dp.as_of_date AND p_ica_hgi_usd.source_id =
> s.source_id AND p_ica_hgi_usd.index_id = m.index_id AND
> p_ica_hgi_usd.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_usd.currency_id = c_usd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_aud ON
> ica_hgi_aud.as_of_date = d.as_of_date AND ica_hgi_aud.source_id =
> s.source_id AND ica_hgi_aud.index_id = m.index_id AND
> ica_hgi_aud.attribute_id = a_hgi.attribute_id AND ica_hgi_aud.currency_id =
> c_aud.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_aud ON
> p_ica_hgi_aud.as_of_date = dp.as_of_date AND p_ica_hgi_aud.source_id =
> s.source_id AND p_ica_hgi_aud.index_id = m.index_id AND
> p_ica_hgi_aud.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_aud.currency_id = c_aud.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_jpy ON
> ica_hgi_jpy.as_of_date = d.as_of_date AND ica_hgi_jpy.source_id =
> s.source_id AND ica_hgi_jpy.index_id = m.index_id AND
> ica_hgi_jpy.attribute_id = a_hgi.attribute_id AND ica_hgi_jpy.currency_id =
> c_jpy.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_jpy ON
> p_ica_hgi_jpy.as_of_date = dp.as_of_date AND p_ica_hgi_jpy.source_id =
> s.source_id AND p_ica_hgi_jpy.index_id = m.index_id AND
> p_ica_hgi_jpy.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_jpy.currency_id = c_jpy.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_zar ON
> ica_hgi_zar.as_of_date = d.as_of_date AND ica_hgi_zar.source_id =
> s.source_id AND ica_hgi_zar.index_id = m.index_id AND
> ica_hgi_zar.attribute_id = a_hgi.attribute_id AND ica_hgi_zar.currency_id =
> c_zar.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_zar ON
> p_ica_hgi_zar.as_of_date = dp.as_of_date AND p_ica_hgi_zar.source_id =
> s.source_id AND p_ica_hgi_zar.index_id = m.index_id AND
> p_ica_hgi_zar.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_zar.currency_id = c_zar.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_hkd ON
> ica_hgi_hkd.as_of_date = d.as_of_date AND ica_hgi_hkd.source_id =
> s.source_id AND ica_hgi_hkd.index_id = m.index_id AND
> ica_hgi_hkd.attribute_id = a_hgi.attribute_id AND ica_hgi_hkd.currency_id =
> c_hkd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_hkd ON
> p_ica_hgi_hkd.as_of_date = dp.as_of_date AND p_ica_hgi_hkd.source_id =
> s.source_id AND p_ica_hgi_hkd.index_id = m.index_id AND
> p_ica_hgi_hkd.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_hkd.currency_id = c_hkd.currency_id
> LEFT JOIN bmk.index_curr_analytics ica_hgi_nzd ON
> ica_hgi_nzd.as_of_date = d.as_of_date AND ica_hgi_nzd.source_id =
> s.source_id AND ica_hgi_nzd.index_id = m.index_id AND
> ica_hgi_nzd.attribute_id = a_hgi.attribute_id AND ica_hgi_nzd.currency_id =
> c_nzd.currency_id
> LEFT JOIN bmk.index_curr_analytics p_ica_hgi_nzd ON
> p_ica_hgi_nzd.as_of_date = dp.as_of_date AND p_ica_hgi_nzd.source_id =
> s.source_id AND p_ica_hgi_nzd.index_id = m.index_id AND
> p_ica_hgi_nzd.attribute_id = a_hgi.attribute_id AND
> p_ica_hgi_nzd.currency_id = c_nzd.currency_id
> WHERE s.src_name::text = 'FTRUCF'::text;
>
>