explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WVLy

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,086.443 ↑ 10.0 1 1

HashAggregate (cost=4,387.42..4,387.52 rows=10 width=51) (actual time=1,086.443..1,086.443 rows=1 loops=1)

  • Output: b.id, a.id, a.account_number, b.number, (ltrim((b.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c.fund_company)::text), ''::text), NULLIF(btrim((c.custodial_provider_name)::text), ''::text))), b.id, b.opened_on
  • Group Key: b.id, a.id, a.account_number, b.number, (ltrim((b.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c.fund_company)::text), ''::text), NULLIF(btrim((c.custodial_provider_name)::text), ''::text))), b.id, b.opened_on
2. 0.001 1,086.439 ↑ 10.0 1 1

Append (cost=75.69..4,387.22 rows=10 width=51) (actual time=1.305..1,086.439 rows=1 loops=1)

3. 0.000 0.786 ↓ 0.0 0 1

HashAggregate (cost=75.69..75.72 rows=1 width=51) (actual time=0.786..0.786 rows=0 loops=1)

  • Output: b.id, a.id, a.account_number, b.number, (ltrim((b.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c.fund_company)::text), ''::text), NULLIF(btrim((c.custodial_provider_name)::text), ''::text))), b.id, b.opened_on
  • Group Key: b.id, a.id, a.account_number, b.number, ltrim((b.number)::text, '0'::text), COALESCE(NULLIF(btrim((c.fund_company)::text), ''::text), NULLIF(btrim((c.custodial_provider_name)::text), ''::text)), b.id, b.opened_on
4. 0.000 0.786 ↓ 0.0 0 1

Nested Loop (cost=9.02..75.67 rows=1 width=51) (actual time=0.786..0.786 rows=0 loops=1)

  • Output: b.id, a.id, a.account_number, b.number, ltrim((b.number)::text, '0'::text), COALESCE(NULLIF(btrim((c.fund_company)::text), ''::text), NULLIF(btrim((c.custodial_provider_name)::text), ''::text)), b.id, b.opened_on
5. 0.009 0.786 ↓ 0.0 0 1

Nested Loop (cost=8.47..67.07 rows=1 width=35) (actual time=0.786..0.786 rows=0 loops=1)

  • Output: a.id, a.account_number, c.fund_company, c.custodial_provider_name, cp.id
  • Join Filter: ((c.custodial_provider_name)::text = (cp.name)::text)
  • Rows Removed by Join Filter: 37
6. 0.098 0.766 ↑ 1.0 1 1

Hash Join (cost=8.47..65.11 rows=1 width=27) (actual time=0.732..0.766 rows=1 loops=1)

  • Output: a.id, a.account_number, c.fund_company, c.custodial_provider_name
  • Hash Cond: ((c.where_held_classic_id)::text = (a.where_held_classic_id)::text)
7. 0.654 0.654 ↑ 1.4 1,024 1

Seq Scan on public.provider_where_held_mapping c (cost=0.00..51.41 rows=1,391 width=14) (actual time=0.009..0.654 rows=1,024 loops=1)

  • Output: c.fund_company, c.custodial_provider_name, c.where_held_classic_id
  • Filter: (((c.realm)::text <> 'cg'::text) AND (c.is_obsolete = 'N'::bpchar))
  • Rows Removed by Filter: 468
8. 0.001 0.014 ↑ 1.0 1 1

Hash (cost=8.46..8.46 rows=1 width=22) (actual time=0.014..0.014 rows=1 loops=1)

  • Output: a.id, a.account_number, a.where_held_classic_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
9. 0.013 0.013 ↑ 1.0 1 1

Index Scan using accounts_pkey on public.accounts a (cost=0.44..8.46 rows=1 width=22) (actual time=0.012..0.013 rows=1 loops=1)

  • Output: a.id, a.account_number, a.where_held_classic_id
  • Index Cond: (a.id = 4,054,877)
10. 0.011 0.011 ↓ 1.1 37 1

Seq Scan on public.custodial_providers cp (cost=0.00..1.52 rows=35 width=17) (actual time=0.006..0.011 rows=37 loops=1)

  • Output: cp.id, cp.name, cp.priority
  • Filter: ((cp.name)::text <> ALL ('{DST,DAZL,DTCC}'::text[]))
  • Rows Removed by Filter: 3
11. 0.000 0.000 ↓ 0.0 0

Index Scan using index_cust_accounts_on_cust_account_custodial_provider_id on public.custodial_accounts b (cost=0.56..8.58 rows=1 width=32) (never executed)

  • Output: b.id, b.number, b.opened_on, b.custodial_provider_id
  • Index Cond: (((b.number)::text = (a.account_number)::text) AND (b.custodial_provider_id = cp.id))
12. 0.001 0.084 ↓ 0.0 0 1

HashAggregate (cost=52.53..52.55 rows=1 width=51) (actual time=0.084..0.084 rows=0 loops=1)

  • Output: b_1.id, a_1.id, a_1.account_number, b_1.number, (ltrim((b_1.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c_1.fund_company)::text), ''::text), NULLIF(btrim((c_1.custodial_provider_name)::text), ''::text))), b_1.id, b_1.opened_on
  • Group Key: b_1.id, a_1.id, a_1.account_number, b_1.number, ltrim((b_1.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_1.fund_company)::text), ''::text), NULLIF(btrim((c_1.custodial_provider_name)::text), ''::text)), b_1.id, b_1.opened_on
13. 0.000 0.083 ↓ 0.0 0 1

Nested Loop (cost=1.27..52.51 rows=1 width=51) (actual time=0.083..0.083 rows=0 loops=1)

  • Output: b_1.id, a_1.id, a_1.account_number, b_1.number, ltrim((b_1.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_1.fund_company)::text), ''::text), NULLIF(btrim((c_1.custodial_provider_name)::text), ''::text)), b_1.id, b_1.opened_on
  • Join Filter: ((a_1.where_held_classic_id)::text = (c_1.where_held_classic_id)::text)
14. 0.003 0.083 ↓ 0.0 0 1

Nested Loop (cost=0.99..35.81 rows=2 width=59) (actual time=0.083..0.083 rows=0 loops=1)

  • Output: a_1.id, a_1.account_number, a_1.where_held_classic_id, b_1.id, b_1.number, b_1.opened_on, b_1.where_held, cp_1.name
15. 0.005 0.005 ↑ 1.0 1 1

Index Scan using accounts_pkey on public.accounts a_1 (cost=0.44..8.46 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=1)

  • Output: a_1.id, a_1.realm_id, a_1.account_forms_collection_id, a_1.classic_multivalue_position, a_1.where_held_classic_id, a_1.held_where_id, a_1.account_number, a_1.hidden, a_1.dirty, a_1.created_at, a_1.updated_at, a_1.form_type, a_1.acctype, a_1.acctype_other, a_1.advisory_bus, a_1.alt_address_1, a_1.alt_address_2, a_1.alt_city_1, a_1.alt_city_2, a_1.alt_email_address_1, a_1.alt_email_address_2, a_1.alt_first_name_1, a_1.alt_first_name_2, a_1.alt_h_phone_1, a_1.alt_h_phone_2, a_1.alt_last_name_1, a_1.alt_last_name_2, a_1.alt_m_phone_1, a_1.alt_m_phone_2, a_1.alt_no_email_1, a_1.alt_no_email_2, a_1.alt_relationship_1, a_1.alt_relationship_2, a_1.alt_state_1, a_1.alt_state_2, a_1.alt_zip_1, a_1.alt_zip_2, a_1.backup_withholding, a_1.balance, a_1.bal_asof, a_1.benefactor_name, a_1.bene_ira_dob, a_1.bene_ira_dob_rmd, a_1.bene_ira_dod, a_1.bene_ira_spousal, a_1.ben_responsible_individual, a_1.buy_amt, a_1.buy_sec, a_1.b_phone_cont, a_1.cam_type, a_1.ca_state, a_1.ca_term, a_1.ca_type, a_1.certif, a_1.citizen_other_cont, a_1.city_coverdell_cont, a_1.ck_amt, a_1.ck_enc, a_1.comm_sched, a_1.company_owner, a_1.company_phone, a_1.convert_cash, a_1.country_coverdell_cont, a_1.date2_cont, a_1.dc_name, a_1.dep_addr, a_1.dep_amt, a_1.dep_bus_tel_num, a_1.dep_first_name, a_1.dep_last_name, a_1.dep_sec, a_1.dep_soc_sec, a_1.dep_tel_num, a_1.div_opt, a_1.email_cont, a_1.employer_tin, a_1.f13, a_1.fatca_code, a_1.form_print_status, a_1.gov_id_cont, a_1.gov_id_expire_cont, a_1.hsa_account_number, a_1.id_country_cont, a_1.init_tran_buy, a_1.init_tran_dep_cash, a_1.init_tran_dep_stock, a_1.init_tran_none, a_1.init_tran_sell, a_1.init_tran_trans, a_1.invest_link, a_1.invest_time_horizon3, a_1.marital_cont, a_1.mmbal, a_1.mmf, a_1.number_of_participants, a_1.num_signers, a_1.obj0, a_1.obj1, a_1.obj2, a_1.obj3, a_1.obj4, a_1.obj5, a_1.obj6, a_1.obj7, a_1.obj8, a_1.obj9, a_1.ofac_comp_cont, a_1.officer, a_1.open_req_ids, a_1.open_warning, a_1.opt_level, a_1.other_source_of_funds, a_1.per_stirpes, a_1.photo_id_copy_cont, a_1.plan_number, a_1.pp_via, a_1.prim_objective_entity, a_1.prim_objective_raw, a_1.princ_app, a_1.question_1, a_1.question_2, a_1.relationship_cont, a_1.rep, a_1.resident_other_cont, a_1.resp_ind_rule1, a_1.resp_ind_rule2, a_1.resp_ind_rule3, a_1.risk_tolerance, a_1.rollover_days, a_1.rollover_dollars, a_1.rollover_eligible, a_1.rollover_percentage, a_1.roth_type, a_1.rp_relationship, a_1.sec_objective, a_1.sell_amt, a_1.sell_sec, a_1.signer, a_1.simple_date, a_1.source_of_funds, a_1.state_coverdell_cont, a_1.succ_resp_ind_name, a_1.succ_resp_ind_ssn, a_1.succ_resp_ind_tel, a_1.succ_resp_ind_addr, a_1.succ_resp_ind_city, a_1.succ_resp_ind_state, a_1.succ_resp_ind_zip, a_1.swept, a_1.tenants1_name, a_1.tenants1_percent, a_1.tenants2_name, a_1.tenants2_percent, a_1."timestamp", a_1.tin_type_dep, a_1.tin_type_succ_resp, a_1.title, a_1.trade_level, a_1.trade_method, a_1.transfer, a_1.tran_amt, a_1.tran_sec, a_1.us_citizen_cont, a_1.us_resident_cont, a_1.vendor_name, a_1.waive_fee_date, a_1.wire, a_1.zip_coverdell_cont, a_1.open_date, a_1.account_master_id, a_1.complete_at_save, a_1.ca_date, a_1.business_type, a_1.fundvision_pp_via, a_1.fundvision_statements_via, a_1.fundvision_tax_documents_via, a_1.fundvision_proxy_comms_via, a_1.fundvision_prospectuses_via, a_1.consent_id, a_1.participant_investments, a_1.services_ips, a_1.services_liaison, a_1.services_reports, a_1.services_options, a_1.services_education, a_1.services_participant_education, a_1.services_enrollment, a_1.services_plan_fees, a_1.services_investment_monitoring, a_1.acting_capacity, a_1.invest_stocks, a_1.invest_mfs, a_1.invest_va, a_1.invest_fa, a_1.invest_life_ins, a_1.invest_advisory, a_1.invest_other, a_1.invest_other_desc, a_1.firelight_trans_classic_id, a_1.no_ach, a_1.private_banking_or_foreign, a_1.private_banking_or_foreign_information, a_1.investment_strategy_short_term_growth, a_1.investment_strategy_long_term_growth, a_1.investment_strategy_aggressive_growth, a_1.investment_strategy_growth, a_1.investment_strategy_growth_income, a_1.investment_strategy_balanced, a_1.investment_strategy_balanced_income, a_1.funding_source_investment_proceeds, a_1.funding_source_legal_settlement, a_1.funding_source_gift, a_1.funding_source_trust_will, a_1.funding_source_income_from_earnings_savings, a_1.funding_source_pension_ira_retirement, a_1.funding_source_spouse_parent, a_1.funding_source_insurance_proceeds, a_1.funding_source_other, a_1.contact_beneficiary_tuition_plan, a_1.manual_account, a_1.alt_country_1, a_1.alt_country_2
  • Index Cond: (a_1.id = 4,054,877)
