explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tMfg

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

Unique (cost=5,235,871,457,800,224.00..5,285,467,281,095,034.00 rows=151,437,628,381,098 width=7,974) (actual rows= loops=)

2.          

CTE t_dcps_case

3. 0.000 0.000 ↓ 0.0

Unique (cost=69,041.04..87,443.22 rows=163,575 width=303) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=69,041.04..69,449.97 rows=163,575 width=303) (actual rows= loops=)

  • 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
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,306.64..31,950.78 rows=163,575 width=303) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,305.34..29,692.38 rows=163,575 width=253) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,304.04..27,842.88 rows=163,575 width=255) (actual rows= loops=)

  • Hash Cond: ((dcpscase_t.cdr_typ)::text = (cdrtyp.cd)::text)
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,302.84..27,397.39 rows=163,575 width=247) (actual rows= loops=)

  • Hash Cond: (userorganization_t.org_uid = organization_t.org_uid)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,179.45..26,843.94 rows=163,575 width=190) (actual rows= loops=)

  • Hash Cond: (userorganization_t.user_uid = dcpsuser_t.user_uid)
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,085.82..26,320.12 rows=163,575 width=149) (actual rows= loops=)

  • Hash Cond: (dcpscase_t.asgnd_user_org_uid = userorganization_t.user_org_uid)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,901.99..25,706.52 rows=163,575 width=133) (actual rows= loops=)

  • Hash Cond: ((caseclm_t.juris_ocd)::text = (ofcd_t.ocd)::text)
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,677.63..25,052.45 rows=163,575 width=106) (actual rows= loops=)

  • Hash Cond: ((dcpscase_t.case_stus_cd)::text = (adjstus_t.cd)::text)
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,676.54..24,167.03 rows=163,575 width=95) (actual rows= loops=)

  • Hash Cond: ((dcpscase_t.adjulvl_cd)::text = (adjlvl_t.cd)::text)
14. 0.000 0.000 ↓ 0.0

Hash Join (cost=12,675.43..23,372.58 rows=163,575 width=80) (actual rows= loops=)

  • Hash Cond: (caseclm_t.dcps_case_uid = dcpscase_t.dcps_case_uid)
15. 0.000 0.000 ↓ 0.0

Seq Scan on caseclm_t (cost=0.00..7,143.75 rows=163,575 width=13) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash (cost=9,521.97..9,521.97 rows=130,197 width=71) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on dcpscase_t (cost=0.00..9,521.97 rows=130,197 width=71) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=17) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on adjlvl_t (cost=0.00..1.05 rows=5 width=17) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=4 width=13) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on adjstus_t (cost=0.00..1.04 rows=4 width=13) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=156.38..156.38 rows=5,438 width=31) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on ofcd_t (cost=0.00..156.38 rows=5,438 width=31) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=123.37..123.37 rows=4,837 width=16) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on userorganization_t (cost=0.00..123.37 rows=4,837 width=16) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=65.50..65.50 rows=2,250 width=45) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on dcpsuser_t (cost=0.00..65.50 rows=2,250 width=45) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=89.84..89.84 rows=2,684 width=65) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on organization_t (cost=0.00..89.84 rows=2,684 width=65) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash (cost=1.09..1.09 rows=9 width=11) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on cdrtyp_t cdrtyp (cost=0.00..1.09 rows=9 width=11) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash (cost=1.11..1.11 rows=11 width=5) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on cslvlguicd_t clmlvl (cost=0.00..1.11 rows=11 width=5) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=1.11..1.11 rows=11 width=23) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on cslvlguicd_t cselvl (cost=0.00..1.11 rows=11 width=23) (actual rows= loops=)

36.          

CTE t_dcpscase_title

37. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=38,300.34..43,207.59 rows=163,575 width=102) (actual rows= loops=)

  • Group Key: caseclm_t_1.dcps_case_uid, dcpscase_t_1.dds_ccrnt_case_ind
38. 0.000 0.000 ↓ 0.0

Sort (cost=38,300.34..38,709.28 rows=163,575 width=12) (actual rows= loops=)

  • Sort Key: caseclm_t_1.dcps_case_uid, dcpscase_t_1.dds_ccrnt_case_ind
39. 0.000 0.000 ↓ 0.0

Hash Join (cost=11,658.43..21,338.58 rows=163,575 width=12) (actual rows= loops=)

  • Hash Cond: (caseclm_t_1.dcps_case_uid = dcpscase_t_1.dcps_case_uid)
40. 0.000 0.000 ↓ 0.0

Seq Scan on caseclm_t caseclm_t_1 (cost=0.00..7,143.75 rows=163,575 width=10) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash (cost=9,521.97..9,521.97 rows=130,197 width=6) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on dcpscase_t dcpscase_t_1 (cost=0.00..9,521.97 rows=130,197 width=6) (actual rows= loops=)

43.          

CTE t_dcpscssrc

44. 0.000 0.000 ↓ 0.0

Unique (cost=38,280.36..38,285.31 rows=660 width=6) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Sort (cost=38,280.36..38,282.01 rows=660 width=6) (actual rows= loops=)

  • Sort Key: dcpscssrc_t.dcps_case_uid, dcpscssrc_t.critl_src_sw
46. 0.000 0.000 ↓ 0.0

Hash Join (cost=35,834.50..38,249.45 rows=660 width=6) (actual rows= loops=)

  • Hash Cond: (dcpscssrc_t.dcps_case_src_uid = "ANY_subquery".max)
47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3,680.86..6,085.01 rows=1,319 width=10) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

HashAggregate (cost=3,680.44..3,682.44 rows=200 width=4) (actual rows= loops=)

  • Group Key: t_dcps_case.dcps_case_uid
49. 0.000 0.000 ↓ 0.0

CTE Scan on t_dcps_case (cost=0.00..3,271.50 rows=163,575 width=4) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Index Scan using ixf01_dcpscssrc on dcpscssrc_t (cost=0.42..11.93 rows=7 width=10) (actual rows= loops=)

  • Index Cond: (dcps_case_uid = t_dcps_case.dcps_case_uid)
