explain.depesz.com

PostgreSQL's explain analyze made readable

Result: z32K

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=9,326,638.07..9,326,739.78 rows=40,683 width=618) (actual rows= loops=)

  • Output: (CASE WHEN ((a.ecpd_profile_id)::text = '0000000000'::text) THEN NULL::character varying ELSE a.ecpd_profile_id END), a.accountname, a.custtype, a.vsn_cust_type, a.state, a.cust_id, a.acct_num, (((a.cust_id || '-'::text) || lpad((a.acct_num)::text, 5, '0'::text))), a.missing_accts, a.expired_accts, a.expiring_30_accts, a.expiring_60_accts, a.expiring_90_accts, '1', (to_char(a.end_date, 'MM-DD-YYYY'::text)), (CASE WHEN (a.missing_accts IS NOT NULL) THEN to_char(now(), 'MM-DD-YYYY'::text) ELSE to_char(a.end_date, 'MM-DD-YYYY'::text) END), a.ecpd_profile_id, (((a.cust_id || '-'::text) || (a.acct_num)::text))
  • Sort Key: (CASE WHEN (a.missing_accts IS NOT NULL) THEN to_char(now(), 'MM-DD-YYYY'::text) ELSE to_char(a.end_date, 'MM-DD-YYYY'::text) END), a.ecpd_profile_id, (((a.cust_id || '-'::text) || (a.acct_num)::text))
  • a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXPIRED'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_30'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_60'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_90'::text) THEN a_1.acct_num ELSE NULL::text END), a_1.end_date
  • THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXPIRED'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_30'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type =
  • 'EXP_60'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_90'::text) THEN a_1.acct_num ELSE NULL::text END), a_1.end_date
  • va_1.tcms_cust_type, va_1.parent_account, va_1.customer_type, eis.market_name, eis.market_code, an.description, ae_1.tax_id, ae_1.account_number, t_2.tax_id, va_2.account_number
  • = 'FFFEXC0001'::text) AND (va_1.fed_cert_required = 'N'::bpchar)) OR ((substr((ae_1.tax_id)::text, 1, 1) = ANY ('{S,L}'::text[])) AND (va_1.state_cert_required = 'N'::bpchar))))
  • ae_1.document_id, ae_1.status, ae_1.document_source, ae_1.assigned_date, ae_1.tx_xmpt_tmpl_id, ae_1.ecpd_profile_id, ae_1.vision_user_id, ae_1.tx_xmpt_add_type, ae_1.tx_xmpt_dup_ind, ae_1.vision_inst
  • va_4.parent_account, t_4.tax_name, t_4.state, ae_3.tx_xmpt_add_type, ae_3.vision_user_id, ae_3.assigned_date, ae_3.expire_date, substr((va_4.account_number)::text, 1, (length((va_4.account_number)::text) - 5)), ltrim(substr((va_4.account_number)::text, (length((va_4.account_number)::text) - 4), 5), '0'::text)
  • va_4.parent_account, va_4.market_code, va_4.tcms_cust_type, va_4.alternate_name_id, va_4.fed_cert_required, va_4.state_cert_required, va_4.customer_type, va_4.cust_id, va_4.acct_num
  • lpad((a.acct_num)::text, 5, '0'::text)), a.missing_accts, a.expired_accts, a.expiring_30_accts, a.expiring_60_accts, a.expiring_90_accts, '1', to_char(a.end_date, 'MM-DD-YYYY'::text), CASE WHEN (a.missing_accts IS NOT NULL) THEN to_char(now(), 'MM-DD-YYYY'::text) ELSE to_char(a.end_date, 'MM-DD-YYYY'::text) END, a.ecpd_profile_id, ((a.cust_id || '-'::text) || (a.acct_num)::text)
2.          

CTE cte_vision_account

3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=55,891.02..142,310.56 rows=222,063 width=214) (actual rows= loops=)

  • Output: COALESCE(va.vision_inst, ''::character varying), va.ecpd_profile_id, va.account_number, va.business_name, va.cust_type, va.load_date, am.parent_account, va.market_code, concat(am.customer_type, concat(' - ', ct.description)), am.alternate_name_id, am.fed_cert_required, am.state_cert_required, am.customer_type, substr((va.account_number)::text, 1, (length((va.account_number)::text) - 5)), ltrim(substr((va.account_number)::text, (length((va.account_number)::text) - 4), 5), '0'::text)
  • Hash Cond: ((am.customer_type)::text = (ct.customer_type)::text)
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=55,889.10..134,258.86 rows=222,063 width=89) (actual rows= loops=)

  • Output: va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.cust_type, va.load_date, va.market_code, am.parent_account, am.customer_type, am.alternate_name_id, am.fed_cert_required, am.state_cert_required
  • Hash Cond: ((am.account_number)::text = (va.account_number)::text)
5. 0.000 0.000 ↓ 0.0

Seq Scan on dbotcms.account_master am (cost=0.00..49,319.07 rows=1,135,481 width=41) (actual rows= loops=)

  • Output: am.parent_account, am.customer_type, am.alternate_name_id, am.fed_cert_required, am.state_cert_required, am.account_number
  • Filter: ((am.customer_type)::text <> 'FGA'::text)
6. 0.000 0.000 ↓ 0.0

