explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zqTQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 19,849.811 ↓ 0.0 0 1

HashAggregate (cost=456,727.58..456,728.00 rows=42 width=76) (actual time=19,849.811..19,849.811 rows=0 loops=1)

  • Group Key: ph.provider_id, pd.payment_detail_id, pd.final_amount_no, p.client_id, pd.final_service_start_dt, pd.final_service_end_dt, p.placement_id, ('1004'::text)
2. 0.005 19,849.808 ↓ 0.0 0 1

Append (cost=1,002.66..456,726.74 rows=42 width=76) (actual time=19,849.808..19,849.808 rows=0 loops=1)

3. 0.006 4,825.562 ↓ 0.0 0 1

Nested Loop (cost=1,002.66..114,186.16 rows=18 width=66) (actual time=4,825.562..4,825.562 rows=0 loops=1)

  • Join Filter: (ph.payment_id = ps.payment_id)
4. 0.013 4,825.533 ↑ 20.0 1 1

Nested Loop (cost=1,002.23..114,176.02 rows=20 width=42) (actual time=4,823.453..4,825.533 rows=1 loops=1)

5. 1,705.896 4,823.325 ↑ 22.0 1 1

Hash Join (cost=1,001.81..114,165.63 rows=22 width=34) (actual time=4,821.249..4,823.325 rows=1 loops=1)

  • Hash Cond: (pd.placement_id = p.placement_id)
6. 3,060.220 3,060.220 ↓ 1.0 2,485,390 1

Seq Scan on tb_payment_detail pd (cost=0.00..104,039.83 rows=2,433,005 width=30) (actual time=0.029..3,060.220 rows=2,485,390 loops=1)

  • Filter: ((final_service_id IS NOT NULL) AND (final_amount_no <> '0'::numeric) AND (delete_sw = 'N'::bpchar))
  • Rows Removed by Filter: 434864
7. 0.163 57.209 ↓ 141.0 141 1