51. 0.000 0.000 ↓ 0.0

Hash (cost=32,151.14..32,151.14 rows=200 width=4) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

HashAggregate (cost=32,149.14..32,151.14 rows=200 width=4) (actual rows= loops=)

  • Group Key: "ANY_subquery".max
53. 0.000 0.000 ↓ 0.0

Subquery Scan on ANY_subquery (cost=0.42..31,924.31 rows=89,932 width=4) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.42..31,024.99 rows=89,932 width=8) (actual rows= loops=)

  • Group Key: dcpscssrc_t_1.dcps_case_uid
55. 0.000 0.000 ↓ 0.0

Index Scan using ixf01_dcpscssrc on dcpscssrc_t dcpscssrc_t_1 (cost=0.42..27,159.40 rows=593,253 width=8) (actual rows= loops=)

56.          

CTE t_evrqst

57. 0.000 0.000 ↓ 0.0

Unique (cost=7,621.02..7,648.23 rows=907 width=112) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Sort (cost=7,621.02..7,623.28 rows=907 width=112) (actual rows= loops=)

  • Sort 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. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,695.02..7,576.46 rows=907 width=112) (actual rows= loops=)

  • Hash Cond: (ct.pkg_id = pt.pkg_uid)
60. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,681.71..7,550.68 rows=907 width=28) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3,681.29..6,999.84 rows=904 width=24) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3,680.86..5,909.20 rows=2,136 width=20) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

HashAggregate (cost=3,680.44..3,682.44 rows=200 width=4) (actual rows= loops=)

  • Group Key: t_dcps_case_1.dcps_case_uid
64. 0.000 0.000 ↓ 0.0

CTE Scan on t_dcps_case t_dcps_case_1 (cost=0.00..3,271.50 rows=163,575 width=4) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Index Scan using ix01_evrqst_t_dcps_case_uid on evrqst_t et (cost=0.42..11.01 rows=11 width=20) (actual rows= loops=)

  • Index Cond: (dcps_case_uid = t_dcps_case_1.dcps_case_uid)
66. 0.000 0.000 ↓ 0.0

Index Only Scan using ixf04_evrqstuid on pmtrqst_t pmt (cost=0.42..0.50 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (ev_rqst_uid = et.ev_rqst_uid)
67. 0.000 0.000 ↓ 0.0

Index Scan using ixf03_rqstuid on corrtrkg_t ct (cost=0.42..0.60 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (et.ev_rqst_uid = rqst_uid)
  • Filter: ((rqst_typ_cd)::text = 'MER'::text)
68. 0.000 0.000 ↓ 0.0

Hash (cost=10.73..10.73 rows=206 width=56) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=8.64..10.73 rows=206 width=56) (actual rows= loops=)

  • Hash Cond: (ft.pkg_uid = pt.pkg_uid)
70. 0.000 0.000 ↓ 0.0

Seq Scan on followup_t ft (cost=0.00..1.87 rows=87 width=15) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash (cost=6.06..6.06 rows=206 width=45) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on pkg_t pt (cost=0.00..6.06 rows=206 width=45) (actual rows= loops=)

73.          

CTE t_barcdtrkg

74. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=2,255.31..2,261.45 rows=1 width=60) (actual rows= loops=)

  • Filter: (a.rnk = 1)
75. 0.000 0.000 ↓ 0.0

WindowAgg (cost=2,255.31..2,259.09 rows=189 width=60) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Sort (cost=2,255.31..2,255.78 rows=189 width=52) (actual rows= loops=)

  • Sort Key: ct_1.rqst_uid, brt.resp_docu_rcvts
77. 0.000 0.000 ↓ 0.0

Nested Loop (cost=21.26..2,248.17 rows=189 width=52) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Nested Loop (cost=20.83..2,111.69 rows=273 width=35) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

HashAggregate (cost=20.41..22.41 rows=200 width=4) (actual rows= loops=)

  • Group Key: t_evrqst.ev_rqst_uid
80. 0.000 0.000 ↓ 0.0

CTE Scan on t_evrqst (cost=0.00..18.14 rows=907 width=4) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Index Scan using ixf03_rqstuid on corrtrkg_t ct_1 (cost=0.42..10.43 rows=1 width=35) (actual rows= loops=)

  • Index Cond: (rqst_uid = t_evrqst.ev_rqst_uid)
  • Filter: ((rqst_typ_cd)::text = 'MER'::text)
82. 0.000 0.000 ↓ 0.0

Index Scan using ixf01_barcdtrkg_t_corrtrkg on barcdtrkg_t brt (cost=0.42..0.49 rows=1 width=21) (actual rows= loops=)

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

CTE t_todoitemlist

84. 0.000 0.000 ↓ 0.0

Nested Loop (cost=15.28..6,422.15 rows=1,220 width=104) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

HashAggregate (cost=14.85..16.85 rows=200 width=4) (actual rows= loops=)

  • Group Key: t_dcpscssrc.dcps_case_uid
86. 0.000 0.000 ↓ 0.0

CTE Scan on t_dcpscssrc (cost=0.00..13.20 rows=660 width=4) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Index Scan using ix_todoitemlist_t_dcps_case_uid on todoitemlist_t (cost=0.43..31.93 rows=6 width=108) (actual rows= loops=)

  • Index Cond: (dcps_case_uid = t_dcpscssrc.dcps_case_uid)
  • Filter: ((fup_typ)::text = ANY ('{ME01,ME02}'::text[]))
88.          

CTE t_max_ts

89. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.43..190,767.05 rows=280,696 width=44) (actual rows= loops=)

  • Group Key: pmtrqavt_t.pmtrqst_pkg_uid
90. 0.000 0.000 ↓ 0.0

Index Scan using ixb01_pmtrqavt_t on pmtrqavt_t (cost=0.43..140,653.93 rows=1,720,224 width=26) (actual rows= loops=)

91.          

CTE t_pmtrqst

92. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,147.39..9,369.94 rows=1,026 width=190) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,146.97..8,821.73 rows=1,026 width=190) (actual rows= loops=)

  • Hash Cond: (mt.pmtrqst_pkg_uid = pmt_1.pmtrqst_pkg_uid)
94. 0.000 0.000 ↓ 0.0

CTE Scan on t_max_ts mt (cost=0.00..5,613.92 rows=280,696 width=44) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Hash (cost=2,134.14..2,134.14 rows=1,026 width=150) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Nested Loop (cost=15.27..2,134.14 rows=1,026 width=150) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

HashAggregate (cost=14.85..16.85 rows=200 width=4) (actual rows= loops=)

  • Group Key: t_dcpscssrc_1.dcps_case_uid
98. 0.000 0.000 ↓ 0.0

CTE Scan on t_dcpscssrc t_dcpscssrc_1 (cost=0.00..13.20 rows=660 width=4) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

Index Scan using ix_pmtrqst_t_dcps_case_uid on pmtrqst_t pmt_1 (cost=0.42..10.53 rows=5 width=150) (actual rows= loops=)

  • Index Cond: (dcps_case_uid = t_dcpscssrc_1.dcps_case_uid)
  • Filter: ((rqst_typ_cd)::text = 'MER'::text)
100. 0.000 0.000 ↓ 0.0

Index Scan using pmtrqstpkg_t_pkey on pmtrqstpkg_t pkg (cost=0.42..0.53 rows=1 width=8) (actual rows= loops=)

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

CTE t_pmtresp

102. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=29.88..2,468.02 rows=200 width=47) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Hash Join (cost=29.59..2,395.48 rows=200 width=12) (actual rows= loops=)

  • Hash Cond: (a_1.pmtrqst_uid = t_pmtrqst.pt_pmtrqst_uid)
104. 0.000 0.000 ↓ 0.0

Seq Scan on pkgpmta_t a_1 (cost=0.00..2,083.55 rows=107,555 width=12) (actual rows= loops=)

105. 0.000 0.000 ↓ 0.0

Hash (cost=27.09..27.09 rows=200 width=4) (actual rows= loops=)

106. 0.000 0.000 ↓ 0.0

HashAggregate (cost=23.09..25.09 rows=200 width=4) (actual rows= loops=)

  • Group Key: t_pmtrqst.pt_pmtrqst_uid
107. 0.000 0.000 ↓ 0.0

CTE Scan on t_pmtrqst (cost=0.00..20.52 rows=1,026 width=4) (actual rows= loops=)

108. 0.000 0.000 ↓ 0.0

Index Scan using ixpk_pmt_t on pmt_t b (cost=0.29..0.36 rows=1 width=39) (actual rows= loops=)

  • Index Cond: (a_1.pmt_uid = pmt_uid)
109.          

CTE t_omin

110. 0.000 0.000 ↓ 0.0

HashAggregate (cost=113,586.91..113,864.64 rows=27,773 width=8) (actual rows= loops=)

  • Group Key: pmtrqavt_t_1.pmtrqst_uid
111. 0.000 0.000 ↓ 0.0

Seq Scan on pmtrqavt_t pmtrqavt_t_1 (cost=49,794.76..113,442.12 rows=28,957 width=8) (actual rows= loops=)

  • Filter: ((NOT (hashed SubPlan 10)) AND ((pmtrqact_cd)::text = 'OBACH'::text))
112.          

SubPlan (for Seq Scan)

113. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..49,789.90 rows=1,945 width=4) (actual rows= loops=)

  • Workers Planned: 2
114. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on pmtrqavt_t pt_1 (cost=0.00..48,595.40 rows=810 width=4) (actual rows= loops=)

  • Filter: ((prev_amt IS NOT NULL) AND ((fscl_stus_cd)::text = 'DEND'::text) AND ((pmtrqact_cd)::text = 'STSCH'::text))
115.          

CTE t_orgobl_1

116. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,606.15..90,083.36 rows=1 width=13) (actual rows= loops=)

117. 0.000 0.000 ↓ 0.0

Merge Join (cost=2,605.73..90,082.89 rows=1 width=13) (actual rows= loops=)

  • Merge Cond: (pt_2.pmtrqah_uid = tm.min_pmtrqah_uid)
  • Join Filter: (pt_2.pmtrqst_uid = tm.pmtrqst_uid)
118. 0.000 0.000 ↓ 0.0

Index Scan using pmtrqavt_t_pkey on pmtrqavt_t pt_2 (cost=0.43..82,691.00 rows=1,720,224 width=13) (actual rows= loops=)

119. 0.000 0.000 ↓ 0.0

Sort (cost=2,605.30..2,674.73 rows=27,773 width=8) (actual rows= loops=)

  • Sort Key: tm.min_pmtrqah_uid
120. 0.000 0.000 ↓ 0.0

CTE Scan on t_omin tm (cost=0.00..555.46 rows=27,773 width=8) (actual rows= loops=)

121. 0.000 0.000 ↓ 0.0

Index Scan using pmtrqst_pkey on pmtrqst_t rt (cost=0.42..0.47 rows=1 width=8) (actual rows= loops=)

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

CTE t_orgobl_2

123. 0.000 0.000 ↓ 0.0

Gather (cost=82,394.17..205,650,676.67 rows=201,988 width=12) (actual rows= loops=)

  • Workers Planned: 2
124. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on pmtrqst_t (cost=81,394.17..205,629,477.87 rows=84,162 width=12) (actual rows= loops=)

  • Filter: (NOT (SubPlan 13))
125.          

SubPlan (for Parallel Seq Scan)

126. 0.000 0.000 ↓ 0.0

Materialize (cost=81,394.17..83,494.02 rows=136,921 width=4) (actual rows= loops=)

127. 0.000 0.000 ↓ 0.0

Unique (cost=81,394.17..82,274.42 rows=136,921 width=4) (actual rows= loops=)

128. 0.000 0.000 ↓ 0.0

