explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hP8b

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

Unique (cost=679,931.62..679,931.74 rows=1 width=1,538) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=679,931.62..679,931.63 rows=1 width=1,538) (actual rows= loops=)

  • Sort Key: (to_char(c.case_regn_date, 'DD/MM/YYYY'::text)), c.case_id, p.scheme_id, c.case_no, (to_char(p.reg_hosp_date, 'DD/MM/YYYY'::text)), (COALESCE(c.claim_no, 'NA'::character varying)), p.card_no, p.name, p.patient_id, p.patient_ipop, cs.workflow_status_desc, c.case_status, c.flagged, rc.bg_colors,p.contact_no, p.patient_ipop_no, c.case_hosp_code, p.hosp_name, p.non_pmjay, p.hosp_disp_code, p.hosp_dist, (string_agg(((((ms.dis_main_name)::text || '('::text) || (aa.category_disp_code)::text) || ')'::text), ','::text)), (string_agg(((((mt.proc_name)::text || '('::text) || (aa.procedure_disp_code)::text) || ')'::text), ','::text)), lg3.loc_name, lg1.loc_name, (to_char(c.cs_surg_dt, 'DD/MM/YYYY HH12:MI:SS PM'::text)), (to_char(c.cs_death_dt, 'DD/MM/YYYY HH12:MI:SSPM'::text)), (to_char(c.cs_preauth_dt, 'DD/MM/YYYY HH12:MI:SS PM'::text)), c.tot_pckg_amt, (((c.pck_appv_amt)::text || ''::text)), (to_char(c.preauth_aprv_dt, 'DD/MM/YYYY HH12:MI:SS PM'::text)), (to_char(c.cs_dis_dt, 'DD/MM/YYYY HH12:MI:SS PM'::text)), (to_char(c.actual_clm_sub_dt, 'DD/MM/YYYY HH12:MI:SS PM'::text)), (to_char(c.clm_sub_dt, 'DD/MM/YYYY HH12:MI:SS PM'::text)), (((c.claiminitamt)::text || ''::text)), (CASE WHEN ((c.case_status)::text = 'CD51'::text) THEN ((ff.cpd_aprv_amt)::text || ''::text) ELSE '0'::text END), (((ff.cpd_aprv_amt)::text || ''::text)), (CASE WHEN ((w.case_speciality)::text = 'MULTIPLE'::text) THEN 'SURGICAL'::text WHEN ((w.case_speciality)::text ~~ 'S%'::text) THEN 'SURGICAL'::text ELSE 'MEDICAL'::text END), (CASE WHEN ((p.gender)::text = 's2nab2BgKftnNWuiGrtcCw=='::text) THEN 'MALE'::text WHEN ((p.gender)::text = 'nPKfqll3Rmn7tlSBf6VFGA=='::text) THEN 'FEMALE'::text ELSE 'TRANSGENDER'::text END),(((p.age)::text || ''::text)), (to_char(c.cs_adm_dt, 'DD/MM/YYYY HH12:MI:SS PM'::text)), (to_char(p.crt_dt, 'DD/MM/YYYY HH12:MI:SS PM'::text)), (to_char(c.preauth_rej_dt, 'DD/MM/YYYY HH12:MI:SS PM'::text)), p.family_id, (CASE WHEN ((w.assign_flag)::text = 'Y'::text) THEN w.assign_flag ELSE 'NA'::character varying END), ((SubPlan 1)), ((SubPlan 2))
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=660,766.57..679,931.61 rows=1 width=1,538) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=660,766.14..679,768.34 rows=1 width=504) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=660,765.71..679,767.77 rows=1 width=512) (actual rows= loops=)

  • Join Filter: ((c.case_status)::text = (rc.case_status)::text)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=660,765.71..679,759.72 rows=1 width=509) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=660,765.43..679,759.40 rows=1 width=487) (actual rows= loops=)

  • Join Filter: ((p.patient_id)::text = (w.transaction_id)::text)
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=593,645.26..606,809.64 rows=1 width=466) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=593,644.84..603,940.24 rows=6,163 width=459) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Merge Join (cost=593,644.41..597,190.94 rows=6,170 width=267) (actual rows= loops=)

  • Merge Cond: ((aa.case_id)::text = (c.case_id)::text)
11. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=407,211.04..409,089.41 rows=44,197 width=75) (actual rows= loops=)

  • Group Key: aa.case_id, ms.state_code
12. 0.000 0.000 ↓ 0.0

Sort (cost=407,211.04..407,321.53 rows=44,197 width=83) (actual rows= loops=)

  • Sort Key: aa.case_id
13. 0.000 0.000 ↓ 0.0

Gather (cost=1,001.12..403,800.88 rows=44,197 width=83) (actual rows= loops=)

  • Workers Planned: 5
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.12..398,381.18 rows=8,839 width=83) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..395,655.80 rows=8,854 width=67) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on trn_case tc (cost=0.00..111,333.89 rows=301,989 width=10) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..0.93 rows=1 width=65) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using trn_case_therapy_ind_1 on trn_case_therapyaa (cost=0.43..0.48 rows=1 width=19) (actual rows= loops=)

  • Index Cond: ((case_id)::text = (tc.case_id)::text)
  • Filter: (((proc_type)::text <> 'D'::text) AND ((activeyn)::text = 'Y'::text))
19. 0.000 0.000 ↓ 0.0