Hash (cost=1,001.79..1,001.79 rows=1 width=12) (actual time=57.209..57.209 rows=141 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
8. 36.620 57.046 ↓ 141.0 141 1

Subquery Scan on p (cost=1,001.42..1,001.79 rows=1 width=12) (actual time=26.777..57.046 rows=141 loops=1)

  • Filter: (hashed SubPlan 7)
  • Rows Removed by Filter: 36
9. 0.421 20.396 ↓ 177.0 177 1

Unique (cost=999.99..1,000.11 rows=1 width=726) (actual time=19.786..20.396 rows=177 loops=1)

10. 0.735 19.975 ↓ 177.0 177 1

Sort (cost=999.99..1,000.00 rows=1 width=726) (actual time=19.784..19.975 rows=177 loops=1)

  • Sort Key: pl.alternateid, pl.altproviderid, pl.providerorganizationid, pl.contractprogramid, pl.startdatetime, pl.service_id, (CASE WHEN ((SubPlan 8) > 0) THEN '3047'::character varying ELSE NULL::character varying END), ((CASE COALESCE(pl.isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::character varying), pl.enddatetime, pl.paymentheaderid, pr.cjamspid, pl.ratestructureid, ((sc.servicecasenumber)::bigint), pl.intakeservicerequestactorid, pl.servicecaseid, irl.removalid, pl.tfcifcconversionflag, ((CASE COALESCE(pl.overunderflag, '0'::bpchar) WHEN '1'::bpchar THEN 'Y'::text WHEN '0'::bpchar THEN 'N'::text ELSE NULL::text END)::character varying), pl.voidapprovaldate, pl.starttime, pl.endtime, pl.intakeserviceid, pl.voidreasontypekey, (CASE WHEN (pl.courtorderedflag = 1) THEN 'Y'::character(1) WHEN (pl.courtorderedflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl.origplacementid, pl.voidapprovalstatustypekey, pl.exitreasontypekey, pl.insertedby, pl.insertedon, pl.updatedby, pl.updatedon, pl.caseid, pl.clientmergeid, (CASE WHEN (pl.datavalidflag = 1) THEN 'Y'::character(1) WHEN (pl.datavalidflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl.exittypekey, pl.facilityid, pl.fiscalcategorytypekey, (CASE WHEN (pl.icpcapprovedflag = 1) THEN 'Y'::character(1) WHEN (pl.icpcapprovedflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), (CASE WHEN (pl.medicaidpaidflag = 1) THEN 'Y'::character(1) WHEN (pl.medicaidpaidflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl.otherservices, pl.personid, pl.placementid, pl.providerid, pl.shortlistid
  • Sort Method: quicksort Memory: 115kB
11. 1.487 19.240 ↓ 177.0 177 1

Nested Loop (cost=1.27..999.98 rows=1 width=726) (actual time=0.399..19.240 rows=177 loops=1)

12. 0.711 11.965 ↓ 220.0 220 1

Nested Loop (cost=0.99..987.17 rows=1 width=553) (actual time=0.164..11.965 rows=220 loops=1)

13. 0.692 8.174 ↓ 220.0 220 1

Nested Loop (cost=0.57..978.72 rows=1 width=545) (actual time=0.133..8.174 rows=220 loops=1)

14. 5.502 5.502 ↓ 220.0 220 1

Index Scan using idx_placement_altproviderid on placement pl (cost=0.29..970.41 rows=1 width=537) (actual time=0.102..5.502 rows=220 loops=1)

  • Index Cond: (altproviderid IS NOT NULL)
  • Filter: (((CASE COALESCE(isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::character varying IS NOT NULL) AND (activeflag = 1) AND ((CASE COALESCE(isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::text = 'Y'::text) AND ((COALESCE((CASE COALESCE(overunderflag, '0'::bpchar) WHEN '1'::bpchar THEN 'Y'::text WHEN '0'::bpchar THEN 'N'::text ELSE NULL::text END)::character varying, 'N'::character varying))::text = 'N'::text))
  • Rows Removed by Filter: 4945
15. 1.980 1.980 ↑ 1.0 1 220

Index Scan using "IX1001_servicecase" on servicecase sc (cost=0.29..8.30 rows=1 width=24) (actual time=0.008..0.009 rows=1 loops=220)

  • Index Cond: ((servicecaseid = pl.servicecaseid) AND (activeflag = 1))
16. 3.080 3.080 ↑ 1.0 1 220

Index Scan using pk_person on person pr (cost=0.42..8.44 rows=1 width=24) (actual time=0.013..0.014 rows=1 loops=220)

  • Index Cond: (personid = pl.personid)
  • Filter: (activeflag = 1)
17. 1.540 1.540 ↑ 1.0 1 220

Index Scan using ix1010_intakeservreqchildremoval on intakeservreqchildremoval irl (cost=0.28..8.30 rows=1 width=24) (actual time=0.006..0.007 rows=1 loops=220)

  • Index Cond: (intakeservreqchildremovalid = pl.intakeservreqchildremovalid)
  • Filter: (activeflag = 1)
18.          

SubPlan (for Nested Loop)

19. 0.708 4.248 ↑ 1.0 1 177

Aggregate (cost=4.46..4.46 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=177)

20. 3.540 3.540 ↑ 1.0 1 177

Index Only Scan using idx_routing_worker on routing routing_3 (cost=0.43..4.45 rows=1 width=0) (actual time=0.019..0.020 rows=1 loops=177)

  • Index Cond: ((objectid = ((pl.placementid)::character varying)::text) AND (eventcode = 'PLTR'::text) AND (routingstatustypeid = 16) AND (activeflag = 1))
  • Heap Fetches: 0
21.          

SubPlan (for Subquery Scan)

22. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on county county_3 (cost=0.00..1.42 rows=1 width=5) (actual time=0.028..0.030 rows=1 loops=1)

  • Filter: (golivedate <= current_date)
  • Rows Removed by Filter: 27
23. 2.195 2.195 ↑ 1.0 1 1

Index Only Scan using ix1010_tb_payment_header on tb_payment_header ph (cost=0.43..0.46 rows=1 width=8) (actual time=2.194..2.195 rows=1 loops=1)

  • Index Cond: ((payment_id = pd.payment_id) AND (delete_sw = 'N'::bpchar))
  • Heap Fetches: 0
24. 0.023 0.023 ↓ 0.0 0 1

Index Scan using ix1001_tb_payment_status on tb_payment_status ps (cost=0.43..0.49 rows=1 width=4) (actual time=0.023..0.023 rows=0 loops=1)

  • Index Cond: ((payment_id = pd.payment_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((payment_status_cd)::text = '1636'::text)
  • Rows Removed by Filter: 1
25. 0.002 5,405.257 ↓ 0.0 0 1

Nested Loop (cost=1,002.66..114,170.55 rows=3 width=66) (actual time=5,405.257..5,405.257 rows=0 loops=1)

  • Join Filter: (ph_1.payment_id = ps_1.payment_id)
26. 0.003 5,405.255 ↓ 0.0 0 1

Nested Loop (cost=1,002.23..114,169.02 rows=3 width=42) (actual time=5,405.255..5,405.255 rows=0 loops=1)

  • Join Filter: ((p_1.exit_dt < ph_1.payment_start_dt) OR (p_1.exit_dt > ph_1.payment_end_dt))
27. 1,726.607 5,405.252 ↓ 0.0 0 1

Hash Join (cost=1,001.81..114,165.68 rows=7 width=42) (actual time=5,405.252..5,405.252 rows=0 loops=1)

  • Hash Cond: (pd_1.placement_id = p_1.placement_id)
  • Join Filter: (pd_1.final_service_start_dt > p_1.exit_dt)
  • Rows Removed by Join Filter: 19199
28. 2,671.810 2,671.810 ↓ 1.0 2,485,390 1

Seq Scan on tb_payment_detail pd_1 (cost=0.00..104,039.83 rows=2,433,005 width=30) (actual time=0.012..2,671.810 rows=2,485,390 loops=1)

  • Filter: ((final_service_id IS NOT NULL) AND (final_amount_no <> '0'::numeric) AND (delete_sw = 'N'::bpchar))
  • Rows Removed by Filter: 434864
29. 5.608 1,006.835 ↓ 4,092.0 4,092 1

Hash (cost=1,001.79..1,001.79 rows=1 width=20) (actual time=1,006.835..1,006.835 rows=4,092 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 256kB
30. 776.809 1,001.227 ↓ 4,092.0 4,092 1

Subquery Scan on p_1 (cost=1,001.42..1,001.79 rows=1 width=20) (actual time=207.014..1,001.227 rows=4,092 loops=1)

  • Filter: (hashed SubPlan 5)
  • Rows Removed by Filter: 578
31. 13.173 224.396 ↓ 4,670.0 4,670 1

Unique (cost=999.99..1,000.11 rows=1 width=726) (actual time=205.265..224.396 rows=4,670 loops=1)

32. 15.670 211.223 ↓ 4,670.0 4,670 1

Sort (cost=999.99..1,000.00 rows=1 width=726) (actual time=205.262..211.223 rows=4,670 loops=1)

  • Sort Key: pl_1.alternateid, pl_1.altproviderid, pl_1.providerorganizationid, pl_1.contractprogramid, pl_1.startdatetime, pl_1.service_id, (CASE WHEN ((SubPlan 6) > 0) THEN '3047'::character varying ELSE NULL::character varying END), ((CASE COALESCE(pl_1.isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::character varying), pl_1.enddatetime, pl_1.paymentheaderid, pr_1.cjamspid, pl_1.ratestructureid, ((sc_1.servicecasenumber)::bigint), pl_1.intakeservicerequestactorid, pl_1.servicecaseid, irl_1.removalid, pl_1.tfcifcconversionflag, ((CASE COALESCE(pl_1.overunderflag, '0'::bpchar) WHEN '1'::bpchar THEN 'Y'::text WHEN '0'::bpchar THEN 'N'::text ELSE NULL::text END)::character varying), pl_1.voidapprovaldate, pl_1.starttime, pl_1.endtime, pl_1.intakeserviceid, pl_1.voidreasontypekey, (CASE WHEN (pl_1.courtorderedflag = 1) THEN 'Y'::character(1) WHEN (pl_1.courtorderedflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl_1.origplacementid, pl_1.voidapprovalstatustypekey, pl_1.exitreasontypekey, pl_1.insertedby, pl_1.insertedon, pl_1.updatedby, pl_1.updatedon, pl_1.caseid, pl_1.clientmergeid, (CASE WHEN (pl_1.datavalidflag = 1) THEN 'Y'::character(1) WHEN (pl_1.datavalidflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl_1.exittypekey, pl_1.facilityid, pl_1.fiscalcategorytypekey, (CASE WHEN (pl_1.icpcapprovedflag = 1) THEN 'Y'::character(1) WHEN (pl_1.icpcapprovedflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), (CASE WHEN (pl_1.medicaidpaidflag = 1) THEN 'Y'::character(1) WHEN (pl_1.medicaidpaidflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl_1.otherservices, pl_1.personid, pl_1.placementid, pl_1.providerid, pl_1.shortlistid
  • Sort Method: quicksort Memory: 2595kB
33. 27.144 195.553 ↓ 4,670.0 4,670 1

Nested Loop (cost=1.27..999.98 rows=1 width=726) (actual time=0.114..195.553 rows=4,670 loops=1)

34. 13.622 79.667 ↓ 4,673.0 4,673 1

Nested Loop (cost=0.99..987.17 rows=1 width=553) (actual time=0.049..79.667 rows=4,673 loops=1)

35. 13.137 42.680 ↓ 4,673.0 4,673 1

Nested Loop (cost=0.57..978.72 rows=1 width=545) (actual time=0.035..42.680 rows=4,673 loops=1)

36. 10.851 10.851 ↓ 4,673.0 4,673 1

Index Scan using idx_placement_altproviderid on placement pl_1 (cost=0.29..970.41 rows=1 width=537) (actual time=0.022..10.851 rows=4,673 loops=1)

  • Index Cond: (altproviderid IS NOT NULL)
  • Filter: ((enddatetime IS NOT NULL) AND (activeflag = 1) AND (((CASE COALESCE(isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::character varying IS NULL) OR ((CASE COALESCE(isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::text = 'N'::text)) AND ((COALESCE((CASE COALESCE(overunderflag, '0'::bpchar) WHEN '1'::bpchar THEN 'Y'::text WHEN '0'::bpchar THEN 'N'::text ELSE NULL::text END)::character varying, 'N'::character varying))::text = 'N'::text))
  • Rows Removed by Filter: 492
37. 18.692 18.692 ↑ 1.0 1 4,673

Index Scan using "IX1001_servicecase" on servicecase sc_1 (cost=0.29..8.30 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=4,673)

  • Index Cond: ((servicecaseid = pl_1.servicecaseid) AND (activeflag = 1))
38. 23.365 23.365 ↑ 1.0 1 4,673

Index Scan using pk_person on person pr_1 (cost=0.42..8.44 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=4,673)

  • Index Cond: (personid = pl_1.personid)
  • Filter: (activeflag = 1)
39. 18.692 18.692 ↑ 1.0 1 4,673

Index Scan using ix1010_intakeservreqchildremoval on intakeservreqchildremoval irl_1 (cost=0.28..8.30 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=4,673)

  • Index Cond: (intakeservreqchildremovalid = pl_1.intakeservreqchildremovalid)
  • Filter: (activeflag = 1)
  • Rows Removed by Filter: 0
40.          

SubPlan (for Nested Loop)

41. 18.680 70.050 ↑ 1.0 1 4,670

Aggregate (cost=4.46..4.46 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=4,670)

42. 51.370 51.370 ↑ 1.0 1 4,670

Index Only Scan using idx_routing_worker on routing routing_2 (cost=0.43..4.45 rows=1 width=0) (actual time=0.010..0.011 rows=1 loops=4,670)

  • Index Cond: ((objectid = ((pl_1.placementid)::character varying)::text) AND (eventcode = 'PLTR'::text) AND (routingstatustypeid = 16) AND (activeflag = 1))
  • Heap Fetches: 38
43.          

SubPlan (for Subquery Scan)

44. 0.022 0.022 ↑ 1.0 1 1

Seq Scan on county county_2 (cost=0.00..1.42 rows=1 width=5) (actual time=0.020..0.022 rows=1 loops=1)

  • Filter: (golivedate <= current_date)
  • Rows Removed by Filter: 27
45. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix1010_tb_payment_header on tb_payment_header ph_1 (cost=0.43..0.46 rows=1 width=16) (never executed)

  • Index Cond: ((payment_id = pd_1.payment_id) AND (delete_sw = 'N'::bpchar))
  • Heap Fetches: 0
46. 0.000 0.000 ↓ 0.0 0

Index Scan using ix1001_tb_payment_status on tb_payment_status ps_1 (cost=0.43..0.49 rows=1 width=4) (never executed)

  • Index Cond: ((payment_id = pd_1.payment_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((payment_status_cd)::text = '1636'::text)
47. 0.002 5,182.835 ↓ 0.0 0 1

Nested Loop (cost=1,002.66..114,170.55 rows=3 width=66) (actual time=5,182.835..5,182.835 rows=0 loops=1)

  • Join Filter: (ph_2.payment_id = ps_2.payment_id)
48. 0.191 5,182.833 ↓ 0.0 0 1

Nested Loop (cost=1,002.23..114,169.02 rows=3 width=42) (actual time=5,182.833..5,182.833 rows=0 loops=1)

  • Join Filter: ((p_2.entry_dt < ph_2.payment_start_dt) OR (p_2.entry_dt > ph_2.payment_end_dt))
  • Rows Removed by Join Filter: 39
49. 1,736.386 5,159.788 ↓ 5.6 39 1

Hash Join (cost=1,001.81..114,165.68 rows=7 width=42) (actual time=760.874..5,159.788 rows=39 loops=1)

  • Hash Cond: (pd_2.placement_id = p_2.placement_id)
  • Join Filter: (pd_2.final_service_start_dt < p_2.entry_dt)
  • Rows Removed by Join Filter: 20168
50. 2,677.454 2,677.454 ↓ 1.0 2,485,390 1

Seq Scan on tb_payment_detail pd_2 (cost=0.00..104,039.83 rows=2,433,005 width=30) (actual time=0.011..2,677.454 rows=2,485,390 loops=1)

  • Filter: ((final_service_id IS NOT NULL) AND (final_amount_no <> '0'::numeric) AND (delete_sw = 'N'::bpchar))
  • Rows Removed by Filter: 434864
51. 4.438 745.948 ↓ 4,212.0 4,212 1

Hash (cost=1,001.79..1,001.79 rows=1 width=20) (actual time=745.948..745.948 rows=4,212 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 295kB
52. 527.315 741.510 ↓ 4,212.0 4,212 1

Subquery Scan on p_2 (cost=1,001.42..1,001.79 rows=1 width=20) (actual time=200.108..741.510 rows=4,212 loops=1)

  • Filter: (hashed SubPlan 3)
  • Rows Removed by Filter: 612
53. 10.488 214.174 ↓ 4,824.0 4,824 1

Unique (cost=999.99..1,000.11 rows=1 width=726) (actual time=198.399..214.174 rows=4,824 loops=1)

54. 15.480 203.686 ↓ 4,824.0 4,824 1

Sort (cost=999.99..1,000.00 rows=1 width=726) (actual time=198.396..203.686 rows=4,824 loops=1)

  • Sort Key: pl_2.alternateid, pl_2.altproviderid, pl_2.providerorganizationid, pl_2.contractprogramid, pl_2.startdatetime, pl_2.service_id, (CASE WHEN ((SubPlan 4) > 0) THEN '3047'::character varying ELSE NULL::character varying END), ((CASE COALESCE(pl_2.isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::character varying), pl_2.enddatetime, pl_2.paymentheaderid, pr_2.cjamspid, pl_2.ratestructureid, ((sc_2.servicecasenumber)::bigint), pl_2.intakeservicerequestactorid, pl_2.servicecaseid, irl_2.removalid, pl_2.tfcifcconversionflag, ((CASE COALESCE(pl_2.overunderflag, '0'::bpchar) WHEN '1'::bpchar THEN 'Y'::text WHEN '0'::bpchar THEN 'N'::text ELSE NULL::text END)::character varying), pl_2.voidapprovaldate, pl_2.starttime, pl_2.endtime, pl_2.intakeserviceid, pl_2.voidreasontypekey, (CASE WHEN (pl_2.courtorderedflag = 1) THEN 'Y'::character(1) WHEN (pl_2.courtorderedflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl_2.origplacementid, pl_2.voidapprovalstatustypekey, pl_2.exitreasontypekey, pl_2.insertedby, pl_2.insertedon, pl_2.updatedby, pl_2.updatedon, pl_2.caseid, pl_2.clientmergeid, (CASE WHEN (pl_2.datavalidflag = 1) THEN 'Y'::character(1) WHEN (pl_2.datavalidflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl_2.exittypekey, pl_2.facilityid, pl_2.fiscalcategorytypekey, (CASE WHEN (pl_2.icpcapprovedflag = 1) THEN 'Y'::character(1) WHEN (pl_2.icpcapprovedflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), (CASE WHEN (pl_2.medicaidpaidflag = 1) THEN 'Y'::character(1) WHEN (pl_2.medicaidpaidflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl_2.otherservices, pl_2.personid, pl_2.placementid, pl_2.providerid, pl_2.shortlistid
  • Sort Method: quicksort Memory: 2669kB
55. 31.125 188.206 ↓ 4,824.0 4,824 1

Nested Loop (cost=1.27..999.98 rows=1 width=726) (actual time=0.114..188.206 rows=4,824 loops=1)

56. 16.893 79.857 ↓ 4,834.0 4,834 1

Nested Loop (cost=0.99..987.17 rows=1 width=553) (actual time=0.054..79.857 rows=4,834 loops=1)

57. 13.560 43.628 ↓ 4,834.0 4,834 1

Nested Loop (cost=0.57..978.72 rows=1 width=545) (actual time=0.038..43.628 rows=4,834 loops=1)

58. 10.732 10.732 ↓ 4,834.0 4,834 1

Index Scan using idx_placement_altproviderid on placement pl_2 (cost=0.29..970.41 rows=1 width=537) (actual time=0.024..10.732 rows=4,834 loops=1)

  • Index Cond: (altproviderid IS NOT NULL)
  • Filter: ((activeflag = 1) AND (((CASE COALESCE(isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::character varying IS NULL) OR ((CASE COALESCE(isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::text = 'N'::text)) AND ((COALESCE((CASE COALESCE(overunderflag, '0'::bpchar) WHEN '1'::bpchar THEN 'Y'::text WHEN '0'::bpchar THEN 'N'::text ELSE NULL::text END)::character varying, 'N'::character varying))::text = 'N'::text))
  • Rows Removed by Filter: 331
59. 19.336 19.336 ↑ 1.0 1 4,834

Index Scan using "IX1001_servicecase" on servicecase sc_2 (cost=0.29..8.30 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=4,834)

  • Index Cond: ((servicecaseid = pl_2.servicecaseid) AND (activeflag = 1))
60. 19.336 19.336 ↑ 1.0 1 4,834

Index Scan using pk_person on person pr_2 (cost=0.42..8.44 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=4,834)

  • Index Cond: (personid = pl_2.personid)
  • Filter: (activeflag = 1)
61. 19.336 19.336 ↑ 1.0 1 4,834

Index Scan using ix1010_intakeservreqchildremoval on intakeservreqchildremoval irl_2 (cost=0.28..8.30 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=4,834)

  • Index Cond: (intakeservreqchildremovalid = pl_2.intakeservreqchildremovalid)
  • Filter: (activeflag = 1)
  • Rows Removed by Filter: 0
62.          

SubPlan (for Nested Loop)

63. 14.472 57.888 ↑ 1.0 1 4,824

Aggregate (cost=4.46..4.46 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=4,824)

64. 43.416 43.416 ↑ 1.0 1 4,824

Index Only Scan using idx_routing_worker on routing routing_1 (cost=0.43..4.45 rows=1 width=0) (actual time=0.008..0.009 rows=1 loops=4,824)

  • Index Cond: ((objectid = ((pl_2.placementid)::character varying)::text) AND (eventcode = 'PLTR'::text) AND (routingstatustypeid = 16) AND (activeflag = 1))
  • Heap Fetches: 64
65.          

SubPlan (for Subquery Scan)

66. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on county county_1 (cost=0.00..1.42 rows=1 width=5) (actual time=0.018..0.021 rows=1 loops=1)

  • Filter: (golivedate <= current_date)
  • Rows Removed by Filter: 27
67. 22.854 22.854 ↑ 1.0 1 39

Index Only Scan using ix1010_tb_payment_header on tb_payment_header ph_2 (cost=0.43..0.46 rows=1 width=16) (actual time=0.585..0.586 rows=1 loops=39)

  • Index Cond: ((payment_id = pd_2.payment_id) AND (delete_sw = 'N'::bpchar))
  • Heap Fetches: 0
68. 0.000 0.000 ↓ 0.0 0

Index Scan using ix1001_tb_payment_status on tb_payment_status ps_2 (cost=0.43..0.49 rows=1 width=4) (never executed)

  • Index Cond: ((payment_id = pd_2.payment_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((payment_status_cd)::text = '1636'::text)
69. 0.002 4,436.149 ↓ 0.0 0 1

Nested Loop (cost=1,015.57..114,199.07 rows=18 width=66) (actual time=4,436.149..4,436.149 rows=0 loops=1)

  • Join Filter: (ph_3.payment_id = ps_3.payment_id)
70. 0.002 4,436.147 ↓ 0.0 0 1

Nested Loop (cost=1,015.14..114,188.93 rows=20 width=42) (actual time=4,436.147..4,436.147 rows=0 loops=1)

71. 1,710.642 4,436.145 ↓ 0.0 0 1

Hash Join (cost=1,014.71..114,178.53 rows=22 width=34) (actual time=4,436.145..4,436.145 rows=0 loops=1)

  • Hash Cond: (pd_3.placement_id = p_3.placement_id)
72. 2,680.040 2,680.040 ↓ 1.0 2,485,390 1

Seq Scan on tb_payment_detail pd_3 (cost=0.00..104,039.83 rows=2,433,005 width=30) (actual time=0.011..2,680.040 rows=2,485,390 loops=1)

  • Filter: ((final_service_id IS NOT NULL) AND (final_amount_no <> '0'::numeric) AND (delete_sw = 'N'::bpchar))
  • Rows Removed by Filter: 434864
73. 0.279 45.463 ↓ 276.0 276 1

Hash (cost=1,014.70..1,014.70 rows=1 width=12) (actual time=45.463..45.463 rows=276 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
74. 26.334 45.184 ↓ 276.0 276 1

Subquery Scan on p_3 (cost=1,014.32..1,014.70 rows=1 width=12) (actual time=18.228..45.184 rows=276 loops=1)

  • Filter: (hashed SubPlan 1)
  • Rows Removed by Filter: 19
75. 0.661 18.832 ↓ 295.0 295 1

Unique (cost=1,012.90..1,013.01 rows=1 width=726) (actual time=17.874..18.832 rows=295 loops=1)

76. 1.087 18.171 ↓ 295.0 295 1

Sort (cost=1,012.90..1,012.91 rows=1 width=726) (actual time=17.871..18.171 rows=295 loops=1)

  • Sort Key: pl_3.alternateid, pl_3.altproviderid, pl_3.providerorganizationid, pl_3.contractprogramid, pl_3.startdatetime, pl_3.service_id, (CASE WHEN ((SubPlan 2) > 0) THEN '3047'::character varying ELSE NULL::character varying END), ((CASE COALESCE(pl_3.isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::character varying), pl_3.paymentheaderid, pr_3.cjamspid, pl_3.ratestructureid, ((sc_3.servicecasenumber)::bigint), pl_3.intakeservicerequestactorid, pl_3.servicecaseid, irl_3.removalid, pl_3.tfcifcconversionflag, ((CASE COALESCE(pl_3.overunderflag, '0'::bpchar) WHEN '1'::bpchar THEN 'Y'::text WHEN '0'::bpchar THEN 'N'::text ELSE NULL::text END)::character varying), pl_3.voidapprovaldate, pl_3.starttime, pl_3.endtime, pl_3.intakeserviceid, pl_3.voidreasontypekey, (CASE WHEN (pl_3.courtorderedflag = 1) THEN 'Y'::character(1) WHEN (pl_3.courtorderedflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl_3.origplacementid, pl_3.voidapprovalstatustypekey, pl_3.exitreasontypekey, pl_3.insertedby, pl_3.insertedon, pl_3.updatedby, pl_3.updatedon, pl_3.caseid, pl_3.clientmergeid, (CASE WHEN (pl_3.datavalidflag = 1) THEN 'Y'::character(1) WHEN (pl_3.datavalidflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl_3.exittypekey, pl_3.facilityid, pl_3.fiscalcategorytypekey, (CASE WHEN (pl_3.icpcapprovedflag = 1) THEN 'Y'::character(1) WHEN (pl_3.icpcapprovedflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), (CASE WHEN (pl_3.medicaidpaidflag = 1) THEN 'Y'::character(1) WHEN (pl_3.medicaidpaidflag = 0) THEN 'N'::character(1) ELSE NULL::character(1) END), pl_3.otherservices, pl_3.personid, pl_3.placementid, pl_3.providerid, pl_3.shortlistid
  • Sort Method: quicksort Memory: 177kB
77. 2.293 17.084 ↓ 295.0 295 1

Nested Loop (cost=1.27..1,012.89 rows=1 width=726) (actual time=0.123..17.084 rows=295 loops=1)

78. 1.006 8.886 ↓ 296.0 296 1

Nested Loop (cost=0.99..1,000.08 rows=1 width=553) (actual time=0.061..8.886 rows=296 loops=1)

79. 0.991 6.104 ↓ 296.0 296 1

Nested Loop (cost=0.57..991.63 rows=1 width=545) (actual time=0.046..6.104 rows=296 loops=1)

80. 3.633 3.633 ↓ 296.0 296 1

Index Scan using idx_placement_altproviderid on placement pl_3 (cost=0.29..983.32 rows=1 width=537) (actual time=0.032..3.633 rows=296 loops=1)

  • Index Cond: (altproviderid IS NOT NULL)
  • Filter: ((startdatetime IS NOT NULL) AND (enddatetime IS NOT NULL) AND (activeflag = 1) AND (startdatetime = enddatetime) AND (((CASE COALESCE(isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::character varying IS NULL) OR ((CASE COALESCE(isvoided, 0) WHEN 1 THEN 'Y'::text ELSE NULL::text END)::text = 'N'::text)) AND ((COALESCE((CASE COALESCE(overunderflag, '0'::bpchar) WHEN '1'::bpchar THEN 'Y'::text WHEN '0'::bpchar THEN 'N'::text ELSE NULL::text END)::character varying, 'N'::character varying))::text = 'N'::text))
  • Rows Removed by Filter: 4869
81. 1.480 1.480 ↑ 1.0 1 296

Index Scan using "IX1001_servicecase" on servicecase sc_3 (cost=0.29..8.30 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=296)

  • Index Cond: ((servicecaseid = pl_3.servicecaseid) AND (activeflag = 1))
82. 1.776 1.776 ↑ 1.0 1 296

Index Scan using pk_person on person pr_3 (cost=0.42..8.44 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=296)

  • Index Cond: (personid = pl_3.personid)
  • Filter: (activeflag = 1)
83. 1.480 1.480 ↑ 1.0 1 296

Index Scan using ix1010_intakeservreqchildremoval on intakeservreqchildremoval irl_3 (cost=0.28..8.30 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=296)

  • Index Cond: (intakeservreqchildremovalid = pl_3.intakeservreqchildremovalid)
  • Filter: (activeflag = 1)
  • Rows Removed by Filter: 0
84.          

SubPlan (for Nested Loop)

85. 1.180 4.425 ↑ 1.0 1 295

Aggregate (cost=4.46..4.46 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=295)

86. 3.245 3.245 ↑ 1.0 1 295

Index Only Scan using idx_routing_worker on routing (cost=0.43..4.45 rows=1 width=0) (actual time=0.010..0.011 rows=1 loops=295)

  • Index Cond: ((objectid = ((pl_3.placementid)::character varying)::text) AND (eventcode = 'PLTR'::text) AND (routingstatustypeid = 16) AND (activeflag = 1))
  • Heap Fetches: 3
87.          

SubPlan (for Subquery Scan)

88. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on county (cost=0.00..1.42 rows=1 width=5) (actual time=0.017..0.018 rows=1 loops=1)

  • Filter: (golivedate <= current_date)
  • Rows Removed by Filter: 27
89. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix1010_tb_payment_header on tb_payment_header ph_3 (cost=0.43..0.46 rows=1 width=8) (never executed)

  • Index Cond: ((payment_id = pd_3.payment_id) AND (delete_sw = 'N'::bpchar))
  • Heap Fetches: 0
90. 0.000 0.000 ↓ 0.0 0

Index Scan using ix1001_tb_payment_status on tb_payment_status ps_3 (cost=0.43..0.49 rows=1 width=4) (never executed)

  • Index Cond: ((payment_id = pd_3.payment_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((payment_status_cd)::text = '1636'::text)
Planning time : 28.932 ms
Execution time : 19,852.768 ms