Sort (cost=81,394.17..81,834.30 rows=176,049 width=4) (actual rows= loops=)

  • Sort Key: pmtrqavt_t_2.pmtrqst_uid
129. 0.000 0.000 ↓ 0.0

Seq Scan on pmtrqavt_t pmtrqavt_t_2 (cost=0.00..63,647.36 rows=176,049 width=4) (actual rows= loops=)

  • Filter: (((pmtrqact_cd)::text = 'OBACH'::text) OR ((fscl_stus_cd)::text = 'DEND'::text))
130.          

CTE t_dend_o

131. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.42..54,873.87 rows=1,945 width=13) (actual rows= loops=)

  • Workers Planned: 2
132. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..53,679.37 rows=810 width=13) (actual rows= loops=)

133. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on pmtrqavt_t pt_3 (cost=0.00..48,595.40 rows=810 width=9) (actual rows= loops=)

  • Filter: ((prev_amt IS NOT NULL) AND ((fscl_stus_cd)::text = 'DEND'::text) AND ((pmtrqact_cd)::text = 'STSCH'::text))
134. 0.000 0.000 ↓ 0.0

Index Scan using pmtrqst_pkey on pmtrqst_t rt_1 (cost=0.42..6.28 rows=1 width=8) (actual rows= loops=)

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

CTE t_org_oblgd_amt

136. 0.000 0.000 ↓ 0.0

Unique (cost=29,304.87..31,344.21 rows=203,934 width=22) (actual rows= loops=)

137. 0.000 0.000 ↓ 0.0

Sort (cost=29,304.87..29,814.70 rows=203,934 width=22) (actual rows= loops=)

  • Sort Key: t_orgobl_1.dcps_case_uid, t_orgobl_1.pmtrqst_uid, t_orgobl_1.org_oblgd_amt
138. 0.000 0.000 ↓ 0.0

Append (cost=0.00..7,137.69 rows=203,934 width=22) (actual rows= loops=)

139. 0.000 0.000 ↓ 0.0

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

140. 0.000 0.000 ↓ 0.0

CTE Scan on t_orgobl_2 (cost=0.00..4,039.76 rows=201,988 width=22) (actual rows= loops=)

141. 0.000 0.000 ↓ 0.0

CTE Scan on t_dend_o (cost=0.00..38.90 rows=1,945 width=22) (actual rows= loops=)

142.          

CTE t_omin_pa

143. 0.000 0.000 ↓ 0.0

HashAggregate (cost=113,691.99..114,157.03 rows=46,504 width=8) (actual rows= loops=)

  • Group Key: pmtrqavt_t_3.pmtrqst_uid
144. 0.000 0.000 ↓ 0.0

Seq Scan on pmtrqavt_t pmtrqavt_t_3 (cost=49,794.76..113,442.12 rows=49,973 width=8) (actual rows= loops=)

  • Filter: ((NOT (hashed SubPlan 17)) AND ((pmtrqact_cd)::text = 'PAACH'::text))
145.          

SubPlan (for Seq Scan)

146. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..49,789.90 rows=1,945 width=4) (actual rows= loops=)

  • Workers Planned: 2
147. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on pmtrqavt_t pt_4 (cost=0.00..48,595.40 rows=810 width=4) (actual rows= loops=)

  • Filter: ((prev_amt IS NOT NULL) AND ((fscl_stus_cd)::text = 'DEND'::text) AND ((pmtrqact_cd)::text = 'STSCH'::text))
148.          

CTE t_orgpaa_1

149. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,536.17..92,341.16 rows=1 width=13) (actual rows= loops=)

150. 0.000 0.000 ↓ 0.0

Merge Join (cost=4,535.75..92,340.70 rows=1 width=13) (actual rows= loops=)

  • Merge Cond: (pt_5.pmtrqah_uid = tm_1.min_pmtrqah_uid)
  • Join Filter: (pt_5.pmtrqst_uid = tm_1.pmtrqst_uid)
151. 0.000 0.000 ↓ 0.0

Index Scan using pmtrqavt_t_pkey on pmtrqavt_t pt_5 (cost=0.43..82,691.00 rows=1,720,224 width=13) (actual rows= loops=)

152. 0.000 0.000 ↓ 0.0

Sort (cost=4,535.32..4,651.58 rows=46,504 width=8) (actual rows= loops=)

  • Sort Key: tm_1.min_pmtrqah_uid
153. 0.000 0.000 ↓ 0.0

CTE Scan on t_omin_pa tm_1 (cost=0.00..930.08 rows=46,504 width=8) (actual rows= loops=)

154. 0.000 0.000 ↓ 0.0

Index Scan using pmtrqst_pkey on pmtrqst_t rt_2 (cost=0.42..0.47 rows=1 width=8) (actual rows= loops=)

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

CTE t_orgpaa_2

156. 0.000 0.000 ↓ 0.0

Gather (cost=86,642.29..242,695,947.12 rows=201,988 width=12) (actual rows= loops=)

  • Workers Planned: 2
157. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on pmtrqst_t pmtrqst_t_1 (cost=85,642.29..242,674,748.32 rows=84,162 width=12) (actual rows= loops=)

  • Filter: (NOT (SubPlan 20))
158.          

SubPlan (for Parallel Seq Scan)

159. 0.000 0.000 ↓ 0.0

Materialize (cost=85,642.29..88,128.63 rows=158,374 width=4) (actual rows= loops=)

160. 0.000 0.000 ↓ 0.0

Unique (cost=85,642.29..86,717.76 rows=158,374 width=4) (actual rows= loops=)

161. 0.000 0.000 ↓ 0.0

Sort (cost=85,642.29..86,180.03 rows=215,093 width=4) (actual rows= loops=)

  • Sort Key: pmtrqavt_t_4.pmtrqst_uid
162. 0.000 0.000 ↓ 0.0

Seq Scan on pmtrqavt_t pmtrqavt_t_4 (cost=0.00..63,647.36 rows=215,093 width=4) (actual rows= loops=)

  • Filter: (((pmtrqact_cd)::text = 'PAACH'::text) OR ((fscl_stus_cd)::text = 'DEND'::text))