Hash (cost=50,238.36..50,238.36 rows=243,099 width=63) (actual rows= loops=)

  • Output: va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.cust_type, va.load_date, va.market_code
7. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on dbotcms.vision_account va (cost=8,641.56..50,238.36 rows=243,099 width=63) (actual rows= loops=)

  • Output: va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.cust_type, va.load_date, va.market_code
  • Recheck Cond: ((va.status)::text = 'A'::text)
  • Filter: ((va.ecpd_profile_id)::text <> ALL ('{0003570849,0000061063,0000587956,0000588062,0000598373,0000613547,0000621118,0000637291,0000638469,0000687467,0000691620,0000809100,0001018879,0002531179,0002824817,0003401729,0003564401,0003691041,0003697103,0003820638}'::text[]))
8. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on vision_account_status_idx (cost=0.00..8,580.79 rows=243,248 width=0) (actual rows= loops=)

  • Index Cond: ((va.status)::text = 'A'::text)
9. 0.000 0.000 ↓ 0.0

Hash (cost=1.41..1.41 rows=41 width=25) (actual rows= loops=)

  • Output: ct.description, ct.customer_type
10. 0.000 0.000 ↓ 0.0

Seq Scan on dbotcms.customer_type ct (cost=0.00..1.41 rows=41 width=25) (actual rows= loops=)

  • Output: ct.description, ct.customer_type
11.          

CTE cte_account_exemption

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5,020.97..55,134.84 rows=11,065 width=80) (actual rows= loops=)

  • Output: ae.account_number, ae.tax_id, ae.exemption_source, ae.document_id, ae.document_source, ae.assigned_date, ae.tx_xmpt_tmpl_id, ae.tx_xmpt_add_type, ae.tx_xmpt_dup_ind, ae.vision_inst, d.expire_date, ae.vision_user_id
13. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,020.55..50,157.32 rows=11,065 width=72) (actual rows= loops=)

  • Output: ae.account_number, ae.tax_id, ae.exemption_source, ae.document_id, ae.document_source, ae.assigned_date, ae.tx_xmpt_tmpl_id, ae.tx_xmpt_add_type, ae.tx_xmpt_dup_ind, ae.vision_inst, ae.vision_user_id
  • Hash Cond: ((ae.tax_id)::text = (t.tax_id)::text)
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,996.98..49,981.54 rows=11,084 width=72) (actual rows= loops=)

  • Output: ae.account_number, ae.tax_id, ae.exemption_source, ae.document_id, ae.document_source, ae.assigned_date, ae.tx_xmpt_tmpl_id, ae.tx_xmpt_add_type, ae.tx_xmpt_dup_ind, ae.vision_inst, ae.vision_user_id
15. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,996.42..4,998.42 rows=200 width=48) (actual rows= loops=)

  • Output: cte_vision_account.account_number
  • Group Key: cte_vision_account.account_number
16. 0.000 0.000 ↓ 0.0

CTE Scan on cte_vision_account (cost=0.00..4,441.26 rows=222,063 width=48) (actual rows= loops=)

  • Output: cte_vision_account.vision_inst, cte_vision_account.ecpd_profile_id, cte_vision_account.account_number, cte_vision_account.business_name, cte_vision_account.vsn_cust_type, cte_vision_account.load_date, cte_vision_account.parent_account, cte_vision_account.market_code, cte_vision_account.tcms_cust_type, cte_vision_account.alternate_name_id, cte_vision_account.fed_cert_required, cte_vision_account.state_cert_required, cte_vision_account.customer_type, cte_vision_account.cust_id, cte_vision_account.acct_num
17. 0.000 0.000 ↓ 0.0

Index Scan using account_exemption_ix2 on dbotcms.account_exemption ae (cost=0.56..224.36 rows=55 width=72) (actual rows= loops=)

  • Output: ae.account_number, ae.tax_id, ae.assigned_by, ae.exemption_source, ae.document_id, ae.status, ae.document_source, ae.assigned_date, ae.tx_xmpt_tmpl_id, ae.ecpd_profile_id, ae.vision_user_id, ae.tx_xmpt_add_type, ae.tx_xmpt_dup_ind, ae.vision_inst
  • Index Cond: (((ae.account_number)::text = (cte_vision_account.account_number)::text) AND ((ae.status)::text = 'A'::text))
18. 0.000 0.000 ↓ 0.0

Hash (cost=16.29..16.29 rows=582 width=10) (actual rows= loops=)

  • Output: t.tax_id
19. 0.000 0.000 ↓ 0.0

Seq Scan on dbotcms.tax t (cost=0.00..16.29 rows=582 width=10) (actual rows= loops=)

  • Output: t.tax_id
  • Filter: ((t.tax_id)::text <> 'LCAUUT0001'::text)
20. 0.000 0.000 ↓ 0.0

Index Scan using document_pk on dbotcms.document d (cost=0.42..0.44 rows=1 width=16) (actual rows= loops=)

  • Output: d.document_id, d.document_date, d.expire_date, d.scan_date, d.scaned_by, d.edit_date, d.status, d.bar_code, d.serial_number, d.share_lock, d.tax_id, d.comments, d.queue_id, d.image_1_docid, d.image_2_docid, d.image_docid, d.pdfimage, d.update_dt
  • Index Cond: (ae.document_id = d.document_id)
21.          

CTE missing_expired

