explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gten

Settings
# exclusive inclusive rows x rows loops node
1. 172.879 3,499,863.250 ↑ 1.3 29,872 1

Sort (cost=8,981,879.06..8,981,972.54 rows=37,391 width=618) (actual time=3,499,858.480..3,499,863.250 rows=29,872 loops=1)

  • 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))
  • Sort Method: quicksort Memory: 8,695kB
  • Buffers: shared hit=61,631,490, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
2.          

CTE cte_account_exemption

3. 785.448 36,722.720 ↑ 2.4 4,740,771 1

Hash Left Join (cost=3,157,021.45..3,409,025.82 rows=11,286,465 width=80) (actual time=21,475.745..36,722.720 rows=4,740,771 loops=1)

  • 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
  • Hash Cond: (ae.document_id = d.document_id)
  • Buffers: shared hit=1,072,900, local hit=41,896 read=11,643, temp read=75,180 written=75,180
  • I/O Timings: read=43.272
4. 1,727.424 35,859.712 ↑ 2.4 4,740,771 1

Merge Join (cost=3,146,490.64..3,354,489.07 rows=11,286,465 width=72) (actual time=21,397.109..35,859.712 rows=4,740,771 loops=1)

  • 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
  • Merge Cond: ((cte_vision_account.account_number)::text = (ae.account_number)::text)
  • Buffers: shared hit=1,068,211, local hit=41,896 read=11,643, temp read=75,180 written=75,180
  • I/O Timings: read=43.272
5. 52.503 174.407 ↑ 1.0 231,540 1

Group (cost=0.42..14,630.99 rows=231,540 width=16) (actual time=0.749..174.407 rows=231,540 loops=1)

  • Output: cte_vision_account.account_number
  • Group Key: cte_vision_account.account_number
  • Buffers: local hit=41,896 read=11,643
  • I/O Timings: read=43.272
6. 121.904 121.904 ↑ 1.0 231,540 1

Index Only Scan using cva_account_number_idx on pg_temp_91.cte_vision_account (cost=0.42..14,052.14 rows=231,540 width=16) (actual time=0.747..121.904 rows=231,540 loops=1)

  • Output: cte_vision_account.account_number
  • Heap Fetches: 231,540
  • Buffers: local hit=41,896 read=11,643
  • I/O Timings: read=43.272
7. 941.658 33,957.881 ↑ 1.0 8,419,852 1

Materialize (cost=3,146,490.22..3,188,843.47 rows=8,470,650 width=72) (actual time=21,396.355..33,957.881 rows=8,419,852 loops=1)

  • 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
  • Buffers: shared hit=1,068,211, temp read=75,180 written=75,180
8. 23,355.949 33,016.223 ↑ 1.0 8,419,852 1

Sort (cost=3,146,490.22..3,167,666.85 rows=8,470,650 width=72) (actual time=21,396.349..33,016.223 rows=8,419,852 loops=1)

  • 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
  • Sort Key: ae.account_number
  • Sort Method: external merge Disk: 601,264kB
  • Buffers: shared hit=1,068,211, temp read=75,180 written=75,180
9. 2,360.420 9,660.274 ↑ 1.0 8,423,536 1

Hash Join (cost=23.56..1,824,339.78 rows=8,470,650 width=72) (actual time=0.214..9,660.274 rows=8,423,536 loops=1)

  • 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)
  • Buffers: shared hit=1,068,211
10. 7,299.666 7,299.666 ↑ 1.0 8,448,233 1

Seq Scan on dbotcms.account_exemption ae (cost=0.00..1,707,790.20 rows=8,485,204 width=72) (actual time=0.016..7,299.666 rows=8,448,233 loops=1)

  • 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
  • Filter: ((ae.status)::text = 'A'::text)
  • Rows Removed by Filter: 42,752,661
  • Buffers: shared hit=1,068,202
11. 0.095 0.188 ↑ 1.0 582 1

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

  • Output: t.tax_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
  • Buffers: shared hit=9