16. 0.004 0.075 ↓ 0.0 0 1

Nested Loop (cost=0.56..27.33 rows=3 width=37) (actual time=0.075..0.075 rows=0 loops=1)

  • Output: b_1.id, b_1.number, b_1.opened_on, b_1.where_held, cp_1.name
17. 0.008 0.008 ↑ 1.0 3 1

Seq Scan on public.custodial_providers cp_1 (cost=0.00..1.52 rows=3 width=17) (actual time=0.005..0.008 rows=3 loops=1)

  • Output: cp_1.id, cp_1.name, cp_1.priority
  • Filter: ((cp_1.name)::text = ANY ('{DST,DAZL,DTCC}'::text[]))
  • Rows Removed by Filter: 37
18. 0.063 0.063 ↓ 0.0 0 3

Index Scan using index_cust_accounts_on_cust_account_custodial_provider_id on public.custodial_accounts b_1 (cost=0.56..8.59 rows=1 width=36) (actual time=0.021..0.021 rows=0 loops=3)

  • Output: b_1.id, b_1.number, b_1.opened_on, b_1.where_held, b_1.custodial_provider_id
  • Index Cond: (((b_1.number)::text = (a_1.account_number)::text) AND (b_1.custodial_provider_id = cp_1.id))
  • Filter: (((b_1.where_held)::text <> ALL ('{ACMAC,ACMLQ,AMVAM,FTGFF,JHKJH,MASFM,PMCAR,PMCPM}'::text[])) OR (b_1.where_held IS NULL))
19. 0.000 0.000 ↓ 0.0 0

Index Scan using provider_where_held_mapping_index_provider_name_fund_company on public.provider_where_held_mapping c_1 (cost=0.28..8.33 rows=1 width=18) (never executed)

  • Output: c_1.fund_company, c_1.custodial_provider_name, c_1.where_held_classic_id, c_1.realm
  • Index Cond: (((c_1.custodial_provider_name)::text = (cp_1.name)::text) AND ((c_1.fund_company)::text = (b_1.where_held)::text))
  • Filter: (((c_1.realm)::text <> 'cg'::text) AND (c_1.is_obsolete = 'N'::bpchar) AND ((((b_1.where_held)::text <> ALL ('{ACMAC,ACMLQ,AMVAM,FTGFF,JHKJH,MASFM,PMCAR,PMCPM}'::text[])) AND (((b_1.where_held)::text <> ALL ('{DSTLA}'::text[])) OR ((c_1.realm)::text <> ALL ('{cfs,spf}'::text[]))) AND (((b_1.where_held)::text <> 'DLWDG'::text) OR ((c_1.realm)::text <> 'cfs'::text))) OR (b_1.where_held IS NULL)))
20. 0.005 0.434 ↑ 1.0 1 1

HashAggregate (cost=75.63..75.65 rows=1 width=51) (actual time=0.434..0.434 rows=1 loops=1)

  • Output: b_2.id, a_2.id, a_2.account_number, b_2.number, (ltrim((b_2.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c_2.fund_company)::text), ''::text), NULLIF(btrim((c_2.custodial_provider_name)::text), ''::text))), b_2.id, b_2.opened_on
  • Group Key: b_2.id, a_2.id, a_2.account_number, b_2.number, ltrim((b_2.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_2.fund_company)::text), ''::text), NULLIF(btrim((c_2.custodial_provider_name)::text), ''::text)), b_2.id, b_2.opened_on
21. 0.013 0.429 ↑ 1.0 1 1

Nested Loop (cost=9.03..75.61 rows=1 width=51) (actual time=0.396..0.429 rows=1 loops=1)

  • Output: b_2.id, a_2.id, a_2.account_number, b_2.number, ltrim((b_2.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_2.fund_company)::text), ''::text), NULLIF(btrim((c_2.custodial_provider_name)::text), ''::text)), b_2.id, b_2.opened_on
  • Join Filter: (((c_2.fund_company)::text = (b_2.where_held)::text) AND (((b_2.where_held)::text = ANY ('{PMCPM,PMCAR,ALLNZ,ACMAC,FTGFF,AMVAM,JHKJH,PMCPM}'::text[])) OR (((b_2.where_held)::text = 'MASFM'::text) AND ((c_2.realm)::text <> 'spf'::text))))
22. 0.005 0.383 ↑ 1.0 1 1

Nested Loop (cost=8.47..66.96 rows=1 width=39) (actual time=0.352..0.383 rows=1 loops=1)

  • Output: a_2.id, a_2.account_number, c_2.fund_company, c_2.custodial_provider_name, c_2.realm, cp_2.id
  • Join Filter: ((c_2.custodial_provider_name)::text = (cp_2.name)::text)
  • Rows Removed by Join Filter: 39
23. 0.084 0.373 ↑ 1.0 1 1

Hash Join (cost=8.47..65.11 rows=1 width=31) (actual time=0.344..0.373 rows=1 loops=1)

  • Output: a_2.id, a_2.account_number, c_2.fund_company, c_2.custodial_provider_name, c_2.realm
  • Hash Cond: ((c_2.where_held_classic_id)::text = (a_2.where_held_classic_id)::text)
24. 0.284 0.284 ↑ 1.4 1,024 1

Seq Scan on public.provider_where_held_mapping c_2 (cost=0.00..51.41 rows=1,391 width=18) (actual time=0.006..0.284 rows=1,024 loops=1)

  • Output: c_2.fund_company, c_2.custodial_provider_name, c_2.where_held_classic_id, c_2.realm
  • Filter: (((c_2.realm)::text <> 'cg'::text) AND (c_2.is_obsolete = 'N'::bpchar))
  • Rows Removed by Filter: 468
25. 0.001 0.005 ↑ 1.0 1 1

Hash (cost=8.46..8.46 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=1)

  • Output: a_2.id, a_2.account_number, a_2.where_held_classic_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
26. 0.004 0.004 ↑ 1.0 1 1

Index Scan using accounts_pkey on public.accounts a_2 (cost=0.44..8.46 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: a_2.id, a_2.account_number, a_2.where_held_classic_id
  • Index Cond: (a_2.id = 4,054,877)
  • Filter: (a_2.account_number IS NOT NULL)
27. 0.005 0.005 ↓ 1.1 40 1

Seq Scan on public.custodial_providers cp_2 (cost=0.00..1.38 rows=38 width=17) (actual time=0.001..0.005 rows=40 loops=1)

  • Output: cp_2.id, cp_2.name, cp_2.priority
28. 0.033 0.033 ↑ 1.0 1 1

Index Scan using index_cust_accounts_on_cust_account_custodial_provider_id on public.custodial_accounts b_2 (cost=0.56..8.61 rows=1 width=42) (actual time=0.031..0.033 rows=1 loops=1)

  • Output: b_2.id, b_2.number, b_2.opened_on, b_2.where_held, b_2.cusip, b_2.custodial_provider_id
  • Index Cond: (((b_2.number)::text = "left"((a_2.account_number)::text, (-9))) AND (b_2.custodial_provider_id = cp_2.id))
  • Filter: (("right"((a_2.account_number)::text, 9) = (b_2.cusip)::text) AND (((b_2.where_held)::text = ANY ('{PMCPM,PMCAR,ALLNZ,ACMAC,FTGFF,AMVAM,JHKJH,PMCPM}'::text[])) OR ((b_2.where_held)::text = 'MASFM'::text)))
  • Rows Removed by Filter: 2
29. 0.000 0.252 ↓ 0.0 0 1

HashAggregate (cost=175.39..175.42 rows=1 width=51) (actual time=0.252..0.252 rows=0 loops=1)

  • Output: b_3.id, a_3.id, a_3.account_number, b_3.number, (ltrim((b_3.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c_3.fund_company)::text), ''::text), NULLIF(btrim((c_3.custodial_provider_name)::text), ''::text))), b_3.id, b_3.opened_on
  • Group Key: b_3.id, a_3.id, a_3.account_number, b_3.number, ltrim((b_3.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_3.fund_company)::text), ''::text), NULLIF(btrim((c_3.custodial_provider_name)::text), ''::text)), b_3.id, b_3.opened_on