22. 0.000 0.000 ↓ 0.0

Sort (cost=9,112,879.00..9,112,980.71 rows=40,683 width=442) (actual rows= loops=)

  • Output: c.ecpd_profile_id, c.cust_id, c.accountname, c.custtype, c.vsn_cust_type, c.state, c.acct_num, ((c.missing_accts)::character varying(10)), ((c.expired_accts)::character varying(10)), ((c.expiring_30_accts)::character varying(10)), ((c.expiring_60_accts)::character varying(10)), ((c.expiring_90_accts)::character varying(10)), (rank() OVER (?)), c.end_date
  • Sort Key: c.cust_id, (rank() OVER (?))
23. 0.000 0.000 ↓ 0.0

WindowAgg (cost=9,099,217.51..9,101,556.78 rows=40,683 width=442) (actual rows= loops=)

  • Output: c.ecpd_profile_id, c.cust_id, c.accountname, c.custtype, c.vsn_cust_type, c.state, c.acct_num, (c.missing_accts)::character varying(10), (c.expired_accts)::character varying(10), (c.expiring_30_accts)::charactervarying(10), (c.expiring_60_accts)::character varying(10), (c.expiring_90_accts)::character varying(10), rank() OVER (?), c.end_date
24. 0.000 0.000 ↓ 0.0

Sort (cost=9,099,217.51..9,099,319.22 rows=40,683 width=404) (actual rows= loops=)

  • Output: c.cust_id, c.acct_num, c.ecpd_profile_id, c.accountname, c.custtype, c.vsn_cust_type, c.state, c.missing_accts, c.expired_accts, c.expiring_30_accts, c.expiring_60_accts, c.expiring_90_accts, c.end_date
  • Sort Key: c.cust_id, c.acct_num
25. 0.000 0.000 ↓ 0.0

Subquery Scan on c (cost=9,062,554.49..9,088,591.79 rows=40,683 width=404) (actual rows= loops=)

  • Output: c.cust_id, c.acct_num, c.ecpd_profile_id, c.accountname, c.custtype, c.vsn_cust_type, c.state, c.missing_accts, c.expired_accts, c.expiring_30_accts, c.expiring_60_accts, c.expiring_90_accts, c.end_date
26. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=9,062,554.49..9,088,184.96 rows=40,683 width=404) (actual rows= loops=)

  • Output: b.ecpd_profile_id, b.cust_id, (CASE WHEN (b.acct_num < '10'::text) THEN (concat('0', b.acct_num))::character varying ELSE (b.acct_num)::character varying(10) END), b.accountname, b.custtype, b.vsn_cust_type, b.state, sum((b.missing_accts)::bigint), sum((b.expired_accts)::bigint), sum((b.expiring_30_accts)::bigint), sum((b.expiring_60_accts)::bigint), sum((b.expiring_90_accts)::bigint), b.end_date
  • Group Key: b.ecpd_profile_id, b.cust_id, (CASE WHEN (b.acct_num < '10'::text) THEN (concat('0', b.acct_num))::character varying ELSE (b.acct_num)::character varying(10) END), b.accountname, b.custtype,b.vsn_cust_type, b.state, b.end_date
27. 0.000 0.000 ↓ 0.0

Sort (cost=9,062,554.49..9,063,571.58 rows=406,833 width=404) (actual rows= loops=)

  • Output: b.ecpd_profile_id, b.cust_id, (CASE WHEN (b.acct_num < '10'::text) THEN (concat('0', b.acct_num))::character varying ELSE (b.acct_num)::character varying(10) END), b.accountname, b.custtype, b.vsn_cust_type, b.state, b.end_date, b.missing_accts, b.expired_accts, b.expiring_30_accts, b.expiring_60_accts, b.expiring_90_accts
  • Sort Key: b.ecpd_profile_id, b.cust_id, (CASE WHEN (b.acct_num < '10'::text) THEN (concat('0', b.acct_num))::character varying ELSE (b.acct_num)::character varying(10) END), b.accountname, b.custtype, b.vsn_cust_type, b.state, b.end_date
28. 0.000 0.000 ↓ 0.0

Subquery Scan on b (cost=8,709,697.34..8,874,464.70 rows=406,833 width=404) (actual rows= loops=)

  • Output: b.ecpd_profile_id, b.cust_id, CASE WHEN (b.acct_num < '10'::text) THEN (concat('0', b.acct_num))::character varying ELSE (b.acct_num)::character varying(10) END, b.accountname, b.custtype, b.vsn_cust_type, b.state, b.end_date, b.missing_accts, b.expired_accts, b.expiring_30_accts, b.expiring_60_accts, b.expiring_90_accts
29. 0.000 0.000 ↓ 0.0