12. 0.093 0.093 ↑ 1.0 582 1

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

  • Output: t.tax_id
  • Filter: ((t.tax_id)::text <> 'LCAUUT0001'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=9
13. 40.731 77.560 ↓ 1.0 259,652 1

Hash (cost=7,285.36..7,285.36 rows=259,636 width=16) (actual time=77.560..77.560 rows=259,652 loops=1)

  • Output: d.expire_date, d.document_id
  • Buckets: 262,144 Batches: 1 Memory Usage: 13,660kB
  • Buffers: shared hit=4,689
14. 36.829 36.829 ↓ 1.0 259,652 1

Seq Scan on dbotcms.document d (cost=0.00..7,285.36 rows=259,636 width=16) (actual time=0.011..36.829 rows=259,652 loops=1)

  • Output: d.expire_date, d.document_id
  • Buffers: shared hit=4,689
15.          

CTE missing_expired

16. 21.230 3,499,640.671 ↑ 1.3 29,872 1

Sort (cost=5,568,237.25..5,568,330.73 rows=37,391 width=442) (actual time=3,499,637.327..3,499,640.671 rows=29,872 loops=1)

  • 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 (?))
  • Sort Method: quicksort Memory: 6,296kB
  • Buffers: shared hit=61,631,490, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
17. 23.681 3,499,619.441 ↑ 1.3 29,872 1

WindowAgg (cost=5,563,247.34..5,565,397.33 rows=37,391 width=442) (actual time=3,499,590.668..3,499,619.441 rows=29,872 loops=1)

  • 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
  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
18. 92.376 3,499,595.760 ↑ 1.3 29,872 1

Sort (cost=5,563,247.34..5,563,340.82 rows=37,391 width=404) (actual time=3,499,590.638..3,499,595.760 rows=29,872 loops=1)

  • 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
  • Sort Method: quicksort Memory: 5,490kB
  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
19. 3.220 3,499,503.384 ↑ 1.3 29,872 1

Subquery Scan on c (cost=5,558,911.78..5,560,407.42 rows=37,391 width=404) (actual time=3,499,479.173..3,499,503.384 rows=29,872 loops=1)

  • 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
  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
20. 58.103 3,499,500.164 ↑ 1.3 29,872 1

HashAggregate (cost=5,558,911.78..5,560,033.51 rows=37,391 width=404) (actual time=3,499,479.172..3,499,500.164 rows=29,872 loops=1)

  • 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
  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
21. 15.026 3,499,442.061 ↑ 12.5 29,872 1

Subquery Scan on b (cost=5,385,980.33..5,537,412.19 rows=373,906 width=404) (actual time=3,499,410.583..3,499,442.061 rows=29,872 loops=1)

  • 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
  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
22. 12.992 3,499,427.035 ↑ 12.5 29,872 1

Group (cost=5,385,980.33..5,530,868.83 rows=373,906 width=436) (actual time=3,499,410.561..3,499,427.035 rows=29,872 loops=1)

  • 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 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
  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
23. 164.742 3,499,414.043 ↑ 115.8 32,292 1

Sort (cost=5,385,980.33..5,395,327.98 rows=3,739,058 width=436) (actual time=3,499,410.560..3,499,414.043 rows=32,292 loops=1)

  • 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) 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 Method: quicksort Memory: 6,632kB
  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
24. 13.797 3,499,249.301 ↑ 115.8 32,292 1

Subquery Scan on a_1 (cost=3,965,460.62..4,236,542.32 rows=3,739,058 width=436) (actual time=3,499,219.380..3,499,249.301 rows=32,292 loops=1)

  • 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
  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
25. 10.602 3,499,235.504 ↑ 115.8 32,292 1

Unique (cost=3,965,460.62..4,152,413.52 rows=3,739,058 width=888) (actual time=3,499,219.355..3,499,235.504 rows=32,292 loops=1)

  • Output: ('MISSING'::text), va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.load_date, ((((eis.market_name)::text || '-'::text) || (eis.market_code)::text)), an.description, va.vsn_cust_type, va.tcms_cust_type, va.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.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))
  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
26. 240.143 3,499,224.902 ↑ 115.3 32,430 1