30. 0.000 0.252 ↓ 0.0 0 1

Nested Loop (cost=13.11..175.37 rows=1 width=51) (actual time=0.252..0.252 rows=0 loops=1)

  • Output: b_3.id, a_3.id, a_3.account_number, b_3.number, ltrim((b_3.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_3.fund_company)::text), ''::text), NULLIF(btrim((c_3.custodial_provider_name)::text), ''::text)), b_3.id, b_3.opened_on
  • Join Filter: (cp_3.id = b_3.custodial_provider_id)
31. 0.002 0.252 ↓ 0.0 0 1

Nested Loop (cost=8.47..63.05 rows=1 width=35) (actual time=0.252..0.252 rows=0 loops=1)

  • Output: a_3.id, a_3.account_number, c_3.fund_company, c_3.custodial_provider_name, cp_3.id
  • Join Filter: ((c_3.custodial_provider_name)::text = (cp_3.name)::text)
32. 0.037 0.250 ↓ 0.0 0 1

Hash Join (cost=8.47..61.09 rows=1 width=27) (actual time=0.250..0.250 rows=0 loops=1)

  • Output: a_3.id, a_3.account_number, c_3.fund_company, c_3.custodial_provider_name
  • Hash Cond: ((c_3.where_held_classic_id)::text = (a_3.where_held_classic_id)::text)
33. 0.209 0.209 ↑ 1.2 278 1

Seq Scan on public.provider_where_held_mapping c_3 (cost=0.00..51.41 rows=320 width=14) (actual time=0.005..0.209 rows=278 loops=1)

  • Output: c_3.fund_company, c_3.custodial_provider_name, c_3.where_held_classic_id
  • Filter: ((c_3.is_obsolete = 'N'::bpchar) AND ((c_3.realm)::text = 'cg'::text))
  • Rows Removed by Filter: 1,214
34. 0.001 0.004 ↑ 1.0 1 1

Hash (cost=8.46..8.46 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: a_3.id, a_3.account_number, a_3.where_held_classic_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
35. 0.003 0.003 ↑ 1.0 1 1

Index Scan using accounts_pkey on public.accounts a_3 (cost=0.44..8.46 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: a_3.id, a_3.account_number, a_3.where_held_classic_id
  • Index Cond: (a_3.id = 4,054,877)
36. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.custodial_providers cp_3 (cost=0.00..1.52 rows=35 width=17) (never executed)

  • Output: cp_3.id, cp_3.name, cp_3.priority
  • Filter: ((cp_3.name)::text <> ALL ('{DST,DAZL,DTCC}'::text[]))
37. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.custodial_accounts b_3 (cost=4.64..111.97 rows=27 width=32) (never executed)

  • Output: b_3.id, b_3.number, b_3.opened_on, b_3.custodial_provider_id
  • Recheck Cond: (ltrim((b_3.number)::text, '0'::text) = (a_3.account_number)::text)
38. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on custodial_accounts_no_leading_zero (cost=0.00..4.64 rows=27 width=0) (never executed)

  • Index Cond: (ltrim((b_3.number)::text, '0'::text) = (a_3.account_number)::text)
39. 0.001 0.040 ↓ 0.0 0 1

HashAggregate (cost=140.20..140.22 rows=1 width=51) (actual time=0.040..0.040 rows=0 loops=1)

  • Output: b_4.id, a_4.id, a_4.account_number, b_4.number, (ltrim((b_4.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c_4.fund_company)::text), ''::text), NULLIF(btrim((c_4.custodial_provider_name)::text), ''::text))), b_4.id, b_4.opened_on
  • Group Key: b_4.id, a_4.id, a_4.account_number, b_4.number, ltrim((b_4.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_4.fund_company)::text), ''::text), NULLIF(btrim((c_4.custodial_provider_name)::text), ''::text)), b_4.id, b_4.opened_on
40. 0.000 0.039 ↓ 0.0 0 1

Nested Loop (cost=5.36..140.18 rows=1 width=51) (actual time=0.039..0.039 rows=0 loops=1)

  • Output: b_4.id, a_4.id, a_4.account_number, b_4.number, ltrim((b_4.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_4.fund_company)::text), ''::text), NULLIF(btrim((c_4.custodial_provider_name)::text), ''::text)), b_4.id, b_4.opened_on
  • Join Filter: ((a_4.where_held_classic_id)::text = (c_4.where_held_classic_id)::text)
41. 0.001 0.039 ↓ 0.0 0 1

Nested Loop (cost=5.08..123.52 rows=2 width=59) (actual time=0.039..0.039 rows=0 loops=1)

  • Output: a_4.id, a_4.account_number, a_4.where_held_classic_id, b_4.id, b_4.number, b_4.opened_on, b_4.where_held, cp_4.name
  • Join Filter: (b_4.custodial_provider_id = cp_4.id)
42. 0.003 0.038 ↓ 0.0 0 1

Nested Loop (cost=5.08..120.96 rows=23 width=58) (actual time=0.038..0.038 rows=0 loops=1)

  • Output: a_4.id, a_4.account_number, a_4.where_held_classic_id, b_4.id, b_4.number, b_4.opened_on, b_4.where_held, b_4.custodial_provider_id
43. 0.003 0.003 ↑ 1.0 1 1

Index Scan using accounts_pkey on public.accounts a_4 (cost=0.44..8.46 rows=1 width=22) (actual time=0.002..0.003 rows=1 loops=1)

  • Output: a_4.id, a_4.realm_id, a_4.account_forms_collection_id, a_4.classic_multivalue_position, a_4.where_held_classic_id, a_4.held_where_id, a_4.account_number, a_4.hidden, a_4.dirty, a_4.created_at, a_4.updated_at, a_4.form_type, a_4.acctype, a_4.acctype_other, a_4.advisory_bus, a_4.alt_address_1, a_4.alt_address_2, a_4.alt_city_1, a_4.alt_city_2, a_4.alt_email_address_1, a_4.alt_email_address_2, a_4.alt_first_name_1, a_4.alt_first_name_2, a_4.alt_h_phone_1, a_4.alt_h_phone_2, a_4.alt_last_name_1, a_4.alt_last_name_2, a_4.alt_m_phone_1, a_4.alt_m_phone_2, a_4.alt_no_email_1, a_4.alt_no_email_2, a_4.alt_relationship_1, a_4.alt_relationship_2, a_4.alt_state_1, a_4.alt_state_2, a_4.alt_zip_1, a_4.alt_zip_2, a_4.backup_withholding, a_4.balance, a_4.bal_asof, a_4.benefactor_name, a_4.bene_ira_dob, a_4.bene_ira_dob_rmd, a_4.bene_ira_dod, a_4.bene_ira_spousal, a_4.ben_responsible_individual, a_4.buy_amt, a_4.buy_sec, a_4.b_phone_cont, a_4.cam_type, a_4.ca_state, a_4.ca_term, a_4.ca_type, a_4.certif, a_4.citizen_other_cont, a_4.city_coverdell_cont, a_4.ck_amt, a_4.ck_enc, a_4.comm_sched, a_4.company_owner, a_4.company_phone, a_4.convert_cash, a_4.country_coverdell_cont, a_4.date2_cont, a_4.dc_name, a_4.dep_addr, a_4.dep_amt, a_4.dep_bus_tel_num, a_4.dep_first_name, a_4.dep_last_name, a_4.dep_sec, a_4.dep_soc_sec, a_4.dep_tel_num, a_4.div_opt, a_4.email_cont, a_4.employer_tin, a_4.f13, a_4.fatca_code, a_4.form_print_status, a_4.gov_id_cont, a_4.gov_id_expire_cont, a_4.hsa_account_number, a_4.id_country_cont, a_4.init_tran_buy, a_4.init_tran_dep_cash, a_4.init_tran_dep_stock, a_4.init_tran_none, a_4.init_tran_sell, a_4.init_tran_trans, a_4.invest_link, a_4.invest_time_horizon3, a_4.marital_cont, a_4.mmbal, a_4.mmf, a_4.number_of_participants, a_4.num_signers, a_4.obj0, a_4.obj1, a_4.obj2, a_4.obj3, a_4.obj4, a_4.obj5, a_4.obj6, a_4.obj7, a_4.obj8, a_4.obj9, a_4.ofac_comp_cont, a_4.officer, a_4.open_req_ids, a_4.open_warning, a_4.opt_level, a_4.other_source_of_funds, a_4.per_stirpes, a_4.photo_id_copy_cont, a_4.plan_number, a_4.pp_via, a_4.prim_objective_entity, a_4.prim_objective_raw, a_4.princ_app, a_4.question_1, a_4.question_2, a_4.relationship_cont, a_4.rep, a_4.resident_other_cont, a_4.resp_ind_rule1, a_4.resp_ind_rule2, a_4.resp_ind_rule3, a_4.risk_tolerance, a_4.rollover_days, a_4.rollover_dollars, a_4.rollover_eligible, a_4.rollover_percentage, a_4.roth_type, a_4.rp_relationship, a_4.sec_objective, a_4.sell_amt, a_4.sell_sec, a_4.signer, a_4.simple_date, a_4.source_of_funds, a_4.state_coverdell_cont, a_4.succ_resp_ind_name, a_4.succ_resp_ind_ssn, a_4.succ_resp_ind_tel, a_4.succ_resp_ind_addr, a_4.succ_resp_ind_city, a_4.succ_resp_ind_state, a_4.succ_resp_ind_zip, a_4.swept, a_4.tenants1_name, a_4.tenants1_percent, a_4.tenants2_name, a_4.tenants2_percent, a_4."timestamp", a_4.tin_type_dep, a_4.tin_type_succ_resp, a_4.title, a_4.trade_level, a_4.trade_method, a_4.transfer, a_4.tran_amt, a_4.tran_sec, a_4.us_citizen_cont, a_4.us_resident_cont, a_4.vendor_name, a_4.waive_fee_date, a_4.wire, a_4.zip_coverdell_cont, a_4.open_date, a_4.account_master_id, a_4.complete_at_save, a_4.ca_date, a_4.business_type, a_4.fundvision_pp_via, a_4.fundvision_statements_via, a_4.fundvision_tax_documents_via, a_4.fundvision_proxy_comms_via, a_4.fundvision_prospectuses_via, a_4.consent_id, a_4.participant_investments, a_4.services_ips, a_4.services_liaison, a_4.services_reports, a_4.services_options, a_4.services_education, a_4.services_participant_education, a_4.services_enrollment, a_4.services_plan_fees, a_4.services_investment_monitoring, a_4.acting_capacity, a_4.invest_stocks, a_4.invest_mfs, a_4.invest_va, a_4.invest_fa, a_4.invest_life_ins, a_4.invest_advisory, a_4.invest_other, a_4.invest_other_desc, a_4.firelight_trans_classic_id, a_4.no_ach, a_4.private_banking_or_foreign, a_4.private_banking_or_foreign_information, a_4.investment_strategy_short_term_growth, a_4.investment_strategy_long_term_growth, a_4.investment_strategy_aggressive_growth, a_4.investment_strategy_growth, a_4.investment_strategy_growth_income, a_4.investment_strategy_balanced, a_4.investment_strategy_balanced_income, a_4.funding_source_investment_proceeds, a_4.funding_source_legal_settlement, a_4.funding_source_gift, a_4.funding_source_trust_will, a_4.funding_source_income_from_earnings_savings, a_4.funding_source_pension_ira_retirement, a_4.funding_source_spouse_parent, a_4.funding_source_insurance_proceeds, a_4.funding_source_other, a_4.contact_beneficiary_tuition_plan, a_4.manual_account, a_4.alt_country_1, a_4.alt_country_2
  • Index Cond: (a_4.id = 4,054,877)
