explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mMwC : prod_log

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 488,194.426 ↓ 0.0 0 1

HashAggregate (cost=12,295,076.25..12,295,099.25 rows=46 width=76) (actual time=488,194.426..488,194.426 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.004 488,194.425 ↓ 0.0 0 1

Append (cost=25,814.90..12,294,892.25 rows=46 width=76) (actual time=488,194.425..488,194.425 rows=0 loops=1)

3. 0.000 8,144.977 ↓ 0.0 0 1

Nested Loop (cost=25,814.90..3,074,531.24 rows=19 width=66) (actual time=8,144.977..8,144.977 rows=0 loops=1)

4. 0.001 8,144.977 ↓ 0.0 0 1

Nested Loop (cost=25,729.40..3,072,681.42 rows=21 width=42) (actual time=8,144.977..8,144.977 rows=0 loops=1)

5. 0.246 8,144.976 ↓ 0.0 0 1

Nested Loop (cost=25,643.90..3,070,580.85 rows=24 width=34) (actual time=8,144.976..8,144.976 rows=0 loops=1)

6. 37.895 52.030 ↓ 140.0 140 1

Subquery Scan on p (cost=25,532.90..25,606.90 rows=1 width=12) (actual time=17.703..52.030 rows=140 loops=1)

  • Filter: (hashed SubPlan 7)
  • Rows Removed by Filter: 36
7. 0.518 14.117 ↓ 176.0 176 1

Unique (cost=25,489.40..25,512.40 rows=1 width=725) (actual time=13.306..14.117 rows=176 loops=1)

8. 0.618 13.599 ↓ 176.0 176 1

Sort (cost=25,489.40..25,490.40 rows=1 width=725) (actual time=13.305..13.599 rows=176 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
9. 0.279 12.981 ↓ 176.0 176 1

Nested Loop (cost=254.50..25,487.40 rows=1 width=725) (actual time=0.254..12.981 rows=176 loops=1)

10. 0.224 8.572 ↓ 219.0 219 1

Nested Loop (cost=198.00..25,317.89 rows=1 width=552) (actual time=0.141..8.572 rows=219 loops=1)

11. 0.101 5.939 ↓ 219.0 219 1

Nested Loop (cost=114.50..25,222.38 rows=1 width=544) (actual time=0.113..5.939 rows=219 loops=1)

12. 4.305 4.305 ↓ 219.0 219 1

Index Scan using idx_placement_altproviderid on placement pl (cost=57.50..25,153.38 rows=1 width=536) (actual time=0.096..4.305 rows=219 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: 4813
13. 1.533 1.533 ↑ 1.0 1 219

Index Scan using "IX1001_servicecase" on servicecase sc (cost=57.00..68.50 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=219)

  • Index Cond: ((servicecaseid = pl.servicecaseid) AND (activeflag = 1))
14. 2.409 2.409 ↑ 1.0 1 219

Index Scan using pk_person on person pr (cost=83.50..95.00 rows=1 width=24) (actual time=0.011..0.011 rows=1 loops=219)

  • Index Cond: (personid = pl.personid)
  • Filter: (activeflag = 1)
15. 1.314 1.314 ↑ 1.0 1 219

Index Scan using pk_intakeservreqchildremoval on intakeservreqchildremoval irl (cost=56.50..68.00 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=219)

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

SubPlan (for Nested Loop)

17. 0.176 2.816 ↑ 1.0 1 176

Aggregate (cost=93.50..94.00 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=176)

18. 2.640 2.640 ↑ 1.0 1 176

Index Only Scan using idx_routing_worker on routing routing_3 (cost=85.50..93.00 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=176)

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

SubPlan (for Subquery Scan)

20. 0.018 0.018 ↑ 1.0 1 1

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

  • Filter: (golivedate <= current_date)
  • Rows Removed by Filter: 27
21. 8,092.700 8,092.700 ↓ 0.0 0 140

Index Scan using ix1010_tb_payment_detail on tb_payment_detail pd (cost=111.00..3,044,961.96 rows=24 width=30) (actual time=57.805..57.805 rows=0 loops=140)

  • Index Cond: ((placement_id = p.placement_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((final_service_id IS NOT NULL) AND (final_amount_no <> '0'::numeric))
  • Rows Removed by Filter: 0
22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix1010_tb_payment_header on tb_payment_header ph (cost=85.50..87.02 rows=1 width=8) (never executed)

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

Index Scan using ix1001_tb_payment_status on tb_payment_status ps (cost=85.50..87.59 rows=1 width=4) (never executed)

  • Index Cond: ((payment_id = ph.payment_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((payment_status_cd)::text = '1636'::text)
24. 0.000 228,811.398 ↓ 0.0 0 1

Nested Loop (cost=25,814.90..3,071,645.89 rows=4 width=66) (actual time=228,811.398..228,811.398 rows=0 loops=1)

25. 0.000 228,811.398 ↓ 0.0 0 1

Nested Loop (cost=25,729.40..3,071,293.54 rows=4 width=42) (actual time=228,811.398..228,811.398 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))
26. 7.754 228,811.398 ↓ 0.0 0 1

Nested Loop (cost=25,643.90..3,070,585.35 rows=8 width=42) (actual time=228,811.398..228,811.398 rows=0 loops=1)

27. 792.123 904.356 ↓ 3,992.0 3,992 1

Subquery Scan on p_1 (cost=25,532.90..25,606.90 rows=1 width=20) (actual time=92.636..904.356 rows=3,992 loops=1)

  • Filter: (hashed SubPlan 5)
  • Rows Removed by Filter: 577
28. 14.241 112.216 ↓ 4,569.0 4,569 1

Unique (cost=25,489.40..25,512.40 rows=1 width=725) (actual time=91.177..112.216 rows=4,569 loops=1)

29. 11.599 97.975 ↓ 4,569.0 4,569 1

Sort (cost=25,489.40..25,490.40 rows=1 width=725) (actual time=91.177..97.975 rows=4,569 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: 2549kB
30. 8.284 86.376 ↓ 4,569.0 4,569 1

Nested Loop (cost=254.50..25,487.40 rows=1 width=725) (actual time=0.095..86.376 rows=4,569 loops=1)

31. 2.328 32.396 ↓ 4,572.0 4,572 1

Nested Loop (cost=198.00..25,317.89 rows=1 width=552) (actual time=0.039..32.396 rows=4,572 loops=1)

32. 2.113 16.352 ↓ 4,572.0 4,572 1

Nested Loop (cost=114.50..25,222.38 rows=1 width=544) (actual time=0.029..16.352 rows=4,572 loops=1)

33. 5.095 5.095 ↓ 4,572.0 4,572 1

Index Scan using idx_placement_altproviderid on placement pl_1 (cost=57.50..25,153.38 rows=1 width=536) (actual time=0.019..5.095 rows=4,572 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: 460
34. 9.144 9.144 ↑ 1.0 1 4,572

Index Scan using "IX1001_servicecase" on servicecase sc_1 (cost=57.00..68.50 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=4,572)

  • Index Cond: ((servicecaseid = pl_1.servicecaseid) AND (activeflag = 1))
35. 13.716 13.716 ↑ 1.0 1 4,572

Index Scan using pk_person on person pr_1 (cost=83.50..95.00 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=4,572)

  • Index Cond: (personid = pl_1.personid)
  • Filter: (activeflag = 1)
36. 9.144 9.144 ↑ 1.0 1 4,572

Index Scan using pk_intakeservreqchildremoval on intakeservreqchildremoval irl_1 (cost=56.50..68.00 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=4,572)

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

SubPlan (for Nested Loop)

38. 4.569 36.552 ↑ 1.0 1 4,569

Aggregate (cost=93.50..94.00 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=4,569)

39. 31.983 31.983 ↑ 1.0 1 4,569

Index Only Scan using idx_routing_worker on routing routing_2 (cost=85.50..93.00 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=4,569)

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

SubPlan (for Subquery Scan)

41. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on county county_2 (cost=0.00..43.00 rows=1 width=5) (actual time=0.016..0.017 rows=1 loops=1)

  • Filter: (golivedate <= current_date)
  • Rows Removed by Filter: 27
42. 227,899.288 227,899.288 ↓ 0.0 0 3,992

Index Scan using ix1010_tb_payment_detail on tb_payment_detail pd_1 (cost=111.00..3,044,974.46 rows=8 width=30) (actual time=57.089..57.089 rows=0 loops=3,992)

  • Index Cond: ((placement_id = p_1.placement_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((final_service_id IS NOT NULL) AND (final_amount_no <> '0'::numeric) AND (final_service_start_dt > p_1.exit_dt))
  • Rows Removed by Filter: 5
43. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: ((payment_id = ph_1.payment_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((payment_status_cd)::text = '1636'::text)
45. 0.000 235,694.945 ↓ 0.0 0 1

Nested Loop (cost=25,814.90..3,071,645.89 rows=4 width=66) (actual time=235,694.945..235,694.945 rows=0 loops=1)

46. 0.138 235,694.945 ↓ 0.0 0 1

Nested Loop (cost=25,729.40..3,071,293.54 rows=4 width=42) (actual time=235,694.945..235,694.945 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: 38
47. 6.980 235,692.413 ↓ 4.8 38 1

Nested Loop (cost=25,643.90..3,070,585.35 rows=8 width=42) (actual time=37,858.547..235,692.413 rows=38 loops=1)

48. 765.748 866.124 ↓ 4,091.0 4,091 1

Subquery Scan on p_2 (cost=25,532.90..25,606.90 rows=1 width=20) (actual time=83.738..866.124 rows=4,091 loops=1)

  • Filter: (hashed SubPlan 3)
  • Rows Removed by Filter: 601
49. 12.450 100.360 ↓ 4,692.0 4,692 1

Unique (cost=25,489.40..25,512.40 rows=1 width=725) (actual time=82.481..100.360 rows=4,692 loops=1)

50. 10.237 87.910 ↓ 4,692.0 4,692 1

Sort (cost=25,489.40..25,490.40 rows=1 width=725) (actual time=82.480..87.910 rows=4,692 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: 2613kB
51. 6.190 77.673 ↓ 4,692.0 4,692 1

Nested Loop (cost=254.50..25,487.40 rows=1 width=725) (actual time=0.078..77.673 rows=4,692 loops=1)

52. 4.026 29.235 ↓ 4,702.0 4,702 1

Nested Loop (cost=198.00..25,317.89 rows=1 width=552) (actual time=0.035..29.235 rows=4,702 loops=1)

53. 1.266 15.805 ↓ 4,702.0 4,702 1

Nested Loop (cost=114.50..25,222.38 rows=1 width=544) (actual time=0.024..15.805 rows=4,702 loops=1)

54. 5.135 5.135 ↓ 4,702.0 4,702 1

Index Scan using idx_placement_altproviderid on placement pl_2 (cost=57.50..25,153.38 rows=1 width=536) (actual time=0.015..5.135 rows=4,702 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: 330
55. 9.404 9.404 ↑ 1.0 1 4,702

Index Scan using "IX1001_servicecase" on servicecase sc_2 (cost=57.00..68.50 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=4,702)

  • Index Cond: ((servicecaseid = pl_2.servicecaseid) AND (activeflag = 1))
56. 9.404 9.404 ↑ 1.0 1 4,702

Index Scan using pk_person on person pr_2 (cost=83.50..95.00 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=4,702)

  • Index Cond: (personid = pl_2.personid)
  • Filter: (activeflag = 1)
57. 9.404 9.404 ↑ 1.0 1 4,702

Index Scan using pk_intakeservreqchildremoval on intakeservreqchildremoval irl_2 (cost=56.50..68.00 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=4,702)

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

SubPlan (for Nested Loop)

59. 4.692 32.844 ↑ 1.0 1 4,692

Aggregate (cost=93.50..94.00 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=4,692)

60. 28.152 28.152 ↑ 1.0 1 4,692

Index Only Scan using idx_routing_worker on routing routing_1 (cost=85.50..93.00 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=4,692)

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

SubPlan (for Subquery Scan)

62. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on county county_1 (cost=0.00..43.00 rows=1 width=5) (actual time=0.015..0.016 rows=1 loops=1)

  • Filter: (golivedate <= current_date)
  • Rows Removed by Filter: 27
63. 234,819.309 234,819.309 ↓ 0.0 0 4,091

Index Scan using ix1010_tb_payment_detail on tb_payment_detail pd_2 (cost=111.00..3,044,974.46 rows=8 width=30) (actual time=56.993..57.399 rows=0 loops=4,091)

  • Index Cond: ((placement_id = p_2.placement_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((final_service_id IS NOT NULL) AND (final_amount_no <> '0'::numeric) AND (final_service_start_dt < p_2.entry_dt))
  • Rows Removed by Filter: 5
64. 2.394 2.394 ↑ 1.0 1 38

Index Only Scan using ix1010_tb_payment_header on tb_payment_header ph_2 (cost=85.50..87.02 rows=1 width=16) (actual time=0.062..0.063 rows=1 loops=38)

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

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

  • Index Cond: ((payment_id = ph_2.payment_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((payment_status_cd)::text = '1636'::text)
66. 0.000 15,543.101 ↓ 0.0 0 1

Nested Loop (cost=28,330.40..3,077,046.24 rows=19 width=66) (actual time=15,543.101..15,543.101 rows=0 loops=1)

67. 0.000 15,543.101 ↓ 0.0 0 1

Nested Loop (cost=28,244.90..3,075,196.42 rows=21 width=42) (actual time=15,543.101..15,543.101 rows=0 loops=1)

68. 0.379 15,543.101 ↓ 0.0 0 1

Nested Loop (cost=28,159.40..3,073,095.85 rows=24 width=34) (actual time=15,543.101..15,543.101 rows=0 loops=1)

69. 41.648 50.743 ↓ 269.0 269 1

Subquery Scan on p_3 (cost=28,048.40..28,121.90 rows=1 width=12) (actual time=8.157..50.743 rows=269 loops=1)

  • Filter: (hashed SubPlan 1)
  • Rows Removed by Filter: 19
70. 0.870 9.079 ↓ 288.0 288 1

Unique (cost=28,004.90..28,027.40 rows=1 width=725) (actual time=7.838..9.079 rows=288 loops=1)

71. 0.671 8.209 ↓ 288.0 288 1

Sort (cost=28,004.90..28,005.90 rows=1 width=725) (actual time=7.837..8.209 rows=288 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: 173kB
72. 0.774 7.538 ↓ 288.0 288 1

Nested Loop (cost=254.50..28,002.90 rows=1 width=725) (actual time=0.091..7.538 rows=288 loops=1)

73. 0.264 4.170 ↓ 289.0 289 1

Nested Loop (cost=198.00..27,833.39 rows=1 width=552) (actual time=0.045..4.170 rows=289 loops=1)

74. 0.308 3.039 ↓ 289.0 289 1

Nested Loop (cost=114.50..27,737.88 rows=1 width=544) (actual time=0.034..3.039 rows=289 loops=1)

75. 2.153 2.153 ↓ 289.0 289 1

Index Scan using idx_placement_altproviderid on placement pl_3 (cost=57.50..27,668.88 rows=1 width=536) (actual time=0.023..2.153 rows=289 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: 4743
76. 0.578 0.578 ↑ 1.0 1 289

Index Scan using "IX1001_servicecase" on servicecase sc_3 (cost=57.00..68.50 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=289)

  • Index Cond: ((servicecaseid = pl_3.servicecaseid) AND (activeflag = 1))
77. 0.867 0.867 ↑ 1.0 1 289

Index Scan using pk_person on person pr_3 (cost=83.50..95.00 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=289)

  • Index Cond: (personid = pl_3.personid)
  • Filter: (activeflag = 1)
78. 0.578 0.578 ↑ 1.0 1 289

Index Scan using pk_intakeservreqchildremoval on intakeservreqchildremoval irl_3 (cost=56.50..68.00 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=289)

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

SubPlan (for Nested Loop)

80. 0.000 2.016 ↑ 1.0 1 288

Aggregate (cost=93.50..94.00 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=288)

81. 2.016 2.016 ↑ 1.0 1 288

Index Only Scan using idx_routing_worker on routing (cost=85.50..93.00 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=288)

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

SubPlan (for Subquery Scan)

83. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on county (cost=0.00..43.00 rows=1 width=5) (actual time=0.015..0.016 rows=1 loops=1)

  • Filter: (golivedate <= current_date)
  • Rows Removed by Filter: 27
84. 15,491.979 15,491.979 ↓ 0.0 0 269

Index Scan using ix1010_tb_payment_detail on tb_payment_detail pd_3 (cost=111.00..3,044,961.96 rows=24 width=30) (actual time=57.591..57.591 rows=0 loops=269)

  • Index Cond: ((placement_id = p_3.placement_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((final_service_id IS NOT NULL) AND (final_amount_no <> '0'::numeric))
  • Rows Removed by Filter: 0
85. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: ((payment_id = ph_3.payment_id) AND (delete_sw = 'N'::bpchar))
  • Filter: ((payment_status_cd)::text = '1636'::text)
Planning time : 16.830 ms
Execution time : 488,196.967 ms