Sort (cost=3,965,460.62..3,974,808.26 rows=3,739,058 width=888) (actual time=3,499,219.354..3,499,224.902 rows=32,430 loops=1)

  • Output: ('MISSING'::text), va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.load_date, ((((eis.market_name)::text || '-'::text) || (eis.market_code)::text)), an.description, va.vsn_cust_type, va.tcms_cust_type, va.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.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))
  • Sort Key: ('MISSING'::text), va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.load_date, ((((eis.market_name)::text || '-'::text) || (eis.market_code)::text)), an.description, va.vsn_cust_type, va.tcms_cust_type, va.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.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))
  • Sort Method: quicksort Memory: 11,266kB
  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
27. 5.982 3,498,984.759 ↑ 115.3 32,430 1

Append (cost=982,225.53..2,100,342.60 rows=3,739,058 width=888) (actual time=3,188,213.542..3,498,984.759 rows=32,430 loops=1)

  • Buffers: shared hit=61,631,487, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
28. 4,565.800 3,458,526.766 ↓ 3,542.3 21,254 1

Nested Loop (cost=982,225.53..1,000,880.02 rows=6 width=325) (actual time=3,188,213.541..3,458,526.766 rows=21,254 loops=1)

  • Output: 'MISSING'::text, va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.load_date, (((eis.market_name)::text || '-'::text) || (eis.market_code)::text), an.description, va.vsn_cust_type, va.tcms_cust_type, va.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.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)
  • Buffers: shared hit=60,550,258, local hit=1,105,611,061 read=1,698,546, temp read=9,692,865 written=9,692,865
  • I/O Timings: read=7,630.808
29. 18,567.455 3,432,866.082 ↓ 35,875.7 5,273,721 1

Hash Join (cost=982,225.24..1,000,643.91 rows=147 width=255) (actual time=3,188,170.549..3,432,866.082 rows=5,273,721 loops=1)

  • Output: va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.load_date, va.vsn_cust_type, va.tcms_cust_type, va.parent_account, va.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
  • Hash Cond: (((vae.geolvl2)::text = (s.geolvl2)::text) AND ((t_1.state)::text = (s.state)::text))
  • Buffers: shared hit=44,750,784, local hit=1,105,611,061 read=1,698,546, temp read=9,692,865 written=9,692,865
  • I/O Timings: read=7,630.808
30. 24,647.960 3,414,298.604 ↓ 18,739.9 143,697,311 1

Hash Left Join (cost=982,222.94..1,000,582.63 rows=7,668 width=258) (actual time=3,188,170.494..3,414,298.604 rows=143,697,311 loops=1)

  • Output: va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.load_date, va.vsn_cust_type, va.tcms_cust_type, va.parent_account, va.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
  • Hash Cond: (va.alternate_name_id = an.alternate_name_id)
  • Buffers: shared hit=44,750,783, local hit=1,105,611,061 read=1,698,546, temp read=9,692,865 written=9,692,865
  • I/O Timings: read=7,630.808
31. 37,687.254 3,389,638.184 ↓ 18,739.9 143,697,311 1

Hash Join (cost=980,864.11..999,188.16 rows=7,668 width=240) (actual time=3,188,157.914..3,389,638.184 rows=143,697,311 loops=1)

  • Output: va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.load_date, va.vsn_cust_type, va.tcms_cust_type, va.parent_account, va.alternate_name_id, va.customer_type, eis.market_name, eis.market_code, 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
  • Hash Cond: ((va.market_code)::text = (eis.market_code)::text)
  • Buffers: shared hit=44,750,453, local hit=1,105,611,061 read=1,698,546, temp read=9,692,865 written=9,692,865
  • I/O Timings: read=7,630.808
32. 46,991.026 3,351,949.230 ↓ 18,747.8 143,758,241 1

Merge Join (cost=980,718.22..998,936.84 rows=7,668 width=220) (actual time=3,188,156.160..3,351,949.230 rows=143,758,241 loops=1)

  • Output: va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.load_date, va.vsn_cust_type, va.tcms_cust_type, va.parent_account, va.market_code, va.alternate_name_id, va.customer_type, 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
  • Merge Cond: ((va_1.account_number)::text = (ae_1.account_number)::text)
  • Buffers: shared hit=44,750,415, local hit=1,105,611,061 read=1,698,546, temp read=9,692,865 written=9,692,865
  • I/O Timings: read=7,630.808
33. 67.725 1,944.203 ↑ 1.0 231,540 1