Group (cost=8,709,697.34..8,867,345.13 rows=406,833 width=436) (actual rows= loops=)

  • Output: a_1.work_type, a_1.ecpd_profile_id, a_1.cust_id, a_1.acct_num, a_1.accountname, a_1.custtype, a_1.vsn_cust_type, a_1.state, (CASE WHEN (a_1.work_type = 'MISSING'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXPIRED'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_30'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_60'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_90'::text) THEN a_1.acct_num ELSE NULL::text END), a_1.end_date
  • Group Key: a_1.work_type, a_1.ecpd_profile_id, a_1.cust_id, a_1.acct_num, a_1.accountname, a_1.custtype, a_1.vsn_cust_type, a_1.state, (CASE WHEN (a_1.work_type = 'MISSING'::text) THEN
30. 0.000 0.000 ↓ 0.0

Sort (cost=8,709,697.34..8,719,868.16 rows=4,068,330 width=436) (actual rows= loops=)

  • Output: a_1.work_type, a_1.ecpd_profile_id, a_1.cust_id, a_1.acct_num, a_1.accountname, a_1.custtype, a_1.vsn_cust_type, a_1.state, (CASE WHEN (a_1.work_type = 'MISSING'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXPIRED'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_30'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_60'::text) THEN a_1.acct_num ELSE NULL::text END), (CASE WHEN (a_1.work_type = 'EXP_90'::text) THEN a_1.acct_num ELSE NULL::text END), a_1.end_date
  • Sort Key: a_1.work_type, a_1.ecpd_profile_id, a_1.cust_id, a_1.acct_num, a_1.accountname, a_1.custtype, a_1.vsn_cust_type, a_1.state, (CASE WHEN (a_1.work_type = 'MISSING'::text)
31. 0.000 0.000 ↓ 0.0

Subquery Scan on a_1 (cost=5,548,575.54..5,843,529.47 rows=4,068,330 width=436) (actual rows= loops=)

  • Output: a_1.work_type, a_1.ecpd_profile_id, a_1.cust_id, a_1.acct_num, a_1.accountname, a_1.custtype, a_1.vsn_cust_type, a_1.state, CASE WHEN (a_1.work_type = 'MISSING'::text) THEN a_1.acct_num ELSE NULL::text END, CASE WHEN (a_1.work_type = 'EXPIRED'::text) THEN a_1.acct_num ELSE NULL::text END, CASE WHEN (a_1.work_type = 'EXP_30'::text) THEN a_1.acct_num ELSE NULL::text END, CASE WHEN (a_1.work_type = 'EXP_60'::text) THEN a_1.acct_num ELSE NULL::text END, CASE WHEN (a_1.work_type = 'EXP_90'::text) THEN a_1.acct_num ELSE NULL::text END, a_1.end_date
32. 0.000 0.000 ↓ 0.0

Unique (cost=5,548,575.54..5,751,992.04 rows=4,068,330 width=888) (actual rows= loops=)

  • Output: ('MISSING'::text), va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, ((((eis.market_name)::text || '-'::text) || (eis.market_code)::text)), an.description, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, t_1.tax_name, t_1.state, vae.tx_xmpt_add_type, vae.vision_user_id, vae.effective_date, vae.end_date, (substr((va_1.account_number)::text, 1, (length((va_1.account_number)::text) - 5))), (ltrim(substr((va_1.account_number)::text, (length((va_1.account_number)::text) - 4), 5), '0'::text))
33. 0.000 0.000 ↓ 0.0

Sort (cost=5,548,575.54..5,558,746.37 rows=4,068,330 width=888) (actual rows= loops=)

  • Output: ('MISSING'::text), va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, ((((eis.market_name)::text || '-'::text) || (eis.market_code)::text)), an.description, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, t_1.tax_name, t_1.state, vae.tx_xmpt_add_type, vae.vision_user_id, vae.effective_date, vae.end_date, (substr((va_1.account_number)::text, 1, (length((va_1.account_number)::text) - 5))), (ltrim(substr((va_1.account_number)::text, (length((va_1.account_number)::text) - 4), 5), '0'::text))
  • Sort Key: ('MISSING'::text), va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, ((((eis.market_name)::text || '-'::text) || (eis.market_code)::text)), an.description, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, t_1.tax_name, t_1.state, vae.tx_xmpt_add_type, vae.vision_user_id, vae.effective_date, vae.end_date, (substr((va_1.account_number)::text, 1, (length((va_1.account_number)::text) - 5))), (ltrim(substr((va_1.account_number)::text, (length((va_1.account_number)::text) - 4), 5), '0'::text))
34. 0.000 0.000 ↓ 0.0

Append (cost=5,551.48..346,294.17 rows=4,068,330 width=888) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,551.48..49,584.30 rows=1 width=516) (actual rows= loops=)

  • Output: 'MISSING'::text, va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, (((eis.market_name)::text|| '-'::text) || (eis.market_code)::text), an.description, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, t_1.tax_name, t_1.state, vae.tx_xmpt_add_type, vae.vision_user_id, vae.effective_date, vae.end_date, substr((va_1.account_number)::text, 1, (length((va_1.account_number)::text) - 5)), ltrim(substr((va_1.account_number)::text, (length((va_1.account_number)::text) - 4), 5), '0'::text)
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,551.19..49,582.80 rows=1 width=466) (actual rows= loops=)

  • Output: va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, va_1.customer_type, eis.market_name, eis.market_code, an.description, ae_1.tax_id, t_2.tax_id, t_1.tax_name, t_1.state, t_1.tax_id, vae.tx_xmpt_add_type, vae.vision_user_id, vae.effective_date, vae.end_date
  • Join Filter: ((t_1.state)::text = (s.state)::text)
37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,551.05..49,582.12 rows=4 width=469) (actual rows= loops=)

  • Output: va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, va_1.customer_type, eis.market_name, eis.market_code, an.description, ae_1.tax_id, t_2.tax_id, t_1.tax_name, t_1.state, t_1.tax_id, vae.tx_xmpt_add_type, vae.vision_user_id, vae.effective_date, vae.end_date, vae.geolvl2
38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,550.49..49,550.33 rows=1 width=499) (actual rows= loops=)

  • Output: va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, va_1.customer_type, eis.market_name, eis.market_code, an.description, ae_1.tax_id, ae_1.account_number, t_2.tax_id, va_2.account_number, t_1.tax_name, t_1.state, t_1.tax_id, t_1.vision_tax_code