Index Scan using m_main_therapy_pkey on m_main_therapy mt (cost=0.41..0.44 rows=1 width=53) (actual rows= loops=)

  • Index Cond: (((state)::text = '22'::text) AND ((scheme_id)::text = (tc.scheme_id)::text) AND ((asri_code)::text = (aa.category_disp_code)::text) AND ((icd_proc_code)::text = (aa.procedure_disp_code)::text))
20. 0.000 0.000 ↓ 0.0

Index Scan using m_specialities_pkey on m_specialities ms (cost=0.28..0.30 rows=1 width=30) (actual rows= loops=)

  • Index Cond: (((dis_main_id)::text = (mt.asri_code)::text) AND ((scheme_id)::text = (mt.scheme_id)::text) AND ((state_code)::text = '22'::text))
21. 0.000 0.000 ↓ 0.0

Sort (cost=186,433.37..186,960.37 rows=210,800 width=192) (actual rows= loops=)

  • Sort Key: c.case_id
22. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.43..167,792.84 rows=210,800 width=192) (actual rows= loops=)

  • Workers Planned: 5
23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..145,712.84 rows=42,160 width=192) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on trn_case c (cost=0.00..112,843.84 rows=42,160 width=184) (actual rows= loops=)

  • Filter: (((scheme_id)::text = 'S'::text) AND ((phase_id)::text = 'PS1'::text))
25. 0.000 0.000 ↓ 0.0

Index Scan using trn_case_claim_pkey on trn_case_claim ff (cost=0.43..0.77 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((c.case_id)::text = (case_id)::text)
26. 0.000 0.000 ↓ 0.0

Index Scan using trn_patient_dtls_pkey on trn_patient_dtls p (cost=0.43..1.08 rows=1 width=195) (actual rows= loops=)

  • Index Cond: ((patient_id)::text = (c.case_patient_no)::text)
  • Filter: (((state_code)::text = '22'::text) AND ((hosp_state)::text = '22'::text) AND ((portability)::text = 'N'::text))
27. 0.000 0.000 ↓ 0.0

Index Scan using ind_loc_new on m_locations_lg lg1 (cost=0.42..0.46 rows=1 width=21) (actual rows= loops=)

  • Index Cond: ((loc_id)::text = (p.district_code)::text)
  • Filter: (((loc_parnt_id)::text = '22'::text) AND ((type)::text = 'DT'::text))
28. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=67,120.17..68,869.50 rows=326,421 width=37) (actual rows= loops=)

  • Merge Cond: ((w.assignedto_unitid)::text = (b.unit_id)::text)
29. 0.000 0.000 ↓ 0.0

Sort (cost=67,011.49..67,827.54 rows=326,421 width=38) (actual rows= loops=)

  • Sort Key: w.assignedto_unitid
30. 0.000 0.000 ↓ 0.0

Index Scan using t_workflow_details_scheme_id on t_workflow_details w (cost=0.43..37,117.24 rows=326,421 width=38) (actual rows= loops=)

  • Index Cond: ((scheme_id)::text = 'S'::text)
  • Filter: ((workflow_type)::text = ANY ('{PREUATH,CLAIM}'::text[]))
31. 0.000 0.000 ↓ 0.0

Index Only Scan using units_master_pkey on units_master b (cost=0.28..210.26 rows=6,125 width=9) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Scan using scheme_wrkflw_mst_pkey on scheme_wrkflw_mst cs (cost=0.28..0.31 rows=1 width=43) (actual rows= loops=)

  • Index Cond: ((workflow_id = w.current_workflow_id) AND ((scheme)::text = 'S'::text) AND ((state_code)::text = '22'::text))
33. 0.000 0.000 ↓ 0.0

Seq Scan on result_code rc (cost=0.00..4.69 rows=269 width=8) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Index Scan using trn_case_ext_pkey on trn_case_ext ce (cost=0.43..0.56 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((case_id)::text = (c.case_id)::text)
  • Filter: (((covid_case)::text = 'N'::text) OR (covid_case IS NULL) OR ((covid_case)::text = ''::text))
35. 0.000 0.000 ↓ 0.0

Index Scan using ind_loc_new on m_locations_lg lg3 (cost=0.42..0.46 rows=1 width=21) (actual rows= loops=)

  • Index Cond: ((loc_id)::text = (p.hosp_dist)::text)
  • Filter: (((loc_parnt_id)::text = '22'::text) AND ((type)::text = 'DT'::text))
36.          

SubPlan (for Nested Loop)

37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..160.36 rows=1 width=32) (actual rows= loops=)

  • Join Filter: ((g.user_id)::text = (f.user_id)::text)
38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..159.91 rows=1 width=44) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on user_unit_mpg g (cost=0.00..157.40 rows=1 width=8) (actual rows= loops=)

  • Filter: ((unit_id)::text = (b.unit_id)::text)
40. 0.000 0.000 ↓ 0.0

Index Scan using user_master_pkey on user_master h (cost=0.28..2.50 rows=1 width=36) (actual rows= loops=)

  • Index Cond: ((user_id)::text = (g.user_id)::text)
41. 0.000 0.000 ↓ 0.0

Index Scan using user_master_pkey on user_master f (cost=0.28..0.43 rows=1 width=17) (actual rows= loops=)

  • Index Cond: ((user_id)::text = (h.user_id)::text)
42. 0.000 0.000 ↓ 0.0

Index Scan using group_master_pkey on group_master k (cost=0.14..2.36 rows=1 width=11) (actual rows= loops=)

  • Index Cond: ((group_id)::text = (w.assignedto_grp_id)::text)