Group (cost=0.42..15,209.84 rows=231,540 width=16) (actual time=0.038..1,944.203 rows=231,540 loops=1)

  • Output: va_1.account_number
  • Group Key: va_1.account_number
  • Buffers: local hit=41,776 read=11,763
  • I/O Timings: read=1,730.567
34. 1,876.478 1,876.478 ↑ 1.0 231,540 1

Index Scan using cva_account_number_idx on pg_temp_91.cte_vision_account va_1 (cost=0.42..14,630.99 rows=231,540 width=16) (actual time=0.033..1,876.478 rows=231,540 loops=1)

  • 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)
  • Buffers: local hit=41,776 read=11,763
  • I/O Timings: read=1,730.567
35. 1,000,111.172 3,303,014.001 ↓ 24,997.1 143,758,241 1

Sort (cost=980,717.80..980,732.18 rows=5,751 width=251) (actual time=3,188,154.856..3,303,014.001 rows=143,758,241 loops=1)

  • Output: va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.load_date, va.vsn_cust_type, va.tcms_cust_type, va.parent_account, va.market_code, va.alternate_name_id, va.customer_type, ae_1.tax_id, ae_1.account_number, 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.account_number, vae.geolvl2
  • Sort Key: va.account_number
  • Sort Method: external sort Disk: 36,167,952kB
  • Buffers: shared hit=44,750,415, local hit=1,105,569,285 read=1,686,783, temp read=9,692,865 written=9,692,865
  • I/O Timings: read=5,900.241
36. 82,118.556 2,302,902.829 ↓ 24,997.1 143,758,241 1

Nested Loop (cost=2.10..980,358.66 rows=5,751 width=251) (actual time=0.933..2,302,902.829 rows=143,758,241 loops=1)

  • Output: va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.load_date, va.vsn_cust_type, va.tcms_cust_type, va.parent_account, va.market_code, va.alternate_name_id, va.customer_type, ae_1.tax_id, ae_1.account_number, 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.account_number, vae.geolvl2
  • Join Filter: (((ae_1.account_number)::text = (va.account_number)::text) AND ((((ae_1.tax_id)::text = 'FFFEXC0001'::text) AND (va.fed_cert_required = 'N'::bpchar)) OR ((substr((ae_1.tax_id)::text, 1, 1) = ANY ('{S,L}'::text[])) AND (va.state_cert_required = 'N'::bpchar))))
  • Rows Removed by Join Filter: 11,056,173
  • Buffers: shared hit=44,750,415, local hit=1,105,569,285 read=1,686,783
  • I/O Timings: read=5,900.241
37. 81,997.037 320,139.565 ↓ 53,836.5 316,774,118 1

Nested Loop (cost=1.68..977,617.38 rows=5,884 width=134) (actual time=0.123..320,139.565 rows=316,774,118 loops=1)

  • Output: ae_1.tax_id, ae_1.account_number, 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.account_number, vae.geolvl2
  • Buffers: shared hit=44,750,415
38. 1,174.793 8,594.860 ↓ 5,299.4 8,198,131 1

Nested Loop (cost=1.11..928,436.81 rows=1,547 width=85) (actual time=0.092..8,594.860 rows=8,198,131 loops=1)

  • Output: ae_1.tax_id, ae_1.account_number, t_2.tax_id, t_1.tax_name, t_1.state, t_1.tax_id, t_1.vision_tax_code
  • Buffers: shared hit=838,239
39. 1.613 7.133 ↓ 1.0 582 1

Nested Loop (cost=0.55..263.71 rows=581 width=60) (actual time=0.049..7.133 rows=582 loops=1)

  • Output: t_2.tax_id, t_1.tax_name, t_1.state, t_1.tax_id, t_1.vision_tax_code
  • Buffers: shared hit=1,451
40. 1.446 1.446 ↑ 1.0 582 1

