explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZdeB

Settings
# exclusive inclusive rows x rows loops node
1. 7.224 1,238.818 ↑ 2,681.6 4,642 1

Unique (cost=173,772,238.93..177,849,006.75 rows=12,448,146 width=7,974) (actual time=1,225.797..1,238.818 rows=4,642 loops=1)

  • IS NULL) THEN NULL::text WHEN (cv.addrln64_2 IS NULL) THEN CASE WHEN (cv.zip4 IS NULL) THEN concat(cv.addrln64_1, ', ', cv.city35, ', ', cv.us_st_cd, ' ', cv.zip5) ELSE concat(cv.addrln64_1, ', ', cv.city35, ', ', cv.us_st_cd, ' ', cv.zip5, '-', cv.zip4) END WHEN (cv.addrln64_3 IS NULL) THEN CASE WHEN (cv.zip4 IS NULL) THEN concat(cv.addrln64_1, ', ', cv.addrln64_2, ', ', cv.city35, ', ', cv.us_st_cd, ' ', cv.zip5) ELSE concat(cv.addrln64_1, ', ', cv.addrln64_2, ', ', cv.city35, ', ', cv.us_st_cd, ' ', cv.zip5, '-', cv.zip4) END WHEN (cv.addrln64_4 IS NULL) THEN CASE WHEN (cv.zip4 IS NULL) THEN concat(cv.addrln64_1, ', ', cv.addrln64_2, ', ', cv.addrln64_3, ', ', cv.city35, ', ', cv.us_st_cd, ' ', cv.zip5) ELSE concat(cv.addrln64_1, ', ', cv.addrln64_2, ', ', cv.addrln64_3, ', ', cv.city35, ', ', cv.us_st_cd, ' ', cv.zip5, '-', cv.zip4) END ELSE CASE WHEN (cv.zip4 IS NULL) THEN concat(cv.addrln64_1, ' ', cv.addrln64_2, ' ', cv.addrln64_3, ' ', cv.addrln64_4, ' ', cv.city35, ', ', cv.us_st_cd, ' ', cv.zip5) ELSE concat(cv.addrln64_1, ' ', cv.addrln64_2, ' ', cv.addrln64_3, ' ', cv.addrln64_4, ' ', cv.city35, ', ', cv.us_st_cd, ' ', cv.zip5, '-', cv.zip4) END END), cv.tin, rv.addr_attn_ln, (btrim(concat(rv.org_nm, ' ', rv.fnm, ' ', rv.mnm, ' ', rv.lnm, ' ', rv.nm_sfx))), rv.tin, rv.addrln64_1, rv.addrln64_2, rv.addrln64_3, rv.addrln64_4, rv.city35, rv.us_st_cd, rv.zip5, (CASE WHEN (rv.addrln64_1 IS NULL) THEN NULL::text WHEN (rv.addrln64_2 IS NULL) THEN CASE WHEN (rv.zip4 IS NULL) THEN concat(rv.addrln64_1, ', ', rv.city35, ', ', rv.us_st_cd, ' ', rv.zip5) ELSE concat(rv.addrln64_1, ', ', rv.city35,', ', rv.us_st_cd, ' ', rv.zip5, '-', rv.zip4) END WHEN (rv.addrln64_3 IS NULL) THEN CASE WHEN (rv.zip4 IS NULL) THEN concat(rv.addrln64_1, ', ', rv.addrln64_2, ', ', rv.city35, ', ', rv.us_st_cd, ' ', rv.zip5) ELSE concat(rv.addrln64_1, ', ', rv.addrln64_2, ', ', rv.city35, ', ', rv.us_st_cd, ' ', rv.zip5, '-', rv.zip4) END WHEN (rv.addrln64_4 IS NULL) THEN CASE WHEN (rv.zip4 IS NULL) THEN concat(rv.addrln64_1, ', ', rv.addrln64_2, ', ', rv.addrln64_3, ', ', rv.city35, ', ', rv.us_st_cd, ' ', rv.zip5) ELSE concat(rv.addrln64_1, ', ', rv.addrln64_2, ', ', rv.addrln64_3, ', ', rv.city35, ', ', rv.us_st_cd, ' ', rv.zip5, '-', rv.zip4) END ELSE CASE WHEN (rv.zip4 IS NULL) THEN concat(rv.addrln64_1, ' ', rv.addrln64_2, ' ', rv.addrln64_3, ' ', rv.addrln64_4, ' ', rv.city35, ', ', rv.us_st_cd, ' ', rv.zip5)
  • ELSE concat(rv.addrln64_1, ' ', rv.addrln64_2, ' ', rv.addrln64_3, ' ', rv.addrln64_4, ' ', rv.city35, ', ', rv.us_st_cd, ' ', rv.zip5, '-', rv.zip4) END END), rv.addr_uid, rv.lgcy_vndr_id, rv.lgcy_sfx_id, tpt.pt_pmtrqst_uid, tpt.dds_fscl_ref_id, tpt.rqst_typ_cd, tpt.fscl_stus_cd, tpt.pmtrqact_rsn_txt, tpt.paybl_amt, tpt.oblgd_amt, tooa.org_oblgd_amt, topa.org_paybl_amt, tpt.stsch_insrt_ts, tpt.paach_insrt_ts, tpt.obach_insrt_ts, tpt.nmnch_insrt_ts, (CASE WHEN ((tpr.pmt_dt IS NULL) AND (((tpt.fscl_stus_cd)::text = 'SBMT'::text) OR ((tpt.fscl_stus_cd)::text = 'PAID'::text))) THEN date(timezone('America/New_York'::text, tpt.stsch_insrt_ts)) WHEN (tpr.pmt_dt IS NOT NULL) THEN tpr.pmt_dt ELSE NULL::date END), (CASE WHEN (((tpt.fscl_stus_cd)::text = 'SBMT'::text) OR ((tpt.fscl_stus_cd)::text = 'PAID'::text)) THEN tpt.paybl_amt ELSE NULL::numeric END), tpr.pmt_num, tpt.dds_pmt_grp_ref_id, tpt.dds_pmt_btch_fy, tpt.dds_pmt_btch_num, (timezone('America/New_York'::text, tpt.xmitd_ts)), tpt.pmtrqst_pkg_uid, tpt.pmtrqst_li_id, (date(timezone('America/New_York'::text, tpt.stus_chg_ts))), sfl.stfsys_vndr_id, sfl.stfsys_addr_id, (date(timezone('America/New_York'::text, aprv.pmtrqact_ts))), aprv.pmtrqact_pin, aprv.aprv_name, (date(timezone('America/New_York'::text, cert.pmtrqact_ts))), cert.pmtrqact_pin, cert.cert_name
2.          

CTE t_dcps_case

3. 8.427 88.164 ↑ 1.3 6,547 1

Unique (cost=8,553.18..9,523.38 rows=8,624 width=1,515) (actual time=79.150..88.164 rows=6,547 loops=1)

4. 28.982 79.737 ↓ 1.0 8,627 1

Sort (cost=8,553.18..8,574.74 rows=8,624 width=1,515) (actual time=79.148..79.737 rows=8,627 loops=1)

  • Sort Key: dcpscase_t.dcps_case_uid, dcpscase_t.ccreatr_ocd, dcpscase_t.fnm, dcpscase_t.lnm, dcpscase_t.dob, dcpscase_t.sex, dcpscase_t.most_rcnt_flg_dt, dcpscase_t.cossn, ("right"((dcpscase_t.cossn)::text, 4)), dcpscase_t.case_rcpdt, dcpscase_t.case_stus_cd, dcpscase_t.lun, dcpscase_t.lex, dcpscase_t.asgnd_user_org_uid, dcpscase_t.case_asgnd_dt, dcpscase_t.adjulvl_cd, adjlvl_t.cdesc, adjstus_t.cdesc, dcpscase_t.case_clsfn_cd, cselvl.lbl, cselvl.short_desc, caseclm_t.dds_cdr_apl_lvl, dcpscase_t.cdr_typ, cdrtyp.short_desc, userorganization_t.user_org_uid, userorganization_t.lex, organization_t.ofc_cd, organization_t.st_cd, organization_t.rgn_cd, organization_t.lun, dcpsuser_t.user_uid, dcpsuser_t.pin, dcpsuser_t.fnm, dcpsuser_t.lnm, dcpsuser_t.emailaddr, organization_t.org_nm, organization_t.org_desc, organization_t.org_typ_cd, ofcd_t.ocd, ofcd_t.otyp, ofcd_t.ofc_nm, ofcd_t.cmpnt_typ, ofcd_t.st, ofcd_t.roacr
  • Sort Method: quicksort Memory: 3,621kB
5. 4.999 50.755 ↓ 1.0 8,627 1

Hash Left Join (cost=1,445.82..2,298.42 rows=8,624 width=1,515) (actual time=16.514..50.755 rows=8,627 loops=1)

  • Hash Cond: (((dcpscase_t.adjulvl_cd)::text = (cselvl.adjulvl_cd)::text) AND ((dcpscase_t.case_clsfn_cd)::text = (cselvl.case_clsfn_cd)::text) AND ((COALESCE(caseclm_t.dds_cdr_apl_lvl, ''::character varying))::text = (COALESCE(cselvl.cdr_apl_lvl_typ))::text))
6. 4.029 45.747 ↓ 1.0 8,627 1

Hash Left Join (cost=1,444.52..2,178.50 rows=8,624 width=1,367) (actual time=16.485..45.747 rows=8,627 loops=1)

  • Hash Cond: (((caseclm_t.adjulvl_cd)::text = (clmlvl.adjulvl_cd)::text) AND ((dcpscase_t.case_clsfn_cd)::text = (clmlvl.case_clsfn_cd)::text) AND ((COALESCE(caseclm_t.dds_cdr_apl_lvl, ''::character varying))::text = (COALESCE(clmlvl.cdr_apl_lvl_typ))::text))
7. 2.460 41.708 ↓ 1.0 8,627 1

Hash Left Join (cost=1,443.21..2,080.14 rows=8,624 width=1,369) (actual time=16.456..41.708 rows=8,627 loops=1)

  • Hash Cond: ((dcpscase_t.cdr_typ)::text = (cdrtyp.cd)::text)
8. 2.819 39.239 ↓ 1.0 8,627 1

Hash Left Join (cost=1,442.01..2,053.04 rows=8,624 width=1,251) (actual time=16.433..39.239 rows=8,627 loops=1)

  • Hash Cond: (userorganization_t.org_uid = organization_t.org_uid)
9. 2.685 35.196 ↓ 1.0 8,627 1

Hash Left Join (cost=1,315.84..1,904.19 rows=8,624 width=1,195) (actual time=15.186..35.196 rows=8,627 loops=1)

  • Hash Cond: (userorganization_t.user_uid = dcpsuser_t.user_uid)