163.          

CTE t_org_paybl_amt

164. 0.000 0.000 ↓ 0.0

Unique (cost=29,304.87..31,344.21 rows=203,934 width=22) (actual rows= loops=)

165. 0.000 0.000 ↓ 0.0

Sort (cost=29,304.87..29,814.70 rows=203,934 width=22) (actual rows= loops=)

  • Sort Key: t_orgpaa_1.dcps_case_uid, t_orgpaa_1.pmtrqst_uid, t_orgpaa_1.org_paybl_amt
166. 0.000 0.000 ↓ 0.0

Append (cost=0.00..7,137.69 rows=203,934 width=22) (actual rows= loops=)

167. 0.000 0.000 ↓ 0.0

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

168. 0.000 0.000 ↓ 0.0

CTE Scan on t_orgpaa_2 (cost=0.00..4,039.76 rows=201,988 width=22) (actual rows= loops=)

169. 0.000 0.000 ↓ 0.0

CTE Scan on t_dend_o t_dend_o_1 (cost=0.00..38.90 rows=1,945 width=22) (actual rows= loops=)

170.          

CTE t_evrqst_vndrsrv

171. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.70..9,390.69 rows=1,029 width=78) (actual rows= loops=)

172. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.85..8,218.75 rows=1,026 width=66) (actual rows= loops=)

173. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..7,646.53 rows=1,026 width=24) (actual rows= loops=)

174. 0.000 0.000 ↓ 0.0

CTE Scan on t_pmtrqst pmt_2 (cost=0.00..20.52 rows=1,026 width=8) (actual rows= loops=)

175. 0.000 0.000 ↓ 0.0

Index Scan using evrqst_pkey on evrqst_t et_1 (cost=0.42..7.43 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (ev_rqst_uid = pmt_2.ev_rqst_uid)
176. 0.000 0.000 ↓ 0.0

Index Scan using vndrsrv_t_pkey on vndrsrv_t vt (cost=0.43..0.56 rows=1 width=46) (actual rows= loops=)

  • Index Cond: (et_1.vndr_srv_uid = vndr_srv_uid)
177. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.85..1.13 rows=1 width=16) (actual rows= loops=)

178. 0.000 0.000 ↓ 0.0

Index Scan using ixf03_rqstuid on corrtrkg_t ct_2 (cost=0.42..0.60 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (et_1.ev_rqst_uid = rqst_uid)
  • Filter: ((rqst_typ_cd)::text = 'MER'::text)
179. 0.000 0.000 ↓ 0.0

Index Scan using ixn01_dmthdtrkg_t on dmthdtrkg_t dtrk (cost=0.42..0.52 rows=1 width=12) (actual rows= loops=)

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

CTE p_vndr

181. 0.000 0.000 ↓ 0.0

Unique (cost=8,490.17..8,490.32 rows=2 width=377) (actual rows= loops=)

182. 0.000 0.000 ↓ 0.0

Sort (cost=8,490.17..8,490.18 rows=2 width=377) (actual rows= loops=)

  • 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
183. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.71..8,490.16 rows=2 width=377) (actual rows= loops=)

184. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.28..8,489.56 rows=1 width=367) (actual rows= loops=)

185. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..8,489.00 rows=1 width=315) (actual rows= loops=)

186. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..8,488.49 rows=1 width=97) (actual rows= loops=)

187. 0.000 0.000 ↓ 0.0

CTE Scan on t_evrqst_vndrsrv tev (cost=0.00..20.58 rows=1,029 width=60) (actual rows= loops=)

188. 0.000 0.000 ↓ 0.0

Index Scan using vndraddr_t_pkey on vndraddr_t vat (cost=0.43..8.22 rows=1 width=41) (actual rows= loops=)

  • Index Cond: (vndr_addr_uid = tev.loc_vndr_addr_uid)
  • Filter: (((addr_use_cd)::text = 'P'::text) AND (tev.offering_vndr_uid = vndr_uid))
189. 0.000 0.000 ↓ 0.0

Index Scan using address_t_pkey on address_t adt (cost=0.43..0.51 rows=1 width=222) (actual rows= loops=)

  • Index Cond: (vat.addr_uid = addr_uid)
190. 0.000 0.000 ↓ 0.0

Index Scan using vendor_t_pkey on vendor_t vdt (cost=0.43..0.56 rows=1 width=56) (actual rows= loops=)

  • Index Cond: (vat.vndr_uid = vndr_uid)
191. 0.000 0.000 ↓ 0.0

Index Scan using lgcy_vndr_num_t_vndr_uid on lgcy_vndr_num_t lvn (cost=0.43..0.58 rows=2 width=14) (actual rows= loops=)

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

CTE c_vndr

193. 0.000 0.000 ↓ 0.0

Unique (cost=9,275.90..9,330.00 rows=698 width=377) (actual rows= loops=)

194. 0.000 0.000 ↓ 0.0

Sort (cost=9,275.90..9,277.65 rows=698 width=377) (actual rows= loops=)

  • 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
195. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.14..9,242.93 rows=698 width=377) (actual rows= loops=)

196. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.71..9,039.80 rows=332 width=367) (actual rows= loops=)

197. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.28..8,850.92 rows=332 width=315) (actual rows= loops=)

198. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..8,680.15 rows=332 width=97) (actual rows= loops=)

199. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..8,038.49 rows=1,029 width=60) (actual rows= loops=)

200. 0.000 0.000 ↓ 0.0

CTE Scan on t_evrqst_vndrsrv tev_1 (cost=0.00..20.58 rows=1,029 width=56) (actual rows= loops=)

201. 0.000 0.000 ↓ 0.0