Index Scan using tax_state_idx on dbotcms.tax t_1 (cost=0.28..66.20 rows=582 width=50) (actual time=0.023..1.446 rows=582 loops=1)

  • 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
  • Filter: ((t_1.tax_id)::text <> 'LCAUUT0001'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=280
41. 4.074 4.074 ↑ 1.0 1 582

Index Only Scan using tax_pk on dbotcms.tax t_2 (cost=0.28..0.33 rows=1 width=10) (actual time=0.006..0.007 rows=1 loops=582)

  • Output: t_2.tax_id
  • Index Cond: (t_2.tax_id = (t_1.tax_id)::text)
  • Filter: ((t_2.tax_id)::text <> 'LCAUUT0001'::text)
  • Heap Fetches: 0
  • Buffers: shared hit=1,171
42. 7,412.934 7,412.934 ↓ 4,695.3 14,086 582

Index Only Scan using account_exemption_ix5 on dbotcms.account_exemption ae_1 (cost=0.56..1,597.51 rows=3 width=25) (actual time=0.037..12.737 rows=14,086 loops=582)

  • Output: ae_1.tax_id, ae_1.status, ae_1.document_id, ae_1.account_number
  • Index Cond: ((ae_1.tax_id = (t_2.tax_id)::text) AND (ae_1.status = 'A'::text) AND (ae_1.document_id IS NULL))
  • 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[]))))
  • Rows Removed by Filter: 39
  • Heap Fetches: 0
  • Buffers: shared hit=836,788
43. 229,547.668 229,547.668 ↓ 2.8 39 8,198,131

Index Scan using vision_account_exemption_ix4 on dbotcms.vision_account_exemption vae (cost=0.56..31.65 rows=14 width=55) (actual time=0.014..0.028 rows=39 loops=8,198,131)

  • 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))
  • Buffers: shared hit=43,912,176
44. 1,900,644.708 1,900,644.708 ↓ 0.0 0 316,774,118

Index Scan using cva_account_number_idx on pg_temp_91.cte_vision_account va (cost=0.42..0.44 rows=1 width=121) (actual time=0.006..0.006 rows=0 loops=316,774,118)

  • Output: va.vision_inst, va.ecpd_profile_id, va.account_number, va.business_name, va.vsn_cust_type, va.load_date, va.parent_account, va.market_code, va.tcms_cust_type, va.alternate_name_id, va.fed_cert_required, va.state_cert_required, va.customer_type, va.cust_id, va.acct_num
  • Index Cond: ((va.account_number)::text = (vae.account_number)::text)
  • Filter: ((va.customer_type)::text <> 'FGA'::text)
  • Buffers: local hit=1,105,569,285 read=1,686,783
  • I/O Timings: read=5,900.241
45. 0.998 1.700 ↑ 1.0 4,795 1

Hash (cost=85.95..85.95 rows=4,795 width=24) (actual time=1.700..1.700 rows=4,795 loops=1)

  • Output: eis.market_name, eis.market_code
  • Buckets: 8,192 Batches: 1 Memory Usage: 329kB
  • Buffers: shared hit=38
46. 0.702 0.702 ↑ 1.0 4,795 1

Seq Scan on dbotcms.eis_market eis (cost=0.00..85.95 rows=4,795 width=24) (actual time=0.003..0.702 rows=4,795 loops=1)

  • Output: eis.market_name, eis.market_code
  • Buffers: shared hit=38
47. 7.183 12.460 ↑ 1.0 45,859 1

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

  • Output: an.description, an.alternate_name_id
  • Buckets: 65,536 Batches: 1 Memory Usage: 3,197kB
  • Buffers: shared hit=327
48. 5.277 5.277 ↑ 1.0 45,859 1

Seq Scan on dbotcms.alternate_name an (cost=0.00..785.59 rows=45,859 width=26) (actual time=0.007..5.277 rows=45,859 loops=1)

  • Output: an.description, an.alternate_name_id
  • Buffers: shared hit=327
49. 0.012 0.023 ↑ 1.0 52 1

Hash (cost=1.52..1.52 rows=52 width=6) (actual time=0.023..0.023 rows=52 loops=1)

  • Output: s.geolvl2, s.state
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
50. 0.011 0.011 ↑ 1.0 52 1

Seq Scan on dbotcms.state s (cost=0.00..1.52 rows=52 width=6) (actual time=0.003..0.011 rows=52 loops=1)

  • Output: s.geolvl2, s.state
  • Buffers: shared hit=1
51. 21,094.884 21,094.884 ↓ 0.0 0 5,273,721