39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,550.21..49,549.99 rows=1 width=449) (actual rows= loops=)

  • Output: va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, va_1.vsn_cust_type,
  • Join Filter: ((ae_1.tax_id)::text = (t_2.tax_id)::text)
40. 0.000 0.000 ↓ 0.0

Index Only Scan using tax_pk on dbotcms.tax t_2 (cost=0.28..30.48 rows=582 width=10) (actual rows= loops=)

  • Output: t_2.tax_id
  • Filter: ((t_2.tax_id)::text <> 'LCAUUT0001'::text)
41. 0.000 0.000 ↓ 0.0

Materialize (cost=5,549.94..49,510.79 rows=1 width=439) (actual rows= loops=)

  • Output: va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, va_1.customer_type, eis.market_name, eis.market_code, an.description, ae_1.tax_id, ae_1.account_number, va_2.account_number
42. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5,549.94..49,510.78 rows=1 width=439) (actual rows= loops=)

  • Output: va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, va_1.customer_type, eis.market_name, eis.market_code, an.description, ae_1.tax_id, ae_1.account_number, va_2.account_number
43. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,549.65..49,509.51 rows=1 width=421) (actual rows= loops=)

  • Output: va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, va_1.alternate_name_id, va_1.customer_type, eis.market_name, eis.market_code, ae_1.tax_id, ae_1.account_number, va_2.account_number