44. 0.006 0.032 ↓ 0.0 0 1

Bitmap Heap Scan on public.custodial_accounts b_4 (cost=4.64..112.27 rows=23 width=36) (actual time=0.032..0.032 rows=0 loops=1)

  • Output: b_4.id, b_4.number, b_4.opened_on, b_4.where_held, b_4.custodial_provider_id
  • Recheck Cond: (ltrim((b_4.number)::text, '0'::text) = (a_4.account_number)::text)
  • Filter: (((b_4.where_held)::text <> ALL ('{PMCPM,PMCAR,ALLNZ,ACMAC,FTGFF,AMVAM,JHKJH,MASFM,PMCPM}'::text[])) OR (b_4.where_held IS NULL))
45. 0.026 0.026 ↓ 0.0 0 1

Bitmap Index Scan on custodial_accounts_no_leading_zero (cost=0.00..4.64 rows=27 width=0) (actual time=0.026..0.026 rows=0 loops=1)

  • Index Cond: (ltrim((b_4.number)::text, '0'::text) = (a_4.account_number)::text)
46. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.54 rows=3 width=17) (never executed)

  • Output: cp_4.id, cp_4.name
47. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.custodial_providers cp_4 (cost=0.00..1.52 rows=3 width=17) (never executed)

  • Output: cp_4.id, cp_4.name
  • Filter: ((cp_4.name)::text = ANY ('{DST,DAZL,DTCC}'::text[]))
48. 0.000 0.000 ↓ 0.0 0

Index Scan using provider_where_held_mapping_index_provider_name_fund_company on public.provider_where_held_mapping c_4 (cost=0.28..8.31 rows=1 width=14) (never executed)

  • Output: c_4.fund_company, c_4.custodial_provider_name, c_4.where_held_classic_id
  • Index Cond: (((c_4.custodial_provider_name)::text = (cp_4.name)::text) AND ((c_4.fund_company)::text = (b_4.where_held)::text))
  • Filter: ((c_4.is_obsolete = 'N'::bpchar) AND ((c_4.realm)::text = 'cg'::text))
49. 0.002 0.787 ↓ 0.0 0 1

HashAggregate (cost=125.35..125.38 rows=1 width=51) (actual time=0.787..0.787 rows=0 loops=1)

  • Output: b_5.id, a_5.id, a_5.account_number, b_5.number, (ltrim((b_5.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c_5.fund_company)::text), ''::text), NULLIF(btrim((c_5.custodial_provider_name)::text), ''::text))), b_5.id, b_5.opened_on
  • Group Key: b_5.id, a_5.id, a_5.account_number, b_5.number, ltrim((b_5.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_5.fund_company)::text), ''::text), NULLIF(btrim((c_5.custodial_provider_name)::text), ''::text)), b_5.id, b_5.opened_on
50. 0.001 0.785 ↓ 0.0 0 1

Nested Loop (cost=5.50..125.33 rows=1 width=51) (actual time=0.785..0.785 rows=0 loops=1)

  • Output: b_5.id, a_5.id, a_5.account_number, b_5.number, ltrim((b_5.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_5.fund_company)::text), ''::text), NULLIF(btrim((c_5.custodial_provider_name)::text), ''::text)), b_5.id, b_5.opened_on
  • Join Filter: ((a_5.where_held_classic_id)::text = (c_5.where_held_classic_id)::text)
51. 0.000 0.784 ↓ 0.0 0 1

Nested Loop (cost=5.22..121.11 rows=1 width=59) (actual time=0.784..0.784 rows=0 loops=1)

  • Output: a_5.id, a_5.account_number, a_5.where_held_classic_id, b_5.id, b_5.number, b_5.opened_on, b_5.where_held, cp_5.name
52. 0.002 0.784 ↓ 0.0 0 1

Nested Loop (cost=5.08..120.94 rows=1 width=58) (actual time=0.784..0.784 rows=0 loops=1)

  • Output: a_5.id, a_5.account_number, a_5.where_held_classic_id, b_5.id, b_5.number, b_5.opened_on, b_5.where_held, b_5.custodial_provider_id
53. 0.497 0.497 ↑ 1.0 1 1

Index Scan using accounts_pkey on public.accounts a_5 (cost=0.44..8.46 rows=1 width=22) (actual time=0.496..0.497 rows=1 loops=1)

  • Output: a_5.id, a_5.realm_id, a_5.account_forms_collection_id, a_5.classic_multivalue_position, a_5.where_held_classic_id, a_5.held_where_id, a_5.account_number, a_5.hidden, a_5.dirty, a_5.created_at, a_5.updated_at, a_5.form_type, a_5.acctype, a_5.acctype_other, a_5.advisory_bus, a_5.alt_address_1, a_5.alt_address_2, a_5.alt_city_1, a_5.alt_city_2, a_5.alt_email_address_1, a_5.alt_email_address_2, a_5.alt_first_name_1, a_5.alt_first_name_2, a_5.alt_h_phone_1, a_5.alt_h_phone_2, a_5.alt_last_name_1, a_5.alt_last_name_2, a_5.alt_m_phone_1, a_5.alt_m_phone_2, a_5.alt_no_email_1, a_5.alt_no_email_2, a_5.alt_relationship_1, a_5.alt_relationship_2, a_5.alt_state_1, a_5.alt_state_2, a_5.alt_zip_1, a_5.alt_zip_2, a_5.backup_withholding, a_5.balance, a_5.bal_asof, a_5.benefactor_name, a_5.bene_ira_dob, a_5.bene_ira_dob_rmd, a_5.bene_ira_dod, a_5.bene_ira_spousal, a_5.ben_responsible_individual, a_5.buy_amt, a_5.buy_sec, a_5.b_phone_cont, a_5.cam_type, a_5.ca_state, a_5.ca_term, a_5.ca_type, a_5.certif, a_5.citizen_other_cont, a_5.city_coverdell_cont, a_5.ck_amt, a_5.ck_enc, a_5.comm_sched, a_5.company_owner, a_5.company_phone, a_5.convert_cash, a_5.country_coverdell_cont, a_5.date2_cont, a_5.dc_name, a_5.dep_addr, a_5.dep_amt, a_5.dep_bus_tel_num, a_5.dep_first_name, a_5.dep_last_name, a_5.dep_sec, a_5.dep_soc_sec, a_5.dep_tel_num, a_5.div_opt, a_5.email_cont, a_5.employer_tin, a_5.f13, a_5.fatca_code, a_5.form_print_status, a_5.gov_id_cont, a_5.gov_id_expire_cont, a_5.hsa_account_number, a_5.id_country_cont, a_5.init_tran_buy, a_5.init_tran_dep_cash, a_5.init_tran_dep_stock, a_5.init_tran_none, a_5.init_tran_sell, a_5.init_tran_trans, a_5.invest_link, a_5.invest_time_horizon3, a_5.marital_cont, a_5.mmbal, a_5.mmf, a_5.number_of_participants, a_5.num_signers, a_5.obj0, a_5.obj1, a_5.obj2, a_5.obj3, a_5.obj4, a_5.obj5, a_5.obj6, a_5.obj7, a_5.obj8, a_5.obj9, a_5.ofac_comp_cont, a_5.officer, a_5.open_req_ids, a_5.open_warning, a_5.opt_level, a_5.other_source_of_funds, a_5.per_stirpes, a_5.photo_id_copy_cont, a_5.plan_number, a_5.pp_via, a_5.prim_objective_entity, a_5.prim_objective_raw, a_5.princ_app, a_5.question_1, a_5.question_2, a_5.relationship_cont, a_5.rep, a_5.resident_other_cont, a_5.resp_ind_rule1, a_5.resp_ind_rule2, a_5.resp_ind_rule3, a_5.risk_tolerance, a_5.rollover_days, a_5.rollover_dollars, a_5.rollover_eligible, a_5.rollover_percentage, a_5.roth_type, a_5.rp_relationship, a_5.sec_objective, a_5.sell_amt, a_5.sell_sec, a_5.signer, a_5.simple_date, a_5.source_of_funds, a_5.state_coverdell_cont, a_5.succ_resp_ind_name, a_5.succ_resp_ind_ssn, a_5.succ_resp_ind_tel, a_5.succ_resp_ind_addr, a_5.succ_resp_ind_city, a_5.succ_resp_ind_state, a_5.succ_resp_ind_zip, a_5.swept, a_5.tenants1_name, a_5.tenants1_percent, a_5.tenants2_name, a_5.tenants2_percent, a_5."timestamp", a_5.tin_type_dep, a_5.tin_type_succ_resp, a_5.title, a_5.trade_level, a_5.trade_method, a_5.transfer, a_5.tran_amt, a_5.tran_sec, a_5.us_citizen_cont, a_5.us_resident_cont, a_5.vendor_name, a_5.waive_fee_date, a_5.wire, a_5.zip_coverdell_cont, a_5.open_date, a_5.account_master_id, a_5.complete_at_save, a_5.ca_date, a_5.business_type, a_5.fundvision_pp_via, a_5.fundvision_statements_via, a_5.fundvision_tax_documents_via, a_5.fundvision_proxy_comms_via, a_5.fundvision_prospectuses_via, a_5.consent_id, a_5.participant_investments, a_5.services_ips, a_5.services_liaison, a_5.services_reports, a_5.services_options, a_5.services_education, a_5.services_participant_education, a_5.services_enrollment, a_5.services_plan_fees, a_5.services_investment_monitoring, a_5.acting_capacity, a_5.invest_stocks, a_5.invest_mfs, a_5.invest_va, a_5.invest_fa, a_5.invest_life_ins, a_5.invest_advisory, a_5.invest_other, a_5.invest_other_desc, a_5.firelight_trans_classic_id, a_5.no_ach, a_5.private_banking_or_foreign, a_5.private_banking_or_foreign_information, a_5.investment_strategy_short_term_growth, a_5.investment_strategy_long_term_growth, a_5.investment_strategy_aggressive_growth, a_5.investment_strategy_growth, a_5.investment_strategy_growth_income, a_5.investment_strategy_balanced, a_5.investment_strategy_balanced_income, a_5.funding_source_investment_proceeds, a_5.funding_source_legal_settlement, a_5.funding_source_gift, a_5.funding_source_trust_will, a_5.funding_source_income_from_earnings_savings, a_5.funding_source_pension_ira_retirement, a_5.funding_source_spouse_parent, a_5.funding_source_insurance_proceeds, a_5.funding_source_other, a_5.contact_beneficiary_tuition_plan, a_5.manual_account, a_5.alt_country_1, a_5.alt_country_2
  • Index Cond: (a_5.id = 4,054,877)
  • Filter: (a_5.account_number IS NOT NULL)