Index Scan using exe_master_pk on dbotcms.exemption_master em (cost=0.29..1.60 rows=1 width=14) (actual time=0.004..0.004 rows=0 loops=5,273,721)

  • 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.customer_type)::text))
  • Filter: ((em.exemption_type)::text = ANY ('{Y,YA}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=15,799,474
52. 19.366 39,751.151 ↑ 422.5 8,845 1

Hash Join (cost=15,607.03..569,287.37 rows=3,736,923 width=380) (actual time=21,836.788..39,751.151 rows=8,845 loops=1)

  • Output: 'EXPIRED'::text, va_2.vision_inst, va_2.ecpd_profile_id, va_2.account_number, va_2.business_name, va_2.load_date, (((eis_1.market_name)::text || '-'::text) || (eis_1.market_code)::text), an_1.description, va_2.vsn_cust_type, va_2.tcms_cust_type, va_2.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_2.account_number)::text, 1, (length((va_2.account_number)::text) - 5)), ltrim(substr((va_2.account_number)::text, (length((va_2.account_number)::text) - 4), 5), '0'::text)
  • Hash Cond: ((ae_2.account_number)::text = (va_2.account_number)::text)
  • Buffers: shared hit=1,073,274, local hit=41,910 read=16,238, temp read=75,180 written=117,948
  • I/O Timings: read=139.977
53. 4.862 39,378.202 ↑ 422.5 8,845 1

Hash Join (cost=23.56..418,240.44 rows=3,736,923 width=173) (actual time=21,482.067..39,378.202 rows=8,845 loops=1)

  • 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)
  • Buffers: shared hit=1,072,909, local hit=41,896 read=11,643, temp read=75,180 written=117,948
  • I/O Timings: read=43.272
54. 39,373.060 39,373.060 ↑ 423.2 8,845 1

CTE Scan on cte_account_exemption ae_2 (cost=0.00..366,810.11 rows=3,743,344 width=174) (actual time=21,481.771..39,373.060 rows=8,845 loops=1)

  • 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))))
  • Rows Removed by Filter: 4,731,926
  • Buffers: shared hit=1,072,900, local hit=41,896 read=11,643, temp read=75,180 written=117,948
  • I/O Timings: read=43.272
55. 0.166 0.280 ↑ 1.0 582 1

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

  • Output: t_3.tax_name, t_3.state, t_3.tax_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
  • Buffers: shared hit=9