44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,549.36..49,509.09 rows=1 width=413) (actual rows= loops=)

  • Output: va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.load_date, va_1.vsn_cust_type, va_1.tcms_cust_type, va_1.parent_account, va_1.market_code, va_1.alternate_name_id, va_1.customer_type, ae_1.tax_id, ae_1.account_number, va_2.account_number
  • Join Filter: (((ae_1.account_number)::text = (va_1.account_number)::text) AND ((((ae_1.tax_id)::text
45. 0.000 0.000 ↓ 0.0

CTE Scan on cte_vision_account va_1 (cost=0.00..6,106.73 rows=2,204 width=356) (actual rows= loops=)

  • Output: va_1.vision_inst, va_1.ecpd_profile_id, va_1.account_number, va_1.business_name, va_1.vsn_cust_type, va_1.load_date, va_1.parent_account, va_1.market_code, va_1.tcms_cust_type, va_1.alternate_name_id, va_1.fed_cert_required, va_1.state_cert_required, va_1.customer_type, va_1.cust_id, va_1.acct_num
  • Filter: (((va_1.customer_type)::text <> 'FGA'::text) AND ((va_1.fed_cert_required = 'N'::bpchar) OR (va_1.state_cert_required = 'N'::bpchar)))
46. 0.000 0.000 ↓ 0.0

Materialize (cost=5,549.36..43,281.14 rows=2 width=73) (actual rows= loops=)

  • Output: ae_1.tax_id, ae_1.account_number, va_2.account_number
47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,549.36..43,281.13 rows=2 width=73) (actual rows= loops=)

  • Output: ae_1.tax_id, ae_1.account_number, va_2.account_number
48. 0.000 0.000 ↓ 0.0

HashAggregate (cost=5,548.80..5,550.80 rows=200 width=48) (actual rows= loops=)

  • Output: va_2.account_number
  • Group Key: va_2.account_number
49. 0.000 0.000 ↓ 0.0

CTE Scan on cte_vision_account va_2 (cost=0.00..4,996.42 rows=220,953 width=48) (actual rows= loops=)

  • Output: va_2.vision_inst, va_2.ecpd_profile_id, va_2.account_number, va_2.business_name, va_2.vsn_cust_type, va_2.load_date, va_2.parent_account, va_2.market_code, va_2.tcms_cust_type, va_2.alternate_name_id, va_2.fed_cert_required, va_2.state_cert_required, va_2.customer_type, va_2.cust_id, va_2.acct_num
  • Filter: ((va_2.customer_type)::text <> 'FGA'::text)
50. 0.000 0.000 ↓ 0.0

Index Scan using account_exemption_ix4 on dbotcms.account_exemption ae_1 (cost=0.56..188.63 rows=1 width=25) (actual rows= loops=)

  • Output: ae_1.account_number, ae_1.tax_id, ae_1.assigned_by, ae_1.exemption_source,
  • Index Cond: (((ae_1.status)::text = 'A'::text) AND (ae_1.document_id IS NULL) AND((ae_1.account_number)::text = (va_2.account_number)::text))
  • Filter: ((((ae_1.tax_id)::text = 'FFFEXC0001'::text) OR (substr((ae_1.tax_id)::text, 1, 1) = ANY ('{S,L}'::text[]))) AND (((ae_1.tax_id)::text = 'FFFEXC0001'::text) OR (substr((ae_1.tax_id)::text, 1, 1) = ANY ('{S,L}'::text[]))))
51. 0.000 0.000 ↓ 0.0

Index Scan using eis_market_ix1 on dbotcms.eis_market eis (cost=0.28..0.41 rows=1 width=24) (actual rows= loops=)

  • Output: eis.sor_id, eis.area_code, eis.region_code, eis.market_code, eis.market_name, eis.major_market_id
  • Index Cond: ((eis.market_code)::text = (va_1.market_code)::text)
52. 0.000 0.000 ↓ 0.0

Index Scan using alternate_name_pk on dbotcms.alternate_name an (cost=0.29..1.27 rows=1 width=26) (actual rows= loops=)

  • Output: an.alternate_name_id, an.description
  • Index Cond: (va_1.alternate_name_id = an.alternate_name_id)
53. 0.000 0.000 ↓ 0.0

Index Scan using tax_pk on dbotcms.tax t_1 (cost=0.28..0.33 rows=1 width=50) (actual rows= loops=)

  • Output: t_1.tax_id, t_1.state, t_1.tax_type, t_1.jurisdiction, t_1.tax_name, t_1.tax_seller_user, t_1.vision_tax_code, t_1.request_text, t_1.termination_text, t_1.renewal_text, t_1.tems_enabled, t_1.eligible_text, t_1.exemption_life, t_1.tax_group_tax_id, t_1.tax_phase, t_1.tax_sequence
  • Index Cond: ((t_1.tax_id)::text = (ae_1.tax_id)::text)
  • Filter: ((t_1.tax_id)::text <> 'LCAUUT0001'::text)
54. 0.000 0.000 ↓ 0.0

Index Scan using vision_account_exemption_ix4 on dbotcms.vision_account_exemption vae (cost=0.56..31.65 rows=14 width=55) (actual rows= loops=)

  • Output: vae.account_number, vae.bill_type, vae.country_code, vae.geolvl2, vae.geolvl3, vae.geolvl4, vae.geolvl5, vae.tax_code, vae.effective_date, vae.end_date, vae.tax_exemption_code, vae.tx_xmpt_tmpl_id, vae.ecpd_profile_id, vae.vision_user_id, vae.tx_xmpt_add_type, vae.tx_xmpt_dup_ind, vae.vision_inst, vae.load_date, vae.edit_date, vae.status
  • Index Cond: (((vae.account_number)::text = (ae_1.account_number)::text) AND ((vae.tax_code)::text = (t_1.vision_tax_code)::text))
55. 0.000 0.000 ↓ 0.0

Index Scan using state_geolvl2_uk on dbotcms.state s (cost=0.14..0.16 rows=1 width=6) (actual rows= loops=)

  • Output: s.state, s.state_name, s.geolvl2, s.vertex_code
  • Index Cond: ((s.geolvl2)::text = (vae.geolvl2)::text)
56. 0.000 0.000 ↓ 0.0

Index Scan using exe_master_pk on dbotcms.exemption_master em (cost=0.29..1.47 rows=1 width=14) (actual rows= loops=)

  • Output: em.tax_id, em.customer_type, em.exemption_type, em.foot_note
  • Index Cond: (((em.tax_id)::text = (ae_1.tax_id)::text) AND ((em.customer_type)::text = (va_1.customer_type)::text))
  • Filter: ((em.exemption_type)::text = ANY ('{Y,YA}'::text[]))
57. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,984.07..246,889.99 rows=4,067,867 width=571) (actual rows= loops=)

  • Output: 'EXPIRED'::text, va_3.vision_inst, va_3.ecpd_profile_id, va_3.account_number, va_3.business_name, va_3.load_date, (((eis_1.market_name)::text || '-'::text) || (eis_1.market_code)::text), an_1.description, va_3.vsn_cust_type, va_3.tcms_cust_type, va_3.parent_account, t_3.tax_name, t_3.state, ae_2.tx_xmpt_add_type, ae_2.vision_user_id, ae_2.assigned_date, ae_2.expire_date, substr((va_3.account_number)::text, 1, (length((va_3.account_number)::text) - 5)), ltrim(substr((va_3.account_number)::text, (length((va_3.account_number)::text) - 4), 5), '0'::text)
  • Hash Cond: ((va_3.account_number)::text = (ae_2.account_number)::text)
58. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,504.69..12,052.69 rows=222,063 width=342) (actual rows= loops=)

  • Output: va_3.vision_inst, va_3.ecpd_profile_id, va_3.account_number, va_3.business_name, va_3.load_date, va_3.vsn_cust_type, va_3.tcms_cust_type, va_3.parent_account, an_1.description, eis_1.market_name, eis_1.market_code
  • Hash Cond: ((va_3.market_code)::text = (eis_1.market_code)::text)
59. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,358.83..8,853.45 rows=222,063 width=334) (actual rows= loops=)

  • Output: va_3.vision_inst, va_3.ecpd_profile_id, va_3.account_number, va_3.business_name, va_3.load_date, va_3.vsn_cust_type, va_3.tcms_cust_type, va_3.parent_account, va_3.market_code, an_1.description
  • Hash Cond: (va_3.alternate_name_id = an_1.alternate_name_id)
60. 0.000 0.000 ↓ 0.0

