explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p3hU

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

Sort (cost=68,128.49..68,128.50 rows=1 width=1,514) (actual rows= loops=)

  • Sort Key: c.case_regn_date
2. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=67,995.62..68,128.48 rows=1 width=1,514) (actual rows= loops=)

  • Group Key: c.case_id, p.patient_id, h.hosp_name, cs.workflow_status_desc, rc.bg_colors, h.hosp_type, lg1.loc_name, lg2.loc_name, w.case_speciality, h.hosp_disp_code, w.assign_flag, b.unit_id, w.assignedto_grp_id
3. 0.000 0.000 ↓ 0.0

Sort (cost=67,995.62..67,995.63 rows=1 width=475) (actual rows= loops=)

  • Sort Key: c.case_id, p.patient_id, h.hosp_name, cs.workflow_status_desc, rc.bg_colors, h.hosp_type, lg1.loc_name, lg2.loc_name, w.case_speciality, h.hosp_disp_code, w.assign_flag, b.unit_id, w.assignedto_grp_id
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=38,425.98..67,995.61 rows=1 width=475) (actual rows= loops=)

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

Nested Loop (cost=38,425.98..67,990.38 rows=1 width=469) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash Join (cost=38,425.83..67,990.21 rows=1 width=452) (actual rows= loops=)

  • Hash Cond: ((w.transaction_id)::text = (p.patient_id)::text)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=222.39..27,447.36 rows=623,839 width=34) (actual rows= loops=)

  • Hash Cond: ((w.assignedto_unitid)::text = (b.unit_id)::text)
8. 0.000 0.000 ↓ 0.0

Seq Scan on t_workflow_details w (cost=0.00..24,882.66 rows=623,839 width=35) (actual rows= loops=)

  • Filter: ((workflow_type)::text = ANY ('{PREAUTH,CLAIM}'::text[]))
9. 0.000 0.000 ↓ 0.0

Hash (cost=164.95..164.95 rows=4,595 width=9) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on units_master b (cost=0.00..164.95 rows=4,595 width=9) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Hash (cost=38,203.43..38,203.43 rows=1 width=430) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Gather (cost=7,536.20..38,203.43 rows=1 width=430) (actual rows= loops=)

  • Workers Planned: 3
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6,536.20..37,203.33 rows=1 width=430) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6,535.77..37,200.98 rows=5 width=423) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash Join (cost=6,535.35..37,198.22 rows=5 width=289) (actual rows= loops=)

  • Hash Cond: ((c.case_hosp_code)::integer = h.hosp_id)
16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,914.11..32,510.53 rows=10,625 width=248) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,913.69..26,217.86 rows=10,625 width=79) (actual rows= loops=)

  • Hash Cond: (((aa.category_disp_code)::text = (ss.dis_main_id)::text) AND ((aa.procedure_disp_code)::text = (mm.icd_proc_code)::text))
18. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on trn_case_therapy aa (cost=0.00..22,629.36 rows=212,039 width=18) (actual rows= loops=)

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

Hash (cost=1,889.27..1,889.27 rows=1,628 width=75) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Join (cost=69.52..1,889.27 rows=1,628 width=75) (actual rows= loops=)

  • Hash Cond: ((mm.asri_code)::text = (ss.dis_main_id)::text)
21. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on m_main_therapy mm (cost=67.89..1,865.26 rows=1,628 width=51) (actual rows= loops=)

  • Recheck Cond: (active_yn = 'Y'::bpchar)
22. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on active_yn_ind3 (cost=0.00..67.49 rows=1,628 width=0) (actual rows= loops=)

  • Index Cond: (active_yn = 'Y'::bpchar)
23. 0.000 0.000 ↓ 0.0

Hash (cost=1.31..1.31 rows=25 width=24) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on m_specialities ss (cost=0.00..1.31 rows=25 width=24) (actual rows= loops=)

  • Filter: (dis_active_yn = 'Y'::bpchar)
25. 0.000 0.000 ↓ 0.0

Index Scan using trn_case_pkey on trn_case c (cost=0.42..0.58 rows=1 width=176) (actual rows= loops=)

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

Hash (cost=4,621.22..4,621.22 rows=1 width=54) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..4,621.22 rows=1 width=54) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on scheme_hosp_master h (cost=0.00..131.68 rows=2,068 width=47) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Index Scan using ind_loc_new on m_locations_lg lg2 (cost=0.42..2.16 rows=1 width=17) (actual rows= loops=)

  • Index Cond: ((loc_id)::text = (h.hosp_district)::text)
  • Filter: ((type)::text = 'DT'::text)
30. 0.000 0.000 ↓ 0.0

Index Scan using trn_patient_dtls_pkey on trn_patient_dtls p (cost=0.42..0.54 rows=1 width=134) (actual rows= loops=)

  • Index Cond: ((patient_id)::text = (c.case_patient_no)::text)
  • Filter: ((patient_scheme)::text = '341'::text)
31. 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=17) (actual rows= loops=)

  • Index Cond: ((loc_id)::text = (p.district_code)::text)
  • Filter: ((type)::text = 'DT'::text)
32. 0.000 0.000 ↓ 0.0

Index Scan using scheme_wrkflw_mst_pkey on scheme_wrkflw_mst cs (cost=0.15..0.17 rows=1 width=33) (actual rows= loops=)

  • Index Cond: (workflow_id = w.current_workflow_id)
33. 0.000 0.000 ↓ 0.0

Seq Scan on result_code rc (cost=0.00..2.88 rows=188 width=11) (actual rows= loops=)

34.          

SubPlan (forGroupAggregate)

35. 0.000 0.000 ↓ 0.0

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

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

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

37. 0.000 0.000 ↓ 0.0

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

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

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

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

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

  • Index Cond: ((user_id)::text = (h_1.user_id)::text)
40. 0.000 0.000 ↓ 0.0

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

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