56. 0.114 0.114 ↑ 1.0 582 1

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

  • Output: t_3.tax_name, t_3.state, t_3.tax_id
  • Filter: ((t_3.tax_id)::text <> 'LCAUUT0001'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=9
57. 106.630 353.583 ↑ 1.0 231,540 1

Hash (cost=12,689.21..12,689.21 rows=231,540 width=151) (actual time=353.583..353.583 rows=231,540 loops=1)

  • Output: va_2.vision_inst, va_2.ecpd_profile_id, va_2.account_number, va_2.business_name, va_2.load_date, va_2.vsn_cust_type, va_2.tcms_cust_type, va_2.parent_account, an_1.description, eis_1.market_name, eis_1.market_code
  • Buckets: 262,144 Batches: 1 Memory Usage: 38,158kB
  • Buffers: shared hit=365, local hit=14 read=4,595
  • I/O Timings: read=96.705
58. 60.822 246.953 ↑ 1.0 231,540 1

Hash Left Join (cost=1,504.72..12,689.21 rows=231,540 width=151) (actual time=15.725..246.953 rows=231,540 loops=1)

  • Output: va_2.vision_inst, va_2.ecpd_profile_id, va_2.account_number, va_2.business_name, va_2.load_date, va_2.vsn_cust_type, va_2.tcms_cust_type, va_2.parent_account, an_1.description, eis_1.market_name, eis_1.market_code
  • Hash Cond: ((va_2.market_code)::text = (eis_1.market_code)::text)
  • Buffers: shared hit=365, local hit=14 read=4,595
  • I/O Timings: read=96.705
59. 55.137 184.646 ↑ 1.0 231,540 1

Hash Left Join (cost=1,358.83..9,359.65 rows=231,540 width=131) (actual time=14.185..184.646 rows=231,540 loops=1)

  • Output: va_2.vision_inst, va_2.ecpd_profile_id, va_2.account_number, va_2.business_name, va_2.load_date, va_2.vsn_cust_type, va_2.tcms_cust_type, va_2.parent_account, va_2.market_code, an_1.description
  • Hash Cond: (va_2.alternate_name_id = an_1.alternate_name_id)
  • Buffers: shared hit=327, local hit=14 read=4,595
  • I/O Timings: read=96.705
60. 115.637 115.637 ↑ 1.0 231,540 1

Seq Scan on pg_temp_91.cte_vision_account va_2 (cost=0.00..6,924.40 rows=231,540 width=113) (actual time=0.009..115.637 rows=231,540 loops=1)

  • 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
  • Buffers: local hit=14 read=4,595
  • I/O Timings: read=96.705
61. 8.913 13.872 ↑ 1.0 45,859 1

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

  • Output: an_1.description, an_1.alternate_name_id
  • Buckets: 65,536 Batches: 1 Memory Usage: 3,197kB
  • Buffers: shared hit=327
62. 4.959 4.959 ↑ 1.0 45,859 1

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

  • Output: an_1.description, an_1.alternate_name_id
  • Buffers: shared hit=327
63. 0.941 1.485 ↑ 1.0 4,795 1

Hash (cost=85.95..85.95 rows=4,795 width=24) (actual time=1.485..1.485 rows=4,795 loops=1)

  • Output: eis_1.market_name, eis_1.market_code
  • Buckets: 8,192 Batches: 1 Memory Usage: 329kB
  • Buffers: shared hit=38
64. 0.544 0.544 ↑ 1.0 4,795 1

Seq Scan on dbotcms.eis_market eis_1 (cost=0.00..85.95 rows=4,795 width=24) (actual time=0.003..0.544 rows=4,795 loops=1)

  • Output: eis_1.market_name, eis_1.market_code
  • Buffers: shared hit=38
65. 6.137 700.860 ↓ 1.1 2,331 1

Nested Loop Left Join (cost=10,275.58..492,784.64 rows=2,129 width=380) (actual time=156.867..700.860 rows=2,331 loops=1)

  • 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_3.vision_inst, va_3.ecpd_profile_id, va_3.account_number, va_3.business_name, va_3.load_date, (((eis_2.market_name)::text || '-'::text) || (eis_2.market_code)::text), an_2.description, va_3.vsn_cust_type, va_3.tcms_cust_type, va_3.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_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)
  • Buffers: shared hit=7,955, local hit=2 read=4,607, temp read=42,770 written=1
  • I/O Timings: read=12.913
66. 1.919 687.730 ↓ 1.1 2,331 1

Nested Loop Left Join (cost=10,275.30..492,000.06 rows=2,129 width=256) (actual time=156.821..687.730 rows=2,331 loops=1)

  • 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, 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
  • Buffers: shared hit=961, local hit=2 read=4,607, temp read=42,770 written=1
  • I/O Timings: read=12.913
67. 1.499 685.811 ↓ 1.1 2,331 1

Hash Join (cost=10,275.01..491,304.61 rows=2,129 width=238) (actual time=156.783..685.811 rows=2,331 loops=1)

  • 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.alternate_name_id, va_3.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_3.customer_type)::text = (em_1.customer_type)::text))
  • Buffers: shared hit=265, local hit=2 read=4,607, temp read=42,770 written=1
  • I/O Timings: read=12.913
68. 3.005 682.971 ↑ 13.5 4,172 1

Hash Join (cost=9,818.65..490,265.48 rows=56,150 width=243) (actual time=154.155..682.971 rows=4,172 loops=1)

  • 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.alternate_name_id, va_3.market_code, va_3.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: ((ae_3.account_number)::text = (va_3.account_number)::text)
  • Buffers: local hit=2 read=4,607, temp read=42,770 written=1
  • I/O Timings: read=12.913
69. 527.684 527.684 ↑ 13.5 4,172 1