CTE Scan on cte_vision_account va_3 (cost=0.00..4,441.26 rows=222,063 width=316) (actual rows= loops=)

  • Output: va_3.vision_inst, va_3.ecpd_profile_id, va_3.account_number, va_3.business_name, va_3.vsn_cust_type, va_3.load_date, va_3.parent_account, va_3.market_code, va_3.tcms_cust_type, va_3.alternate_name_id, va_3.fed_cert_required, va_3.state_cert_required, va_3.customer_type, va_3.cust_id, va_3.acct_num
61. 0.000 0.000 ↓ 0.0

Hash (cost=785.59..785.59 rows=45,859 width=26) (actual rows= loops=)

  • Output: an_1.description, an_1.alternate_name_id
62. 0.000 0.000 ↓ 0.0

Seq Scan on dbotcms.alternate_name an_1 (cost=0.00..785.59 rows=45,859 width=26) (actual rows= loops=)

  • Output: an_1.description, an_1.alternate_name_id
63. 0.000 0.000 ↓ 0.0

Hash (cost=85.94..85.94 rows=4,794 width=24) (actual rows= loops=)

  • Output: eis_1.market_name, eis_1.market_code
64. 0.000 0.000 ↓ 0.0

Seq Scan on dbotcms.eis_market eis_1 (cost=0.00..85.94 rows=4,794 width=24) (actual rows= loops=)

  • Output: eis_1.market_name, eis_1.market_code
65. 0.000 0.000 ↓ 0.0

Hash (cost=433.58..433.58 rows=3,664 width=173) (actual rows= loops=)

  • Output: ae_2.tx_xmpt_add_type, ae_2.vision_user_id, ae_2.assigned_date, ae_2.expire_date, ae_2.account_number, t_3.tax_name, t_3.state
66. 0.000 0.000 ↓ 0.0

Hash Join (cost=23.56..433.58 rows=3,664 width=173) (actual rows= loops=)

  • Output: ae_2.tx_xmpt_add_type, ae_2.vision_user_id, ae_2.assigned_date, ae_2.expire_date, ae_2.account_number, t_3.tax_name, t_3.state
  • Hash Cond: ((ae_2.tax_id)::text = (t_3.tax_id)::text)
67. 0.000 0.000 ↓ 0.0

CTE Scan on cte_account_exemption ae_2 (cost=0.00..359.61 rows=3,670 width=174) (actual rows= loops=)

  • Output: ae_2.account_number, ae_2.tax_id, ae_2.exemption_source, ae_2.document_id, ae_2.document_source, ae_2.assigned_date, ae_2.tx_xmpt_tmpl_id, ae_2.tx_xmpt_add_type, ae_2.tx_xmpt_dup_ind, ae_2.vision_inst, ae_2.expire_date, ae_2.vision_user_id
  • Filter: ((ae_2.document_id IS NOT NULL) AND (date_trunc('Day'::text, ae_2.expire_date) <= date_trunc('Day'::text, (now() - '1 day'::interval))))
68. 0.000 0.000 ↓ 0.0

Hash (cost=16.29..16.29 rows=582 width=47) (actual rows= loops=)

  • Output: t_3.tax_name, t_3.state, t_3.tax_id
69. 0.000 0.000 ↓ 0.0

Seq Scan on dbotcms.tax t_3 (cost=0.00..16.29 rows=582 width=47) (actual rows= loops=)

  • Output: t_3.tax_name, t_3.state, t_3.tax_id
  • Filter: ((t_3.tax_id)::text <> 'LCAUUT0001'::text)
70. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=927.93..9,136.58 rows=462 width=571) (actual rows= loops=)

  • Output: CASE WHEN (date_part('day'::text, ((ae_3.expire_date)::timestamp with time zone - date_trunc('Day'::text, (now() + '1 day'::interval)))) < '31'::double precision) THEN 'EXP_30'::text WHEN (date_part('day'::text, ((ae_3.expire_date)::timestamp with time zone - date_trunc('Day'::text, (now() + '1 day'::interval)))) < '61'::double precision) THEN 'EXP_60'::text ELSE 'EXP_90'::text END, va_4.vision_inst, va_4.ecpd_profile_id, va_4.account_number, va_4.business_name, va_4.load_date, (((eis_2.market_name)::text || '-'::text) || (eis_2.market_code)::text), an_2.description, va_4.vsn_cust_type, va_4.tcms_cust_type,
71. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=927.64..8,966.30 rows=462 width=459) (actual rows= loops=)

  • Output: va_4.vision_inst, va_4.ecpd_profile_id, va_4.account_number, va_4.business_name, va_4.load_date, va_4.vsn_cust_type, va_4.tcms_cust_type, va_4.parent_account, va_4.market_code, ae_3.expire_date, ae_3.tx_xmpt_add_type, ae_3.vision_user_id, ae_3.assigned_date, an_2.description, t_4.tax_name, t_4.state
72. 0.000 0.000 ↓ 0.0

Hash Join (cost=927.35..8,815.20 rows=462 width=441) (actual rows= loops=)

  • Output: va_4.vision_inst, va_4.ecpd_profile_id, va_4.account_number, va_4.business_name, va_4.load_date, va_4.vsn_cust_type, va_4.tcms_cust_type, va_4.parent_account, va_4.alternate_name_id, va_4.market_code, ae_3.expire_date, ae_3.tx_xmpt_add_type, ae_3.vision_user_id, ae_3.assigned_date, t_4.tax_name, t_4.state
  • Hash Cond: (((ae_3.tax_id)::text = (t_4.tax_id)::text) AND ((va_4.customer_type)::text = (em_1.customer_type)::text))