54. 0.006 0.285 ↓ 0.0 0 1

Bitmap Heap Scan on public.custodial_accounts b_5 (cost=4.64..112.47 rows=1 width=42) (actual time=0.285..0.285 rows=0 loops=1)

  • Output: b_5.id, b_5.number, b_5.opened_on, b_5.where_held, b_5.cusip, b_5.custodial_provider_id
  • Recheck Cond: (ltrim((b_5.number)::text, '0'::text) = "left"((a_5.account_number)::text, (-9)))
  • Filter: (("right"((a_5.account_number)::text, 9) = (b_5.cusip)::text) AND ((b_5.where_held)::text = ANY ('{PMCPM,PMCAR,ALLNZ,ACMAC,FTGFF,AMVAM,JHKJH,MASFM,PMCPM}'::text[])))
55. 0.279 0.279 ↓ 0.0 0 1

Bitmap Index Scan on custodial_accounts_no_leading_zero (cost=0.00..4.64 rows=27 width=0) (actual time=0.279..0.279 rows=0 loops=1)

  • Index Cond: (ltrim((b_5.number)::text, '0'::text) = "left"((a_5.account_number)::text, (-9)))
56. 0.000 0.000 ↓ 0.0 0

Index Scan using custodial_providers_pkey on public.custodial_providers cp_5 (cost=0.14..0.16 rows=1 width=17) (never executed)

  • Output: cp_5.id, cp_5.name, cp_5.priority
  • Index Cond: (cp_5.id = b_5.custodial_provider_id)
57. 0.000 0.000 ↓ 0.0 0

Index Scan using provider_where_held_mapping_index_provider_name_fund_company on public.provider_where_held_mapping c_5 (cost=0.28..4.20 rows=1 width=14) (never executed)

  • Output: c_5.fund_company, c_5.custodial_provider_name, c_5.where_held_classic_id
  • Index Cond: (((c_5.custodial_provider_name)::text = (cp_5.name)::text) AND ((c_5.fund_company)::text = (b_5.where_held)::text))
  • Filter: ((c_5.is_obsolete = 'N'::bpchar) AND ((c_5.realm)::text = 'cg'::text))
58. 0.001 0.063 ↓ 0.0 0 1

HashAggregate (cost=31.61..31.63 rows=1 width=51) (actual time=0.063..0.063 rows=0 loops=1)

  • Output: b_6.id, a_6.id, a_6.account_number, b_6.number, (ltrim((b_6.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c_6.fund_company)::text), ''::text), NULLIF(btrim((c_6.custodial_provider_name)::text), ''::text))), b_6.id, b_6.opened_on
  • Group Key: b_6.id, a_6.id, a_6.account_number, b_6.number, ltrim((b_6.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_6.fund_company)::text), ''::text), NULLIF(btrim((c_6.custodial_provider_name)::text), ''::text)), b_6.id, b_6.opened_on
59. 0.002 0.062 ↓ 0.0 0 1

Nested Loop (cost=5.30..31.59 rows=1 width=51) (actual time=0.062..0.062 rows=0 loops=1)

  • Output: b_6.id, a_6.id, a_6.account_number, b_6.number, ltrim((b_6.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_6.fund_company)::text), ''::text), NULLIF(btrim((c_6.custodial_provider_name)::text), ''::text)), b_6.id, b_6.opened_on
60. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on public.custodial_providers cp_6 (cost=0.00..1.48 rows=1 width=17) (actual time=0.010..0.011 rows=1 loops=1)

  • Output: cp_6.id, cp_6.name, cp_6.priority
  • Filter: ((cp_6.name)::text = 'Pershing'::text)
  • Rows Removed by Filter: 39
61. 0.000 0.049 ↓ 0.0 0 1

Nested Loop (cost=5.30..30.09 rows=1 width=59) (actual time=0.049..0.049 rows=0 loops=1)

  • Output: a_6.id, a_6.account_number, b_6.id, b_6.number, b_6.opened_on, b_6.custodial_provider_id, c_6.fund_company, c_6.custodial_provider_name
62. 0.006 0.049 ↓ 0.0 0 1

Nested Loop (cost=4.74..21.50 rows=1 width=27) (actual time=0.049..0.049 rows=0 loops=1)

  • Output: a_6.id, a_6.account_number, c_6.fund_company, c_6.custodial_provider_name
  • Join Filter: ((a_6.where_held_classic_id)::text = (c_6.where_held_classic_id)::text)
  • Rows Removed by Join Filter: 1
63. 0.004 0.004 ↑ 1.0 1 1

