explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nU7y

Settings
# exclusive inclusive rows x rows loops node
1. 68.556 251,424.388 ↑ 2.2 13,730 1

Sort (cost=7,852,274.62..7,852,349.69 rows=30,028 width=618) (actual time=251,422.550..251,424.388 rows=13,730 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: 4,026kB
  • Buffers: shared hit=29,033,185, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
2.          

CTE cte_account_exemption

3. 979.760 54,564.568 ↑ 1.6 5,630,598 1

Hash Left Join (cost=2,817,259.73..3,164,471.10 rows=9,063,869 width=81) (actual time=42,178.851..54,564.568 rows=5,630,598 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=768,955, local hit=57,673 read=171,210, temp read=82,949 written=82,949
  • I/O Timings: read=493.418
4. 1,458.291 53,512.668 ↑ 1.6 5,630,598 1

Hash Join (cost=2,806,924.34..3,118,834.96 rows=9,063,869 width=73) (actual time=42,105.853..53,512.668 rows=5,630,598 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=764,332, local hit=57,673 read=171,210, temp read=82,949 written=82,949
  • I/O Timings: read=493.418
5. 1,883.642 52,054.165 ↑ 1.6 5,638,931 1

Merge Join (cost=2,806,900.80..2,994,124.72 rows=9,079,469 width=73) (actual time=42,105.620..52,054.165 rows=5,638,931 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=764,323, local hit=57,673 read=171,210, temp read=82,949 written=82,949
  • I/O Timings: read=493.418
6. 59.016 688.444 ↑ 1.0 228,079 1

Group (cost=0.42..25,566.64 rows=228,079 width=16) (actual time=0.042..688.444 rows=228,079 loops=1)

  • Output: cte_vision_account.account_number
  • Group Key: cte_vision_account.account_number
  • Buffers: local hit=57,673 read=171,210
  • I/O Timings: read=493.418
7. 629.428 629.428 ↑ 1.0 228,079 1

Index Only Scan using cva_account_number_idx on pg_temp_136.cte_vision_account (cost=0.42..24,996.44 rows=228,079 width=16) (actual time=0.042..629.428 rows=228,079 loops=1)

  • Output: cte_vision_account.account_number
  • Heap Fetches: 228,079
  • Buffers: local hit=57,673 read=171,210
  • I/O Timings: read=493.418
8. 969.699 49,482.079 ↓ 1.0 9,104,116 1

Materialize (cost=2,806,900.38..2,852,129.74 rows=9,045,871 width=73) (actual time=42,105.572..49,482.079 rows=9,104,116 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=764,323, temp read=82,949 written=82,949
9. 41,059.133 48,512.380 ↓ 1.0 9,104,116 1

Sort (cost=2,806,900.38..2,829,515.06 rows=9,045,871 width=73) (actual time=42,105.566..48,512.380 rows=9,104,116 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: 663,400kB
  • Buffers: shared hit=764,323, temp read=82,949 written=82,949
10. 7,453.247 7,453.247 ↓ 1.0 9,105,555 1

Seq Scan on dbotcms.account_exemption ae (cost=0.00..1,359,759.50 rows=9,045,871 width=73) (actual time=0.007..7,453.247 rows=9,105,555 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
  • Filter: ((ae.status)::text = 'A'::text)
  • Rows Removed by Filter: 38,529,365
  • Buffers: shared hit=764,323
11. 0.110 0.212 ↑ 1.0 581 1

Hash (cost=16.27..16.27 rows=581 width=10) (actual time=0.211..0.212 rows=581 loops=1)

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

Seq Scan on dbotcms.tax t (cost=0.00..16.27 rows=581 width=10) (actual time=0.014..0.102 rows=581 loops=1)

  • Output: t.tax_id
  • Filter: ((t.tax_id)::text <> 'LCAUUT0001'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=9
13. 38.481 72.140 ↑ 1.0 253,884 1

Hash (cost=7,161.84..7,161.84 rows=253,884 width=16) (actual time=72.140..72.140 rows=253,884 loops=1)

  • Output: d.expire_date, d.document_id
  • Buckets: 262,144 Batches: 1 Memory Usage: 13,389kB
  • Buffers: shared hit=4,623
14. 33.659 33.659 ↑ 1.0 253,884 1

Seq Scan on dbotcms.document d (cost=0.00..7,161.84 rows=253,884 width=16) (actual time=0.010..33.659 rows=253,884 loops=1)

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

CTE missing_expired

16. 9.188 251,333.540 ↑ 2.2 13,730 1

Sort (cost=4,684,144.00..4,684,219.07 rows=30,028 width=442) (actual time=251,332.224..251,333.540 rows=13,730 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: 2,892kB
  • Buffers: shared hit=29,033,185, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
17. 10.898 251,324.352 ↑ 2.2 13,730 1

WindowAgg (cost=4,680,184.21..4,681,910.82 rows=30,028 width=442) (actual time=251,312.046..251,324.352 rows=13,730 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=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
18. 39.931 251,313.454 ↑ 2.2 13,730 1

Sort (cost=4,680,184.21..4,680,259.28 rows=30,028 width=404) (actual time=251,312.021..251,313.454 rows=13,730 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: 2,589kB
  • Buffers: shared hit=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
19. 1.556 251,273.523 ↑ 2.2 13,730 1

Subquery Scan on c (cost=4,658,733.40..4,677,951.02 rows=30,028 width=404) (actual time=251,258.353..251,273.523 rows=13,730 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=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
20. 12.527 251,271.967 ↑ 2.2 13,730 1

GroupAggregate (cost=4,658,733.40..4,677,650.74 rows=30,028 width=404) (actual time=251,258.352..251,271.967 rows=13,730 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=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
21. 53.430 251,259.440 ↑ 21.9 13,730 1

Sort (cost=4,658,733.40..4,659,484.09 rows=300,275 width=404) (actual time=251,258.332..251,259.440 rows=13,730 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
  • 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
  • Sort Method: quicksort Memory: 2,771kB
  • Buffers: shared hit=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
22. 6.638 251,206.010 ↑ 21.9 13,730 1

Subquery Scan on b (cost=4,509,803.08..4,631,414.49 rows=300,275 width=404) (actual time=251,193.122..251,206.010 rows=13,730 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=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
23. 5.122 251,199.372 ↑ 21.9 13,730 1

Group (cost=4,509,803.08..4,626,159.68 rows=300,275 width=436) (actual time=251,193.107..251,199.372 rows=13,730 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=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
24. 58.496 251,194.250 ↑ 210.7 14,254 1

Sort (cost=4,509,803.08..4,517,309.96 rows=3,002,751 width=436) (actual time=251,193.105..251,194.250 rows=14,254 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: 2,938kB
  • Buffers: shared hit=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
25. 6.032 251,135.754 ↑ 210.7 14,254 1

Subquery Scan on a_1 (cost=3,373,766.86..3,591,466.30 rows=3,002,751 width=436) (actual time=251,123.582..251,135.754 rows=14,254 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=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
26. 4.208 251,129.722 ↑ 210.7 14,254 1

Unique (cost=3,373,766.86..3,523,904.41 rows=3,002,751 width=888) (actual time=251,123.563..251,129.722 rows=14,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=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
27. 87.648 251,125.514 ↑ 209.8 14,315 1

Sort (cost=3,373,766.86..3,381,273.73 rows=3,002,751 width=888) (actual time=251,123.562..251,125.514 rows=14,315 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: 5,434kB
  • Buffers: shared hit=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
28. 2.274 251,037.866 ↑ 209.8 14,315 1

Append (cost=921,168.19..1,880,684.58 rows=3,002,751 width=888) (actual time=47,257.910..251,037.866 rows=14,315 loops=1)

  • Buffers: shared hit=29,033,182, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
29. 87.532 192,248.684 ↓ 1,199.0 2,398 1

Nested Loop (cost=921,168.19..993,002.68 rows=2 width=326) (actual time=47,257.910..192,248.684 rows=2,398 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=28,262,509, local hit=21,774,235 read=171,600, temp read=112,626 written=112,631
  • I/O Timings: read=535.693
30. 1,585.567 189,500.922 ↓ 10,432.3 532,046 1

Hash Join (cost=921,167.90..992,931.56 rows=51 width=256) (actual time=47,170.045..189,500.922 rows=532,046 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=26,660,713, local hit=21,774,235 read=171,600, temp read=112,626 written=112,631
  • I/O Timings: read=535.693
31. 3,437.811 187,915.336 ↓ 4,229.6 11,259,309 1

Hash Join (cost=921,165.60..992,908.79 rows=2,662 width=259) (actual time=47,170.005..187,915.336 rows=11,259,309 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.market_code)::text = (eis.market_code)::text)
  • Buffers: shared hit=26,660,712, local hit=21,774,235 read=171,600, temp read=112,626 written=112,631
  • I/O Timings: read=535.693
32. 14,428.740 184,476.083 ↓ 4,229.6 11,259,309 1

Hash Join (cost=921,019.98..992,726.57 rows=2,662 width=239) (actual time=47,168.527..184,476.083 rows=11,259,309 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.customer_type, 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: (((ae_1.tax_id)::text = (t_1.tax_id)::text) AND ((vae.tax_code)::text = (t_1.vision_tax_code)::text))
  • Buffers: shared hit=26,660,674, local hit=21,774,235 read=171,600, temp read=112,626 written=112,631
  • I/O Timings: read=535.693
33. 31,941.172 170,047.077 ↓ 1,028.7 101,358,215 1

Nested Loop (cost=920,994.99..991,935.99 rows=98,529 width=195) (actual time=47,168.238..170,047.077 rows=101,358,215 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.customer_type, an.description, ae_1.tax_id, t_2.tax_id, vae.tx_xmpt_add_type, vae.vision_user_id, vae.effective_date, vae.end_date, vae.tax_code, vae.geolvl2
  • Join Filter: ((ae_1.account_number)::text = (vae.account_number)::text)
  • Buffers: shared hit=26,660,665, local hit=21,774,235 read=171,600, temp read=112,626 written=112,631
  • I/O Timings: read=535.693
34. 2,393.807 88,331.917 ↓ 2,563.7 4,524,908 1

Nested Loop Left Join (cost=920,994.43..951,604.76 rows=1,765 width=187) (actual time=47,168.206..88,331.917 rows=4,524,908 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.customer_type, an.description, ae_1.tax_id, ae_1.account_number, t_2.tax_id, va_1.account_number
  • Buffers: shared hit=7,694,573, local hit=21,774,235 read=171,600, temp read=112,626 written=112,631
  • I/O Timings: read=535.693
35. 2,376.007 85,938.110 ↓ 2,563.7 4,524,908 1

Nested Loop (cost=920,994.14..951,027.92 rows=1,765 width=169) (actual time=47,168.204..85,938.110 rows=4,524,908 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, va_1.account_number
  • 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: 887,742
  • Buffers: shared hit=7,239,733, local hit=21,774,235 read=171,600, temp read=112,626 written=112,631
  • I/O Timings: read=535.693
36. 1,694.910 51,086.203 ↓ 2,948.1 5,412,650 1

Merge Join (cost=920,993.72..950,000.06 rows=1,836 width=51) (actual time=47,167.594..51,086.203 rows=5,412,650 loops=1)

  • Output: ae_1.tax_id, ae_1.account_number, t_2.tax_id, va_1.account_number
  • Merge Cond: ((va_1.account_number)::text = (ae_1.account_number)::text)
  • Buffers: shared hit=7,239,733, local hit=57,877 read=171,006, temp read=112,626 written=112,631
  • I/O Timings: read=533.562
37. 65.234 790.208 ↓ 1.0 228,079 1

Group (cost=0.42..26,135.40 rows=227,505 width=16) (actual time=0.031..790.208 rows=228,079 loops=1)

  • Output: va_1.account_number
  • Group Key: va_1.account_number
  • Buffers: local hit=57,877 read=171,006
  • I/O Timings: read=533.562
38. 724.974 724.974 ↓ 1.0 228,079 1

Index Scan using cva_account_number_idx on pg_temp_136.cte_vision_account va_1 (cost=0.42..25,566.64 rows=227,505 width=16) (actual time=0.029..724.974 rows=228,079 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=57,877 read=171,006
  • I/O Timings: read=533.562
39. 41,424.849 48,601.085 ↓ 4,836.9 8,870,908 1

Sort (cost=920,993.30..920,997.89 rows=1,834 width=35) (actual time=47,167.538..48,601.085 rows=8,870,908 loops=1)

  • Output: ae_1.tax_id, ae_1.account_number, t_2.tax_id
  • Sort Key: ae_1.account_number
  • Sort Method: external sort Disk: 450,488kB
  • Buffers: shared hit=7,239,733, temp read=112,626 written=112,631
40. 980.610 7,176.236 ↓ 4,837.4 8,871,723 1

Nested Loop (cost=0.56..920,893.90 rows=1,834 width=35) (actual time=0.102..7,176.236 rows=8,871,723 loops=1)

  • Output: ae_1.tax_id, ae_1.account_number, t_2.tax_id
  • Buffers: shared hit=7,239,733
41. 0.423 0.423 ↑ 1.0 581 1

Seq Scan on dbotcms.tax t_2 (cost=0.00..16.27 rows=581 width=10) (actual time=0.003..0.423 rows=581 loops=1)

  • Output: t_2.tax_id, t_2.state, t_2.tax_type, t_2.jurisdiction, t_2.tax_name, t_2.tax_seller_user, t_2.vision_tax_code, t_2.request_text, t_2.termination_text, t_2.renewal_text, t_2.tems_enabled, t_2.eligible_text, t_2.exemption_life, t_2.tax_group_tax_id, t_2.tax_phase, t_2.tax_sequence
  • Filter: ((t_2.tax_id)::text <> 'LCAUUT0001'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=9
42. 6,195.203 6,195.203 ↓ 5,090.0 15,270 581

Index Only Scan using account_exemption_ix5 on dbotcms.account_exemption ae_1 (cost=0.56..1,584.96 rows=3 width=25) (actual time=0.066..10.663 rows=15,270 loops=581)

  • 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: 83
  • Heap Fetches: 0
  • Buffers: shared hit=7,239,724
43. 32,475.900 32,475.900 ↑ 1.0 1 5,412,650

Index Scan using cva_account_number_idx on pg_temp_136.cte_vision_account va (cost=0.42..0.53 rows=1 width=122) (actual time=0.005..0.006 rows=1 loops=5,412,650)

  • 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 = (va_1.account_number)::text)
  • Filter: ((va.customer_type)::text <> 'FGA'::text)
  • Buffers: local hit=21,716,358 read=594
  • I/O Timings: read=2.131
44. 0.000 0.000 ↓ 0.0 0 4,524,908

Index Scan using alternate_name_pk on dbotcms.alternate_name an (cost=0.29..0.32 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=4,524,908)

  • Output: an.alternate_name_id, an.description
  • Index Cond: (va.alternate_name_id = an.alternate_name_id)
  • Buffers: shared hit=454,840
45. 49,773.988 49,773.988 ↑ 26.5 22 4,524,908

Index Scan using vision_account_exemption_ix1 on dbotcms.vision_account_exemption vae (cost=0.56..15.58 rows=582 width=55) (actual time=0.007..0.011 rows=22 loops=4,524,908)

  • 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 = (va_1.account_number)::text)
  • Buffers: shared hit=18,966,092
46. 0.140 0.266 ↑ 1.0 581 1

Hash (cost=16.27..16.27 rows=581 width=50) (actual time=0.266..0.266 rows=581 loops=1)

  • Output: t_1.tax_name, t_1.state, t_1.tax_id, t_1.vision_tax_code
  • Buckets: 1,024 Batches: 1 Memory Usage: 56kB
  • Buffers: shared hit=9
47. 0.126 0.126 ↑ 1.0 581 1

Seq Scan on dbotcms.tax t_1 (cost=0.00..16.27 rows=581 width=50) (actual time=0.005..0.126 rows=581 loops=1)

  • Output: t_1.tax_name, t_1.state, t_1.tax_id, t_1.vision_tax_code
  • Filter: ((t_1.tax_id)::text <> 'LCAUUT0001'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=9
48. 0.749 1.442 ↑ 1.0 4,783 1

Hash (cost=85.83..85.83 rows=4,783 width=24) (actual time=1.442..1.442 rows=4,783 loops=1)

  • Output: eis.market_name, eis.market_code
  • Buckets: 8,192 Batches: 1 Memory Usage: 328kB
  • Buffers: shared hit=38
49. 0.693 0.693 ↑ 1.0 4,783 1

Seq Scan on dbotcms.eis_market eis (cost=0.00..85.83 rows=4,783 width=24) (actual time=0.004..0.693 rows=4,783 loops=1)

  • Output: eis.market_name, eis.market_code
  • Buffers: shared hit=38
50. 0.008 0.019 ↑ 1.0 52 1

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

  • Output: s.geolvl2, s.state
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
51. 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.002..0.011 rows=52 loops=1)

  • Output: s.geolvl2, s.state
  • Buffers: shared hit=1
52. 2,660.230 2,660.230 ↓ 0.0 0 532,046

Index Scan using exe_master_pk on dbotcms.exemption_master em (cost=0.29..1.38 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=532,046)

  • 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=1,601,796
53. 26.035 58,081.350 ↑ 259.2 11,578 1

Hash Join (cost=15,406.72..460,052.73 rows=3,001,018 width=381) (actual time=42,438.016..58,081.350 rows=11,578 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=769,332, local hit=57,754 read=175,642, temp read=82,949 written=135,394
  • I/O Timings: read=504.501
54. 5.464 57,799.773 ↑ 259.2 11,578 1

Hash Join (cost=23.54..335,882.65 rows=3,001,018 width=173) (actual time=42,181.600..57,799.773 rows=11,578 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=768,964, local hit=57,673 read=171,210, temp read=82,949 written=135,394
  • I/O Timings: read=493.418
55. 57,794.067 57,794.067 ↑ 259.6 11,578 1

CTE Scan on cte_account_exemption ae_2 (cost=0.00..294,575.74 rows=3,006,183 width=174) (actual time=42,181.338..57,794.067 rows=11,578 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: 5,619,020
  • Buffers: shared hit=768,955, local hit=57,673 read=171,210, temp read=82,949 written=135,394
  • I/O Timings: read=493.418
56. 0.113 0.242 ↑ 1.0 581 1

Hash (cost=16.27..16.27 rows=581 width=47) (actual time=0.242..0.242 rows=581 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
57. 0.129 0.129 ↑ 1.0 581 1

Seq Scan on dbotcms.tax t_3 (cost=0.00..16.27 rows=581 width=47) (actual time=0.009..0.129 rows=581 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
58. 97.174 255.542 ↑ 1.0 228,079 1

Hash (cost=12,532.19..12,532.19 rows=228,079 width=152) (actual time=255.542..255.542 rows=228,079 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: 37,359kB
  • Buffers: shared hit=368, local hit=81 read=4,432
  • I/O Timings: read=11.083
59. 60.183 158.368 ↑ 1.0 228,079 1

Hash Left Join (cost=1,504.42..12,532.19 rows=228,079 width=152) (actual time=14.812..158.368 rows=228,079 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=368, local hit=81 read=4,432
  • I/O Timings: read=11.083
60. 53.658 96.764 ↑ 1.0 228,079 1

Hash Left Join (cost=1,358.80..9,250.49 rows=228,079 width=132) (actual time=13.343..96.764 rows=228,079 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=330, local hit=81 read=4,432
  • I/O Timings: read=11.083
61. 30.013 30.013 ↑ 1.0 228,079 1

Seq Scan on pg_temp_136.cte_vision_account va_2 (cost=0.00..6,793.79 rows=228,079 width=114) (actual time=0.006..30.013 rows=228,079 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=81 read=4,432
  • I/O Timings: read=11.083
62. 8.035 13.093 ↑ 1.0 45,858 1

Hash (cost=785.58..785.58 rows=45,858 width=26) (actual time=13.093..13.093 rows=45,858 loops=1)

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

Seq Scan on dbotcms.alternate_name an_1 (cost=0.00..785.58 rows=45,858 width=26) (actual time=0.004..5.058 rows=45,858 loops=1)

  • Output: an_1.description, an_1.alternate_name_id
  • Buffers: shared hit=327
64. 0.787 1.421 ↑ 1.0 4,783 1

Hash (cost=85.83..85.83 rows=4,783 width=24) (actual time=1.421..1.421 rows=4,783 loops=1)

  • Output: eis_1.market_name, eis_1.market_code
  • Buckets: 8,192 Batches: 1 Memory Usage: 328kB
  • Buffers: shared hit=38
65. 0.634 0.634 ↑ 1.0 4,783 1

Seq Scan on dbotcms.eis_market eis_1 (cost=0.00..85.83 rows=4,783 width=24) (actual time=0.004..0.634 rows=4,783 loops=1)

  • Output: eis_1.market_name, eis_1.market_code
  • Buffers: shared hit=38
66. 1.080 705.558 ↑ 5.1 339 1

Nested Loop Left Join (cost=10,095.92..397,601.66 rows=1,731 width=381) (actual time=139.535..705.558 rows=339 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=1,341, local hit=46 read=4,467, temp read=52,447 written=1
  • I/O Timings: read=9.481
67. 0.361 703.122 ↑ 5.1 339 1

Nested Loop Left Join (cost=10,095.64..396,963.73 rows=1,731 width=257) (actual time=139.491..703.122 rows=339 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=324, local hit=46 read=4,467, temp read=52,447 written=1
  • I/O Timings: read=9.481
68. 0.632 702.761 ↑ 5.1 339 1

Hash Join (cost=10,095.35..396,398.04 rows=1,731 width=239) (actual time=139.475..702.761 rows=339 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=264, local hit=46 read=4,467, temp read=52,447 written=1
  • I/O Timings: read=9.481
69. 3.022 700.889 ↑ 17.6 2,557 1

Hash Join (cost=9,644.78..395,479.24 rows=45,093 width=244) (actual time=137.483..700.889 rows=2,557 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=46 read=4,467, temp read=52,447 written=1
  • I/O Timings: read=9.481
70. 562.487 562.487 ↑ 17.6 2,557 1

CTE Scan on cte_account_exemption ae_3 (cost=0.00..385,214.43 rows=45,093 width=174) (actual time=1.242..562.487 rows=2,557 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: 5,628,041
  • Buffers: temp read=52,447 written=1
71. 82.023 135.380 ↑ 1.0 228,079 1

Hash (cost=6,793.79..6,793.79 rows=228,079 width=118) (actual time=135.380..135.380 rows=228,079 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: 34,384kB
  • Buffers: local hit=46 read=4,467
  • I/O Timings: read=9.481
72. 53.357 53.357 ↑ 1.0 228,079 1

Seq Scan on pg_temp_136.cte_vision_account va_3 (cost=0.00..6,793.79 rows=228,079 width=118) (actual time=0.009..53.357 rows=228,079 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=46 read=4,467
  • I/O Timings: read=9.481
73. 0.210 1.240 ↑ 1.0 885 1

Hash (cost=437.28..437.28 rows=886 width=61) (actual time=1.240..1.240 rows=885 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=264
74. 0.277 1.030 ↑ 1.0 885 1

Hash Join (cost=46.99..437.28 rows=886 width=61) (actual time=0.422..1.030 rows=885 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=264
75. 0.378 0.511 ↑ 1.0 888 1

Bitmap Heap Scan on dbotcms.exemption_master em_1 (cost=23.46..401.56 rows=888 width=14) (actual time=0.162..0.511 rows=888 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=246
  • Buffers: shared hit=255
76. 0.133 0.133 ↑ 1.0 888 1

Bitmap Index Scan on exe_master_exe_type_fk_ix5 (cost=0.00..23.23 rows=888 width=0) (actual time=0.133..0.133 rows=888 loops=1)

  • Index Cond: ((em_1.exemption_type)::text = ANY ('{Y,YA}'::text[]))
  • Buffers: shared hit=9
77. 0.113 0.242 ↑ 1.0 581 1

Hash (cost=16.27..16.27 rows=581 width=47) (actual time=0.242..0.242 rows=581 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
78. 0.129 0.129 ↑ 1.0 581 1

Seq Scan on dbotcms.tax t_4 (cost=0.00..16.27 rows=581 width=47) (actual time=0.013..0.129 rows=581 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
79. 0.000 0.000 ↓ 0.0 0 339

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=339)

  • Output: an_2.alternate_name_id, an_2.description
  • Index Cond: (va_3.alternate_name_id = an_2.alternate_name_id)
  • Buffers: shared hit=60
80. 1.356 1.356 ↑ 1.0 1 339

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

  • 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=1,017
81. 251,355.832 251,355.832 ↑ 2.2 13,730 1

CTE Scan on missing_expired a (cost=0.00..1,351.26 rows=30,028 width=618) (actual time=251,332.312..251,355.832 rows=13,730 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=29,033,185, local hit=21,832,035 read=351,709, temp read=248,022 written=248,026
  • I/O Timings: read=1,049.675
Planning time : 29.053 ms
Execution time : 251,603.071 ms