10. 2.665 31.936 ↓ 1.0 8,627 1

Hash Left Join (cost=1,251.05..1,816.70 rows=8,624 width=1,154) (actual time=14.596..31.936 rows=8,627 loops=1)

  • Hash Cond: (dcpscase_t.asgnd_user_org_uid = userorganization_t.user_org_uid)
11. 2.872 26.046 ↓ 1.0 8,627 1

Hash Left Join (cost=812.54..1,355.54 rows=8,624 width=1,138) (actual time=11.299..26.046 rows=8,627 loops=1)

  • Hash Cond: ((caseclm_t.juris_ocd)::text = (ofcd_t.ocd)::text)
12. 2.250 20.756 ↓ 1.0 8,627 1

Hash Left Join (cost=587.35..1,107.69 rows=8,624 width=1,111) (actual time=8.840..20.756 rows=8,627 loops=1)

  • Hash Cond: ((dcpscase_t.case_stus_cd)::text = (adjstus_t.cd)::text)
13. 2.200 18.500 ↓ 1.0 8,627 1

Hash Left Join (cost=586.26..1,059.98 rows=8,624 width=595) (actual time=8.821..18.500 rows=8,627 loops=1)

  • Hash Cond: ((dcpscase_t.adjulvl_cd)::text = (adjlvl_t.cd)::text)
14. 6.403 16.291 ↓ 1.0 8,627 1

Hash Join (cost=585.15..1,017.04 rows=8,624 width=79) (actual time=8.790..16.291 rows=8,627 loops=1)

  • Hash Cond: (caseclm_t.dcps_case_uid = dcpscase_t.dcps_case_uid)
15. 1.146 1.146 ↓ 1.0 8,627 1

Seq Scan on caseclm_t (cost=0.00..409.24 rows=8,624 width=13) (actual time=0.005..1.146 rows=8,627 loops=1)

16. 2.249 8.742 ↓ 1.0 6,545 1

Hash (cost=503.40..503.40 rows=6,540 width=70) (actual time=8.741..8.742 rows=6,545 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 755kB
17. 6.493 6.493 ↓ 1.0 6,545 1

Seq Scan on dcpscase_t (cost=0.00..503.40 rows=6,540 width=70) (actual time=0.008..6.493 rows=6,545 loops=1)

18. 0.004 0.009 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=524) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.005 0.005 ↑ 1.0 5 1

Seq Scan on adjlvl_t (cost=0.00..1.05 rows=5 width=524) (actual time=0.004..0.005 rows=5 loops=1)