73. 0.000 0.000 ↓ 0.0

Hash Join (cost=470.95..7,743.51 rows=61,067 width=466) (actual rows= loops=)

  • Output: va_4.vision_inst, va_4.ecpd_profile_id, va_4.account_number, va_4.business_name, va_4.load_date, va_4.vsn_cust_type, va_4.tcms_cust_type, va_4.parent_account, va_4.alternate_name_id, va_4.market_code, va_4.customer_type, ae_3.expire_date, ae_3.tx_xmpt_add_type, ae_3.vision_user_id, ae_3.assigned_date, ae_3.tax_id
  • Hash Cond: ((va_4.account_number)::text = (ae_3.account_number)::text)
74. 0.000 0.000 ↓ 0.0

CTE Scan on cte_vision_account va_4 (cost=0.00..4,441.26 rows=222,063 width=340) (actual rows= loops=)

  • Output: va_4.vision_inst, va_4.ecpd_profile_id, va_4.account_number, va_4.business_name, va_4.vsn_cust_type, va_4.load_date,
75. 0.000 0.000 ↓ 0.0

Hash (cost=470.26..470.26 rows=55 width=174) (actual rows= loops=)

  • Output: ae_3.expire_date, ae_3.tx_xmpt_add_type, ae_3.vision_user_id, ae_3.assigned_date, ae_3.account_number, ae_3.tax_id
76. 0.000 0.000 ↓ 0.0

CTE Scan on cte_account_exemption ae_3 (cost=0.00..470.26 rows=55 width=174) (actual rows= loops=)

  • Output: ae_3.expire_date, ae_3.tx_xmpt_add_type, ae_3.vision_user_id, ae_3.assigned_date, ae_3.account_number, ae_3.tax_id
  • Filter: ((ae_3.document_id IS NOT NULL) AND (date_trunc('Day'::text, ae_3.expire_date) >= date_trunc('Day'::text, now())) AND (date_trunc('Day'::text, ae_3.expire_date) <= date_trunc('Day'::text, (now() + '3 mons'::interval))))
77. 0.000 0.000 ↓ 0.0

Hash (cost=443.17..443.17 rows=882 width=61) (actual rows= loops=)

  • Output: t_4.tax_name, t_4.state, t_4.tax_id, em_1.tax_id, em_1.customer_type
78. 0.000 0.000 ↓ 0.0

Hash Join (cost=46.99..443.17 rows=882 width=61) (actual rows= loops=)

  • Output: t_4.tax_name, t_4.state, t_4.tax_id, em_1.tax_id, em_1.customer_type
  • Hash Cond: ((em_1.tax_id)::text = (t_4.tax_id)::text)
79. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on dbotcms.exemption_master em_1 (cost=23.43..407.48 rows=884 width=14) (actual rows= loops=)

  • Output: em_1.tax_id, em_1.customer_type, em_1.exemption_type, em_1.foot_note
  • Recheck Cond: ((em_1.exemption_type)::text = ANY ('{Y,YA}'::text[]))
80. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on exe_master_exe_type_fk_ix5 (cost=0.00..23.20 rows=884 width=0) (actual rows= loops=)

  • Index Cond: ((em_1.exemption_type)::text = ANY ('{Y,YA}'::text[]))
81. 0.000 0.000 ↓ 0.0

Hash (cost=16.29..16.29 rows=582 width=47) (actual rows= loops=)

  • Output: t_4.tax_name, t_4.state, t_4.tax_id
82. 0.000 0.000 ↓ 0.0

Seq Scan on dbotcms.tax t_4 (cost=0.00..16.29 rows=582 width=47) (actual rows= loops=)

  • Output: t_4.tax_name, t_4.state, t_4.tax_id
  • Filter: ((t_4.tax_id)::text <> 'LCAUUT0001'::text)
83. 0.000 0.000 ↓ 0.0

Index Scan using alternate_name_pk on dbotcms.alternate_name an_2 (cost=0.29..0.32 rows=1 width=26) (actual rows= loops=)

  • Output: an_2.alternate_name_id, an_2.description
  • Index Cond: (va_4.alternate_name_id = an_2.alternate_name_id)
84. 0.000 0.000 ↓ 0.0

Index Scan using eis_market_ix1 on dbotcms.eis_market eis_2 (cost=0.28..0.30 rows=1 width=24) (actual rows= loops=)

  • Output: eis_2.sor_id, eis_2.area_code, eis_2.region_code, eis_2.market_code, eis_2.market_name, eis_2.major_market_id
  • Index Cond: ((va_4.market_code)::text = (eis_2.market_code)::text)
85. 0.000 0.000 ↓ 0.0

CTE Scan on missing_expired a (cost=0.00..1,830.74 rows=40,683 width=618) (actual rows= loops=)

  • Output: CASE WHEN ((a.ecpd_profile_id)::text = '0000000000'::text) THEN NULL::character varying ELSE a.ecpd_profile_id END, a.accountname, a.custtype, a.vsn_cust_type, a.state, a.cust_id, a.acct_num, ((a.cust_id || '-'::text) ||