Index Scan using vndrcommprfl_t_pkey on vndrcommprfl_t vct (cost=0.43..7.79 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (vndr_comm_prfl_uid = tev_1.vndr_comm_prfl_uid)
202. 0.000 0.000 ↓ 0.0

Index Scan using vndraddr_t_pkey on vndraddr_t vat_1 (cost=0.43..0.62 rows=1 width=41) (actual rows= loops=)

  • Index Cond: (vndr_addr_uid = vct.crsp_vndr_addr_uid)
  • Filter: ((addr_use_cd)::text = 'C'::text)
203. 0.000 0.000 ↓ 0.0

Index Scan using address_t_pkey on address_t adt_1 (cost=0.43..0.51 rows=1 width=222) (actual rows= loops=)

  • Index Cond: (vat_1.addr_uid = addr_uid)
204. 0.000 0.000 ↓ 0.0

Index Scan using vendor_t_pkey on vendor_t vdt_1 (cost=0.43..0.57 rows=1 width=56) (actual rows= loops=)

  • Index Cond: (vat_1.vndr_uid = vndr_uid)
205. 0.000 0.000 ↓ 0.0

Index Scan using lgcy_vndr_num_t_vndr_uid on lgcy_vndr_num_t lvn_1 (cost=0.43..0.59 rows=2 width=14) (actual rows= loops=)

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

CTE r_vndr

207. 0.000 0.000 ↓ 0.0

Unique (cost=8,472.74..8,472.87 rows=2 width=341) (actual rows= loops=)

208. 0.000 0.000 ↓ 0.0

Sort (cost=8,472.74..8,472.75 rows=2 width=341) (actual rows= loops=)

  • 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
209. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.71..8,472.73 rows=2 width=341) (actual rows= loops=)

210. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.28..8,472.12 rows=1 width=331) (actual rows= loops=)

211. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..8,471.56 rows=1 width=279) (actual rows= loops=)

212. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..8,471.05 rows=1 width=61) (actual rows= loops=)

213. 0.000 0.000 ↓ 0.0

CTE Scan on t_pmtrqst tev_2 (cost=0.00..20.52 rows=1,026 width=20) (actual rows= loops=)

214. 0.000 0.000 ↓ 0.0

Index Scan using vndraddr_t_pkey on vndraddr_t vat_2 (cost=0.43..8.23 rows=1 width=41) (actual rows= loops=)

  • Index Cond: (vndr_addr_uid = tev_2.pye_rmtaddr_id)
  • Filter: (((addr_use_cd)::text = 'R'::text) AND (tev_2.pye_vndr_id = vndr_uid))
215. 0.000 0.000 ↓ 0.0

Index Scan using address_t_pkey on address_t adt_2 (cost=0.43..0.51 rows=1 width=222) (actual rows= loops=)

  • Index Cond: (vat_2.addr_uid = addr_uid)
216. 0.000 0.000 ↓ 0.0

Index Scan using vendor_t_pkey on vendor_t vdt_2 (cost=0.43..0.57 rows=1 width=56) (actual rows= loops=)

  • Index Cond: (vat_2.vndr_uid = vndr_uid)
217. 0.000 0.000 ↓ 0.0

Index Scan using lgcy_vndr_num_t_vndr_uid on lgcy_vndr_num_t lvn_2 (cost=0.43..0.59 rows=2 width=14) (actual rows= loops=)

  • Index Cond: (vat_2.vndr_uid = vndr_uid)
  • Filter: (eff_end_ts IS NULL)
218.          

CTE tb_aprv

219. 0.000 0.000 ↓ 0.0

Subquery Scan on a_2 (cost=16,199.05..20,188.10 rows=570 width=67) (actual rows= loops=)

  • Filter: (a_2.rnk = 1)
220. 0.000 0.000 ↓ 0.0

WindowAgg (cost=16,199.05..18,763.44 rows=113,973 width=67) (actual rows= loops=)

221. 0.000 0.000 ↓ 0.0

Sort (cost=16,199.05..16,483.98 rows=113,973 width=40) (actual rows= loops=)

  • Sort Key: pmtrqavt_t_5.pmtrqst_pkg_uid, pmtrqavt_t_5.pmtrqah_uid DESC
222. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=117.14..3,507.76 rows=113,973 width=40) (actual rows= loops=)

  • Hash Cond: ((pmtrqavt_t_5.pmtrqact_pin)::text = (dcpsuser_t_1.pin)::text)
223. 0.000 0.000 ↓ 0.0

Nested Loop (cost=23.51..1,704.58 rows=113,965 width=27) (actual rows= loops=)

224. 0.000 0.000 ↓ 0.0

HashAggregate (cost=23.09..25.09 rows=200 width=4) (actual rows= loops=)

  • Group Key: t_pmtrqst_1.pmtrqst_pkg_uid
225. 0.000 0.000 ↓ 0.0

CTE Scan on t_pmtrqst t_pmtrqst_1 (cost=0.00..20.52 rows=1,026 width=4) (actual rows= loops=)

226. 0.000 0.000 ↓ 0.0

Index Scan using ixb01_pmtrqavt_t on pmtrqavt_t pmtrqavt_t_5 (cost=0.43..8.39 rows=1 width=27) (actual rows= loops=)

  • Index Cond: ((pmtrqst_pkg_uid = t_pmtrqst_1.pmtrqst_pkg_uid) AND ((fscl_stus_cd)::text = 'APRV'::text))
227. 0.000 0.000 ↓ 0.0

Hash (cost=65.50..65.50 rows=2,250 width=19) (actual rows= loops=)

228. 0.000 0.000 ↓ 0.0

Seq Scan on dcpsuser_t dcpsuser_t_1 (cost=0.00..65.50 rows=2,250 width=19) (actual rows= loops=)

229.          

CTE tb_cert

230. 0.000 0.000 ↓ 0.0

Subquery Scan on a_3 (cost=1,917.86..1,978.48 rows=9 width=67) (actual rows= loops=)

  • Filter: (a_3.rnk = 1)
231. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1,917.86..1,956.83 rows=1,732 width=67) (actual rows= loops=)

232. 0.000 0.000 ↓ 0.0