Index Scan using accounts_pkey on public.accounts a_6 (cost=0.44..8.46 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: a_6.id, a_6.realm_id, a_6.account_forms_collection_id, a_6.classic_multivalue_position, a_6.where_held_classic_id, a_6.held_where_id, a_6.account_number, a_6.hidden, a_6.dirty, a_6.created_at, a_6.updated_at, a_6.form_type, a_6.acctype, a_6.acctype_other, a_6.advisory_bus, a_6.alt_address_1, a_6.alt_address_2, a_6.alt_city_1, a_6.alt_city_2, a_6.alt_email_address_1, a_6.alt_email_address_2, a_6.alt_first_name_1, a_6.alt_first_name_2, a_6.alt_h_phone_1, a_6.alt_h_phone_2, a_6.alt_last_name_1, a_6.alt_last_name_2, a_6.alt_m_phone_1, a_6.alt_m_phone_2, a_6.alt_no_email_1, a_6.alt_no_email_2, a_6.alt_relationship_1, a_6.alt_relationship_2, a_6.alt_state_1, a_6.alt_state_2, a_6.alt_zip_1, a_6.alt_zip_2, a_6.backup_withholding, a_6.balance, a_6.bal_asof, a_6.benefactor_name, a_6.bene_ira_dob, a_6.bene_ira_dob_rmd, a_6.bene_ira_dod, a_6.bene_ira_spousal, a_6.ben_responsible_individual, a_6.buy_amt, a_6.buy_sec, a_6.b_phone_cont, a_6.cam_type, a_6.ca_state, a_6.ca_term, a_6.ca_type, a_6.certif, a_6.citizen_other_cont, a_6.city_coverdell_cont, a_6.ck_amt, a_6.ck_enc, a_6.comm_sched, a_6.company_owner, a_6.company_phone, a_6.convert_cash, a_6.country_coverdell_cont, a_6.date2_cont, a_6.dc_name, a_6.dep_addr, a_6.dep_amt, a_6.dep_bus_tel_num, a_6.dep_first_name, a_6.dep_last_name, a_6.dep_sec, a_6.dep_soc_sec, a_6.dep_tel_num, a_6.div_opt, a_6.email_cont, a_6.employer_tin, a_6.f13, a_6.fatca_code, a_6.form_print_status, a_6.gov_id_cont, a_6.gov_id_expire_cont, a_6.hsa_account_number, a_6.id_country_cont, a_6.init_tran_buy, a_6.init_tran_dep_cash, a_6.init_tran_dep_stock, a_6.init_tran_none, a_6.init_tran_sell, a_6.init_tran_trans, a_6.invest_link, a_6.invest_time_horizon3, a_6.marital_cont, a_6.mmbal, a_6.mmf, a_6.number_of_participants, a_6.num_signers, a_6.obj0, a_6.obj1, a_6.obj2, a_6.obj3, a_6.obj4, a_6.obj5, a_6.obj6, a_6.obj7, a_6.obj8, a_6.obj9, a_6.ofac_comp_cont, a_6.officer, a_6.open_req_ids, a_6.open_warning, a_6.opt_level, a_6.other_source_of_funds, a_6.per_stirpes, a_6.photo_id_copy_cont, a_6.plan_number, a_6.pp_via, a_6.prim_objective_entity, a_6.prim_objective_raw, a_6.princ_app, a_6.question_1, a_6.question_2, a_6.relationship_cont, a_6.rep, a_6.resident_other_cont, a_6.resp_ind_rule1, a_6.resp_ind_rule2, a_6.resp_ind_rule3, a_6.risk_tolerance, a_6.rollover_days, a_6.rollover_dollars, a_6.rollover_eligible, a_6.rollover_percentage, a_6.roth_type, a_6.rp_relationship, a_6.sec_objective, a_6.sell_amt, a_6.sell_sec, a_6.signer, a_6.simple_date, a_6.source_of_funds, a_6.state_coverdell_cont, a_6.succ_resp_ind_name, a_6.succ_resp_ind_ssn, a_6.succ_resp_ind_tel, a_6.succ_resp_ind_addr, a_6.succ_resp_ind_city, a_6.succ_resp_ind_state, a_6.succ_resp_ind_zip, a_6.swept, a_6.tenants1_name, a_6.tenants1_percent, a_6.tenants2_name, a_6.tenants2_percent, a_6."timestamp", a_6.tin_type_dep, a_6.tin_type_succ_resp, a_6.title, a_6.trade_level, a_6.trade_method, a_6.transfer, a_6.tran_amt, a_6.tran_sec, a_6.us_citizen_cont, a_6.us_resident_cont, a_6.vendor_name, a_6.waive_fee_date, a_6.wire, a_6.zip_coverdell_cont, a_6.open_date, a_6.account_master_id, a_6.complete_at_save, a_6.ca_date, a_6.business_type, a_6.fundvision_pp_via, a_6.fundvision_statements_via, a_6.fundvision_tax_documents_via, a_6.fundvision_proxy_comms_via, a_6.fundvision_prospectuses_via, a_6.consent_id, a_6.participant_investments, a_6.services_ips, a_6.services_liaison, a_6.services_reports, a_6.services_options, a_6.services_education, a_6.services_participant_education, a_6.services_enrollment, a_6.services_plan_fees, a_6.services_investment_monitoring, a_6.acting_capacity, a_6.invest_stocks, a_6.invest_mfs, a_6.invest_va, a_6.invest_fa, a_6.invest_life_ins, a_6.invest_advisory, a_6.invest_other, a_6.invest_other_desc, a_6.firelight_trans_classic_id, a_6.no_ach, a_6.private_banking_or_foreign, a_6.private_banking_or_foreign_information, a_6.investment_strategy_short_term_growth, a_6.investment_strategy_long_term_growth, a_6.investment_strategy_aggressive_growth, a_6.investment_strategy_growth, a_6.investment_strategy_growth_income, a_6.investment_strategy_balanced, a_6.investment_strategy_balanced_income, a_6.funding_source_investment_proceeds, a_6.funding_source_legal_settlement, a_6.funding_source_gift, a_6.funding_source_trust_will, a_6.funding_source_income_from_earnings_savings, a_6.funding_source_pension_ira_retirement, a_6.funding_source_spouse_parent, a_6.funding_source_insurance_proceeds, a_6.funding_source_other, a_6.contact_beneficiary_tuition_plan, a_6.manual_account, a_6.alt_country_1, a_6.alt_country_2
  • Index Cond: (a_6.id = 4,054,877)
64. 0.016 0.039 ↑ 1.0 1 1

Bitmap Heap Scan on public.provider_where_held_mapping c_6 (cost=4.30..13.03 rows=1 width=14) (actual time=0.039..0.039 rows=1 loops=1)

  • Output: c_6.fund_company, c_6.custodial_provider_name, c_6.where_held_classic_id
  • Recheck Cond: ((c_6.custodial_provider_name)::text = 'Pershing'::text)
  • Filter: ((c_6.is_obsolete = 'N'::bpchar) AND ((c_6.realm)::text = 'cg'::text))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=2
65. 0.023 0.023 ↓ 1.3 4 1

Bitmap Index Scan on provider_where_held_mapping_index_provider_name_fund_company (cost=0.00..4.30 rows=3 width=0) (actual time=0.023..0.023 rows=4 loops=1)

  • Index Cond: ((c_6.custodial_provider_name)::text = 'Pershing'::text)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using index_cust_accounts_on_cust_account_custodial_provider_id on public.custodial_accounts b_6 (cost=0.56..8.58 rows=1 width=32) (never executed)

  • Output: b_6.id, b_6.number, b_6.opened_on, b_6.custodial_provider_id
  • Index Cond: (((b_6.number)::text = (a_6.account_number)::text) AND (b_6.custodial_provider_id = cp_6.id))
67. 0.001 603.155 ↓ 0.0 0 1

HashAggregate (cost=1,810.14..1,810.16 rows=1 width=51) (actual time=603.155..603.155 rows=0 loops=1)

  • Output: b_7.id, a_7.id, a_7.account_number, b_7.number, (ltrim((b_7.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c_7.fund_company)::text), ''::text), NULLIF(btrim((c_7.custodial_provider_name)::text), ''::text))), b_7.id, b_7.opened_on
  • Group Key: b_7.id, a_7.id, a_7.account_number, b_7.number, ltrim((b_7.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_7.fund_company)::text), ''::text), NULLIF(btrim((c_7.custodial_provider_name)::text), ''::text)), b_7.id, b_7.opened_on
68. 0.003 603.154 ↓ 0.0 0 1

Nested Loop (cost=33.74..1,810.12 rows=1 width=51) (actual time=603.154..603.154 rows=0 loops=1)

  • Output: b_7.id, a_7.id, a_7.account_number, b_7.number, ltrim((b_7.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_7.fund_company)::text), ''::text), NULLIF(btrim((c_7.custodial_provider_name)::text), ''::text)), b_7.id, b_7.opened_on
  • Join Filter: ((a_7.account_number)::text = replace((b_7.number)::text, '-'::text, ''::text))
69. 0.010 0.392 ↑ 1.0 1 1

Nested Loop (cost=8.47..66.96 rows=1 width=35) (actual time=0.344..0.392 rows=1 loops=1)

  • Output: a_7.id, a_7.account_number, c_7.fund_company, c_7.custodial_provider_name, cp_7.id
  • Join Filter: ((c_7.custodial_provider_name)::text = (cp_7.name)::text)
  • Rows Removed by Join Filter: 39
70. 0.073 0.376 ↑ 1.0 1 1

Hash Join (cost=8.47..65.11 rows=1 width=27) (actual time=0.334..0.376 rows=1 loops=1)

  • Output: a_7.id, a_7.account_number, c_7.fund_company, c_7.custodial_provider_name
  • Hash Cond: ((c_7.where_held_classic_id)::text = (a_7.where_held_classic_id)::text)
71. 0.298 0.298 ↑ 1.4 1,024 1

Seq Scan on public.provider_where_held_mapping c_7 (cost=0.00..51.41 rows=1,391 width=14) (actual time=0.005..0.298 rows=1,024 loops=1)

  • Output: c_7.fund_company, c_7.custodial_provider_name, c_7.where_held_classic_id
  • Filter: (((c_7.realm)::text <> 'cg'::text) AND (c_7.is_obsolete = 'N'::bpchar))
  • Rows Removed by Filter: 468
72. 0.002 0.005 ↑ 1.0 1 1

Hash (cost=8.46..8.46 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=1)

  • Output: a_7.id, a_7.account_number, a_7.where_held_classic_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
73. 0.003 0.003 ↑ 1.0 1 1

Index Scan using accounts_pkey on public.accounts a_7 (cost=0.44..8.46 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: a_7.id, a_7.account_number, a_7.where_held_classic_id
  • Index Cond: (a_7.id = 4,054,877)
74. 0.006 0.006 ↓ 1.1 40 1

Seq Scan on public.custodial_providers cp_7 (cost=0.00..1.38 rows=38 width=17) (actual time=0.002..0.006 rows=40 loops=1)

  • Output: cp_7.id, cp_7.name, cp_7.priority
75. 551.506 602.759 ↓ 0.0 0 1

Bitmap Heap Scan on public.custodial_accounts b_7 (cost=25.27..1,743.07 rows=5 width=36) (actual time=602.759..602.759 rows=0 loops=1)

  • Output: b_7.id, b_7.number, b_7.opened_on, b_7.where_held, b_7.custodial_provider_id
  • Recheck Cond: ((b_7.custodial_provider_id = cp_7.id) AND ((b_7.where_held)::text = (c_7.fund_company)::text))
  • Filter: (((b_7.number)::text ~~ '%-%'::text) AND ((b_7.where_held)::text <> ALL ('{DSTLA,MASFM,DLWDG}'::text[])))
  • Rows Removed by Filter: 130,253
  • Heap Blocks: exact=13,829
76. 51.253 51.253 ↓ 276.5 130,253 1

Bitmap Index Scan on custodial_accounts_index_provider_where_held (cost=0.00..25.27 rows=471 width=0) (actual time=51.253..51.253 rows=130,253 loops=1)

  • Index Cond: ((b_7.custodial_provider_id = cp_7.id) AND ((b_7.where_held)::text = (c_7.fund_company)::text))
77. 0.002 480.579 ↓ 0.0 0 1

HashAggregate (cost=1,825.08..1,825.10 rows=1 width=51) (actual time=480.579..480.579 rows=0 loops=1)

  • Output: b_8.id, a_8.id, a_8.account_number, b_8.number, (ltrim((b_8.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c_8.fund_company)::text), ''::text), NULLIF(btrim((c_8.custodial_provider_name)::text), ''::text))), b_8.id, b_8.opened_on
  • Group Key: b_8.id, a_8.id, a_8.account_number, b_8.number, ltrim((b_8.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_8.fund_company)::text), ''::text), NULLIF(btrim((c_8.custodial_provider_name)::text), ''::text)), b_8.id, b_8.opened_on
78. 28.631 480.577 ↓ 0.0 0 1