CTE Scan on cte_account_exemption ae_3 (cost=0.00..479,674.76 rows=56,150 width=174) (actual time=1.641..527.684 rows=4,172 loops=1)

  • Output: ae_3.account_number, ae_3.tax_id, ae_3.exemption_source, ae_3.document_id, ae_3.document_source, ae_3.assigned_date, ae_3.tx_xmpt_tmpl_id, ae_3.tx_xmpt_add_type, ae_3.tx_xmpt_dup_ind, ae_3.vision_inst, ae_3.expire_date, ae_3.vision_user_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))))
  • Rows Removed by Filter: 4,736,599
  • Buffers: temp read=42,770 written=1
70. 94.518 152.282 ↑ 1.0 231,540 1

Hash (cost=6,924.40..6,924.40 rows=231,540 width=117) (actual time=152.282..152.282 rows=231,540 loops=1)

  • 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.alternate_name_id, va_3.market_code, va_3.customer_type
  • Buckets: 262,144 Batches: 1 Memory Usage: 35,092kB
  • Buffers: local hit=2 read=4,607
  • I/O Timings: read=12.913
71. 57.764 57.764 ↑ 1.0 231,540 1

Seq Scan on pg_temp_91.cte_vision_account va_3 (cost=0.00..6,924.40 rows=231,540 width=117) (actual time=0.006..57.764 rows=231,540 loops=1)

  • 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.alternate_name_id, va_3.market_code, va_3.customer_type
  • Buffers: local hit=2 read=4,607
  • I/O Timings: read=12.913
72. 0.272 1.341 ↑ 1.0 880 1

Hash (cost=443.15..443.15 rows=881 width=61) (actual time=1.341..1.341 rows=880 loops=1)

  • Output: t_4.tax_name, t_4.state, t_4.tax_id, em_1.tax_id, em_1.customer_type
  • Buckets: 1,024 Batches: 1 Memory Usage: 92kB
  • Buffers: shared hit=265
73. 0.239 1.069 ↑ 1.0 880 1

Hash Join (cost=46.99..443.15 rows=881 width=61) (actual time=0.481..1.069 rows=880 loops=1)

  • 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)
  • Buffers: shared hit=265
74. 0.393 0.564 ↑ 1.0 883 1

Bitmap Heap Scan on dbotcms.exemption_master em_1 (cost=23.43..407.46 rows=883 width=14) (actual time=0.202..0.564 rows=883 loops=1)

  • 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[]))
  • Heap Blocks: exact=247
  • Buffers: shared hit=256
75. 0.171 0.171 ↑ 1.0 883 1

Bitmap Index Scan on exe_master_exe_type_fk_ix5 (cost=0.00..23.20 rows=883 width=0) (actual time=0.171..0.171 rows=883 loops=1)

  • Index Cond: ((em_1.exemption_type)::text = ANY ('{Y,YA}'::text[]))
  • Buffers: shared hit=9
76. 0.145 0.266 ↑ 1.0 582 1

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

  • Output: t_4.tax_name, t_4.state, t_4.tax_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
  • Buffers: shared hit=9
77. 0.121 0.121 ↑ 1.0 582 1

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

  • Output: t_4.tax_name, t_4.state, t_4.tax_id
  • Filter: ((t_4.tax_id)::text <> 'LCAUUT0001'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=9
78. 0.000 0.000 ↓ 0.0 0 2,331

Index Scan using alternate_name_pk on dbotcms.alternate_name an_2 (cost=0.29..0.32 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=2,331)

  • Output: an_2.alternate_name_id, an_2.description
  • Index Cond: (va_3.alternate_name_id = an_2.alternate_name_id)
  • Buffers: shared hit=696
79. 6.993 6.993 ↑ 1.0 1 2,331

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

  • 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_3.market_code)::text = (eis_2.market_code)::text)
  • Buffers: shared hit=6,994
80. 3,499,690.371 3,499,690.371 ↑ 1.3 29,872 1

CTE Scan on missing_expired a (cost=0.00..1,682.60 rows=37,391 width=618) (actual time=3,499,637.479..3,499,690.371 rows=29,872 loops=1)

  • 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)
  • Buffers: shared hit=61,631,490, local hit=1,105,652,973 read=1,719,391, temp read=9,810,815 written=9,810,814
  • I/O Timings: read=7,783.698
Planning time : 33.326 ms
Execution time : 3,501,196.496 ms