Sort (cost=1,917.86..1,922.19 rows=1,732 width=40) (actual rows= loops=)

  • Sort Key: pmtrqavt_t_6.pmtrqst_pkg_uid, pmtrqavt_t_6.pmtrqah_uid DESC
233. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=117.14..1,824.69 rows=1,732 width=40) (actual rows= loops=)

  • Hash Cond: ((pmtrqavt_t_6.pmtrqact_pin)::text = (dcpsuser_t_2.pin)::text)
234. 0.000 0.000 ↓ 0.0

Nested Loop (cost=23.51..1,705.08 rows=1,732 width=27) (actual rows= loops=)

235. 0.000 0.000 ↓ 0.0

HashAggregate (cost=23.09..25.09 rows=200 width=4) (actual rows= loops=)

  • Group Key: t_pmtrqst_2.pmtrqst_pkg_uid
236. 0.000 0.000 ↓ 0.0

CTE Scan on t_pmtrqst t_pmtrqst_2 (cost=0.00..20.52 rows=1,026 width=4) (actual rows= loops=)

237. 0.000 0.000 ↓ 0.0

Index Scan using ixb01_pmtrqavt_t on pmtrqavt_t pmtrqavt_t_6 (cost=0.43..8.39 rows=1 width=27) (actual rows= loops=)

  • Index Cond: ((pmtrqst_pkg_uid = t_pmtrqst_2.pmtrqst_pkg_uid) AND ((fscl_stus_cd)::text = 'CERT'::text))
  • Filter: ((pmtrqact_cd)::text = 'UPCRT'::text)
238. 0.000 0.000 ↓ 0.0

Hash (cost=65.50..65.50 rows=2,250 width=19) (actual rows= loops=)

239. 0.000 0.000 ↓ 0.0

Seq Scan on dcpsuser_t dcpsuser_t_2 (cost=0.00..65.50 rows=2,250 width=19) (actual rows= loops=)

240. 0.000 0.000 ↓ 0.0

Sort (cost=5,235,871,008,479,868.00..5,236,249,602,550,821.00 rows=151,437,628,381,098 width=7,974) (actual rows= loops=)

  • 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 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
241. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=549,048,300.78..24,112,304,305,726.89 rows=151,437,628,381,098 width=7,974) (actual rows= loops=)

  • Hash Cond: (tpt.pmtrqst_pkg_uid = cert.pmtrqst_pkg_uid)
242. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=549,048,300.48..2,274,998,293,172.27 rows=151,437,628,381,098 width=9,362) (actual rows= loops=)

  • Merge Cond: (topa.pmtrqst_uid = tpt.pt_pmtrqst_uid)
243. 0.000 0.000 ↓ 0.0

Sort (cost=26,245.86..26,755.69 rows=203,934 width=18) (actual rows= loops=)

  • Sort Key: topa.pmtrqst_uid
244. 0.000 0.000 ↓ 0.0

CTE Scan on t_org_paybl_amt topa (cost=0.00..4,078.68 rows=203,934 width=18) (actual rows= loops=)

245. 0.000 0.000 ↓ 0.0

Materialize (cost=549,022,054.63..3,805,130,970.78 rows=148,516,312,514 width=9,348) (actual rows= loops=)

246. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=549,022,054.63..3,433,840,189.50 rows=148,516,312,514 width=9,348) (actual rows= loops=)

  • Merge Cond: (tpt.pt_pmtrqst_uid = tooa.pmtrqst_uid)
247. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=548,995,808.77..1,205,704,624.62 rows=145,651,350 width=9,334) (actual rows= loops=)

  • Join Filter: (aprv.pmtrqst_pkg_uid = tpt.pmtrqst_pkg_uid)
248. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=548,995,808.77..550,273,541.89 rows=51,105,737 width=9,266) (actual rows= loops=)

  • 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))
249. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=548,995,808.72..549,890,248.56 rows=51,105,737 width=7,668) (actual rows= loops=)

  • 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))
250. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=548,995,761.79..549,506,819.42 rows=51,105,737 width=6,070) (actual rows= loops=)

  • 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))
251. 0.000 0.000 ↓ 0.0

Sort (cost=548,995,761.74..549,123,526.08 rows=51,105,737 width=4,424) (actual rows= loops=)

  • Sort Key: tpt.pt_pmtrqst_uid, te.ev_rqst_uid, td.dcps_case_uid
252. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=322,850.85..56,848,110.82 rows=51,105,737 width=4,424) (actual rows= loops=)

  • Hash Cond: (tpt.pye_rmtaddr_id = sfl.rmtn_vndr_addr_uid)
253. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=289,425.70..1,056,733.12 rows=51,105,737 width=4,414) (actual rows= loops=)

  • Merge Cond: (tdc.dcps_case_uid = tdpt.dcps_case_uid)
254. 0.000 0.000 ↓ 0.0

Sort (cost=263,042.93..263,199.15 rows=62,486 width=4,318) (actual rows= loops=)

  • Sort Key: tdc.dcps_case_uid
255. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=218.99..26,120.53 rows=62,486 width=4,318) (actual rows= loops=)

  • Hash Cond: ((td.dcps_case_uid = ttt.dcps_case_uid) AND (te.ev_rqst_uid = ttt.prnt_tbl_seq_id))
256. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=176.29..6,688.11 rows=62,486 width=4,242) (actual rows= loops=)

  • Hash Cond: (tdc.dcps_case_uid = td.dcps_case_uid)
257. 0.000 0.000 ↓ 0.0

Hash Join (cost=154.84..4,432.33 rows=18,935 width=4,230) (actual rows= loops=)

  • Hash Cond: (tdc.dcps_case_uid = te.dcps_case_uid)
258. 0.000 0.000 ↓ 0.0

CTE Scan on t_dcps_case tdc (cost=0.00..3,271.50 rows=163,575 width=2,986) (actual rows= loops=)

259. 0.000 0.000 ↓ 0.0

Hash (cost=154.56..154.56 rows=23 width=1,252) (actual rows= loops=)

260. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=149.33..154.56 rows=23 width=1,252) (actual rows= loops=)

  • Hash Cond: (tpr.pmtrqst_uid = tpt.pt_pmtrqst_uid)
261. 0.000 0.000 ↓ 0.0

CTE Scan on t_pmtresp tpr (cost=0.00..4.00 rows=200 width=176) (actual rows= loops=)

262. 0.000 0.000 ↓ 0.0

Hash (cost=149.04..149.04 rows=23 width=1,080) (actual rows= loops=)

263. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=134.27..149.04 rows=23 width=1,080) (actual rows= loops=)

  • Merge Cond: (te.ev_rqst_uid = tbt.rqst_uid)
264. 0.000 0.000 ↓ 0.0

Merge Join (cost=134.24..148.93 rows=23 width=1,072) (actual rows= loops=)

  • Merge Cond: ((te.ev_rqst_uid = tpt.ev_rqst_uid) AND (te.dcps_case_uid = tpt.dcps_case_uid))
265. 0.000 0.000 ↓ 0.0

Sort (cost=62.70..64.96 rows=907 width=218) (actual rows= loops=)

  • Sort Key: te.ev_rqst_uid, te.dcps_case_uid
266. 0.000 0.000 ↓ 0.0

CTE Scan on t_evrqst te (cost=0.00..18.14 rows=907 width=218) (actual rows= loops=)

267. 0.000 0.000 ↓ 0.0

Sort (cost=71.55..74.10 rows=1,021 width=858) (actual rows= loops=)

  • Sort Key: tpt.ev_rqst_uid, tpt.dcps_case_uid
268. 0.000 0.000 ↓ 0.0

CTE Scan on t_pmtrqst tpt (cost=0.00..20.52 rows=1,021 width=858) (actual rows= loops=)

  • Filter: (pt_pmtrqst_uid IS NOT NULL)
269. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=12) (actual rows= loops=)

  • Sort Key: tbt.rqst_uid
270. 0.000 0.000 ↓ 0.0

CTE Scan on t_barcdtrkg tbt (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

271. 0.000 0.000 ↓ 0.0

Hash (cost=13.20..13.20 rows=660 width=12) (actual rows= loops=)

272. 0.000 0.000 ↓ 0.0

CTE Scan on t_dcpscssrc td (cost=0.00..13.20 rows=660 width=12) (actual rows= loops=)

273. 0.000 0.000 ↓ 0.0

Hash (cost=24.40..24.40 rows=1,220 width=88) (actual rows= loops=)

274. 0.000 0.000 ↓ 0.0

CTE Scan on t_todoitemlist ttt (cost=0.00..24.40 rows=1,220 width=88) (actual rows= loops=)

275. 0.000 0.000 ↓ 0.0

Materialize (cost=26,382.76..27,200.64 rows=163,575 width=100) (actual rows= loops=)

276. 0.000 0.000 ↓ 0.0

Sort (cost=26,382.76..26,791.70 rows=163,575 width=100) (actual rows= loops=)

  • Sort Key: tdpt.dcps_case_uid
277. 0.000 0.000 ↓ 0.0

CTE Scan on t_dcpscase_title tdpt (cost=0.00..3,271.50 rows=163,575 width=100) (actual rows= loops=)

278. 0.000 0.000 ↓ 0.0

Hash (cost=28,932.70..28,932.70 rows=244,676 width=18) (actual rows= loops=)

279. 0.000 0.000 ↓ 0.0

Seq Scan on stfscllnk_t sfl (cost=0.00..28,932.70 rows=244,676 width=18) (actual rows= loops=)

  • Filter: ((date(actvn_ts) <= CURRENT_DATE) AND ((dactvn_ts IS NULL) OR (date(dactvn_ts) > CURRENT_DATE)))
280. 0.000 0.000 ↓ 0.0

Sort (cost=0.05..0.06 rows=2 width=1,658) (actual rows= loops=)

  • Sort Key: pv.pt_pmtrqst_uid, pv.ev_rqst_uid, pv.dcps_case_uid
281. 0.000 0.000 ↓ 0.0

CTE Scan on p_vndr pv (cost=0.00..0.04 rows=2 width=1,658) (actual rows= loops=)

282. 0.000 0.000 ↓ 0.0

Sort (cost=46.93..48.68 rows=698 width=1,610) (actual rows= loops=)

  • Sort Key: cv.pt_pmtrqst_uid, cv.ev_rqst_uid, cv.dcps_case_uid
283. 0.000 0.000 ↓ 0.0

CTE Scan on c_vndr cv (cost=0.00..13.96 rows=698 width=1,610) (actual rows= loops=)

284. 0.000 0.000 ↓ 0.0

Sort (cost=0.05..0.06 rows=2 width=1,614) (actual rows= loops=)

  • Sort Key: rv.pt_pmtrqst_uid, rv.ev_rqst_uid, rv.dcps_case_uid
285. 0.000 0.000 ↓ 0.0

CTE Scan on r_vndr rv (cost=0.00..0.04 rows=2 width=1,614) (actual rows= loops=)

286. 0.000 0.000 ↓ 0.0

CTE Scan on tb_aprv aprv (cost=0.00..11.40 rows=570 width=72) (actual rows= loops=)

287. 0.000 0.000 ↓ 0.0

Materialize (cost=26,245.86..27,265.53 rows=203,934 width=18) (actual rows= loops=)

288. 0.000 0.000 ↓ 0.0

Sort (cost=26,245.86..26,755.69 rows=203,934 width=18) (actual rows= loops=)

  • Sort Key: tooa.pmtrqst_uid
289. 0.000 0.000 ↓ 0.0

CTE Scan on t_org_oblgd_amt tooa (cost=0.00..4,078.68 rows=203,934 width=18) (actual rows= loops=)

290. 0.000 0.000 ↓ 0.0

Hash (cost=0.18..0.18 rows=9 width=72) (actual rows= loops=)

291. 0.000 0.000 ↓ 0.0

CTE Scan on tb_cert cert (cost=0.00..0.18 rows=9 width=72) (actual rows= loops=)