Nested Loop (cost=25.82..1,825.06 rows=1 width=51) (actual time=480.577..480.577 rows=0 loops=1)

  • Output: b_8.id, a_8.id, a_8.account_number, b_8.number, ltrim((b_8.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_8.fund_company)::text), ''::text), NULLIF(btrim((c_8.custodial_provider_name)::text), ''::text)), b_8.id, b_8.opened_on
  • Join Filter: (((c_8.where_held_classic_id)::text = (a_8.where_held_classic_id)::text) AND ((b_8.fund_code)::text = "left"((a_8.account_number)::text, (-11))) AND (replace((b_8.number)::text, 'CFS'::text, ''::text) = "right"(replace((a_8.account_number)::text, 'CFS'::text, ''::text), 11)))
  • Rows Removed by Join Filter: 179,397
79. 0.019 0.019 ↑ 1.0 1 1

Index Scan using accounts_pkey on public.accounts a_8 (cost=0.44..8.46 rows=1 width=22) (actual time=0.016..0.019 rows=1 loops=1)

  • Output: a_8.id, a_8.realm_id, a_8.account_forms_collection_id, a_8.classic_multivalue_position, a_8.where_held_classic_id, a_8.held_where_id, a_8.account_number, a_8.hidden, a_8.dirty, a_8.created_at, a_8.updated_at, a_8.form_type, a_8.acctype, a_8.acctype_other, a_8.advisory_bus, a_8.alt_address_1, a_8.alt_address_2, a_8.alt_city_1, a_8.alt_city_2, a_8.alt_email_address_1, a_8.alt_email_address_2, a_8.alt_first_name_1, a_8.alt_first_name_2, a_8.alt_h_phone_1, a_8.alt_h_phone_2, a_8.alt_last_name_1, a_8.alt_last_name_2, a_8.alt_m_phone_1, a_8.alt_m_phone_2, a_8.alt_no_email_1, a_8.alt_no_email_2, a_8.alt_relationship_1, a_8.alt_relationship_2, a_8.alt_state_1, a_8.alt_state_2, a_8.alt_zip_1, a_8.alt_zip_2, a_8.backup_withholding, a_8.balance, a_8.bal_asof, a_8.benefactor_name, a_8.bene_ira_dob, a_8.bene_ira_dob_rmd, a_8.bene_ira_dod, a_8.bene_ira_spousal, a_8.ben_responsible_individual, a_8.buy_amt, a_8.buy_sec, a_8.b_phone_cont, a_8.cam_type, a_8.ca_state, a_8.ca_term, a_8.ca_type, a_8.certif, a_8.citizen_other_cont, a_8.city_coverdell_cont, a_8.ck_amt, a_8.ck_enc, a_8.comm_sched, a_8.company_owner, a_8.company_phone, a_8.convert_cash, a_8.country_coverdell_cont, a_8.date2_cont, a_8.dc_name, a_8.dep_addr, a_8.dep_amt, a_8.dep_bus_tel_num, a_8.dep_first_name, a_8.dep_last_name, a_8.dep_sec, a_8.dep_soc_sec, a_8.dep_tel_num, a_8.div_opt, a_8.email_cont, a_8.employer_tin, a_8.f13, a_8.fatca_code, a_8.form_print_status, a_8.gov_id_cont, a_8.gov_id_expire_cont, a_8.hsa_account_number, a_8.id_country_cont, a_8.init_tran_buy, a_8.init_tran_dep_cash, a_8.init_tran_dep_stock, a_8.init_tran_none, a_8.init_tran_sell, a_8.init_tran_trans, a_8.invest_link, a_8.invest_time_horizon3, a_8.marital_cont, a_8.mmbal, a_8.mmf, a_8.number_of_participants, a_8.num_signers, a_8.obj0, a_8.obj1, a_8.obj2, a_8.obj3, a_8.obj4, a_8.obj5, a_8.obj6, a_8.obj7, a_8.obj8, a_8.obj9, a_8.ofac_comp_cont, a_8.officer, a_8.open_req_ids, a_8.open_warning, a_8.opt_level, a_8.other_source_of_funds, a_8.per_stirpes, a_8.photo_id_copy_cont, a_8.plan_number, a_8.pp_via, a_8.prim_objective_entity, a_8.prim_objective_raw, a_8.princ_app, a_8.question_1, a_8.question_2, a_8.relationship_cont, a_8.rep, a_8.resident_other_cont, a_8.resp_ind_rule1, a_8.resp_ind_rule2, a_8.resp_ind_rule3, a_8.risk_tolerance, a_8.rollover_days, a_8.rollover_dollars, a_8.rollover_eligible, a_8.rollover_percentage, a_8.roth_type, a_8.rp_relationship, a_8.sec_objective, a_8.sell_amt, a_8.sell_sec, a_8.signer, a_8.simple_date, a_8.source_of_funds, a_8.state_coverdell_cont, a_8.succ_resp_ind_name, a_8.succ_resp_ind_ssn, a_8.succ_resp_ind_tel, a_8.succ_resp_ind_addr, a_8.succ_resp_ind_city, a_8.succ_resp_ind_state, a_8.succ_resp_ind_zip, a_8.swept, a_8.tenants1_name, a_8.tenants1_percent, a_8.tenants2_name, a_8.tenants2_percent, a_8."timestamp", a_8.tin_type_dep, a_8.tin_type_succ_resp, a_8.title, a_8.trade_level, a_8.trade_method, a_8.transfer, a_8.tran_amt, a_8.tran_sec, a_8.us_citizen_cont, a_8.us_resident_cont, a_8.vendor_name, a_8.waive_fee_date, a_8.wire, a_8.zip_coverdell_cont, a_8.open_date, a_8.account_master_id, a_8.complete_at_save, a_8.ca_date, a_8.business_type, a_8.fundvision_pp_via, a_8.fundvision_statements_via, a_8.fundvision_tax_documents_via, a_8.fundvision_proxy_comms_via, a_8.fundvision_prospectuses_via, a_8.consent_id, a_8.participant_investments, a_8.services_ips, a_8.services_liaison, a_8.services_reports, a_8.services_options, a_8.services_education, a_8.services_participant_education, a_8.services_enrollment, a_8.services_plan_fees, a_8.services_investment_monitoring, a_8.acting_capacity, a_8.invest_stocks, a_8.invest_mfs, a_8.invest_va, a_8.invest_fa, a_8.invest_life_ins, a_8.invest_advisory, a_8.invest_other, a_8.invest_other_desc, a_8.firelight_trans_classic_id, a_8.no_ach, a_8.private_banking_or_foreign, a_8.private_banking_or_foreign_information, a_8.investment_strategy_short_term_growth, a_8.investment_strategy_long_term_growth, a_8.investment_strategy_aggressive_growth, a_8.investment_strategy_growth, a_8.investment_strategy_growth_income, a_8.investment_strategy_balanced, a_8.investment_strategy_balanced_income, a_8.funding_source_investment_proceeds, a_8.funding_source_legal_settlement, a_8.funding_source_gift, a_8.funding_source_trust_will, a_8.funding_source_income_from_earnings_savings, a_8.funding_source_pension_ira_retirement, a_8.funding_source_spouse_parent, a_8.funding_source_insurance_proceeds, a_8.funding_source_other, a_8.contact_beneficiary_tuition_plan, a_8.manual_account, a_8.alt_country_1, a_8.alt_country_2
  • Index Cond: (a_8.id = 4,054,877)
80. 24.433 451.927 ↓ 1,348.8 179,397 1

Nested Loop (cost=25.39..1,812.93 rows=133 width=41) (actual time=13.966..451.927 rows=179,397 loops=1)

  • Output: b_8.id, b_8.number, b_8.opened_on, b_8.fund_code, c_8.fund_company, c_8.custodial_provider_name, c_8.where_held_classic_id
81. 0.031 0.405 ↓ 3.0 3 1

Nested Loop (cost=0.00..67.98 rows=1 width=22) (actual time=0.290..0.405 rows=3 loops=1)

  • Output: c_8.fund_company, c_8.custodial_provider_name, c_8.where_held_classic_id, cp_8.id
  • Join Filter: ((c_8.custodial_provider_name)::text = (cp_8.name)::text)
  • Rows Removed by Join Filter: 117
82. 0.359 0.359 ↓ 3.0 3 1

Seq Scan on public.provider_where_held_mapping c_8 (cost=0.00..66.12 rows=1 width=14) (actual time=0.278..0.359 rows=3 loops=1)

  • Output: c_8.fund_company, c_8.custodial_provider_name, c_8.where_held_classic_id
  • Filter: ((c_8.is_obsolete = 'N'::bpchar) AND ((((c_8.realm)::text = 'cfs'::text) AND ((c_8.fund_company)::text = ANY ('{DLWDG,DSTLA}'::text[]))) OR (((c_8.realm)::text = 'spf'::text) AND ((c_8.fund_company)::text = ANY ('{DSTLA,MASFM}'::text[])))))
  • Rows Removed by Filter: 1,489
83. 0.015 0.015 ↓ 1.1 40 3

Seq Scan on public.custodial_providers cp_8 (cost=0.00..1.38 rows=38 width=17) (actual time=0.002..0.005 rows=40 loops=3)

  • Output: cp_8.id, cp_8.name, cp_8.priority
84. 385.344 427.089 ↓ 127.0 59,799 3

Bitmap Heap Scan on public.custodial_accounts b_8 (cost=25.39..1,740.24 rows=471 width=39) (actual time=15.478..142.363 rows=59,799 loops=3)

  • Output: b_8.id, b_8.number, b_8.opened_on, b_8.where_held, b_8.custodial_provider_id, b_8.fund_code
  • Recheck Cond: ((b_8.custodial_provider_id = cp_8.id) AND ((b_8.where_held)::text = (c_8.fund_company)::text))
  • Heap Blocks: exact=21,747
85. 41.745 41.745 ↓ 127.0 59,800 3

Bitmap Index Scan on custodial_accounts_index_provider_where_held (cost=0.00..25.27 rows=471 width=0) (actual time=13.915..13.915 rows=59,800 loops=3)

  • Index Cond: ((b_8.custodial_provider_id = cp_8.id) AND ((b_8.where_held)::text = (c_8.fund_company)::text))
86. 0.002 0.258 ↓ 0.0 0 1

