explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bGbo

Settings
# exclusive inclusive rows x rows loops node
1. 2,837.649 5,738,742.338 ↑ 120.3 117,150 1

Sort (cost=1,446,534,897.09..1,446,570,144.25 rows=14,098,864 width=2,255) (actual time=5,738,593.804..5,738,742.338 rows=117,150 loops=1)

  • Output: draft_lite.acct_id, draft_lite.dog_num, draft_lite.potreb, ((to_date((draft_outgoing_date.adhoc_char_val)::text, 'DD.MM.YYYY'::text))::timestamp(0) without time zone), draft_num.adhoc_char_val, draft_lite.draft_period, retail_type_descr.descr, draft_hist_status_descr.draft_hist_status_descr, draft_print_form.draft_reprint_reason, draft_lite.draft_return_from_bank_date, draft_lite.draft_status_cd, draft_recall.recall_date, draft_recall.recall_reason_descr, draft_lite.case_id, ft.match_evt_id, (sum(ae.cur_amt)), (sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END)), (sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))), (((sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END)) - (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_is_prepayment_sw = 'Y'::bpchar) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))))), (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_is_prepayment_sw = 'N'::bpchar) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))), (((((((((sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END) - sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN ((ft.parent_id = 'INV-ADJ'::bpchar) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN (ft.parent_id = 'TR-DEBT'::bpchar) THEN ae.cur_amt ELSE '0'::double precision END)))), (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_is_prepayment_sw = 'Y'::bpchar) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))), (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))), (sum(CASE WHEN ((ae.debt_cl_cd <> 'RAB'::bpchar) AND (ae.cm_acct_pay_match_evt_prop_id = ' '::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END)), (sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)), (CASE WHEN (sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END) = '0'::double precision) THEN 'N'::text ELSE 'Y'::text END), (sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)), (sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))), (CASE WHEN (retail_type_descr.char_val = '0'::bpchar) THEN CASE WHEN ((((((((((sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END) - sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN ((ft.parent_id = 'INV-ADJ'::bpchar) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN (ft.parent_id = 'TR-DEBT'::bpchar) THEN ae.cur_amt ELSE '0'::double precision END)))) > '0'::double precision) THEN 'DEBT_PREPAYMENT'::text ELSE 'NOT_DEBT_PREPAYMENT'::text END ELSE CASE WHEN ((((((((((sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END) - sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN ((ft.parent_id = 'INV-ADJ'::bpchar) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN (ft.parent_id = 'TR-DEBT'::bpchar) THEN ae.cur_amt ELSE '0'::double precision END)))) > '0'::double precision) THEN 'DEBT_OTHER'::text ELSE 'NOT_DEBT_OTHER'::text END END), (CASE WHEN (retail_type_descr.char_val = '0'::bpchar) THEN COALESCE(CASE WHEN (btrim((prepayment_procent.adhoc_char_val)::text) ~ '^[+-]?[0-9]*\.?[0-9]+$'::text) THEN (btrim((prepayment_procent.adhoc_char_val)::text))::numeric ELSE NULL::numeric END, '100'::numeric) ELSE NULL::numeric END)
  • Sort Key: draft_lite.dog_num, ((to_date((draft_outgoing_date.adhoc_char_val)::text, 'DD.MM.YYYY'::text))::timestamp(0) without time zone), draft_num.adhoc_char_val
  • Sort Method: quicksort Memory: 66,833kB
  • Buffers: shared hit=226,631,033 read=4,479,352
2.          

CTE period

3. 0.040 0.040 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=40) (actual time=0.038..0.040 rows=1 loops=1)

  • Output: '2019-12'::text, to_date('2019-12-01'::text, 'YYYY-MM-DD'::text), (add_months(to_date('2019-12-01'::text, 'YYYY-MM-DD'::text), 1) - 1)
4.          

CTE acct_lite

5. 211.399 560.247 ↓ 1.0 92,067 1

HashAggregate (cost=3,545.73..4,442.21 rows=89,648 width=11) (actual time=471.148..560.247 rows=92,067 loops=1)

  • Output: a.acct_id
  • Group Key: a.acct_id
  • Buffers: shared hit=4 read=475
6. 170.350 348.848 ↓ 1.0 92,067 1

Merge Join (cost=6.04..3,321.61 rows=89,648 width=11) (actual time=57.305..348.848 rows=92,067 loops=1)

  • Output: a.acct_id
  • Merge Cond: (ag_dar.access_grp_cd = a.access_grp_cd)
  • Buffers: shared hit=4 read=475
7. 11.325 80.050 ↑ 1.1 105 1

Nested Loop (cost=5.53..311.05 rows=111 width=13) (actual time=57.075..80.050 rows=105 loops=1)

  • Output: ag_dar.access_grp_cd
  • Join Filter: (u.dar_cd = ag_dar.dar_cd)
  • Rows Removed by Join Filter: 11,785
  • Buffers: shared hit=3 read=19
8. 0.211 0.211 ↑ 1.0 114 1

Index Only Scan using ci_acc_grp_dar_pkey on cisadm.ci_acc_grp_dar ag_dar (cost=0.14..4.05 rows=114 width=26) (actual time=0.102..0.211 rows=114 loops=1)

  • Output: ag_dar.access_grp_cd, ag_dar.dar_cd
  • Heap Fetches: 0
  • Buffers: shared hit=2
9. 11.578 68.514 ↑ 1.1 104 114

Materialize (cost=5.39..115.76 rows=112 width=13) (actual time=0.417..0.601 rows=104 loops=114)

  • Output: u.dar_cd
  • Buffers: shared hit=1 read=19
10. 18.368 56.936 ↑ 1.1 105 1

Bitmap Heap Scan on cisadm.ci_dar_usr u (cost=5.39..115.20 rows=112 width=13) (actual time=47.468..56.936 rows=105 loops=1)

  • Output: u.dar_cd
  • Recheck Cond: (((u.user_id IS NULL) AND (u.expire_dt > now())) OR ((btrim((u.user_id)::text) = ''::text) AND (u.expire_dt > now())) OR ((btrim((u.user_id)::text) = 'SIGENOV'::text) AND (u.expire_dt > now())))
  • Filter: (u.expire_dt > now())
  • Heap Blocks: exact=14
  • Buffers: shared hit=1 read=19
11. 0.006 38.568 ↓ 0.0 0 1

BitmapOr (cost=5.39..5.39 rows=112 width=0) (actual time=38.567..38.568 rows=0 loops=1)

  • Buffers: shared hit=1 read=5
12. 18.755 18.755 ↓ 0.0 0 1

Bitmap Index Scan on pgpro_test_15 (cost=0.00..1.40 rows=1 width=0) (actual time=18.754..18.755 rows=0 loops=1)

  • Index Cond: ((u.user_id IS NULL) AND (u.expire_dt > now()))
  • Buffers: shared read=2
13. 15.157 15.157 ↓ 0.0 0 1

Bitmap Index Scan on pgpro_test_17 (cost=0.00..1.46 rows=7 width=0) (actual time=15.156..15.157 rows=0 loops=1)

  • Index Cond: ((btrim((u.user_id)::text) = ''::text) AND (u.expire_dt > now()))
  • Buffers: shared read=2
14. 4.650 4.650 ↑ 1.0 105 1

Bitmap Index Scan on pgpro_test_17 (cost=0.00..2.44 rows=105 width=0) (actual time=4.649..4.650 rows=105 loops=1)

  • Index Cond: ((btrim((u.user_id)::text) = 'SIGENOV'::text) AND (u.expire_dt > now()))
  • Buffers: shared hit=1 read=1
15. 98.448 98.448 ↑ 1.0 92,071 1

Index Only Scan using ind_acct_1 on cisadm.ci_acct a (cost=0.42..1,886.38 rows=92,071 width=24) (actual time=0.201..98.448 rows=92,071 loops=1)

  • Output: a.access_grp_cd, a.acct_id
  • Heap Fetches: 0
  • Buffers: shared hit=1 read=456
16.          

CTE calc_gr_prepare_m

17. 0.322 25.504 ↓ 9.1 200 1

Nested Loop (cost=0.56..2.93 rows=22 width=11) (actual time=23.711..25.504 rows=200 loops=1)

  • Output: pc.per_id
  • Buffers: shared hit=5 read=11
18. 0.006 0.006 ↑ 1.0 1 1

CTE Scan on period p_16 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.006 rows=1 loops=1)

  • Output: p_16.period_id, p_16.start_dt, p_16.end_dt
19. 25.176 25.176 ↓ 9.1 200 1

Index Only Scan using cpc_ctcd_ne_per_id_ux on pgpro_test.ci_per_char pc (cost=0.56..2.69 rows=22 width=27) (actual time=23.699..25.176 rows=200 loops=1)

  • Output: pc.char_type_cd, pc.next_effdt, pc.effdt, pc.per_id, pc.adhoc_char_val
  • Index Cond: ((pc.char_type_cd = 'CALC-GR'::bpchar) AND (pc.next_effdt > p_16.end_dt) AND (pc.effdt <= p_16.end_dt))
  • Heap Fetches: 0
  • Buffers: shared hit=5 read=11
20.          

CTE acct_dep_m

21. 213.357 213.357 ↑ 1.0 91,135 1

Index Only Scan using bl_ux on cisadm.ci_acct_per acct_dep (cost=0.42..1,866.84 rows=91,135 width=22) (actual time=27.758..213.357 rows=91,135 loops=1)

  • Output: acct_dep.acct_id, acct_dep.per_id
  • Heap Fetches: 0
  • Buffers: shared hit=1 read=451
22.          

CTE acct

23. 1,068.359 83,596.707 ↓ 42.6 89,205 1

HashAggregate (cost=6,919.54..6,940.50 rows=2,096 width=77) (actual time=83,100.616..83,596.707 rows=89,205 loops=1)

  • Output: dog_num.acct_id, dog_num.adhoc_char_val, p_17.period_id, p_17.end_dt, c6.adhoc_char_val
  • Group Key: dog_num.acct_id, dog_num.adhoc_char_val, p_17.period_id, p_17.end_dt, c6.adhoc_char_val
  • Buffers: shared hit=1,001,672 read=149,566
24. 1,278.434 82,528.348 ↓ 42.6 89,205 1

Nested Loop (cost=2,790.64..6,893.34 rows=2,096 width=77) (actual time=25,136.758..82,528.348 rows=89,205 loops=1)

  • Output: dog_num.acct_id, dog_num.adhoc_char_val, p_17.period_id, p_17.end_dt, c6.adhoc_char_val
  • Buffers: shared hit=1,001,672 read=149,566
25. 564.380 26,567.249 ↓ 80.4 89,205 1

Hash Join (cost=2,790.08..5,391.70 rows=1,110 width=64) (actual time=25,110.013..26,567.249 rows=89,205 loops=1)

  • Output: dog_num.acct_id, dog_num.adhoc_char_val, p_17.period_id, p_17.end_dt, c5.per_id
  • Hash Cond: (a_2.acct_id = dog_num.acct_id)
  • Buffers: shared hit=629,641 read=88,366
26. 369.949 947.836 ↓ 9.1 91,135 1

Hash Join (cost=0.96..2,265.66 rows=10,025 width=44) (actual time=54.563..947.836 rows=91,135 loops=1)

  • Output: a_2.acct_id
  • Hash Cond: (a_2.per_id = c.per_id)
  • Buffers: shared hit=6 read=462
27. 551.145 551.145 ↑ 1.0 91,135 1

CTE Scan on acct_dep_m a_2 (cost=0.00..1,822.70 rows=91,135 width=88) (actual time=27.767..551.145 rows=91,135 loops=1)

  • Output: a_2.acct_id, a_2.per_id
  • Buffers: shared hit=1 read=451
28. 0.263 26.742 ↓ 9.1 200 1

Hash (cost=0.68..0.68 rows=22 width=44) (actual time=26.741..26.742 rows=200 loops=1)

  • Output: c.per_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=5 read=11
29. 0.520 26.479 ↓ 9.1 200 1

Nested Loop (cost=0.00..0.68 rows=22 width=44) (actual time=23.768..26.479 rows=200 loops=1)

  • Output: c.per_id
  • Buffers: shared hit=5 read=11
30. 0.045 0.045 ↑ 1.0 1 1

CTE Scan on period p_18 (cost=0.00..0.02 rows=1 width=0) (actual time=0.043..0.045 rows=1 loops=1)

  • Output: p_18.period_id, p_18.start_dt, p_18.end_dt
31. 25.914 25.914 ↓ 9.1 200 1

CTE Scan on calc_gr_prepare_m c (cost=0.00..0.44 rows=22 width=44) (actual time=23.717..25.914 rows=200 loops=1)

  • Output: c.per_id
  • Buffers: shared hit=5 read=11
32. 268.168 25,055.033 ↓ 8.8 89,969 1

Hash (cost=2,661.80..2,661.80 rows=10,186 width=119) (actual time=25,055.032..25,055.033 rows=89,969 loops=1)

  • Output: dog_num.acct_id, dog_num.adhoc_char_val, p_17.period_id, p_17.end_dt, c5.acct_id, c5.per_id, a_1.acct_id
  • Buckets: 131,072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 10,251kB
  • Buffers: shared hit=629,635 read=87,904
33. 206.552 24,786.865 ↓ 8.8 89,969 1

Nested Loop (cost=2,018.06..2,661.80 rows=10,186 width=119) (actual time=879.456..24,786.865 rows=89,969 loops=1)

  • Output: dog_num.acct_id, dog_num.adhoc_char_val, p_17.period_id, p_17.end_dt, c5.acct_id, c5.per_id, a_1.acct_id
  • Buffers: shared hit=629,635 read=87,904
34. 0.012 0.012 ↑ 1.0 1 1

CTE Scan on period p_17 (cost=0.00..0.02 rows=1 width=36) (actual time=0.003..0.012 rows=1 loops=1)

  • Output: p_17.period_id, p_17.start_dt, p_17.end_dt
35. 453.057 24,580.301 ↓ 449.8 89,969 1

Nested Loop (cost=2,018.06..2,659.78 rows=200 width=99) (actual time=879.438..24,580.301 rows=89,969 loops=1)

  • Output: dog_num.acct_id, dog_num.adhoc_char_val, dog_num.effdt, dog_num.next_effdt, c5.acct_id, c5.per_id, a_1.acct_id
  • Buffers: shared hit=629,635 read=87,904
36. 283.387 1,018.427 ↓ 460.3 92,067 1

HashAggregate (cost=2,017.08..2,019.08 rows=200 width=44) (actual time=852.275..1,018.427 rows=92,067 loops=1)

  • Output: a_1.acct_id
  • Group Key: a_1.acct_id
  • Buffers: shared hit=4 read=475
37. 735.040 735.040 ↓ 1.0 92,067 1

CTE Scan on acct_lite a_1 (cost=0.00..1,792.96 rows=89,648 width=44) (actual time=471.151..735.040 rows=92,067 loops=1)

  • Output: a_1.acct_id
  • Buffers: shared hit=4 read=475
38. 1,381.005 23,108.817 ↑ 1.0 1 92,067

Nested Loop (cost=0.98..3.19 rows=1 width=55) (actual time=0.246..0.251 rows=1 loops=92,067)

  • Output: dog_num.acct_id, dog_num.adhoc_char_val, dog_num.effdt, dog_num.next_effdt, c5.acct_id, c5.per_id
  • Join Filter: (a_1.acct_id = dog_num.acct_id)
  • Buffers: shared hit=629,631 read=87,429
39. 4,235.082 4,235.082 ↑ 1.0 1 92,067

Index Only Scan using cap_aid_pid_ux on cisadm.ci_acct_per c5 (cost=0.42..1.50 rows=1 width=22) (actual time=0.045..0.046 rows=1 loops=92,067)

  • Output: c5.acct_id, c5.main_cust_sw, c5.per_id
  • Index Cond: ((c5.acct_id = a_1.acct_id) AND (c5.main_cust_sw = 'Y'::bpchar))
  • Heap Fetches: 0
  • Buffers: shared hit=273,910 read=2,292
40. 17,492.730 17,492.730 ↑ 1.0 1 92,067

Index Only Scan using cacv_cv_acct_ctcd_edt_ndt_ux on pgpro_test.ci_acct_char dog_num (cost=0.56..1.68 rows=1 width=33) (actual time=0.189..0.190 rows=1 loops=92,067)

  • Output: dog_num.acct_id, dog_num.char_type_cd, dog_num.effdt, dog_num.next_effdt, dog_num.adhoc_char_val, dog_num.char_val
  • Index Cond: ((dog_num.acct_id = c5.acct_id) AND (dog_num.char_type_cd = 'DOG-NUM'::bpchar) AND (dog_num.effdt <= p_17.end_dt) AND (dog_num.next_effdt > p_17.end_dt))
  • Heap Fetches: 0
  • Buffers: shared hit=355,721 read=85,137
41. 54,682.665 54,682.665 ↑ 1.0 1 89,205

Index Only Scan using cpc_pid_ctcd_ne_ach_ux on pgpro_test.ci_per_char c6 (cost=0.56..1.34 rows=1 width=35) (actual time=0.611..0.613 rows=1 loops=89,205)

  • Output: c6.per_id, c6.char_type_cd, c6.next_effdt, c6.adhoc_char_val
  • Index Cond: ((c6.per_id = c5.per_id) AND (c6.char_type_cd = 'SHORT-NM'::bpchar) AND (c6.next_effdt = '4000-01-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=372,031 read=61,200
42.          

CTE draft_hist_status_descr

43. 6.607 6.607 ↑ 1.0 97 1

Index Scan using ci_case_status_l_pkey on cisadm.ci_case_status_l draft_hist_status_descr_1 (cost=0.28..35.18 rows=97 width=63) (actual time=0.139..6.607 rows=97 loops=1)

  • Output: draft_hist_status_descr_1.case_status_cd, draft_hist_status_descr_1.status_lbl
  • Index Cond: ((draft_hist_status_descr_1.case_type_cd = 'DRAFT-HIST'::bpchar) AND (draft_hist_status_descr_1.language_cd = 'RUS'::bpchar))
  • Buffers: shared hit=30 read=20
44.          

CTE draft_lite

45. 2,712.902 885,680.029 ↓ 11.8 117,150 1

Nested Loop (cost=417,382.50..667,006.54 rows=9,897 width=1,177) (actual time=681,093.133..885,680.029 rows=117,150 loops=1)

  • Output: draft.case_id, draft_me.char_val_fk1, draft_hist.case_id, draft_hist.case_status_cd, to_date((draft_return_from_bank_date.adhoc_char_val)::text, 'DD.MM.YYYY'::text), acct.acct_id, acct.dog_num, acct.potreb, to_char((to_date((draft_date.adhoc_char_val)::text, 'DD.MM.YYYY'::text))::timestamp with time zone, 'MM.YYYY'::text), to_date((draft_date.adhoc_char_val)::text, 'DD.MM.YYYY'::text), draft.case_status_cd
  • Buffers: shared hit=58,733,101 read=566,022
46. 1,246.980 780,929.477 ↓ 29.1 117,150 1

Nested Loop (cost=417,381.94..664,476.40 rows=4,025 width=1,157) (actual time=681,063.607..780,929.477 rows=117,150 loops=1)

  • Output: acct.acct_id, acct.dog_num, acct.potreb, draft.case_id, draft.case_status_cd, draft_date.adhoc_char_val, draft_date.case_id, draft_hist_fk.char_val_fk1, draft_hist.case_id, draft_hist.case_status_cd, draft_return_from_bank_date.adhoc_char_val
  • Buffers: shared hit=58,230,795 read=515,322
47. 14,448.431 712,321.247 ↓ 29.1 117,150 1

Hash Join (cost=417,381.38..661,984.93 rows=4,025 width=1,144) (actual time=681,023.452..712,321.247 rows=117,150 loops=1)

  • Output: acct.acct_id, acct.dog_num, acct.potreb, draft.case_id, draft.case_status_cd, draft_date.adhoc_char_val, draft_date.case_id, draft_hist_fk.char_val_fk1, draft_hist_fk.case_id, draft_return_from_bank_date.adhoc_char_val
  • Hash Cond: ((draft_hist_fk.char_val_fk1)::bpchar = draft_date.case_id)
  • Buffers: shared hit=57,708,650 read=451,717
48. 16,903.254 16,903.254 ↑ 1.0 8,762,640 1

Index Only Scan using ci_case_char__v1__drafprnt_case_id_char_type_cd_char_val_fk_idx on pgpro_test.ci_case_char__v1__drafprnt draft_hist_fk (cost=0.56..211,703.96 rows=8,762,640 width=22) (actual time=37.069..16,903.254 rows=8,762,640 loops=1)

  • Output: draft_hist_fk.char_val_fk1, draft_hist_fk.case_id
  • Index Cond: (draft_hist_fk.char_type_cd = 'DRAFPRNT'::bpchar)
  • Heap Fetches: 0
  • Buffers: shared hit=6,303,929 read=52,794
49. 427.859 680,969.562 ↓ 71.6 117,150 1

Hash (cost=417,360.36..417,360.36 rows=1,637 width=1,122) (actual time=680,969.561..680,969.562 rows=117,150 loops=1)

  • Output: acct.acct_id, acct.dog_num, acct.potreb, draft.case_id, draft.case_status_cd, draft_date.adhoc_char_val, draft_date.case_id, draft_return_from_bank_date.adhoc_char_val
  • Buckets: 131,072 (originally 2048) Batches: 1 (originally 1) Memory Usage: 19,848kB
  • Buffers: shared hit=51,404,721 read=398,923
50. 1,038.639 680,541.703 ↓ 71.6 117,150 1

Nested Loop Left Join (cost=1.56..417,360.36 rows=1,637 width=1,122) (actual time=84,872.093..680,541.703 rows=117,150 loops=1)

  • Output: acct.acct_id, acct.dog_num, acct.potreb, draft.case_id, draft.case_status_cd, draft_date.adhoc_char_val, draft_date.case_id, draft_return_from_bank_date.adhoc_char_val
  • Buffers: shared hit=51,404,721 read=398,923
51. 85,091.322 674,465.614 ↓ 71.6 117,150 1

Nested Loop (cost=1.00..416,380.44 rows=1,637 width=1,116) (actual time=84,835.269..674,465.614 rows=117,150 loops=1)

  • Output: acct.acct_id, acct.dog_num, acct.potreb, draft.case_id, draft.case_status_cd, draft_date.adhoc_char_val, draft_date.case_id
  • Join Filter: (acct.period_id = to_char((to_date((draft_date.adhoc_char_val)::text, 'DD.MM.YYYY'::text))::timestamp with time zone, 'YYYY-MM'::text))
  • Rows Removed by Join Filter: 8,642,104
  • Buffers: shared hit=50,936,984 read=398,060
52. 19,946.988 230,244.878 ↓ 26.8 8,759,254 1

Nested Loop (cost=0.43..204,987.72 rows=326,306 width=1,132) (actual time=83,111.215..230,244.878 rows=8,759,254 loops=1)

  • Output: acct.acct_id, acct.dog_num, acct.potreb, acct.period_id, draft.case_id, draft.case_status_cd
  • Buffers: shared hit=9,740,310 read=344,911
53. 83,983.610 83,983.610 ↓ 42.6 89,205 1

CTE Scan on acct (cost=0.00..41.92 rows=2,096 width=1,108) (actual time=83,100.630..83,983.610 rows=89,205 loops=1)

  • Output: acct.acct_id, acct.dog_num, acct.period_id, acct.end_dt, acct.potreb
  • Buffers: shared hit=1,001,672 read=149,566
54. 126,314.280 126,314.280 ↑ 1.6 98 89,205

Index Scan using ci_case__v1__draft_acct_id_idx on pgpro_test.ci_case__v1__draft draft (cost=0.43..96.22 rows=156 width=35) (actual time=0.074..1.416 rows=98 loops=89,205)

  • Output: draft.case_id, draft.case_status_cd, draft.acct_id
  • Index Cond: (draft.acct_id = acct.acct_id)
  • Filter: (draft.case_type_cd = 'DRAFT'::bpchar)
  • Buffers: shared hit=8,738,638 read=195,345
55. 359,129.414 359,129.414 ↑ 1.0 1 8,759,254

Index Only Scan using ci_case_char__v1__draft_char_type_cd_case_id_adhoc_char_val_idx on pgpro_test.ci_case_char__v1__draft draft_date (cost=0.56..0.63 rows=1 width=16) (actual time=0.039..0.041 rows=1 loops=8,759,254)

  • Output: draft_date.adhoc_char_val, draft_date.case_id
  • Index Cond: ((draft_date.char_type_cd = 'DRAFTDT'::bpchar) AND (draft_date.case_id = draft.case_id))
  • Heap Fetches: 0
  • Buffers: shared hit=41,196,674 read=53,149
56. 5,037.450 5,037.450 ↓ 0.0 0 117,150

Index Only Scan using ci_case_char__v1__default_char_type_cd_case_id_adhoc_char_v_idx on pgpro_test.ci_case_char__v1__default draft_return_from_bank_date (cost=0.56..0.59 rows=1 width=17) (actual time=0.043..0.043 rows=0 loops=117,150)

  • Output: draft_return_from_bank_date.adhoc_char_val, draft_return_from_bank_date.case_id
  • Index Cond: ((draft_return_from_bank_date.char_type_cd = 'DRH-RETD'::bpchar) AND (draft_return_from_bank_date.case_id = draft.case_id))
  • Heap Fetches: 0
  • Buffers: shared hit=467,737 read=863
57. 67,361.250 67,361.250 ↑ 1.0 1 117,150

Index Scan using ci_case__v1__draft_hist_case_id_case_status_cd_idx on pgpro_test.ci_case__v1__draft_hist draft_hist (cost=0.56..0.61 rows=1 width=24) (actual time=0.573..0.575 rows=1 loops=117,150)

  • Output: draft_hist.case_id, draft_hist.case_status_cd
  • Index Cond: (draft_hist.case_id = draft_hist_fk.case_id)
  • Filter: (draft_hist.case_type_cd = 'DRAFT-HIST'::bpchar)
  • Buffers: shared hit=522,145 read=63,605
58. 102,037.650 102,037.650 ↑ 1.0 1 117,150

Index Only Scan using ci_case_char__v1__me_id_case_id_char_type_cd_char_val_fk1_idx on pgpro_test.ci_case_char__v1__me_id draft_me (cost=0.56..0.59 rows=1 width=24) (actual time=0.869..0.871 rows=1 loops=117,150)

  • Output: draft_me.char_val_fk1, draft_me.case_id
  • Index Cond: ((draft_me.case_id = draft_date.case_id) AND (draft_me.char_type_cd = 'ME-ID'::bpchar))
  • Heap Fetches: 0
  • Buffers: shared hit=502,306 read=50,700
59.          

CTE draft_print_form

60. 930.513 944,693.933 ↓ 8.0 8 1

Nested Loop (cost=1.14..33,817.46 rows=1 width=50) (actual time=250,974.640..944,693.933 rows=8 loops=1)

  • Output: draft_lite_2.case_id, cc.adhoc_char_val
  • Buffers: shared hit=1,399,167 read=229,258
61. 902.134 936,031.520 ↓ 3,779.0 117,150 1

Nested Loop (cost=0.57..33,766.34 rows=31 width=46) (actual time=0.405..936,031.520 rows=117,150 loops=1)

  • Output: draft_lite_2.case_id, case_log.char_val_fk1
  • Buffers: shared hit=930,622 read=229,195
62. 389.536 389.536 ↓ 11.8 117,150 1

CTE Scan on draft_lite draft_lite_2 (cost=0.00..197.94 rows=9,897 width=44) (actual time=0.003..389.536 rows=117,150 loops=1)

  • Output: draft_lite_2.case_id, draft_lite_2.match_evt_id, draft_lite_2.draft_hist_id, draft_lite_2.case_status_cd, draft_lite_2.draft_return_from_bank_date, draft_lite_2.acct_id, draft_lite_2.dog_num, draft_lite_2.potreb, draft_lite_2.draft_period, draft_lite_2.draft_date, draft_lite_2.draft_status_cd
63. 492,223.898 934,739.850 ↑ 1.0 1 117,150

Index Only Scan using ind_ci_case_log_1 on cisadm.ci_case_log case_log (cost=0.57..3.38 rows=1 width=13) (actual time=7.978..7.979 rows=1 loops=117,150)

  • Output: case_log.case_id, case_log.char_type_cd, case_log.log_dttm, case_log.char_val_fk1
  • Index Cond: ((case_log.case_id = draft_lite_2.case_id) AND (case_log.char_type_cd = 'DR-PR-ID'::bpchar))
  • Filter: (case_log.log_dttm = (SubPlan 9))
  • Rows Removed by Filter: 0
  • Heap Fetches: 0
  • Buffers: shared hit=930,622 read=229,195
64.          

SubPlan (for Index Only Scan)

65. 1,173.160 442,515.952 ↑ 1.0 1 117,316

Result (cost=1.69..1.70 rows=1 width=8) (actual time=3.771..3.772 rows=1 loops=117,316)

  • Output: $19
  • Buffers: shared hit=466,456 read=113,761
66.          

Initplan (for Result)

67. 1,055.844 441,342.792 ↑ 1.0 1 117,316

Limit (cost=0.57..1.69 rows=1 width=8) (actual time=3.761..3.762 rows=1 loops=117,316)

  • Output: o.log_dttm
  • Buffers: shared hit=466,456 read=113,761
68. 440,286.948 440,286.948 ↑ 1.0 1 117,316

Index Only Scan Backward using ind_ci_case_log_3 on cisadm.ci_case_log o (cost=0.57..1.69 rows=1 width=8) (actual time=3.753..3.753 rows=1 loops=117,316)

  • Output: o.log_dttm
  • Index Cond: ((o.case_id = case_log.case_id) AND (o.char_type_cd = case_log.char_type_cd) AND (o.log_dttm IS NOT NULL))
  • Heap Fetches: 0
  • Buffers: shared hit=466,456 read=113,761
69. 7,731.900 7,731.900 ↓ 0.0 0 117,150

Index Only Scan using ci_case_char__v1__dr_char_type_cd_case_id_adhoc_char_val_idx on pgpro_test.ci_case_char__v1__dr cc (cost=0.57..1.64 rows=1 width=17) (actual time=0.066..0.066 rows=0 loops=117,150)

  • Output: cc.adhoc_char_val, cc.case_id
  • Index Cond: ((cc.char_type_cd = 'DR-RP-RS'::bpchar) AND (cc.case_id = (case_log.char_val_fk1)::bpchar))
  • Heap Fetches: 0
  • Buffers: shared hit=468,545 read=63
70.          

CTE draft_recall

71. 0.014 3,653.203 ↓ 0.0 0 1

Nested Loop (cost=17.08..252.19 rows=1 width=81) (actual time=3,653.202..3,653.203 rows=0 loops=1)

  • Output: draft_lite_3.case_id, to_date(btrim((recall_date.adhoc_char_val)::text), 'DD.MM.YYYY'::text), recall_reason_descr.descr
  • Join Filter: (recall_draft.case_id = recall.case_id)
  • Buffers: shared hit=2,612 read=1,012
72. 115.790 3,653.189 ↓ 0.0 0 1

Hash Join (cost=16.66..251.72 rows=1 width=118) (actual time=3,653.185..3,653.189 rows=0 loops=1)

  • Output: draft_lite_3.case_id, recall_draft.case_id, recall_date.adhoc_char_val, recall_date.case_id, recall_reason.case_id, recall_reason_descr.descr
  • Hash Cond: (draft_lite_3.case_id = (recall_draft.char_val_fk1)::bpchar)
  • Buffers: shared hit=2,612 read=1,012
73. 136.812 136.812 ↓ 11.8 117,150 1

CTE Scan on draft_lite draft_lite_3 (cost=0.00..197.94 rows=9,897 width=44) (actual time=0.013..136.812 rows=117,150 loops=1)

  • Output: draft_lite_3.case_id, draft_lite_3.match_evt_id, draft_lite_3.draft_hist_id, draft_lite_3.case_status_cd, draft_lite_3.draft_return_from_bank_date, draft_lite_3.acct_id, draft_lite_3.dog_num, draft_lite_3.potreb, draft_lite_3.draft_period, draft_lite_3.draft_date, draft_lite_3.draft_status_cd
74. 4.111 3,400.587 ↓ 1,089.0 1,089 1

Hash (cost=16.65..16.65 rows=1 width=79) (actual time=3,400.586..3,400.587 rows=1,089 loops=1)

  • Output: recall_draft.char_val_fk1, recall_draft.case_id, recall_date.adhoc_char_val, recall_date.case_id, recall_reason.case_id, recall_reason_descr.descr
  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 155kB
  • Buffers: shared hit=2,612 read=1,012
75. 5.632 3,396.476 ↓ 1,089.0 1,089 1

Nested Loop (cost=2.10..16.65 rows=1 width=79) (actual time=116.188..3,396.476 rows=1,089 loops=1)

  • Output: recall_draft.char_val_fk1, recall_draft.case_id, recall_date.adhoc_char_val, recall_date.case_id, recall_reason.case_id, recall_reason_descr.descr
  • Buffers: shared hit=2,612 read=1,012
76. 3.914 1,039.035 ↓ 359.0 359 1

Nested Loop (cost=1.54..16.00 rows=1 width=63) (actual time=90.752..1,039.035 rows=359 loops=1)

  • Output: recall_date.adhoc_char_val, recall_date.case_id, recall_reason.case_id, recall_reason_descr.descr
  • Buffers: shared hit=1,482 read=422
77. 1.979 73.719 ↓ 179.5 359 1

Merge Join (cost=0.98..12.64 rows=2 width=44) (actual time=43.907..73.719 rows=359 loops=1)

  • Output: recall_reason.case_id, recall_reason_descr.descr
  • Inner Unique: true
  • Merge Cond: (recall_reason.char_val = recall_reason_descr.char_val)
  • Buffers: shared hit=279 read=16
78. 58.324 58.324 ↓ 1.1 359 1

Index Only Scan using ci_case_char__v1__default_char_type_cd_char_val_case_id_idx on pgpro_test.ci_case_char__v1__default recall_reason (cost=0.56..9.72 rows=335 width=37) (actual time=31.336..58.324 rows=359 loops=1)

  • Output: recall_reason.case_id, recall_reason.char_type_cd, recall_reason.char_val
  • Index Cond: (recall_reason.char_type_cd = 'RCALL_RN'::bpchar)
  • Heap Fetches: 0
  • Buffers: shared hit=278 read=12
79. 13.416 13.416 ↑ 1.0 24 1

Index Only Scan using ""idx$$_175a60001"" on cisadm.ci_char_val_l recall_reason_descr (cost=0.42..2.00 rows=24 width=59) (actual time=12.535..13.416 rows=24 loops=1)

  • Output: recall_reason_descr.language_cd, recall_reason_descr.char_type_cd, recall_reason_descr.char_val, recall_reason_descr.descr
  • Index Cond: ((recall_reason_descr.language_cd = 'RUS'::bpchar) AND (recall_reason_descr.char_type_cd = 'RCALL_RN'::bpchar))
  • Heap Fetches: 0
  • Buffers: shared hit=1 read=4
80. 961.402 961.402 ↑ 1.0 1 359

Index Only Scan using ci_case_char__v1__doc_char_type_cd_case_id_adhoc_char_val_idx on pgpro_test.ci_case_char__v1__doc recall_date (cost=0.56..1.67 rows=1 width=19) (actual time=2.676..2.678 rows=1 loops=359)

  • Output: recall_date.adhoc_char_val, recall_date.case_id
  • Index Cond: ((recall_date.char_type_cd = 'DOC-DAT'::bpchar) AND (recall_date.case_id = recall_reason.case_id))
  • Heap Fetches: 0
  • Buffers: shared hit=1,203 read=406
81. 2,351.809 2,351.809 ↓ 3.0 3 359

Index Only Scan using ci_case_char__v1__default_case_id_char_type_cd_char_val_fk1_idx on pgpro_test.ci_case_char__v1__default recall_draft (cost=0.56..0.64 rows=1 width=16) (actual time=6.535..6.551 rows=3 loops=359)

  • Output: recall_draft.char_val_fk1, recall_draft.case_id
  • Index Cond: ((recall_draft.case_id = recall_date.case_id) AND (recall_draft.char_type_cd = 'RC-N-DOC'::bpchar))
  • Heap Fetches: 0
  • Buffers: shared hit=1,130 read=590
82. 0.000 0.000 ↓ 0.0 0

Index Scan using ci_case__v1__default_case_id_case_status_cd_idx on cisadm.ci_case__v1__default recall (cost=0.42..0.45 rows=1 width=11) (never executed)

  • Output: recall.case_id
  • Index Cond: ((recall.case_id = recall_date.case_id) AND (recall.case_status_cd = 'WORK'::bpchar))
  • Filter: (recall.case_type_cd = 'PDRECALL'::bpchar)
83. 1,312,651.932 5,735,904.689 ↑ 120.3 117,150 1

Merge Join (cost=896,277,951.89..1,434,308,209.16 rows=14,098,864 width=2,255) (actual time=5,724,145.955..5,735,904.689 rows=117,150 loops=1)

  • Output: draft_lite.acct_id, draft_lite.dog_num, draft_lite.potreb, (to_date((draft_outgoing_date.adhoc_char_val)::text, 'DD.MM.YYYY'::text))::timestamp(0) without time zone, draft_num.adhoc_char_val, draft_lite.draft_period, retail_type_descr.descr, draft_hist_status_descr.draft_hist_status_descr, draft_print_form.draft_reprint_reason, draft_lite.draft_return_from_bank_date, draft_lite.draft_status_cd, draft_recall.recall_date, draft_recall.recall_reason_descr, draft_lite.case_id, ft.match_evt_id, (sum(ae.cur_amt)), (sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END)), (sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))), ((sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END)) - (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_is_prepayment_sw = 'Y'::bpchar) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END)))), (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_is_prepayment_sw = 'N'::bpchar) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))), (((((((((sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END) - sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN ((ft.parent_id = 'INV-ADJ'::bpchar) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN (ft.parent_id = 'TR-DEBT'::bpchar) THEN ae.cur_amt ELSE '0'::double precision END)))), (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_is_prepayment_sw = 'Y'::bpchar) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))), (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))), (sum(CASE WHEN ((ae.debt_cl_cd <> 'RAB'::bpchar) AND (ae.cm_acct_pay_match_evt_prop_id = ' '::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END)), (sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)), (CASE WHEN (sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END) = '0'::double precision) THEN 'N'::text ELSE 'Y'::text END), (sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)), (sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))), CASE WHEN (retail_type_descr.char_val = '0'::bpchar) THEN CASE WHEN ((((((((((sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END) - sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN ((ft.parent_id = 'INV-ADJ'::bpchar) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN (ft.parent_id = 'TR-DEBT'::bpchar) THEN ae.cur_amt ELSE '0'::double precision END)))) > '0'::double precision) THEN 'DEBT_PREPAYMENT'::text ELSE 'NOT_DEBT_PREPAYMENT'::text END ELSE CASE WHEN ((((((((((sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END) - sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN ((ft.parent_id = 'INV-ADJ'::bpchar) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN (ft.parent_id = 'TR-DEBT'::bpchar) THEN ae.cur_amt ELSE '0'::double precision END)))) > '0'::double precision) THEN 'DEBT_OTHER'::text ELSE 'NOT_DEBT_OTHER'::text END END, CASE WHEN (retail_type_descr.char_val = '0'::bpchar) THEN COALESCE(CASE WHEN (btrim((prepayment_procent.adhoc_char_val)::text) ~ '^[+-]?[0-9]*\.?[0-9]+$'::text) THEN (btrim((prepayment_procent.adhoc_char_val)::text))::numeric ELSE NULL::numeric END, '100'::numeric) ELSE NULL::numeric END
  • Merge Cond: (ft.match_evt_id = (draft_lite.match_evt_id)::bpchar)
  • Buffers: shared hit=226,631,033 read=4,479,352
84. 1,026.401 4,423,252.757 ↓ 5.0 117,150 1

Nested Loop (cost=896,231,211.38..1,433,591,713.10 rows=23,517 width=183) (actual time=4,413,223.245..4,423,252.757 rows=117,150 loops=1)

  • Output: ft.match_evt_id, (sum(ae.cur_amt)), (sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END)), (sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))), (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_is_prepayment_sw = 'Y'::bpchar) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))), (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_is_prepayment_sw = 'N'::bpchar) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))), (((((((((sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END) - sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN ((ft.parent_id = 'INV-ADJ'::bpchar) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN (ft.parent_id = 'TR-DEBT'::bpchar) THEN ae.cur_amt ELSE '0'::double precision END)))), (sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))), (sum(CASE WHEN ((ae.debt_cl_cd <> 'RAB'::bpchar) AND (ae.cm_acct_pay_match_evt_prop_id = ' '::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END)), (sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)), (CASE WHEN (sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END) = '0'::double precision) THEN 'N'::text ELSE 'Y'::text END), (sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)), (sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))), retail_type_descr.descr, retail_type_descr.char_val
  • Inner Unique: true
  • Join Filter: (rp.cm_retail_type = retail_type_descr.char_val)
  • Rows Removed by Join Filter: 222,760
  • Buffers: shared hit=223,727,867 read=4,098,145
85. 6,325.521 4,422,226.356 ↑ 464.2 117,150 1

GroupAggregate (cost=896,231,210.97..1,423,260,379.84 rows=54,377,000 width=150) (actual time=4,413,222.972..4,422,226.356 rows=117,150 loops=1)

  • Output: ft.match_evt_id, rp.cm_retail_type, sum(ae.cur_amt), sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END), sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)), sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END)), sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_is_prepayment_sw = 'Y'::bpchar) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END)), sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_is_prepayment_sw = 'N'::bpchar) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END)), sum(CASE WHEN ((ae.debt_cl_cd <> 'RAB'::bpchar) AND (ae.cm_acct_pay_match_evt_prop_id = ' '::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END), sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END), sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END), sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END)), CASE WHEN (sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END) = '0'::double precision) THEN 'N'::text ELSE 'Y'::text END, ((((((((sum(CASE WHEN ((ae.show_on_bill_sw = 'Y'::bpchar) AND (ft.parent_id <> 'INV-PREP'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END) - sum((- CASE WHEN ((ft.parent_id <> ALL ('{REFOUND,REFOUNDP,INV-ADJ}'::bpchar[])) AND (p.cm_acct_pay_match_evt_prop_id IS NOT NULL) AND ((rp.cm_retail_type <> '0'::bpchar) OR (ft.parent_id <> 'TRANSF'::bpchar))) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN (ft.parent_id = ANY ('{SINH,REFOVPAY}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN (ft.parent_id = ANY ('{REFOUND,REFOUNDP}'::bpchar[])) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN ((ft.parent_id = ANY ('{PREP-DEC,PD-MRPAY}'::bpchar[])) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) - sum((- CASE WHEN ((ft.parent_id = 'INV-ADJ'::bpchar) AND (ae.show_on_bill_sw = 'N'::bpchar)) THEN ae.cur_amt ELSE '0'::double precision END))) + sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum(CASE WHEN ((ae.show_on_bill_sw = 'N'::bpchar) AND (ft.parent_id = 'INV-PREP'::bpchar) AND (p.cm_acct_pay_match_evt_prop_id IS NULL)) THEN ae.cur_amt ELSE '0'::double precision END)) - sum((- CASE WHEN (ft.parent_id = 'TR-DEBT'::bpchar) THEN ae.cur_amt ELSE '0'::double precision END)))
  • Group Key: ft.match_evt_id, rp.cm_retail_type
  • Buffers: shared hit=223,727,866 read=4,098,142
86. 7,612.618 4,415,900.835 ↑ 2,142.9 1,324,947 1

Sort (cost=896,231,210.97..903,329,344.97 rows=2,839,253,602 width=92) (actual time=4,413,222.808..4,415,900.835 rows=1,324,947 loops=1)

  • Output: ft.match_evt_id, rp.cm_retail_type, ae.cur_amt, ae.show_on_bill_sw, ft.parent_id, p.cm_acct_pay_match_evt_prop_id, p.cm_is_prepayment_sw, ae.debt_cl_cd, ae.cm_acct_pay_match_evt_prop_id
  • Sort Key: ft.match_evt_id, rp.cm_retail_type
  • Sort Method: quicksort Memory: 235,473kB
  • Buffers: shared hit=223,727,866 read=4,098,142
87. 72,434.858 4,408,288.217 ↑ 2,142.9 1,324,947 1

Merge Right Join (cost=13,573,663.31..58,542,129.37 rows=2,839,253,602 width=92) (actual time=3,622,787.092..4,408,288.217 rows=1,324,947 loops=1)

  • Output: ft.match_evt_id, rp.cm_retail_type, ae.cur_amt, ae.show_on_bill_sw, ft.parent_id, p.cm_acct_pay_match_evt_prop_id, p.cm_is_prepayment_sw, ae.debt_cl_cd, ae.cm_acct_pay_match_evt_prop_id
  • Merge Cond: (p.cm_acct_pay_match_evt_prop_id = ae.cm_acct_pay_match_evt_prop_id)
  • Buffers: shared hit=223,727,866 read=4,098,142
88. 271,428.858 708,002.159 ↑ 1.2 27,205,234 1

Merge Append (cost=7.16..2,250,372.58 rows=33,060,093 width=18) (actual time=293.218..708,002.159 rows=27,205,234 loops=1)

  • Sort Key: p.cm_acct_pay_match_evt_prop_id
  • Buffers: shared hit=26,488,375 read=820,467
89. 28,497.585 28,497.585 ↑ 1.2 1,703,968 1

Index Scan using cm_acct_pay_match_evt_prop_part0_pkey on cisadm.cm_acct_pay_match_evt_prop_part0 p (cost=0.43..89,143.32 rows=2,069,761 width=18) (actual time=12.611..28,497.585 rows=1,703,968 loops=1)

  • Output: p.cm_acct_pay_match_evt_prop_id, p.cm_is_prepayment_sw
  • Buffers: shared hit=1,659,099 read=51,368
90. 27,063.505 27,063.505 ↑ 1.2 1,700,508 1

Index Scan using cm_acct_pay_match_evt_prop_part1_pkey on cisadm.cm_acct_pay_match_evt_prop_part1 p_1 (cost=0.43..88,995.00 rows=2,066,477 width=18) (actual time=20.726..27,063.505 rows=1,700,508 loops=1)

  • Output: p_1.cm_acct_pay_match_evt_prop_id, p_1.cm_is_prepayment_sw
  • Buffers: shared hit=1,655,690 read=51,280
91. 27,307.463 27,307.463 ↑ 1.2 1,699,208 1

Index Scan using cm_acct_pay_match_evt_prop_part2_pkey on cisadm.cm_acct_pay_match_evt_prop_part2 p_2 (cost=0.43..88,927.68 rows=2,064,772 width=18) (actual time=17.400..27,307.463 rows=1,699,208 loops=1)

  • Output: p_2.cm_acct_pay_match_evt_prop_id, p_2.cm_is_prepayment_sw
  • Buffers: shared hit=1,654,433 read=51,242
92. 27,374.451 27,374.451 ↑ 1.2 1,702,440 1

Index Scan using cm_acct_pay_match_evt_prop_part3_pkey on cisadm.cm_acct_pay_match_evt_prop_part3 p_3 (cost=0.43..89,095.42 rows=2,068,473 width=18) (actual time=26.234..27,374.451 rows=1,702,440 loops=1)

  • Output: p_3.cm_acct_pay_match_evt_prop_id, p_3.cm_is_prepayment_sw
  • Buffers: shared hit=1,657,584 read=51,338
93. 27,770.514 27,770.514 ↑ 1.2 1,699,757 1

Index Scan using cm_acct_pay_match_evt_prop_part4_pkey on cisadm.cm_acct_pay_match_evt_prop_part4 p_4 (cost=0.43..88,926.79 rows=2,064,789 width=18) (actual time=13.911..27,770.514 rows=1,699,757 loops=1)

  • Output: p_4.cm_acct_pay_match_evt_prop_id, p_4.cm_is_prepayment_sw
  • Buffers: shared hit=1,654,978 read=51,244
94. 26,786.996 26,786.996 ↑ 1.2 1,700,409 1

Index Scan using cm_acct_pay_match_evt_prop_part5_pkey on cisadm.cm_acct_pay_match_evt_prop_part5 p_5 (cost=0.43..88,979.77 rows=2,066,043 width=18) (actual time=19.268..26,786.996 rows=1,700,409 loops=1)

  • Output: p_5.cm_acct_pay_match_evt_prop_id, p_5.cm_is_prepayment_sw
  • Buffers: shared hit=1,655,613 read=51,270
95. 27,046.544 27,046.544 ↑ 1.2 1,699,197 1

Index Scan using cm_acct_pay_match_evt_prop_part6_pkey on cisadm.cm_acct_pay_match_evt_prop_part6 p_6 (cost=0.43..88,927.93 rows=2,064,797 width=18) (actual time=5.843..27,046.544 rows=1,699,197 loops=1)

  • Output: p_6.cm_acct_pay_match_evt_prop_id, p_6.cm_is_prepayment_sw
  • Buffers: shared hit=1,654,429 read=51,243
96. 27,674.950 27,674.950 ↑ 1.2 1,699,267 1

Index Scan using cm_acct_pay_match_evt_prop_part7_pkey on cisadm.cm_acct_pay_match_evt_prop_part7 p_7 (cost=0.43..88,985.87 rows=2,066,010 width=18) (actual time=20.640..27,674.950 rows=1,699,267 loops=1)

  • Output: p_7.cm_acct_pay_match_evt_prop_id, p_7.cm_is_prepayment_sw
  • Buffers: shared hit=1,654,473 read=51,277
97. 26,950.735 26,950.735 ↑ 1.2 1,699,151 1

Index Scan using cm_acct_pay_match_evt_prop_part8_pkey on cisadm.cm_acct_pay_match_evt_prop_part8 p_8 (cost=0.43..88,951.44 rows=2,065,255 width=18) (actual time=27.365..26,950.735 rows=1,699,151 loops=1)

  • Output: p_8.cm_acct_pay_match_evt_prop_id, p_8.cm_is_prepayment_sw
  • Buffers: shared hit=1,654,357 read=51,259
98. 27,423.905 27,423.905 ↑ 1.2 1,700,316 1

Index Scan using cm_acct_pay_match_evt_prop_part9_pkey on cisadm.cm_acct_pay_match_evt_prop_part9 p_9 (cost=0.43..89,015.24 rows=2,066,868 width=18) (actual time=21.352..27,423.905 rows=1,700,316 loops=1)

  • Output: p_9.cm_acct_pay_match_evt_prop_id, p_9.cm_is_prepayment_sw
  • Buffers: shared hit=1,655,501 read=51,289
99. 27,571.524 27,571.524 ↑ 1.2 1,700,021 1

Index Scan using cm_acct_pay_match_evt_prop_part10_pkey on cisadm.cm_acct_pay_match_evt_prop_part10 p_10 (cost=0.43..88,912.58 rows=2,064,497 width=18) (actual time=11.336..27,571.524 rows=1,700,021 loops=1)

  • Output: p_10.cm_acct_pay_match_evt_prop_id, p_10.cm_is_prepayment_sw
  • Buffers: shared hit=1,655,260 read=51,239
100. 26,570.453 26,570.453 ↑ 1.2 1,701,259 1

Index Scan using cm_acct_pay_match_evt_prop_part11_pkey on cisadm.cm_acct_pay_match_evt_prop_part11 p_11 (cost=0.43..89,053.24 rows=2,067,646 width=18) (actual time=22.766..26,570.453 rows=1,701,259 loops=1)

  • Output: p_11.cm_acct_pay_match_evt_prop_id, p_11.cm_is_prepayment_sw
  • Buffers: shared hit=1,656,426 read=51,314
101. 26,447.045 26,447.045 ↑ 1.2 1,698,419 1

Index Scan using cm_acct_pay_match_evt_prop_part12_pkey on cisadm.cm_acct_pay_match_evt_prop_part12 p_12 (cost=0.43..88,912.48 rows=2,064,418 width=18) (actual time=18.591..26,447.045 rows=1,698,419 loops=1)

  • Output: p_12.cm_acct_pay_match_evt_prop_id, p_12.cm_is_prepayment_sw
  • Buffers: shared hit=1,653,651 read=51,233
102. 27,320.081 27,320.081 ↑ 1.2 1,699,217 1

Index Scan using cm_acct_pay_match_evt_prop_part13_pkey on cisadm.cm_acct_pay_match_evt_prop_part13 p_13 (cost=0.43..88,974.29 rows=2,065,751 width=18) (actual time=14.493..27,320.081 rows=1,699,217 loops=1)

  • Output: p_13.cm_acct_pay_match_evt_prop_id, p_13.cm_is_prepayment_sw
  • Buffers: shared hit=1,654,421 read=51,267
103. 27,254.386 27,254.386 ↑ 1.2 1,703,794 1

Index Scan using cm_acct_pay_match_evt_prop_part14_pkey on cisadm.cm_acct_pay_match_evt_prop_part14 p_14 (cost=0.43..89,133.74 rows=2,069,562 width=18) (actual time=24.162..27,254.386 rows=1,703,794 loops=1)

  • Output: p_14.cm_acct_pay_match_evt_prop_id, p_14.cm_is_prepayment_sw
  • Buffers: shared hit=1,658,911 read=51,363
104. 27,513.164 27,513.164 ↑ 1.2 1,698,318 1

Index Scan using cm_acct_pay_match_evt_prop_part15_pkey on cisadm.cm_acct_pay_match_evt_prop_part15 p_15 (cost=0.43..88,935.13 rows=2,064,974 width=18) (actual time=16.218..27,513.164 rows=1,698,318 loops=1)

  • Output: p_15.cm_acct_pay_match_evt_prop_id, p_15.cm_is_prepayment_sw
  • Buffers: shared hit=1,653,549 read=51,241
105. 2,549.678 3,627,851.200 ↑ 14.1 1,324,947 1

Materialize (cost=13,603,156.36..13,696,449.11 rows=18,658,550 width=74) (actual time=3,622,493.843..3,627,851.200 rows=1,324,947 loops=1)

  • Output: ft.match_evt_id, ft.parent_id, ae.cur_amt, ae.show_on_bill_sw, ae.debt_cl_cd, ae.cm_acct_pay_match_evt_prop_id, rp.cm_retail_type
  • Buffers: shared hit=197,239,491 read=3,277,675
106. 7,414.759 3,625,301.522 ↑ 14.1 1,324,947 1

Sort (cost=13,603,156.36..13,649,802.73 rows=18,658,550 width=74) (actual time=3,622,493.820..3,625,301.522 rows=1,324,947 loops=1)

  • Output: ft.match_evt_id, ft.parent_id, ae.cur_amt, ae.show_on_bill_sw, ae.debt_cl_cd, ae.cm_acct_pay_match_evt_prop_id, rp.cm_retail_type
  • Sort Key: ae.cm_acct_pay_match_evt_prop_id
  • Sort Method: quicksort Memory: 235,473kB
  • Buffers: shared hit=197,239,491 read=3,277,675
107. 57,931.424 3,617,886.763 ↑ 14.1 1,324,947 1

Merge Join (cost=8,669,074.09..10,864,227.60 rows=18,658,550 width=74) (actual time=2,824,277.181..3,617,886.763 rows=1,324,947 loops=1)

  • Output: ft.match_evt_id, ft.parent_id, ae.cur_amt, ae.show_on_bill_sw, ae.debt_cl_cd, ae.cm_acct_pay_match_evt_prop_id, rp.cm_retail_type
  • Merge Cond: (rp.cm_rate_prop_id = ae.cm_rate_prop_id)
  • Buffers: shared hit=197,239,491 read=3,277,675
108. 213,343.719 732,311.176 ↑ 1.1 22,490,256 1

Merge Append (cost=7.16..1,854,931.83 rows=24,140,237 width=33) (actual time=320.922..732,311.176 rows=22,490,256 loops=1)

  • Sort Key: rp.cm_rate_prop_id
  • Buffers: shared hit=21,774,213 read=801,685
109. 32,152.201 32,152.201 ↑ 1.1 1,406,354 1

Index Scan using cm_rate_prop_part0_pkey on cisadm.cm_rate_prop_part0 rp (cost=0.43..78,234.93 rows=1,509,114 width=33) (actual time=34.078..32,152.201 rows=1,406,354 loops=1)

  • Output: rp.cm_retail_type, rp.cm_rate_prop_id
  • Buffers: shared hit=1,361,588 read=50,121
110. 33,439.786 33,439.786 ↑ 1.1 1,406,720 1

Index Scan using cm_rate_prop_part1_pkey on cisadm.cm_rate_prop_part1 rp_1 (cost=0.43..78,270.72 rows=1,509,850 width=33) (actual time=30.242..33,439.786 rows=1,406,720 loops=1)

  • Output: rp_1.cm_retail_type, rp_1.cm_rate_prop_id
  • Buffers: shared hit=1,361,935 read=50,142
111. 32,868.479 32,868.479 ↑ 1.1 1,405,826 1

Index Scan using cm_rate_prop_part2_pkey on cisadm.cm_rate_prop_part2 rp_2 (cost=0.43..78,227.68 rows=1,508,962 width=33) (actual time=17.237..32,868.479 rows=1,405,826 loops=1)

  • Output: rp_2.cm_retail_type, rp_2.cm_rate_prop_id
  • Buffers: shared hit=1,361,060 read=50,115
112. 32,221.082 32,221.082 ↑ 1.1 1,406,761 1

Index Scan using cm_rate_prop_part3_pkey on cisadm.cm_rate_prop_part3 rp_3 (cost=0.43..78,290.33 rows=1,510,288 width=33) (actual time=19.909..32,221.082 rows=1,406,761 loops=1)

  • Output: rp_3.cm_retail_type, rp_3.cm_rate_prop_id
  • Buffers: shared hit=1,361,970 read=50,152
113. 32,823.963 32,823.963 ↑ 1.1 1,405,073 1

Index Scan using cm_rate_prop_part4_pkey on cisadm.cm_rate_prop_part4 rp_4 (cost=0.43..78,201.37 rows=1,508,602 width=33) (actual time=9.488..32,823.963 rows=1,405,073 loops=1)

  • Output: rp_4.cm_retail_type, rp_4.cm_rate_prop_id
  • Buffers: shared hit=1,360,322 read=50,094
114. 32,247.348 32,247.348 ↑ 1.1 1,403,409 1

Index Scan using cm_rate_prop_part5_pkey on cisadm.cm_rate_prop_part5 rp_5 (cost=0.43..78,100.62 rows=1,506,580 width=33) (actual time=16.367..32,247.348 rows=1,403,409 loops=1)

  • Output: rp_5.cm_retail_type, rp_5.cm_rate_prop_id
  • Buffers: shared hit=1,358,733 read=50,032
115. 32,081.342 32,081.342 ↑ 1.1 1,406,687 1

Index Scan using cm_rate_prop_part6_pkey on cisadm.cm_rate_prop_part6 rp_6 (cost=0.43..78,269.78 rows=1,510,008 width=33) (actual time=23.209..32,081.342 rows=1,406,687 loops=1)

  • Output: rp_6.cm_retail_type, rp_6.cm_rate_prop_id
  • Buffers: shared hit=1,361,890 read=50,138
116. 33,015.972 33,015.972 ↑ 1.1 1,404,143 1

Index Scan using cm_rate_prop_part7_pkey on cisadm.cm_rate_prop_part7 rp_7 (cost=0.43..78,132.76 rows=1,507,126 width=33) (actual time=16.474..33,015.972 rows=1,404,143 loops=1)

  • Output: rp_7.cm_retail_type, rp_7.cm_rate_prop_id
  • Buffers: shared hit=1,359,431 read=50,054
117. 32,465.024 32,465.024 ↑ 1.1 1,406,771 1

Index Scan using cm_rate_prop_part8_pkey on cisadm.cm_rate_prop_part8 rp_8 (cost=0.43..78,268.00 rows=1,509,826 width=33) (actual time=20.988..32,465.024 rows=1,406,771 loops=1)

  • Output: rp_8.cm_retail_type, rp_8.cm_rate_prop_id
  • Buffers: shared hit=1,361,986 read=50,140
118. 32,730.215 32,730.215 ↑ 1.1 1,404,067 1

Index Scan using cm_rate_prop_part9_pkey on cisadm.cm_rate_prop_part9 rp_9 (cost=0.43..78,128.90 rows=1,507,074 width=33) (actual time=23.285..32,730.215 rows=1,404,067 loops=1)

  • Output: rp_9.cm_retail_type, rp_9.cm_rate_prop_id
  • Buffers: shared hit=1,359,367 read=50,051
119. 31,519.119 31,519.119 ↑ 1.1 1,405,334 1

Index Scan using cm_rate_prop_part10_pkey on cisadm.cm_rate_prop_part10 rp_10 (cost=0.43..78,190.21 rows=1,508,307 width=33) (actual time=20.877..31,519.119 rows=1,405,334 loops=1)

  • Output: rp_10.cm_retail_type, rp_10.cm_rate_prop_id
  • Buffers: shared hit=1,360,591 read=50,091
120. 32,936.477 32,936.477 ↑ 1.1 1,405,772 1

Index Scan using cm_rate_prop_part11_pkey on cisadm.cm_rate_prop_part11 rp_11 (cost=0.43..78,253.17 rows=1,509,557 width=33) (actual time=18.179..32,936.477 rows=1,405,772 loops=1)

  • Output: rp_11.cm_retail_type, rp_11.cm_rate_prop_id
  • Buffers: shared hit=1,361,000 read=50,128
121. 32,011.001 32,011.001 ↑ 1.1 1,405,292 1

Index Scan using cm_rate_prop_part12_pkey on cisadm.cm_rate_prop_part12 rp_12 (cost=0.43..78,210.15 rows=1,508,676 width=33) (actual time=23.000..32,011.001 rows=1,405,292 loops=1)

  • Output: rp_12.cm_retail_type, rp_12.cm_rate_prop_id
  • Buffers: shared hit=1,360,547 read=50,102
122. 32,306.128 32,306.128 ↑ 1.1 1,406,698 1

Index Scan using cm_rate_prop_part13_pkey on cisadm.cm_rate_prop_part13 rp_13 (cost=0.43..78,262.34 rows=1,509,668 width=33) (actual time=14.616..32,306.128 rows=1,406,698 loops=1)

  • Output: rp_13.cm_retail_type, rp_13.cm_rate_prop_id
  • Buffers: shared hit=1,361,907 read=50,137
123. 31,960.281 31,960.281 ↑ 1.1 1,406,062 1

Index Scan using cm_rate_prop_part14_pkey on cisadm.cm_rate_prop_part14 rp_14 (cost=0.43..78,214.26 rows=1,508,733 width=33) (actual time=15.875..31,960.281 rows=1,406,062 loops=1)

  • Output: rp_14.cm_retail_type, rp_14.cm_rate_prop_id
  • Buffers: shared hit=1,361,299 read=50,108
124. 32,189.039 32,189.039 ↑ 1.1 1,405,302 1

Index Scan using cm_rate_prop_part15_pkey on cisadm.cm_rate_prop_part15 rp_15 (cost=0.43..78,170.37 rows=1,507,866 width=33) (actual time=16.935..32,189.039 rows=1,405,302 loops=1)

  • Output: rp_15.cm_retail_type, rp_15.cm_rate_prop_id
  • Buffers: shared hit=1,360,587 read=50,080
125. 9,213.612 2,827,644.163 ↑ 3.4 1,324,947 1

Sort (cost=8,525,943.42..8,537,158.13 rows=4,485,885 width=73) (actual time=2,823,955.383..2,827,644.163 rows=1,324,947 loops=1)

  • Output: ft.match_evt_id, ft.parent_id, ae.cur_amt, ae.show_on_bill_sw, ae.debt_cl_cd, ae.cm_acct_pay_match_evt_prop_id, ae.cm_rate_prop_id
  • Sort Key: ae.cm_rate_prop_id
  • Sort Method: quicksort Memory: 235,473kB
  • Buffers: shared hit=175,465,278 read=2,475,990
126. 128,160.114 2,818,430.551 ↑ 3.4 1,324,947 1

Hash Join (cost=3,177,339.94..8,030,321.28 rows=4,485,885 width=73) (actual time=2,353,741.821..2,818,430.551 rows=1,324,947 loops=1)

  • Output: ft.match_evt_id, ft.parent_id, ae.cur_amt, ae.show_on_bill_sw, ae.debt_cl_cd, ae.cm_acct_pay_match_evt_prop_id, ae.cm_rate_prop_id
  • Hash Cond: (ae.cm_generative_ft_id = ft.ft_id)
  • Buffers: shared hit=175,465,278 read=2,475,990
127. 181,725.363 336,560.902 ↓ 1.0 123,233,998 1

Append (cost=0.56..3,883,869.60 rows=123,233,794 width=60) (actual time=0.342..336,560.902 rows=123,233,998 loops=1)

  • Buffers: shared hit=108,818,133 read=1,283,710
128. 10,032.336 10,032.336 ↓ 1.0 7,703,506 1

Index Only Scan using cm_account_entry_part0_cm_generative_ft_id_cm_rate_prop_id__idx on cisadm.cm_account_entry_part0 ae (cost=0.56..204,268.03 rows=7,703,498 width=60) (actual time=0.247..10,032.336 rows=7,703,506 loops=1)

  • Output: ae.cur_amt, ae.show_on_bill_sw, ae.debt_cl_cd, ae.cm_acct_pay_match_evt_prop_id, ae.cm_generative_ft_id, ae.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,801,626 read=80,248
129. 9,681.523 9,681.523 ↓ 1.0 7,700,713 1

Index Only Scan using cm_account_entry_part1_cm_generative_ft_id_cm_rate_prop_id__idx on cisadm.cm_account_entry_part1 ae_1 (cost=0.56..204,194.31 rows=7,700,710 width=60) (actual time=0.252..9,681.523 rows=7,700,713 loops=1)

  • Output: ae_1.cur_amt, ae_1.show_on_bill_sw, ae_1.debt_cl_cd, ae_1.cm_acct_pay_match_evt_prop_id, ae_1.cm_generative_ft_id, ae_1.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,800,172 read=80,219
130. 9,846.875 9,846.875 ↓ 1.0 7,705,133 1

Index Only Scan using cm_account_entry_part2_cm_generative_ft_id_cm_rate_prop_id__idx on cisadm.cm_account_entry_part2 ae_2 (cost=0.56..204,311.22 rows=7,705,131 width=60) (actual time=0.196..9,846.875 rows=7,705,133 loops=1)

  • Output: ae_2.cur_amt, ae_2.show_on_bill_sw, ae_2.debt_cl_cd, ae_2.cm_acct_pay_match_evt_prop_id, ae_2.cm_generative_ft_id, ae_2.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,805,753 read=80,265
131. 10,231.107 10,231.107 ↓ 1.0 7,701,714 1

Index Only Scan using cm_account_entry_part3_cm_generative_ft_id_cm_rate_prop_id__idx on cisadm.cm_account_entry_part3 ae_3 (cost=0.56..204,221.41 rows=7,701,710 width=60) (actual time=0.182..10,231.107 rows=7,701,714 loops=1)

  • Output: ae_3.cur_amt, ae_3.show_on_bill_sw, ae_3.debt_cl_cd, ae_3.cm_acct_pay_match_evt_prop_id, ae_3.cm_generative_ft_id, ae_3.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,799,250 read=80,230
132. 9,881.325 9,881.325 ↓ 1.0 7,697,964 1

Index Only Scan using cm_account_entry_part4_cm_generative_ft_id_cm_rate_prop_id__idx on cisadm.cm_account_entry_part4 ae_4 (cost=0.56..204,122.12 rows=7,697,951 width=60) (actual time=0.191..9,881.325 rows=7,697,964 loops=1)

  • Output: ae_4.cur_amt, ae_4.show_on_bill_sw, ae_4.debt_cl_cd, ae_4.cm_acct_pay_match_evt_prop_id, ae_4.cm_generative_ft_id, ae_4.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,797,641 read=80,191
133. 9,558.267 9,558.267 ↓ 1.0 7,701,009 1

Index Only Scan using cm_account_entry_part5_cm_generative_ft_id_cm_rate_prop_id__idx on cisadm.cm_account_entry_part5 ae_5 (cost=0.56..204,201.70 rows=7,700,983 width=60) (actual time=0.175..9,558.267 rows=7,701,009 loops=1)

  • Output: ae_5.cur_amt, ae_5.show_on_bill_sw, ae_5.debt_cl_cd, ae_5.cm_acct_pay_match_evt_prop_id, ae_5.cm_generative_ft_id, ae_5.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,799,642 read=80,222
134. 9,268.260 9,268.260 ↓ 1.0 7,699,578 1

Index Only Scan using cm_account_entry_part6_cm_generative_ft_id_cm_rate_prop_id__idx on cisadm.cm_account_entry_part6 ae_6 (cost=0.56..204,163.93 rows=7,699,565 width=60) (actual time=0.152..9,268.260 rows=7,699,578 loops=1)

  • Output: ae_6.cur_amt, ae_6.show_on_bill_sw, ae_6.debt_cl_cd, ae_6.cm_acct_pay_match_evt_prop_id, ae_6.cm_generative_ft_id, ae_6.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,797,025 read=80,207
135. 9,325.447 9,325.447 ↓ 1.0 7,701,500 1

Index Only Scan using cm_account_entry_part7_cm_generative_ft_id_cm_rate_prop_id__idx on cisadm.cm_account_entry_part7 ae_7 (cost=0.56..204,213.57 rows=7,701,481 width=60) (actual time=0.200..9,325.447 rows=7,701,500 loops=1)

  • Output: ae_7.cur_amt, ae_7.show_on_bill_sw, ae_7.debt_cl_cd, ae_7.cm_acct_pay_match_evt_prop_id, ae_7.cm_generative_ft_id, ae_7.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,799,995 read=80,227
136. 10,610.312 10,610.312 ↓ 1.0 7,700,557 1

Index Only Scan using cm_account_entry_part8_cm_generative_ft_id_cm_rate_prop_id__idx on cisadm.cm_account_entry_part8 ae_8 (cost=0.56..204,190.85 rows=7,700,553 width=60) (actual time=0.192..10,610.312 rows=7,700,557 loops=1)

  • Output: ae_8.cur_amt, ae_8.show_on_bill_sw, ae_8.debt_cl_cd, ae_8.cm_acct_pay_match_evt_prop_id, ae_8.cm_generative_ft_id, ae_8.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,800,478 read=80,218
137. 9,610.334 9,610.334 ↓ 1.0 7,705,163 1

Index Only Scan using cm_account_entry_part9_cm_generative_ft_id_cm_rate_prop_id__idx on cisadm.cm_account_entry_part9 ae_9 (cost=0.56..204,311.52 rows=7,705,151 width=60) (actual time=0.226..9,610.334 rows=7,705,163 loops=1)

  • Output: ae_9.cur_amt, ae_9.show_on_bill_sw, ae_9.debt_cl_cd, ae_9.cm_acct_pay_match_evt_prop_id, ae_9.cm_generative_ft_id, ae_9.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,803,721 read=80,266
138. 9,388.497 9,388.497 ↓ 1.0 7,700,800 1

Index Only Scan using cm_account_entry_part10_cm_generative_ft_id_cm_rate_prop_i_idx1 on cisadm.cm_account_entry_part10 ae_10 (cost=0.56..204,195.28 rows=7,700,775 width=60) (actual time=0.190..9,388.497 rows=7,700,800 loops=1)

  • Output: ae_10.cur_amt, ae_10.show_on_bill_sw, ae_10.debt_cl_cd, ae_10.cm_acct_pay_match_evt_prop_id, ae_10.cm_generative_ft_id, ae_10.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,799,726 read=80,220
139. 9,448.367 9,448.367 ↓ 1.0 7,703,047 1

Index Only Scan using cm_account_entry_part11_cm_generative_ft_id_cm_rate_prop_i_idx1 on cisadm.cm_account_entry_part11 ae_11 (cost=0.56..204,256.73 rows=7,703,038 width=60) (actual time=0.181..9,448.367 rows=7,703,047 loops=1)

  • Output: ae_11.cur_amt, ae_11.show_on_bill_sw, ae_11.debt_cl_cd, ae_11.cm_acct_pay_match_evt_prop_id, ae_11.cm_generative_ft_id, ae_11.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,801,018 read=80,244
140. 9,584.103 9,584.103 ↓ 1.0 7,702,176 1

Index Only Scan using cm_account_entry_part12_cm_generative_ft_id_cm_rate_prop_i_idx1 on cisadm.cm_account_entry_part12 ae_12 (cost=0.56..204,231.58 rows=7,702,168 width=60) (actual time=0.167..9,584.103 rows=7,702,176 loops=1)

  • Output: ae_12.cur_amt, ae_12.show_on_bill_sw, ae_12.debt_cl_cd, ae_12.cm_acct_pay_match_evt_prop_id, ae_12.cm_generative_ft_id, ae_12.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,801,071 read=80,234
141. 9,074.172 9,074.172 ↓ 1.0 7,703,608 1

Index Only Scan using cm_account_entry_part13_cm_generative_ft_id_cm_rate_prop_i_idx1 on cisadm.cm_account_entry_part13 ae_13 (cost=0.56..204,270.37 rows=7,703,581 width=60) (actual time=0.212..9,074.172 rows=7,703,608 loops=1)

  • Output: ae_13.cur_amt, ae_13.show_on_bill_sw, ae_13.debt_cl_cd, ae_13.cm_acct_pay_match_evt_prop_id, ae_13.cm_generative_ft_id, ae_13.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,803,806 read=80,249
142. 9,887.069 9,887.069 ↓ 1.0 7,705,794 1

Index Only Scan using cm_account_entry_part14_cm_generative_ft_id_cm_rate_prop_i_idx1 on cisadm.cm_account_entry_part14 ae_14 (cost=0.56..204,327.57 rows=7,705,781 width=60) (actual time=0.239..9,887.069 rows=7,705,794 loops=1)

  • Output: ae_14.cur_amt, ae_14.show_on_bill_sw, ae_14.debt_cl_cd, ae_14.cm_acct_pay_match_evt_prop_id, ae_14.cm_generative_ft_id, ae_14.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,806,199 read=80,272
143. 9,407.545 9,407.545 ↓ 1.0 7,701,736 1

Index Only Scan using cm_account_entry_part15_cm_generative_ft_id_cm_rate_prop_i_idx1 on cisadm.cm_account_entry_part15 ae_15 (cost=0.56..204,220.43 rows=7,701,718 width=60) (actual time=0.187..9,407.545 rows=7,701,736 loops=1)

  • Output: ae_15.cur_amt, ae_15.show_on_bill_sw, ae_15.debt_cl_cd, ae_15.cm_acct_pay_match_evt_prop_id, ae_15.cm_generative_ft_id, ae_15.cm_rate_prop_id
  • Heap Fetches: 0
  • Buffers: shared hit=6,801,010 read=80,198
144. 5,554.731 2,353,709.535 ↑ 3.6 1,044,860 1

Hash (cost=3,130,919.27..3,130,919.27 rows=3,713,609 width=39) (actual time=2,353,709.534..2,353,709.535 rows=1,044,860 loops=1)

  • Output: ft.match_evt_id, ft.parent_id, ft.ft_id
  • Buckets: 4,194,304 Batches: 1 Memory Usage: 105,215kB
  • Buffers: shared hit=66,647,145 read=1,192,280
145. 2,348,154.804 2,348,154.804 ↑ 3.6 1,044,860 1

Nested Loop (cost=0.56..3,130,919.27 rows=3,713,609 width=39) (actual time=681,137.192..2,348,154.804 rows=1,044,860 loops=1)