20. 0.003 0.006 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=524) (actual time=0.006..0.006 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.003 0.003 ↑ 1.0 4 1

Seq Scan on adjstus_t (cost=0.00..1.04 rows=4 width=524) (actual time=0.003..0.003 rows=4 loops=1)

22. 1.181 2.418 ↑ 1.0 5,438 1

Hash (cost=156.75..156.75 rows=5,475 width=31) (actual time=2.417..2.418 rows=5,438 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 386kB
23. 1.237 1.237 ↑ 1.0 5,438 1

Seq Scan on ofcd_t (cost=0.00..156.75 rows=5,475 width=31) (actual time=0.007..1.237 rows=5,438 loops=1)

24. 1.757 3.225 ↓ 1.0 11,378 1

Hash (cost=296.56..296.56 rows=11,356 width=16) (actual time=3.225..3.225 rows=11,378 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 704kB
25. 1.468 1.468 ↓ 1.0 11,378 1

Seq Scan on userorganization_t (cost=0.00..296.56 rows=11,356 width=16) (actual time=0.004..1.468 rows=11,378 loops=1)

26. 0.332 0.575 ↓ 1.0 1,547 1

Hash (cost=45.46..45.46 rows=1,546 width=45) (actual time=0.575..0.575 rows=1,547 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 134kB
27. 0.243 0.243 ↓ 1.0 1,547 1

Seq Scan on dcpsuser_t (cost=0.00..45.46 rows=1,546 width=45) (actual time=0.004..0.243 rows=1,547 loops=1)

28. 0.669 1.224 ↓ 1.0 2,720 1

Hash (cost=92.19..92.19 rows=2,719 width=64) (actual time=1.224..1.224 rows=2,720 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 275kB
29. 0.555 0.555 ↓ 1.0 2,720 1

Seq Scan on organization_t (cost=0.00..92.19 rows=2,719 width=64) (actual time=0.004..0.555 rows=2,720 loops=1)

30. 0.004 0.009 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=130) (actual time=0.009..0.009 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.005 0.005 ↑ 1.0 9 1

Seq Scan on cdrtyp_t cdrtyp (cost=0.00..1.09 rows=9 width=130) (actual time=0.004..0.005 rows=9 loops=1)

32. 0.006 0.010 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=36) (actual time=0.009..0.010 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.004 0.004 ↑ 1.0 11 1

Seq Scan on cslvlguicd_t clmlvl (cost=0.00..1.11 rows=11 width=36) (actual time=0.003..0.004 rows=11 loops=1)

34. 0.006 0.009 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=152) (actual time=0.009..0.009 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.003 0.003 ↑ 1.0 11 1

Seq Scan on cslvlguicd_t cselvl (cost=0.00..1.11 rows=11 width=152) (actual time=0.002..0.003 rows=11 loops=1)

36.          

CTE t_dcpscase_title

37. 7.156 22.920 ↑ 1.3 6,545 1

GroupAggregate (cost=1,580.80..1,839.52 rows=8,624 width=102) (actual time=15.244..22.920 rows=6,545 loops=1)

  • Group Key: caseclm_t_1.dcps_case_uid, dcpscase_t_1.dds_ccrnt_case_ind
38. 3.207 15.764 ↓ 1.0 8,627 1

Sort (cost=1,580.80..1,602.36 rows=8,624 width=12) (actual time=15.226..15.764 rows=8,627 loops=1)

  • Sort Key: caseclm_t_1.dcps_case_uid, dcpscase_t_1.dds_ccrnt_case_ind
  • Sort Method: quicksort Memory: 789kB
39. 4.640 12.557 ↓ 1.0 8,627 1

Hash Join (cost=585.15..1,017.04 rows=8,624 width=12) (actual time=7.139..12.557 rows=8,627 loops=1)

  • Hash Cond: (caseclm_t_1.dcps_case_uid = dcpscase_t_1.dcps_case_uid)
40. 0.822 0.822 ↓ 1.0 8,627 1

Seq Scan on caseclm_t caseclm_t_1 (cost=0.00..409.24 rows=8,624 width=10) (actual time=0.007..0.822 rows=8,627 loops=1)

41. 0.876 7.095 ↓ 1.0 6,545 1

Hash (cost=503.40..503.40 rows=6,540 width=6) (actual time=7.095..7.095 rows=6,545 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 320kB
42. 6.219 6.219 ↓ 1.0 6,545 1

Seq Scan on dcpscase_t dcpscase_t_1 (cost=0.00..503.40 rows=6,540 width=6) (actual time=0.007..6.219 rows=6,545 loops=1)

43.          

CTE t_dcpscssrc

44. 0.929 26.545 ↓ 15.7 5,912 1

Unique (cost=1,708.92..1,711.74 rows=376 width=6) (actual time=25.320..26.545 rows=5,912 loops=1)

45. 1.852 25.616 ↓ 15.7 5,912 1

Sort (cost=1,708.92..1,709.86 rows=376 width=6) (actual time=25.318..25.616 rows=5,912 loops=1)

  • Sort Key: dcpscssrc_t.dcps_case_uid, dcpscssrc_t.critl_src_sw
  • Sort Method: quicksort Memory: 470kB
46. 2.387 23.764 ↓ 15.7 5,912 1

Hash Join (cost=1,047.43..1,692.84 rows=376 width=6) (actual time=14.997..23.764 rows=5,912 loops=1)

  • Hash Cond: (dcpscssrc_t.dcps_case_src_uid = "ANY_subquery".max)
47. 4.486 10.196 ↓ 30.2 22,724 1

Hash Join (cost=200.54..839.79 rows=753 width=10) (actual time=3.798..10.196 rows=22,724 loops=1)

  • Hash Cond: (dcpscssrc_t.dcps_case_uid = t_dcps_case.dcps_case_uid)
48. 1.923 1.923 ↑ 1.0 24,327 1

Seq Scan on dcpscssrc_t (cost=0.00..575.33 rows=24,333 width=10) (actual time=0.006..1.923 rows=24,327 loops=1)

49. 0.760 3.787 ↓ 32.7 6,545 1

Hash (cost=198.04..198.04 rows=200 width=4) (actual time=3.787..3.787 rows=6,545 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 295kB
50. 2.232 3.027 ↓ 32.7 6,545 1

HashAggregate (cost=194.04..196.04 rows=200 width=4) (actual time=2.287..3.027 rows=6,545 loops=1)

  • Group Key: t_dcps_case.dcps_case_uid
51. 0.795 0.795 ↑ 1.3 6,547 1

CTE Scan on t_dcps_case (cost=0.00..172.48 rows=8,624 width=4) (actual time=0.001..0.795 rows=6,547 loops=1)

52. 0.747 11.181 ↓ 32.2 6,442 1

Hash (cost=844.39..844.39 rows=200 width=4) (actual time=11.181..11.181 rows=6,442 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 291kB
53. 2.116 10.434 ↓ 32.2 6,442 1

HashAggregate (cost=842.39..844.39 rows=200 width=4) (actual time=9.794..10.434 rows=6,442 loops=1)

  • Group Key: "ANY_subquery".max
54. 0.518 8.318 ↑ 1.0 6,442 1

Subquery Scan on ANY_subquery (cost=697.00..826.24 rows=6,462 width=4) (actual time=6.909..8.318 rows=6,442 loops=1)

55. 5.676 7.800 ↑ 1.0 6,442 1

HashAggregate (cost=697.00..761.62 rows=6,462 width=8) (actual time=6.908..7.800 rows=6,442 loops=1)

  • Group Key: dcpscssrc_t_1.dcps_case_uid
56. 2.124 2.124 ↑ 1.0 24,327 1

Seq Scan on dcpscssrc_t dcpscssrc_t_1 (cost=0.00..575.33 rows=24,333 width=8) (actual time=0.005..2.124 rows=24,327 loops=1)

57.          

CTE t_evrqst

58. 3.893 35.005 ↓ 8.5 4,557 1

HashAggregate (cost=1,143.50..1,148.85 rows=535 width=113) (actual time=33.947..35.005 rows=4,557 loops=1)

  • Group Key: pmt.ev_rqst_uid, et.dcps_case_uid, ct.dmthd_cd, et.med_src_id, CASE WHEN (et.med_src_id IS NULL) THEN 'Claimant'::text ELSE 'Medical'::text END, pt.ltr_typ_cd, pt.pkg_uid, pt.pkg_id, pt.pkg_nm, ft.typ, et.insrt_ts
59. 1.067 31.112 ↓ 8.9 4,769 1

Hash Left Join (cost=459.56..1,128.79 rows=535 width=113) (actual time=18.661..31.112 rows=4,769 loops=1)

  • Hash Cond: (ct.pkg_id = pt.pkg_uid)
60. 0.000 29.849 ↓ 8.6 4,615 1

Nested Loop Left Join (cost=440.07..1,101.94 rows=535 width=28) (actual time=18.459..29.849 rows=4,615 loops=1)

61. 1.576 21.625 ↓ 8.6 4,611 1

Hash Join (cost=439.78..780.54 rows=535 width=24) (actual time=18.439..21.625 rows=4,611 loops=1)

  • Hash Cond: (pmt.ev_rqst_uid = et.ev_rqst_uid)
62. 1.636 1.636 ↑ 1.0 8,932 1

Seq Scan on pmtrqst_t pmt (cost=0.00..301.75 rows=8,975 width=4) (actual time=0.010..1.636 rows=8,932 loops=1)

63. 1.498 18.413 ↓ 8.6 9,362 1

Hash (cost=426.10..426.10 rows=1,094 width=20) (actual time=18.413..18.413 rows=9,362 loops=1)

  • Buckets: 16,384 (originally 2048) Batches: 1 (originally 1) Memory Usage: 604kB
64. 1.890 16.915 ↓ 8.6 9,362 1

Hash Join (cost=200.54..426.10 rows=1,094 width=20) (actual time=14.338..16.915 rows=9,362 loops=1)

  • Hash Cond: (et.dcps_case_uid = t_dcps_case_1.dcps_case_uid)
65. 0.708 0.708 ↑ 1.0 9,362 1

Seq Scan on evrqst_t et (cost=0.00..200.66 rows=9,466 width=20) (actual time=0.004..0.708 rows=9,362 loops=1)

66. 0.798 14.317 ↓ 32.7 6,545 1

Hash (cost=198.04..198.04 rows=200 width=4) (actual time=14.317..14.317 rows=6,545 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 295kB
67. 2.314 13.519 ↓ 32.7 6,545 1

HashAggregate (cost=194.04..196.04 rows=200 width=4) (actual time=12.833..13.519 rows=6,545 loops=1)

  • Group Key: t_dcps_case_1.dcps_case_uid
68. 11.205 11.205 ↑ 1.3 6,547 1

CTE Scan on t_dcps_case t_dcps_case_1 (cost=0.00..172.48 rows=8,624 width=4) (actual time=0.000..11.205 rows=6,547 loops=1)

69. 9.222 9.222 ↑ 1.0 1 4,611

Index Scan using ixf03_rqstuid on corrtrkg_t ct (cost=0.29..0.59 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=4,611)

  • Index Cond: (et.ev_rqst_uid = rqst_uid)
  • Filter: ((rqst_typ_cd)::text = 'MER'::text)
  • Rows Removed by Filter: 0
70. 0.053 0.196 ↓ 1.1 228 1

Hash (cost=16.88..16.88 rows=209 width=57) (actual time=0.196..0.196 rows=228 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
71. 0.037 0.143 ↓ 1.1 228 1

Hash Right Join (cost=13.70..16.88 rows=209 width=57) (actual time=0.103..0.143 rows=228 loops=1)

  • Hash Cond: (ft.pkg_uid = pt.pkg_uid)
72. 0.010 0.010 ↓ 1.0 97 1

Seq Scan on followup_t ft (cost=0.00..2.93 rows=93 width=16) (actual time=0.003..0.010 rows=97 loops=1)

73. 0.047 0.096 ↑ 1.0 209 1

Hash (cost=11.09..11.09 rows=209 width=45) (actual time=0.096..0.096 rows=209 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
74. 0.049 0.049 ↑ 1.0 209 1

Seq Scan on pkg_t pt (cost=0.00..11.09 rows=209 width=45) (actual time=0.004..0.049 rows=209 loops=1)

75.          

CTE t_barcdtrkg

76. 0.372 18.094 ↓ 4,340.0 4,340 1

Subquery Scan on a (cost=951.25..956.94 rows=1 width=60) (actual time=15.202..18.094 rows=4,340 loops=1)

  • Filter: (a.rnk = 1)
  • Rows Removed by Filter: 48
77. 2.309 17.722 ↓ 25.1 4,388 1

WindowAgg (cost=951.25..954.75 rows=175 width=60) (actual time=15.200..17.722 rows=4,388 loops=1)

78. 1.664 15.413 ↓ 25.1 4,388 1

Sort (cost=951.25..951.69 rows=175 width=52) (actual time=15.191..15.413 rows=4,388 loops=1)

  • Sort Key: ct_1.rqst_uid, brt.resp_docu_rcvts
  • Sort Method: quicksort Memory: 644kB
79. 1.366 13.749 ↓ 25.1 4,388 1

Nested Loop (cost=18.83..944.73 rows=175 width=52) (actual time=2.515..13.749 rows=4,388 loops=1)

80. 1.537 7.827 ↓ 23.1 4,556 1

Hash Join (cost=18.54..864.79 rows=197 width=35) (actual time=2.492..7.827 rows=4,556 loops=1)

  • Hash Cond: (ct_1.rqst_uid = t_evrqst.ev_rqst_uid)
81. 3.812 3.812 ↓ 1.0 11,734 1

Seq Scan on corrtrkg_t ct_1 (cost=0.00..815.59 rows=11,679 width=35) (actual time=0.007..3.812 rows=11,734 loops=1)

  • Filter: ((rqst_typ_cd)::text = 'MER'::text)
  • Rows Removed by Filter: 8,732
82. 0.518 2.478 ↓ 22.8 4,553 1

Hash (cost=16.04..16.04 rows=200 width=4) (actual time=2.478..2.478 rows=4,553 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 225kB
83. 1.589 1.960 ↓ 22.8 4,553 1

HashAggregate (cost=12.04..14.04 rows=200 width=4) (actual time=1.452..1.960 rows=4,553 loops=1)

  • Group Key: t_evrqst.ev_rqst_uid
84. 0.371 0.371 ↓ 8.5 4,557 1

CTE Scan on t_evrqst (cost=0.00..10.70 rows=535 width=4) (actual time=0.001..0.371 rows=4,557 loops=1)

85. 4.556 4.556 ↑ 1.0 1 4,556

Index Scan using ixf01_barcdtrkg_t_corrtrkg on barcdtrkg_t brt (cost=0.29..0.40 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=4,556)

  • Index Cond: (crsp_trkg_uid = ct_1.crsp_trkg_uid)
86.          

CTE t_todoitemlist

87. 1.544 6.212 ↓ 25.5 4,507 1

Hash Join (cost=115.27..1,392.91 rows=177 width=123) (actual time=3.468..6.212 rows=4,507 loops=1)

  • Hash Cond: (todoitemlist_t.dcps_case_uid = t_dcpscssrc.dcps_case_uid)
88. 1.301 1.604 ↑ 1.0 4,518 1

Bitmap Heap Scan on todoitemlist_t (cost=100.31..1,364.95 rows=4,611 width=122) (actual time=0.385..1.604 rows=4,518 loops=1)

  • Recheck Cond: ((fup_typ)::text = ANY ('{ME01,ME02}'::text[]))
  • Heap Blocks: exact=910
89. 0.303 0.303 ↓ 1.0 4,751 1

Bitmap Index Scan on ixf04_todoitmcmplddt (cost=0.00..99.15 rows=4,611 width=0) (actual time=0.302..0.303 rows=4,751 loops=1)

  • Index Cond: ((fup_typ)::text = ANY ('{ME01,ME02}'::text[]))
90. 0.682 3.064 ↓ 29.6 5,912 1

Hash (cost=12.46..12.46 rows=200 width=4) (actual time=3.064..3.064 rows=5,912 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 272kB
91. 1.976 2.382 ↓ 29.6 5,912 1

HashAggregate (cost=8.46..10.46 rows=200 width=4) (actual time=1.767..2.382 rows=5,912 loops=1)

  • Group Key: t_dcpscssrc.dcps_case_uid
92. 0.406 0.406 ↓ 15.7 5,912 1

CTE Scan on t_dcpscssrc (cost=0.00..7.52 rows=376 width=4) (actual time=0.000..0.406 rows=5,912 loops=1)

93.          

CTE t_max_ts

94. 8.035 9.433 ↑ 1.0 5,872 1

HashAggregate (cost=955.24..1,016.61 rows=6,137 width=44) (actual time=7.931..9.433 rows=5,872 loops=1)

  • Group Key: pmtrqavt_t.pmtrqst_pkg_uid
95. 1.398 1.398 ↑ 1.0 15,561 1

Seq Scan on pmtrqavt_t (cost=0.00..512.13 rows=16,113 width=26) (actual time=0.008..1.398 rows=15,561 loops=1)

96.          

CTE t_pmtrqst

97. 2.296 54.803 ↓ 8.7 4,611 1

Nested Loop (cost=358.23..747.86 rows=529 width=194) (actual time=42.501..54.803 rows=4,611 loops=1)

98. 2.391 47.896 ↓ 8.7 4,611 1

Hash Right Join (cost=357.95..515.58 rows=529 width=194) (actual time=42.473..47.896 rows=4,611 loops=1)

  • Hash Cond: (mt.pmtrqst_pkg_uid = pmt_1.pmtrqst_pkg_uid)
99. 10.973 10.973 ↑ 1.0 5,872 1

CTE Scan on t_max_ts mt (cost=0.00..122.74 rows=6,137 width=44) (actual time=7.934..10.973 rows=5,872 loops=1)

100. 1.745 34.532 ↓ 8.7 4,611 1

Hash (cost=351.33..351.33 rows=529 width=154) (actual time=34.532..34.532 rows=4,611 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 719kB
101. 1.603 32.787 ↓ 8.7 4,611 1

Hash Join (cost=14.96..351.33 rows=529 width=154) (actual time=30.084..32.787 rows=4,611 loops=1)

  • Hash Cond: (pmt_1.dcps_case_uid = t_dcpscssrc_1.dcps_case_uid)
102. 1.112 1.112 ↑ 1.0 4,612 1

Seq Scan on pmtrqst_t pmt_1 (cost=0.00..324.19 rows=4,630 width=154) (actual time=0.007..1.112 rows=4,612 loops=1)

  • Filter: ((rqst_typ_cd)::text = 'MER'::text)
  • Rows Removed by Filter: 4,320
103. 0.703 30.072 ↓ 29.6 5,912 1

Hash (cost=12.46..12.46 rows=200 width=4) (actual time=30.072..30.072 rows=5,912 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 272kB
104. 1.946 29.369 ↓ 29.6 5,912 1

HashAggregate (cost=8.46..10.46 rows=200 width=4) (actual time=28.745..29.369 rows=5,912 loops=1)

  • Group Key: t_dcpscssrc_1.dcps_case_uid
105. 27.423 27.423 ↓ 15.7 5,912 1

CTE Scan on t_dcpscssrc t_dcpscssrc_1 (cost=0.00..7.52 rows=376 width=4) (actual time=25.321..27.423 rows=5,912 loops=1)

106. 4.611 4.611 ↑ 1.0 1 4,611

Index Scan using pmtrqstpkg_t_pkey on pmtrqstpkg_t pkg (cost=0.28..0.44 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=4,611)

  • Index Cond: (pmtrqst_pkg_uid = pmt_1.pmtrqst_pkg_uid)
107.          

CTE t_pmtresp

108. 0.021 2.661 ↑ 2.3 55 1

Hash Join (cost=21.20..25.16 rows=126 width=33) (actual time=2.605..2.661 rows=55 loops=1)

  • Hash Cond: (a_1.pmtrqst_uid = t_pmtrqst.pt_pmtrqst_uid)
109. 0.039 0.090 ↑ 1.0 126 1

Hash Left Join (cost=2.80..6.41 rows=126 width=33) (actual time=0.051..0.090 rows=126 loops=1)

  • Hash Cond: (a_1.pmt_uid = b.pmt_uid)
110. 0.017 0.017 ↑ 1.0 126 1

Seq Scan on pkgpmta_t a_1 (cost=0.00..3.26 rows=126 width=12) (actual time=0.007..0.017 rows=126 loops=1)

111. 0.019 0.034 ↑ 1.0 80 1

Hash (cost=1.80..1.80 rows=80 width=25) (actual time=0.034..0.034 rows=80 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
112. 0.015 0.015 ↑ 1.0 80 1

Seq Scan on pmt_t b (cost=0.00..1.80 rows=80 width=25) (actual time=0.007..0.015 rows=80 loops=1)

113. 0.498 2.550 ↓ 23.1 4,611 1

Hash (cost=15.90..15.90 rows=200 width=4) (actual time=2.550..2.550 rows=4,611 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 227kB
114. 1.619 2.052 ↓ 23.1 4,611 1

HashAggregate (cost=11.90..13.90 rows=200 width=4) (actual time=1.528..2.052 rows=4,611 loops=1)

  • Group Key: t_pmtrqst.pt_pmtrqst_uid
115. 0.433 0.433 ↓ 8.7 4,611 1

CTE Scan on t_pmtrqst (cost=0.00..10.58 rows=529 width=4) (actual time=0.001..0.433 rows=4,611 loops=1)

116.          

CTE t_omin

117. 0.109 3.153 ↓ 1.5 383 1

GroupAggregate (cost=1,195.47..1,199.85 rows=249 width=8) (actual time=3.019..3.153 rows=383 loops=1)

  • Group Key: pmtrqavt_t_1.pmtrqst_uid
118. 0.137 3.044 ↓ 1.9 477 1

Sort (cost=1,195.47..1,196.10 rows=252 width=8) (actual time=3.012..3.044 rows=477 loops=1)

  • Sort Key: pmtrqavt_t_1.pmtrqst_uid
  • Sort Method: quicksort Memory: 47kB
119. 1.585 2.907 ↓ 1.9 477 1

Seq Scan on pmtrqavt_t pmtrqavt_t_1 (cost=592.72..1,185.42 rows=252 width=8) (actual time=1.343..2.907 rows=477 loops=1)

  • Filter: ((NOT (hashed SubPlan 10)) AND ((pmtrqact_cd)::text = 'OBACH'::text))
  • Rows Removed by Filter: 15,084
120.          

SubPlan (for Seq Scan)

121. 1.322 1.322 ↓ 0.0 0 1

Seq Scan on pmtrqavt_t pt_1 (cost=0.00..592.69 rows=12 width=4) (actual time=1.322..1.322 rows=0 loops=1)

  • Filter: ((prev_amt IS NOT NULL) AND ((fscl_stus_cd)::text = 'DEND'::text) AND ((pmtrqact_cd)::text = 'STSCH'::text))
  • Rows Removed by Filter: 15,561
122.          

CTE t_orgobl_1

123. 0.117 8.496 ↓ 382.0 382 1

Nested Loop (cost=754.11..760.47 rows=1 width=12) (actual time=6.549..8.496 rows=382 loops=1)

124. 0.183 6.851 ↓ 382.0 382 1

Hash Join (cost=753.83..760.11 rows=1 width=12) (actual time=6.533..6.851 rows=382 loops=1)

  • Hash Cond: ((tm.pmtrqst_uid = pt_2.pmtrqst_uid) AND (tm.min_pmtrqah_uid = pt_2.pmtrqah_uid))
125. 3.229 3.229 ↓ 1.5 383 1

CTE Scan on t_omin tm (cost=0.00..4.98 rows=249 width=8) (actual time=3.019..3.229 rows=383 loops=1)

126. 1.656 3.439 ↑ 1.7 9,643 1

Hash (cost=512.13..512.13 rows=16,113 width=12) (actual time=3.439..3.439 rows=9,643 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 512kB
127. 1.783 1.783 ↑ 1.0 15,561 1

Seq Scan on pmtrqavt_t pt_2 (cost=0.00..512.13 rows=16,113 width=12) (actual time=0.003..1.783 rows=15,561 loops=1)

128. 1.528 1.528 ↑ 1.0 1 382

Index Scan using pmtrqst_pkey on pmtrqst_t rt (cost=0.29..0.36 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=382)

  • Index Cond: (pmtrqst_uid = pt_2.pmtrqst_uid)
129.          

CTE t_orgobl_2

130. 1.957 3.783 ↓ 0.0 0 1

Seq Scan on pmtrqst_t (cost=612.72..936.90 rows=4,488 width=12) (actual time=3.783..3.783 rows=0 loops=1)

  • Filter: (NOT (hashed SubPlan 13))
  • Rows Removed by Filter: 8,932
131.          

SubPlan (for Seq Scan)

132. 0.223 1.826 ↑ 3.4 383 1

HashAggregate (cost=596.23..609.42 rows=1,319 width=4) (actual time=1.781..1.826 rows=383 loops=1)

  • Group Key: pmtrqavt_t_2.pmtrqst_uid
133. 1.603 1.603 ↑ 1.3 1,095 1

Seq Scan on pmtrqavt_t pmtrqavt_t_2 (cost=0.00..592.69 rows=1,413 width=4) (actual time=0.007..1.603 rows=1,095 loops=1)

  • Filter: (((pmtrqact_cd)::text = 'OBACH'::text) OR ((fscl_stus_cd)::text = 'DEND'::text))
  • Rows Removed by Filter: 14,466
134.          

CTE t_dend_o

135. 0.000 1.108 ↓ 0.0 0 1

Nested Loop (cost=0.29..684.33 rows=12 width=12) (actual time=1.108..1.108 rows=0 loops=1)

136. 1.108 1.108 ↓ 0.0 0 1

Seq Scan on pmtrqavt_t pt_3 (cost=0.00..592.69 rows=12 width=8) (actual time=1.108..1.108 rows=0 loops=1)

  • Filter: ((prev_amt IS NOT NULL) AND ((fscl_stus_cd)::text = 'DEND'::text) AND ((pmtrqact_cd)::text = 'STSCH'::text))
  • Rows Removed by Filter: 15,561
137. 0.000 0.000 ↓ 0.0 0

Index Scan using pmtrqst_pkey on pmtrqst_t rt_1 (cost=0.29..7.64 rows=1 width=8) (never executed)

  • Index Cond: (pmtrqst_uid = pt_3.pmtrqst_uid)
138.          

CTE t_org_oblgd_amt

139. 0.206 13.713 ↑ 11.8 382 1

HashAggregate (cost=191.29..236.30 rows=4,501 width=22) (actual time=13.640..13.713 rows=382 loops=1)

  • Group Key: t_orgobl_1.dcps_case_uid, t_orgobl_1.pmtrqst_uid, t_orgobl_1.org_oblgd_amt
140. 0.030 13.507 ↑ 11.8 382 1

Append (cost=0.00..157.54 rows=4,501 width=22) (actual time=6.551..13.507 rows=382 loops=1)

141. 8.584 8.584 ↓ 382.0 382 1

CTE Scan on t_orgobl_1 (cost=0.00..0.02 rows=1 width=22) (actual time=6.550..8.584 rows=382 loops=1)

142. 3.784 3.784 ↓ 0.0 0 1

CTE Scan on t_orgobl_2 (cost=0.00..89.76 rows=4,488 width=22) (actual time=3.784..3.784 rows=0 loops=1)

143. 1.109 1.109 ↓ 0.0 0 1

CTE Scan on t_dend_o (cost=0.00..0.24 rows=12 width=22) (actual time=1.109..1.109 rows=0 loops=1)

144.          

CTE t_omin_pa

145. 0.260 2.938 ↓ 1.7 562 1

HashAggregate (cost=1,187.10..1,190.41 rows=331 width=8) (actual time=2.842..2.938 rows=562 loops=1)

  • Group Key: pmtrqavt_t_3.pmtrqst_uid
146. 1.580 2.678 ↓ 1.9 640 1

Seq Scan on pmtrqavt_t pmtrqavt_t_3 (cost=592.72..1,185.42 rows=336 width=8) (actual time=1.120..2.678 rows=640 loops=1)

  • Filter: ((NOT (hashed SubPlan 17)) AND ((pmtrqact_cd)::text = 'PAACH'::text))
  • Rows Removed by Filter: 14,921
147.          

SubPlan (for Seq Scan)

148. 1.098 1.098 ↓ 0.0 0 1

Seq Scan on pmtrqavt_t pt_4 (cost=0.00..592.69 rows=12 width=4) (actual time=1.098..1.098 rows=0 loops=1)

  • Filter: ((prev_amt IS NOT NULL) AND ((fscl_stus_cd)::text = 'DEND'::text) AND ((pmtrqact_cd)::text = 'STSCH'::text))
  • Rows Removed by Filter: 15,561
149.          

CTE t_orgpaa_1

150. 0.290 7.531 ↓ 562.0 562 1

Nested Loop (cost=754.11..762.54 rows=1 width=12) (actual time=6.340..7.531 rows=562 loops=1)

151. 0.224 6.679 ↓ 562.0 562 1

Hash Join (cost=753.83..762.18 rows=1 width=12) (actual time=6.325..6.679 rows=562 loops=1)

  • Hash Cond: ((tm_1.pmtrqst_uid = pt_5.pmtrqst_uid) AND (tm_1.min_pmtrqah_uid = pt_5.pmtrqah_uid))
152. 3.051 3.051 ↓ 1.7 562 1

CTE Scan on t_omin_pa tm_1 (cost=0.00..6.62 rows=331 width=8) (actual time=2.843..3.051 rows=562 loops=1)

153. 1.626 3.404 ↑ 1.7 9,643 1

Hash (cost=512.13..512.13 rows=16,113 width=12) (actual time=3.404..3.404 rows=9,643 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 512kB
154. 1.778 1.778 ↑ 1.0 15,561 1

Seq Scan on pmtrqavt_t pt_5 (cost=0.00..512.13 rows=16,113 width=12) (actual time=0.003..1.778 rows=15,561 loops=1)

155. 0.562 0.562 ↑ 1.0 1 562

Index Scan using pmtrqst_pkey on pmtrqst_t rt_2 (cost=0.29..0.36 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=562)

  • Index Cond: (pmtrqst_uid = pt_5.pmtrqst_uid)
156.          

CTE t_orgpaa_2

157. 1.992 3.845 ↓ 0.0 0 1

Seq Scan on pmtrqst_t pmtrqst_t_1 (cost=614.82..939.01 rows=4,488 width=12) (actual time=3.845..3.845 rows=0 loops=1)

  • Filter: (NOT (hashed SubPlan 20))
  • Rows Removed by Filter: 8,932
158.          

SubPlan (for Seq Scan)

159. 0.274 1.853 ↑ 2.6 563 1

HashAggregate (cost=596.62..611.18 rows=1,456 width=4) (actual time=1.790..1.853 rows=563 loops=1)

  • Group Key: pmtrqavt_t_4.pmtrqst_uid
160. 1.579 1.579 ↑ 1.2 1,258 1

Seq Scan on pmtrqavt_t pmtrqavt_t_4 (cost=0.00..592.69 rows=1,572 width=4) (actual time=0.008..1.579 rows=1,258 loops=1)

  • Filter: (((pmtrqact_cd)::text = 'PAACH'::text) OR ((fscl_stus_cd)::text = 'DEND'::text))
  • Rows Removed by Filter: 14,303
161.          

CTE t_org_paybl_amt

162. 0.290 11.844 ↑ 8.0 562 1

HashAggregate (cost=191.29..236.30 rows=4,501 width=22) (actual time=11.754..11.844 rows=562 loops=1)

  • Group Key: t_orgpaa_1.dcps_case_uid, t_orgpaa_1.pmtrqst_uid, t_orgpaa_1.org_paybl_amt
163. 0.042 11.554 ↑ 8.0 562 1

Append (cost=0.00..157.54 rows=4,501 width=22) (actual time=6.342..11.554 rows=562 loops=1)

164. 7.665 7.665 ↓ 562.0 562 1

CTE Scan on t_orgpaa_1 (cost=0.00..0.02 rows=1 width=22) (actual time=6.341..7.665 rows=562 loops=1)

165. 3.846 3.846 ↓ 0.0 0 1

CTE Scan on t_orgpaa_2 (cost=0.00..89.76 rows=4,488 width=22) (actual time=3.846..3.846 rows=0 loops=1)

166. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on t_dend_o t_dend_o_1 (cost=0.00..0.24 rows=12 width=22) (actual time=0.000..0.001 rows=0 loops=1)

167.          

CTE t_evrqst_vndrsrv

168. 0.000 25.953 ↓ 8.7 4,615 1

Nested Loop Left Join (cost=437.45..993.19 rows=529 width=78) (actual time=3.871..25.953 rows=4,615 loops=1)

169. 0.000 17.223 ↓ 8.7 4,615 1

Nested Loop Left Join (cost=437.16..768.03 rows=529 width=74) (actual time=3.859..17.223 rows=4,615 loops=1)

170. 1.528 8.180 ↓ 8.7 4,611 1

Hash Left Join (cost=436.88..450.23 rows=529 width=66) (actual time=3.838..8.180 rows=4,611 loops=1)

  • Hash Cond: (et_1.vndr_srv_uid = vt.vndr_srv_uid)
171. 1.587 5.409 ↓ 8.7 4,611 1

Hash Join (cost=318.99..330.95 rows=529 width=24) (actual time=2.571..5.409 rows=4,611 loops=1)

  • Hash Cond: (pmt_2.ev_rqst_uid = et_1.ev_rqst_uid)
172. 1.325 1.325 ↓ 8.7 4,611 1

CTE Scan on t_pmtrqst pmt_2 (cost=0.00..10.58 rows=529 width=8) (actual time=0.001..1.325 rows=4,611 loops=1)

173. 1.354 2.497 ↑ 1.0 9,362 1

Hash (cost=200.66..200.66 rows=9,466 width=20) (actual time=2.497..2.497 rows=9,362 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 547kB
174. 1.143 1.143 ↑ 1.0 9,362 1

Seq Scan on evrqst_t et_1 (cost=0.00..200.66 rows=9,466 width=20) (actual time=0.008..1.143 rows=9,362 loops=1)

175. 0.675 1.243 ↓ 1.0 3,302 1

Hash (cost=76.84..76.84 rows=3,284 width=46) (actual time=1.243..1.243 rows=3,302 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 276kB
176. 0.568 0.568 ↓ 1.0 3,302 1

Seq Scan on vndrsrv_t vt (cost=0.00..76.84 rows=3,284 width=46) (actual time=0.007..0.568 rows=3,302 loops=1)

177. 9.222 9.222 ↑ 1.0 1 4,611

Index Scan using ixf03_rqstuid on corrtrkg_t ct_2 (cost=0.29..0.59 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=4,611)

  • Index Cond: (et_1.ev_rqst_uid = rqst_uid)
  • Filter: ((rqst_typ_cd)::text = 'MER'::text)
  • Rows Removed by Filter: 0
178. 9.230 9.230 ↑ 1.0 1 4,615

Index Scan using ixn01_dmthdtrkg_t on dmthdtrkg_t dtrk (cost=0.29..0.42 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=4,615)

  • Index Cond: (crsp_trkg_uid = ct_2.crsp_trkg_uid)
179.          

CTE p_vndr

180. 2.201 55.548 ↓ 4,424.0 4,424 1

Unique (cost=261.03..261.10 rows=1 width=317) (actual time=53.023..55.548 rows=4,424 loops=1)

181. 3.610 53.347 ↓ 4,429.0 4,429 1

Sort (cost=261.03..261.03 rows=1 width=317) (actual time=53.022..53.347 rows=4,429 loops=1)

  • Sort Key: tev.ev_rqst_uid, tev.dcps_case_uid, tev.pt_pmtrqst_uid, tev.tp_src_id, tev.med_src_id, tev.rqst_sent_ts, tev.vndr_srv_uid, tev.dmthd_cd, tev.offering_vndr_uid, vat.vndr_addr_uid, vat.addr_uid, vat.addr_attn_ln, vat.oworg_uid, adt.addrln64_1, adt.addrln64_2, adt.addrln64_3, adt.addrln64_4, adt.city35, adt.us_st_cd, adt.zip5, adt.zip4, lvn.lgcy_vndr_id, lvn.lgcy_sfx_id, vdt.org_nm, vdt.fnm, vdt.mnm, vdt.lnm, vdt.nm_sfx, vdt.tin
  • Sort Method: quicksort Memory: 1,376kB
182. 1.511 49.737 ↓ 4,429.0 4,429 1

Nested Loop Left Join (cost=247.35..261.02 rows=1 width=317) (actual time=5.460..49.737 rows=4,429 loops=1)

183. 1.641 43.797 ↓ 4,429.0 4,429 1

Nested Loop Left Join (cost=247.07..260.61 rows=1 width=224) (actual time=5.447..43.797 rows=4,429 loops=1)

184. 1.395 37.732 ↓ 4,424.0 4,424 1

Nested Loop Left Join (cost=246.79..260.25 rows=1 width=214) (actual time=5.436..37.732 rows=4,424 loops=1)

185. 1.864 31.913 ↓ 4,424.0 4,424 1

Hash Join (cost=246.51..259.87 rows=1 width=102) (actual time=5.423..31.913 rows=4,424 loops=1)

  • Hash Cond: ((tev.offering_vndr_uid = vat.vndr_uid) AND (tev.loc_vndr_addr_uid = vat.vndr_addr_uid))
186. 28.529 28.529 ↓ 8.7 4,615 1

CTE Scan on t_evrqst_vndrsrv tev (cost=0.00..10.58 rows=529 width=60) (actual time=3.873..28.529 rows=4,615 loops=1)

187. 0.515 1.520 ↓ 1.0 2,996 1

Hash (cost=201.88..201.88 rows=2,976 width=46) (actual time=1.520..1.520 rows=2,996 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 185kB
188. 1.005 1.005 ↓ 1.0 2,996 1

Seq Scan on vndraddr_t vat (cost=0.00..201.88 rows=2,976 width=46) (actual time=0.005..1.005 rows=2,996 loops=1)

  • Filter: ((addr_use_cd)::text = 'P'::text)
  • Rows Removed by Filter: 5,614
189. 4.424 4.424 ↑ 1.0 1 4,424

Index Scan using address_t_pkey on address_t adt (cost=0.28..0.38 rows=1 width=116) (actual time=0.001..0.001 rows=1 loops=4,424)

  • Index Cond: (vat.addr_uid = addr_uid)
190. 4.424 4.424 ↑ 1.0 1 4,424

Index Scan using lgcy_vndr_num_t_vndr_uid on lgcy_vndr_num_t lvn (cost=0.28..0.35 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=4,424)

  • Index Cond: (vat.vndr_uid = vndr_uid)
191. 4.429 4.429 ↑ 1.0 1 4,429

Index Scan using vendor_t_pkey on vendor_t vdt (cost=0.28..0.40 rows=1 width=97) (actual time=0.001..0.001 rows=1 loops=4,429)

  • Index Cond: (vat.vndr_uid = vndr_uid)
192.          

CTE c_vndr

193. 2.283 27.641 ↓ 25.5 4,615 1

Unique (cost=546.51..560.54 rows=181 width=317) (actual time=25.051..27.641 rows=4,615 loops=1)

194. 3.323 25.358 ↓ 25.5 4,620 1

Sort (cost=546.51..546.97 rows=181 width=317) (actual time=25.050..25.358 rows=4,620 loops=1)

  • Sort Key: tev_1.ev_rqst_uid, tev_1.dcps_case_uid, tev_1.pt_pmtrqst_uid, tev_1.tp_src_id, tev_1.med_src_id, tev_1.rqst_sent_ts, tev_1.vndr_srv_uid, tev_1.dmthd_cd, tev_1.vndr_comm_prfl_uid, vat_1.vndr_addr_uid, vat_1.vndr_uid, vat_1.addr_uid, vat_1.addr_attn_ln, vat_1.oworg_uid, adt_1.addrln64_1, adt_1.addrln64_2, adt_1.addrln64_3, adt_1.addrln64_4, adt_1.city35, adt_1.us_st_cd, adt_1.zip5, adt_1.zip4, lvn_1.lgcy_vndr_id, lvn_1.lgcy_sfx_id, vdt_1.org_nm, vdt_1.fnm, vdt_1.mnm, vdt_1.lnm, vdt_1.nm_sfx, vdt_1.tin
  • Sort Method: quicksort Memory: 1,434kB
195. 0.855 22.035 ↓ 25.5 4,620 1

Nested Loop Left Join (cost=117.95..539.73 rows=181 width=317) (actual time=3.426..22.035 rows=4,620 loops=1)

196. 1.053 16.560 ↓ 25.5 4,620 1

Nested Loop Left Join (cost=117.67..466.29 rows=181 width=224) (actual time=3.416..16.560 rows=4,620 loops=1)

197. 0.665 10.892 ↓ 25.5 4,615 1

Nested Loop Left Join (cost=117.39..400.72 rows=181 width=214) (actual time=3.406..10.892 rows=4,615 loops=1)

198. 1.315 5.612 ↓ 25.5 4,615 1

Hash Join (cost=117.11..331.74 rows=181 width=102) (actual time=3.394..5.612 rows=4,615 loops=1)

  • Hash Cond: (vat_1.vndr_addr_uid = vct.crsp_vndr_addr_uid)
199. 0.919 0.919 ↓ 1.0 2,940 1

Seq Scan on vndraddr_t vat_1 (cost=0.00..201.88 rows=2,920 width=46) (actual time=0.009..0.919 rows=2,940 loops=1)

  • Filter: ((addr_use_cd)::text = 'C'::text)
  • Rows Removed by Filter: 5,670
200. 1.061 3.378 ↓ 8.7 4,615 1

Hash (cost=110.50..110.50 rows=529 width=60) (actual time=3.378..3.378 rows=4,615 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 448kB
201. 1.230 2.317 ↓ 8.7 4,615 1

Hash Join (cost=98.53..110.50 rows=529 width=60) (actual time=0.712..2.317 rows=4,615 loops=1)

  • Hash Cond: (tev_1.vndr_comm_prfl_uid = vct.vndr_comm_prfl_uid)
202. 0.380 0.380 ↓ 8.7 4,615 1

CTE Scan on t_evrqst_vndrsrv tev_1 (cost=0.00..10.58 rows=529 width=56) (actual time=0.001..0.380 rows=4,615 loops=1)

203. 0.348 0.707 ↓ 1.0 2,799 1

Hash (cost=63.79..63.79 rows=2,779 width=8) (actual time=0.707..0.707 rows=2,799 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 142kB
204. 0.359 0.359 ↓ 1.0 2,799 1

Seq Scan on vndrcommprfl_t vct (cost=0.00..63.79 rows=2,779 width=8) (actual time=0.005..0.359 rows=2,799 loops=1)

205. 4.615 4.615 ↑ 1.0 1 4,615

Index Scan using address_t_pkey on address_t adt_1 (cost=0.28..0.38 rows=1 width=116) (actual time=0.001..0.001 rows=1 loops=4,615)

  • Index Cond: (vat_1.addr_uid = addr_uid)
206. 4.615 4.615 ↑ 1.0 1 4,615

Index Scan using lgcy_vndr_num_t_vndr_uid on lgcy_vndr_num_t lvn_1 (cost=0.28..0.35 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=4,615)

  • Index Cond: (vat_1.vndr_uid = vndr_uid)
207. 4.620 4.620 ↑ 1.0 1 4,620

Index Scan using vendor_t_pkey on vendor_t vdt_1 (cost=0.28..0.41 rows=1 width=97) (actual time=0.001..0.001 rows=1 loops=4,620)

  • Index Cond: (vat_1.vndr_uid = vndr_uid)
208.          

CTE r_vndr

209. 2.010 26.947 ↓ 4,518.0 4,518 1

Unique (cost=256.23..256.29 rows=1 width=281) (actual time=24.612..26.947 rows=4,518 loops=1)

210. 3.400 24.937 ↓ 4,518.0 4,518 1

Sort (cost=256.23..256.23 rows=1 width=281) (actual time=24.611..24.937 rows=4,518 loops=1)

  • Sort Key: tev_2.ev_rqst_uid, tev_2.dcps_case_uid, tev_2.pt_pmtrqst_uid, tev_2.pye_vndr_id, tev_2.pye_rmtaddr_id, vat_2.addr_uid, vat_2.addr_attn_ln, vat_2.oworg_uid, adt_2.addrln64_1, adt_2.addrln64_2, adt_2.addrln64_3, adt_2.addrln64_4, adt_2.city35, adt_2.us_st_cd, adt_2.zip5, adt_2.zip4, lvn_2.lgcy_vndr_id, lvn_2.lgcy_sfx_id, vdt_2.org_nm, vdt_2.fnm, vdt_2.mnm, vdt_2.lnm, vdt_2.nm_sfx, vdt_2.tin
  • Sort Method: quicksort Memory: 1,395kB
211. 1.296 21.537 ↓ 4,518.0 4,518 1

Nested Loop Left Join (cost=242.53..256.22 rows=1 width=281) (actual time=1.498..21.537 rows=4,518 loops=1)

212. 1.546 15.723 ↓ 4,518.0 4,518 1

Nested Loop Left Join (cost=242.25..255.80 rows=1 width=188) (actual time=1.488..15.723 rows=4,518 loops=1)

213. 1.237 9.659 ↓ 4,518.0 4,518 1

Nested Loop Left Join (cost=241.97..255.43 rows=1 width=178) (actual time=1.481..9.659 rows=4,518 loops=1)

214. 1.694 3.904 ↓ 4,518.0 4,518 1

Hash Join (cost=241.69..255.04 rows=1 width=66) (actual time=1.473..3.904 rows=4,518 loops=1)

  • Hash Cond: ((tev_2.pye_rmtaddr_id = vat_2.vndr_addr_uid) AND (tev_2.pye_vndr_id = vat_2.vndr_uid))
215. 0.746 0.746 ↓ 8.7 4,611 1

CTE Scan on t_pmtrqst tev_2 (cost=0.00..10.58 rows=529 width=20) (actual time=0.002..0.746 rows=4,611 loops=1)

216. 0.494 1.464 ↓ 1.0 2,674 1

Hash (cost=201.88..201.88 rows=2,654 width=46) (actual time=1.464..1.464 rows=2,674 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 187kB
217. 0.970 0.970 ↓ 1.0 2,674 1

Seq Scan on vndraddr_t vat_2 (cost=0.00..201.88 rows=2,654 width=46) (actual time=0.009..0.970 rows=2,674 loops=1)

  • Filter: ((addr_use_cd)::text = 'R'::text)
  • Rows Removed by Filter: 5,936
218. 4.518 4.518 ↑ 1.0 1 4,518

Index Scan using address_t_pkey on address_t adt_2 (cost=0.28..0.39 rows=1 width=116) (actual time=0.001..0.001 rows=1 loops=4,518)

  • Index Cond: (vat_2.addr_uid = addr_uid)
219. 4.518 4.518 ↑ 1.0 1 4,518

Index Scan using lgcy_vndr_num_t_vndr_uid on lgcy_vndr_num_t lvn_2 (cost=0.28..0.36 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=4,518)

  • Index Cond: (vat_2.vndr_uid = vndr_uid)
  • Filter: (eff_end_ts IS NULL)
  • Rows Removed by Filter: 0
220. 4.518 4.518 ↑ 1.0 1 4,518

Index Scan using vendor_t_pkey on vendor_t vdt_2 (cost=0.28..0.42 rows=1 width=97) (actual time=0.001..0.001 rows=1 loops=4,518)

  • Index Cond: (vat_2.vndr_uid = vndr_uid)
221.          

CTE tb_aprv

222. 0.088 6.944 ↓ 248.2 993 1

Subquery Scan on a_2 (cost=696.02..723.91 rows=4 width=67) (actual time=6.078..6.944 rows=993 loops=1)

  • Filter: (a_2.rnk = 1)
  • Rows Removed by Filter: 37
223. 0.739 6.856 ↓ 1.3 1,030 1

WindowAgg (cost=696.02..713.95 rows=797 width=67) (actual time=6.076..6.856 rows=1,030 loops=1)

224. 0.382 6.117 ↓ 1.3 1,030 1

Sort (cost=696.02..698.01 rows=797 width=40) (actual time=6.065..6.117 rows=1,030 loops=1)

  • Sort Key: pmtrqavt_t_5.pmtrqst_pkg_uid, pmtrqavt_t_5.pmtrqah_uid DESC
  • Sort Method: quicksort Memory: 129kB
225. 0.221 5.735 ↓ 1.3 1,030 1

Hash Left Join (cost=81.19..657.61 rows=797 width=40) (actual time=3.501..5.735 rows=1,030 loops=1)

  • Hash Cond: ((pmtrqavt_t_5.pmtrqact_pin)::text = (dcpsuser_t_1.pin)::text)
226. 0.383 5.008 ↓ 1.3 1,030 1

Hash Join (cost=16.40..581.87 rows=797 width=27) (actual time=2.987..5.008 rows=1,030 loops=1)

  • Hash Cond: (pmtrqavt_t_5.pmtrqst_pkg_uid = t_pmtrqst_1.pmtrqst_pkg_uid)
227. 1.656 1.656 ↑ 1.0 1,563 1

Seq Scan on pmtrqavt_t pmtrqavt_t_5 (cost=0.00..552.41 rows=1,594 width=27) (actual time=0.010..1.656 rows=1,563 loops=1)

  • Filter: ((fscl_stus_cd)::text = 'APRV'::text)
  • Rows Removed by Filter: 13,998
228. 0.547 2.969 ↓ 23.1 4,611 1

Hash (cost=13.90..13.90 rows=200 width=4) (actual time=2.968..2.969 rows=4,611 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 227kB
229. 1.483 2.422 ↓ 23.1 4,611 1

HashAggregate (cost=11.90..13.90 rows=200 width=4) (actual time=1.961..2.422 rows=4,611 loops=1)

  • Group Key: t_pmtrqst_1.pmtrqst_pkg_uid
230. 0.939 0.939 ↓ 8.7 4,611 1

CTE Scan on t_pmtrqst t_pmtrqst_1 (cost=0.00..10.58 rows=529 width=4) (actual time=0.003..0.939 rows=4,611 loops=1)

231. 0.276 0.506 ↓ 1.0 1,547 1

Hash (cost=45.46..45.46 rows=1,546 width=19) (actual time=0.506..0.506 rows=1,547 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 97kB
232. 0.230 0.230 ↓ 1.0 1,547 1

Seq Scan on dcpsuser_t dcpsuser_t_1 (cost=0.00..45.46 rows=1,546 width=19) (actual time=0.006..0.230 rows=1,547 loops=1)

233.          

CTE tb_cert

234. 0.032 5.795 ↓ 310.0 310 1

Subquery Scan on a_3 (cost=668.56..669.12 rows=1 width=67) (actual time=5.491..5.795 rows=310 loops=1)

  • Filter: (a_3.rnk = 1)
  • Rows Removed by Filter: 59
235. 0.273 5.763 ↓ 23.1 369 1

WindowAgg (cost=668.56..668.92 rows=16 width=67) (actual time=5.490..5.763 rows=369 loops=1)

236. 0.139 5.490 ↓ 23.1 369 1

Sort (cost=668.56..668.60 rows=16 width=40) (actual time=5.472..5.490 rows=369 loops=1)

  • Sort Key: pmtrqavt_t_6.pmtrqst_pkg_uid, pmtrqavt_t_6.pmtrqah_uid DESC
  • Sort Method: quicksort Memory: 53kB
237. 0.233 5.351 ↓ 23.1 369 1

Nested Loop Left Join (cost=16.68..668.24 rows=16 width=40) (actual time=2.973..5.351 rows=369 loops=1)

238. 0.172 4.380 ↓ 23.1 369 1

Hash Join (cost=16.40..609.36 rows=16 width=27) (actual time=2.959..4.380 rows=369 loops=1)

  • Hash Cond: (pmtrqavt_t_6.pmtrqst_pkg_uid = t_pmtrqst_2.pmtrqst_pkg_uid)
239. 1.268 1.268 ↓ 20.4 653 1

Seq Scan on pmtrqavt_t pmtrqavt_t_6 (cost=0.00..592.69 rows=32 width=27) (actual time=0.004..1.268 rows=653 loops=1)

  • Filter: (((fscl_stus_cd)::text = 'CERT'::text) AND ((pmtrqact_cd)::text = 'UPCRT'::text))
  • Rows Removed by Filter: 14,908
240. 0.511 2.940 ↓ 23.1 4,611 1

Hash (cost=13.90..13.90 rows=200 width=4) (actual time=2.940..2.940 rows=4,611 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 227kB
241. 1.519 2.429 ↓ 23.1 4,611 1

HashAggregate (cost=11.90..13.90 rows=200 width=4) (actual time=1.977..2.429 rows=4,611 loops=1)

  • Group Key: t_pmtrqst_2.pmtrqst_pkg_uid
242. 0.910 0.910 ↓ 8.7 4,611 1

CTE Scan on t_pmtrqst t_pmtrqst_2 (cost=0.00..10.58 rows=529 width=4) (actual time=0.002..0.910 rows=4,611 loops=1)

243. 0.738 0.738 ↑ 1.0 1 369

Index Scan using ixf02_pin on dcpsuser_t dcpsuser_t_2 (cost=0.28..3.67 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=369)

  • Index Cond: ((pin)::text = (pmtrqavt_t_6.pmtrqact_pin)::text)
244. 116.745 1,231.594 ↑ 2,667.8 4,666 1

Sort (cost=173,743,465.69..173,774,586.06 rows=12,448,146 width=7,974) (actual time=1,225.796..1,231.594 rows=4,666 loops=1)

  • Sort Key: tdc.roacr, tdc.st, tdc.ocd, tdc.otyp, tdc.ofc_nm, tdc.cmpnt_typ, tdc.lun, tdc.dcps_case_uid, tdpt.all_clm_title_desc, (CASE WHEN (tdpt.all_clm_title_desc = 'T2'::text) THEN '02'::text WHEN (tdpt.all_clm_title_desc = 'T16'::text) THEN '16'::text ELSE 'CN'::text END), tdpt.all_clm_typ, tdpt.dds_ccrnt_case_ind, tdc.adjulvl_cd, tdc.adjulvl_desc, tdc."Case Classification Code", tdc."Case Level Code", tdc."Case Level Desc", tdc."CDR Type Code", tdc."CDR Type Desc", tdc."CDR Appeal Level Code", tdc.case_rcpdt, tdc.case_stus_cd, tdc.case_stus_desc, tdc.case_lun, tdc.case_lex, tdc.case_asgnd_dt, tdc.pin, tdc.user_fnm, tdc.user_lnm, (concat(tdc.user_lnm, ', ', tdc.user_fnm)), tdc.emailaddr, tdc.fnm, tdc.lnm, ((((tdc.lnm)::text || ', '::text) || (tdc.fnm)::text)), tdc.dob, tdc.sex, tdc.cossn, tdc.last_4_ssn, te.ev_rqst_uid, tdc.org_desc, tdc.org_typ_cd, td.critl_src_sw, te.dmthd_cd, te.evid_src_typ, te.pkg_nm, (date(timezone('America/New_York'::text, te.insrt_ts))), (date(timezone('America/New_York'::text, tbt.resp_docu_rcvts))), tpt.evid_certd_ind, pv.med_src_id, te.ltr_typ_cd, (date(timezone('America/New_York'::text, pv.rqst_sent_ts))), pv.dmthd_cd, pv.offering_vndr_uid, pv.addr_uid, pv.addr_use_cd, pv.addr_attn_ln, pv.addrln64_1, pv.addrln64_2, pv.addrln64_3, pv.addrln64_4, pv.city35, pv.us_st_cd, pv.zip5, (CASE WHEN (pv.addrln64_1 IS NULL) THEN NULL::text WHEN (pv.addrln64_2 IS NULL) THEN CASE WHEN (pv.zip4 IS NULL) THEN concat(pv.addrln64_1, ', ', pv.city35, ', ', pv.us_st_cd, ' ', pv.zip5) ELSE concat(pv.addrln64_1, ', ', pv.city35, ', ', pv.us_st_cd, ' ', pv.zip5, '-', pv.zip4) END WHEN (pv.addrln64_3 IS NULL) THEN CASE WHEN (pv.zip4 IS NULL) THEN concat(pv.addrln64_1, ', ', pv.addrln64_2, ', ', pv.city35, ', ', pv.us_st_cd, ' ', pv.zip5) ELSE concat(pv.addrln64_1, ', ', pv.addrln64_2, ', ', pv.city35, ', ', pv.us_st_cd, ' ', pv.zip5, '-', pv.zip4) END WHEN (pv.addrln64_4 IS NULL) THEN CASE WHEN (pv.zip4 IS NULL) THEN concat(pv.addrln64_1, ', ', pv.addrln64_2, ', ', pv.addrln64_3, ', ', pv.city35, ', ', pv.us_st_cd, ' ', pv.zip5) ELSE concat(pv.addrln64_1, ', ', pv.addrln64_2, ', ', pv.addrln64_3, ', ', pv.city35, ', ', pv.us_st_cd, ' ', pv.zip5, '-', pv.zip4) END ELSE CASE WHEN (pv.zip4 IS NULL) THEN concat(pv.addrln64_1, ' ', pv.addrln64_2, ' ', pv.addrln64_3, ' ', pv.addrln64_4, ' ', pv.city35, ', ', pv.us_st_cd, ' ', pv.zip5) ELSE concat(pv.addrln64_1, ' ', pv.addrln64_2, ' ',pv.addrln64_3, ' ', pv.addrln64_4, ' ', pv.city35, ', ', pv.us_st_cd, ' ', pv.zip5, '-', pv.zip4) END END), pv.lgcy_vndr_id, pv.lgcy_sfx_id, (btrim(concat(pv.org_nm, ' ', pv.fnm, ' ', pv.mnm, ' ', pv.lnm, ' ', pv.nm_sfx))), pv.tin, ttt.todo_itm_cd, ttt.fup_duedt, ttt.fup_typ, ttt.fup_dtls, cv.addr_attn_ln, cv.lgcy_vndr_id, cv.lgcy_sfx_id, (btrim(concat(cv.org_nm, ' ', cv.fnm, ' ', cv.mnm, ' ', cv.lnm, ' ', cv.nm_sfx))), cv.addrln64_1, cv.addrln64_2, cv.addrln64_3, cv.addrln64_4, cv.city35, cv.us_st_cd, cv.zip5, (CASE WHEN (cv.addrln64_1
  • Sort Method: external merge Disk: 4,192kB
245. 29.717 1,114.849 ↑ 2,667.8 4,666 1

Hash Left Join (cost=97,650.17..2,086,985.31 rows=12,448,146 width=7,974) (actual time=441.227..1,114.849 rows=4,666 loops=1)

  • Hash Cond: (tpt.pmtrqst_pkg_uid = cert.pmtrqst_pkg_uid)
246. 2.367 1,079.155 ↑ 2,667.8 4,666 1

Merge Left Join (cost=97,650.13..296,941.88 rows=12,448,146 width=9,362) (actual time=434.970..1,079.155 rows=4,666 loops=1)

  • Merge Cond: (tpt.pt_pmtrqst_uid = topa.pmtrqst_uid)
247. 2.364 1,064.597 ↑ 118.5 4,666 1

Merge Left Join (cost=97,286.99..108,473.73 rows=553,128 width=9,348) (actual time=422.847..1,064.597 rows=4,666 loops=1)

  • Merge Cond: (tpt.pt_pmtrqst_uid = tooa.pmtrqst_uid)
248. 298.889 1,048.288 ↑ 5.3 4,666 1

Nested Loop Left Join (cost=96,923.85..99,752.23 rows=24,578 width=9,334) (actual time=408.945..1,048.288 rows=4,666 loops=1)

  • Join Filter: (aprv.pmtrqst_pkg_uid = tpt.pmtrqst_pkg_uid)
  • Rows Removed by Join Filter: 4,632,342
249. 4.393 418.113 ↑ 5.3 4,666 1

Merge Left Join (cost=96,923.85..97,540.17 rows=24,578 width=9,266) (actual time=401.626..418.113 rows=4,666 loops=1)

  • Merge Cond: ((tpt.pt_pmtrqst_uid = rv.pt_pmtrqst_uid) AND (te.ev_rqst_uid = rv.ev_rqst_uid) AND (td.dcps_case_uid = rv.dcps_case_uid))
250. 4.011 381.677 ↑ 5.3 4,666 1

Merge Left Join (cost=96,923.82..97,355.78 rows=24,578 width=7,668) (actual time=369.928..381.677 rows=4,666 loops=1)

  • Merge Cond: ((tpt.pt_pmtrqst_uid = cv.pt_pmtrqst_uid) AND (te.ev_rqst_uid = cv.ev_rqst_uid) AND (td.dcps_case_uid = cv.dcps_case_uid))
251. 6.489 344.798 ↑ 5.3 4,648 1

Merge Left Join (cost=96,913.41..97,159.22 rows=24,578 width=6,070) (actual time=337.379..344.798 rows=4,648 loops=1)

  • Merge Cond: ((tpt.pt_pmtrqst_uid = pv.pt_pmtrqst_uid) AND (te.ev_rqst_uid = pv.ev_rqst_uid) AND (td.dcps_case_uid = pv.dcps_case_uid))
252. 7.341 277.258 ↑ 5.3 4,642 1

Sort (cost=96,913.38..96,974.83 rows=24,578 width=4,424) (actual time=276.664..277.258 rows=4,642 loops=1)

  • Sort Key: tpt.pt_pmtrqst_uid, te.ev_rqst_uid, td.dcps_case_uid
  • Sort Method: quicksort Memory: 2,730kB
253. 1.626 269.917 ↑ 5.3 4,642 1

Hash Left Join (cost=406.83..1,699.02 rows=24,578 width=4,424) (actual time=254.437..269.917 rows=4,642 loops=1)

  • Hash Cond: (tpt.pye_rmtaddr_id = sfl.rmtn_vndr_addr_uid)
254. 3.833 267.572 ↑ 5.3 4,642 1

Hash Right Join (cost=339.13..1,393.41 rows=24,578 width=4,414) (actual time=253.711..267.572 rows=4,642 loops=1)

  • Hash Cond: (tdpt.dcps_case_uid = tdc.dcps_case_uid)
255. 25.293 25.293 ↑ 1.3 6,545 1

CTE Scan on t_dcpscase_title tdpt (cost=0.00..172.48 rows=8,624 width=100) (actual time=15.247..25.293 rows=6,545 loops=1)

256. 4.597 238.446 ↓ 8.1 4,642 1

Hash (cost=332.01..332.01 rows=570 width=4,318) (actual time=238.446..238.446 rows=4,642 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,052kB
257. 1.979 233.849 ↓ 8.1 4,642 1

Hash Left Join (cost=99.01..332.01 rows=570 width=4,318) (actual time=225.925..233.849 rows=4,642 loops=1)

  • Hash Cond: ((td.dcps_case_uid = ttt.dcps_case_uid) AND (te.ev_rqst_uid = ttt.prnt_tbl_seq_id))
258. 1.707 222.457 ↓ 8.1 4,616 1

Hash Left Join (cost=92.82..321.51 rows=570 width=4,242) (actual time=216.460..222.457 rows=4,616 loops=1)

  • Hash Cond: (tdc.dcps_case_uid = td.dcps_case_uid)
259. 3.145 219.268 ↓ 15.2 4,616 1

Hash Join (cost=80.60..288.44 rows=303 width=4,230) (actual time=214.966..219.268 rows=4,616 loops=1)

  • Hash Cond: (tdc.dcps_case_uid = te.dcps_case_uid)
260. 80.321 80.321 ↑ 1.3 6,547 1

CTE Scan on t_dcps_case tdc (cost=0.00..172.48 rows=8,624 width=2,986) (actual time=79.152..80.321 rows=6,547 loops=1)

261. 2.192 135.802 ↓ 659.4 4,616 1

Hash (cost=80.51..80.51 rows=7 width=1,252) (actual time=135.802..135.802 rows=4,616 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 995kB
262. 1.415 133.610 ↓ 659.4 4,616 1

Hash Right Join (cost=77.48..80.51 rows=7 width=1,252) (actual time=132.127..133.610 rows=4,616 loops=1)

  • Hash Cond: (tpr.pmtrqst_uid = tpt.pt_pmtrqst_uid)
263. 2.681 2.681 ↑ 2.3 55 1

CTE Scan on t_pmtresp tpr (cost=0.00..2.52 rows=126 width=176) (actual time=2.607..2.681 rows=55 loops=1)

264. 2.164 129.514 ↓ 659.3 4,615 1

Hash (cost=77.39..77.39 rows=7 width=1,080) (actual time=129.514..129.514 rows=4,615 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 994kB
265. 1.534 127.350 ↓ 659.3 4,615 1

Merge Left Join (cost=69.33..77.39 rows=7 width=1,080) (actual time=121.605..127.350 rows=4,615 loops=1)

  • Merge Cond: (te.ev_rqst_uid = tbt.rqst_uid)
266. 3.053 105.355 ↓ 659.3 4,615 1

Merge Join (cost=69.30..77.32 rows=7 width=1,072) (actual time=101.363..105.355 rows=4,615 loops=1)

  • Merge Cond: ((te.ev_rqst_uid = tpt.ev_rqst_uid) AND (te.dcps_case_uid = tpt.dcps_case_uid))
267. 2.213 39.614 ↓ 8.5 4,557 1

Sort (cost=34.94..36.28 rows=535 width=218) (actual time=39.081..39.614 rows=4,557 loops=1)

  • Sort Key: te.ev_rqst_uid, te.dcps_case_uid
  • Sort Method: quicksort Memory: 833kB
268. 37.401 37.401 ↓ 8.5 4,557 1

CTE Scan on t_evrqst te (cost=0.00..10.70 rows=535 width=218) (actual time=33.952..37.401 rows=4,557 loops=1)

269. 3.246 62.688 ↓ 8.8 4,615 1

Sort (cost=34.35..35.67 rows=526 width=858) (actual time=62.275..62.688 rows=4,615 loops=1)

  • Sort Key: tpt.ev_rqst_uid, tpt.dcps_case_uid
  • Sort Method: quicksort Memory: 992kB
270. 59.442 59.442 ↓ 8.8 4,611 1

CTE Scan on t_pmtrqst tpt (cost=0.00..10.58 rows=526 width=858) (actual time=42.506..59.442 rows=4,611 loops=1)

  • Filter: (pt_pmtrqst_uid IS NOT NULL)
271. 0.810 20.461 ↓ 4,402.0 4,402 1

Sort (cost=0.03..0.04 rows=1 width=12) (actual time=20.238..20.461 rows=4,402 loops=1)

  • Sort Key: tbt.rqst_uid
  • Sort Method: quicksort Memory: 396kB
272. 19.651 19.651 ↓ 4,340.0 4,340 1

CTE Scan on t_barcdtrkg tbt (cost=0.00..0.02 rows=1 width=12) (actual time=15.204..19.651 rows=4,340 loops=1)

273. 0.863 1.482 ↓ 15.7 5,912 1

Hash (cost=7.52..7.52 rows=376 width=12) (actual time=1.481..1.482 rows=5,912 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 295kB
274. 0.619 0.619 ↓ 15.7 5,912 1

CTE Scan on t_dcpscssrc td (cost=0.00..7.52 rows=376 width=12) (actual time=0.001..0.619 rows=5,912 loops=1)

275. 1.220 9.413 ↓ 25.5 4,507 1

Hash (cost=3.54..3.54 rows=177 width=88) (actual time=9.413..9.413 rows=4,507 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 616kB
276. 8.193 8.193 ↓ 25.5 4,507 1

CTE Scan on t_todoitemlist ttt (cost=0.00..3.54 rows=177 width=88) (actual time=3.470..8.193 rows=4,507 loops=1)

277. 0.198 0.719 ↓ 2.9 1,473 1

Hash (cost=61.33..61.33 rows=510 width=18) (actual time=0.719..0.719 rows=1,473 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 90kB
278. 0.521 0.521 ↓ 2.9 1,473 1

Seq Scan on stfscllnk_t sfl (cost=0.00..61.33 rows=510 width=18) (actual time=0.012..0.521 rows=1,473 loops=1)

  • Filter: ((date(actvn_ts) <= CURRENT_DATE) AND ((dactvn_ts IS NULL) OR (date(dactvn_ts) > CURRENT_DATE)))
  • Rows Removed by Filter: 183
279. 3.332 61.051 ↓ 4,455.0 4,455 1

Sort (cost=0.03..0.04 rows=1 width=1,658) (actual time=60.703..61.051 rows=4,455 loops=1)

  • Sort Key: pv.pt_pmtrqst_uid, pv.ev_rqst_uid, pv.dcps_case_uid
  • Sort Method: quicksort Memory: 1,357kB
280. 57.719 57.719 ↓ 4,424.0 4,424 1

CTE Scan on p_vndr pv (cost=0.00..0.02 rows=1 width=1,658) (actual time=53.027..57.719 rows=4,424 loops=1)

281. 3.045 32.868 ↓ 25.8 4,666 1

Sort (cost=10.41..10.86 rows=181 width=1,610) (actual time=32.541..32.868 rows=4,666 loops=1)

  • Sort Key: cv.pt_pmtrqst_uid, cv.ev_rqst_uid, cv.dcps_case_uid
  • Sort Method: quicksort Memory: 1,177kB
282. 29.823 29.823 ↓ 25.5 4,615 1

CTE Scan on c_vndr cv (cost=0.00..3.62 rows=181 width=1,610) (actual time=25.054..29.823 rows=4,615 loops=1)

283. 3.043 32.043 ↓ 4,573.0 4,573 1

Sort (cost=0.03..0.04 rows=1 width=1,614) (actual time=31.689..32.043 rows=4,573 loops=1)

  • Sort Key: rv.pt_pmtrqst_uid, rv.ev_rqst_uid, rv.dcps_case_uid
  • Sort Method: quicksort Memory: 1,184kB
284. 29.000 29.000 ↓ 4,518.0 4,518 1

CTE Scan on r_vndr rv (cost=0.00..0.02 rows=1 width=1,614) (actual time=24.616..29.000 rows=4,518 loops=1)

285. 331.286 331.286 ↓ 248.2 993 4,666

CTE Scan on tb_aprv aprv (cost=0.00..0.08 rows=4 width=72) (actual time=0.001..0.071 rows=993 loops=4,666)

286. 0.133 13.945 ↑ 11.8 382 1

Sort (cost=363.14..374.39 rows=4,501 width=18) (actual time=13.895..13.945 rows=382 loops=1)

  • Sort Key: tooa.pmtrqst_uid
  • Sort Method: quicksort Memory: 42kB
287. 13.812 13.812 ↑ 11.8 382 1

CTE Scan on t_org_oblgd_amt tooa (cost=0.00..90.02 rows=4,501 width=18) (actual time=13.644..13.812 rows=382 loops=1)

288. 0.207 12.191 ↑ 8.0 562 1

Sort (cost=363.14..374.39 rows=4,501 width=18) (actual time=12.116..12.191 rows=562 loops=1)

  • Sort Key: topa.pmtrqst_uid
  • Sort Method: quicksort Memory: 51kB
289. 11.984 11.984 ↑ 8.0 562 1

CTE Scan on t_org_paybl_amt topa (cost=0.00..90.02 rows=4,501 width=18) (actual time=11.756..11.984 rows=562 loops=1)

290. 0.064 5.977 ↓ 310.0 310 1

Hash (cost=0.02..0.02 rows=1 width=72) (actual time=5.976..5.977 rows=310 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
291. 5.913 5.913 ↓ 310.0 310 1

CTE Scan on tb_cert cert (cost=0.00..0.02 rows=1 width=72) (actual time=5.493..5.913 rows=310 loops=1)

Planning time : 77.166 ms
Execution time : 1,247.509 ms