HashAggregate (cost=75.28..75.30 rows=1 width=51) (actual time=0.258..0.258 rows=0 loops=1)

  • Output: b_9.id, a_9.id, a_9.account_number, b_9.number, (ltrim((b_9.number)::text, '0'::text)), (COALESCE(NULLIF(btrim((c_9.fund_company)::text), ''::text), NULLIF(btrim((c_9.custodial_provider_name)::text), ''::text))), b_9.id, b_9.opened_on
  • Group Key: b_9.id, a_9.id, a_9.account_number, b_9.number, ltrim((b_9.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_9.fund_company)::text), ''::text), NULLIF(btrim((c_9.custodial_provider_name)::text), ''::text)), b_9.id, b_9.opened_on
87. 0.000 0.256 ↓ 0.0 0 1

Nested Loop (cost=1.00..75.26 rows=1 width=51) (actual time=0.256..0.256 rows=0 loops=1)

  • Output: b_9.id, a_9.id, a_9.account_number, b_9.number, ltrim((b_9.number)::text, '0'::text), COALESCE(NULLIF(btrim((c_9.fund_company)::text), ''::text), NULLIF(btrim((c_9.custodial_provider_name)::text), ''::text)), b_9.id, b_9.opened_on
88. 0.001 0.256 ↓ 0.0 0 1

Nested Loop (cost=0.44..66.64 rows=1 width=35) (actual time=0.256..0.256 rows=0 loops=1)

  • Output: a_9.id, a_9.account_number, c_9.fund_company, c_9.custodial_provider_name, cp_9.id
  • Join Filter: ((c_9.custodial_provider_name)::text = (cp_9.name)::text)
89. 0.008 0.255 ↓ 0.0 0 1

Nested Loop (cost=0.44..64.79 rows=1 width=27) (actual time=0.255..0.255 rows=0 loops=1)

  • Output: a_9.id, a_9.account_number, c_9.fund_company, c_9.custodial_provider_name
  • Join Filter: ((a_9.where_held_classic_id)::text = (c_9.where_held_classic_id)::text)
  • Rows Removed by Join Filter: 2
90. 0.013 0.013 ↑ 1.0 1 1

Index Scan using accounts_pkey on public.accounts a_9 (cost=0.44..8.46 rows=1 width=22) (actual time=0.013..0.013 rows=1 loops=1)

  • Output: a_9.id, a_9.realm_id, a_9.account_forms_collection_id, a_9.classic_multivalue_position, a_9.where_held_classic_id, a_9.held_where_id, a_9.account_number, a_9.hidden, a_9.dirty, a_9.created_at, a_9.updated_at, a_9.form_type, a_9.acctype, a_9.acctype_other, a_9.advisory_bus, a_9.alt_address_1, a_9.alt_address_2, a_9.alt_city_1, a_9.alt_city_2, a_9.alt_email_address_1, a_9.alt_email_address_2, a_9.alt_first_name_1, a_9.alt_first_name_2, a_9.alt_h_phone_1, a_9.alt_h_phone_2, a_9.alt_last_name_1, a_9.alt_last_name_2, a_9.alt_m_phone_1, a_9.alt_m_phone_2, a_9.alt_no_email_1, a_9.alt_no_email_2, a_9.alt_relationship_1, a_9.alt_relationship_2, a_9.alt_state_1, a_9.alt_state_2, a_9.alt_zip_1, a_9.alt_zip_2, a_9.backup_withholding, a_9.balance, a_9.bal_asof, a_9.benefactor_name, a_9.bene_ira_dob, a_9.bene_ira_dob_rmd, a_9.bene_ira_dod, a_9.bene_ira_spousal, a_9.ben_responsible_individual, a_9.buy_amt, a_9.buy_sec, a_9.b_phone_cont, a_9.cam_type, a_9.ca_state, a_9.ca_term, a_9.ca_type, a_9.certif, a_9.citizen_other_cont, a_9.city_coverdell_cont, a_9.ck_amt, a_9.ck_enc, a_9.comm_sched, a_9.company_owner, a_9.company_phone, a_9.convert_cash, a_9.country_coverdell_cont, a_9.date2_cont, a_9.dc_name, a_9.dep_addr, a_9.dep_amt, a_9.dep_bus_tel_num, a_9.dep_first_name, a_9.dep_last_name, a_9.dep_sec, a_9.dep_soc_sec, a_9.dep_tel_num, a_9.div_opt, a_9.email_cont, a_9.employer_tin, a_9.f13, a_9.fatca_code, a_9.form_print_status, a_9.gov_id_cont, a_9.gov_id_expire_cont, a_9.hsa_account_number, a_9.id_country_cont, a_9.init_tran_buy, a_9.init_tran_dep_cash, a_9.init_tran_dep_stock, a_9.init_tran_none, a_9.init_tran_sell, a_9.init_tran_trans, a_9.invest_link, a_9.invest_time_horizon3, a_9.marital_cont, a_9.mmbal, a_9.mmf, a_9.number_of_participants, a_9.num_signers, a_9.obj0, a_9.obj1, a_9.obj2, a_9.obj3, a_9.obj4, a_9.obj5, a_9.obj6, a_9.obj7, a_9.obj8, a_9.obj9, a_9.ofac_comp_cont, a_9.officer, a_9.open_req_ids, a_9.open_warning, a_9.opt_level, a_9.other_source_of_funds, a_9.per_stirpes, a_9.photo_id_copy_cont, a_9.plan_number, a_9.pp_via, a_9.prim_objective_entity, a_9.prim_objective_raw, a_9.princ_app, a_9.question_1, a_9.question_2, a_9.relationship_cont, a_9.rep, a_9.resident_other_cont, a_9.resp_ind_rule1, a_9.resp_ind_rule2, a_9.resp_ind_rule3, a_9.risk_tolerance, a_9.rollover_days, a_9.rollover_dollars, a_9.rollover_eligible, a_9.rollover_percentage, a_9.roth_type, a_9.rp_relationship, a_9.sec_objective, a_9.sell_amt, a_9.sell_sec, a_9.signer, a_9.simple_date, a_9.source_of_funds, a_9.state_coverdell_cont, a_9.succ_resp_ind_name, a_9.succ_resp_ind_ssn, a_9.succ_resp_ind_tel, a_9.succ_resp_ind_addr, a_9.succ_resp_ind_city, a_9.succ_resp_ind_state, a_9.succ_resp_ind_zip, a_9.swept, a_9.tenants1_name, a_9.tenants1_percent, a_9.tenants2_name, a_9.tenants2_percent, a_9."timestamp", a_9.tin_type_dep, a_9.tin_type_succ_resp, a_9.title, a_9.trade_level, a_9.trade_method, a_9.transfer, a_9.tran_amt, a_9.tran_sec, a_9.us_citizen_cont, a_9.us_resident_cont, a_9.vendor_name, a_9.waive_fee_date, a_9.wire, a_9.zip_coverdell_cont, a_9.open_date, a_9.account_master_id, a_9.complete_at_save, a_9.ca_date, a_9.business_type, a_9.fundvision_pp_via, a_9.fundvision_statements_via, a_9.fundvision_tax_documents_via, a_9.fundvision_proxy_comms_via, a_9.fundvision_prospectuses_via, a_9.consent_id, a_9.participant_investments, a_9.services_ips, a_9.services_liaison, a_9.services_reports, a_9.services_options, a_9.services_education, a_9.services_participant_education, a_9.services_enrollment, a_9.services_plan_fees, a_9.services_investment_monitoring, a_9.acting_capacity, a_9.invest_stocks, a_9.invest_mfs, a_9.invest_va, a_9.invest_fa, a_9.invest_life_ins, a_9.invest_advisory, a_9.invest_other, a_9.invest_other_desc, a_9.firelight_trans_classic_id, a_9.no_ach, a_9.private_banking_or_foreign, a_9.private_banking_or_foreign_information, a_9.investment_strategy_short_term_growth, a_9.investment_strategy_long_term_growth, a_9.investment_strategy_aggressive_growth, a_9.investment_strategy_growth, a_9.investment_strategy_growth_income, a_9.investment_strategy_balanced, a_9.investment_strategy_balanced_income, a_9.funding_source_investment_proceeds, a_9.funding_source_legal_settlement, a_9.funding_source_gift, a_9.funding_source_trust_will, a_9.funding_source_income_from_earnings_savings, a_9.funding_source_pension_ira_retirement, a_9.funding_source_spouse_parent, a_9.funding_source_insurance_proceeds, a_9.funding_source_other, a_9.contact_beneficiary_tuition_plan, a_9.manual_account, a_9.alt_country_1, a_9.alt_country_2
  • Index Cond: (a_9.id = 4,054,877)
91. 0.234 0.234 ↓ 2.0 2 1

Seq Scan on public.provider_where_held_mapping c_9 (cost=0.00..56.32 rows=1 width=14) (actual time=0.216..0.234 rows=2 loops=1)

  • Output: c_9.fund_company, c_9.custodial_provider_name, c_9.where_held_classic_id
  • Filter: (((c_9.realm)::text = ANY ('{cfs,spf}'::text[])) AND ((c_9.fund_company)::text = 'OPPHE'::text) AND (c_9.is_obsolete = 'N'::bpchar))
  • Rows Removed by Filter: 1,490
92. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.custodial_providers cp_9 (cost=0.00..1.38 rows=38 width=17) (never executed)

  • Output: cp_9.id, cp_9.name, cp_9.priority
93. 0.000 0.000 ↓ 0.0 0

Index Scan using index_cust_accounts_on_cust_account_custodial_provider_id on public.custodial_accounts b_9 (cost=0.56..8.60 rows=1 width=39) (never executed)

  • Output: b_9.id, b_9.number, b_9.opened_on, b_9.where_held, b_9.custodial_provider_id, b_9.fund_code
  • Index Cond: (((b_9.number)::text = "right"((a_9.account_number)::text, 10)) AND (b_9.custodial_provider_id = cp_9.id))
  • Filter: (((b_9.where_held)::text = 'OPPHE'::text) AND ("left"((a_9.account_number)::text, (-10)) = (b_9.fund_code)::text))
Planning time : 29.663 ms
Execution time : 1,088.510 ms