explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8x6

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

Unique (cost=376,734.29..376,734.35 rows=1 width=365) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=376,734.29..376,734.29 rows=1 width=365) (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.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, 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))
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=374,320.55..376,734.28 rows=1 width=365) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=374,320.13..376,733.22 rows=1 width=320) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=374,319.84..376,730.70 rows=1 width=298) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Merge Join (cost=374,319.42..376,728.05 rows=1 width=184) (actual rows= loops=)

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

GroupAggregate (cost=374,307.17..376,168.37 rows=43,793 width=75) (actual rows= loops=)

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

Sort (cost=374,307.17..374,416.65 rows=43,793 width=83) (actual rows= loops=)

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

Gather (cost=5,279.86..369,703.13 rows=43,793 width=83) (actual rows= loops=)

  • Workers Planned: 4
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,279.86..364,323.83 rows=10,948 width=83) (actual rows= loops=)

  • Hash Cond: (((mt.scheme_id)::text = (ms.scheme_id)::text) AND ((mt.asri_code)::text = (ms.dis_main_id)::text))
11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,246.03..363,796.64 rows=10,968 width=67) (actual rows= loops=)

  • Join Filter: ((mt.scheme_id)::text = (tc.scheme_id)::text)
  • -> Index Scan using trn_case_pkey on trn_case tc (cost=0.43..0.60 row=1 width=10)
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,245.60..283,354.76 rows=131,818 width=65) (actual rows= loops=)

  • Hash Cond: (((aa.procedure_disp_code)::text = (mt.icd_proc_code)::text) AND ((aa.category_disp_code)::text = (mt.asri_code)::text))
  • Index Cond: ((case_id)::text = (aa.case_id)::text)
13. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on trn_case_therapy aa (cost=0.00..82,200.60 rows=386,951 width=19) (actual rows= loops=)

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

Hash (cost=3,026.28..3,026.28 rows=49,222 width=53) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on m_main_therapy mt (cost=0.00..3,026.28 rows=49,222 width=53) (actual rows= loops=)

  • Filter: ((state)::text = '22'::text)
16. 0.000 0.000 ↓ 0.0

Hash (cost=24.65..24.65 rows=612 width=30) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on m_specialities ms (cost=0.00..24.65 rows=612 width=30) (actual rows= loops=)

  • Filter: ((state_code)::text = '22'::text)
18. 0.000 0.000 ↓ 0.0

Sort (cost=12.24..12.25 rows=1 width=109) (actual rows= loops=)

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

Hash Join (cost=6.53..12.23 rows=1 width=109) (actual rows= loops=)

  • Hash Cond: ((rc.case_status)::text = (c.case_status)::text)
20. 0.000 0.000 ↓ 0.0

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

21. 0.000 0.000 ↓ 0.0

Hash (cost=6.51..6.51 rows=1 width=106) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..6.51 rows=1 width=106) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using workflow_type_inx on t_workflow_details w (cost=0.43..3.85 rows=1 width=17) (actual rows= loops=)

  • Index Cond: ((workflow_type)::text = ANY ('{1349097,319}'::text[]))
  • Filter: ((scheme_id)::text = 'CK'::text)
24. 0.000 0.000 ↓ 0.0

Index Scan using trn_case_ukey on trn_case c (cost=0.43..2.65 rows=1 width=91) (actual rows= loops=)

  • Index Cond: ((case_patient_no)::text = (w.transaction_id)::text)
  • Filter: (((scheme_id)::text = 'CK'::text) AND ((case_hosp_code)::text ='CHH3175'::text))
25. 0.000 0.000 ↓ 0.0

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

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

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

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

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

  • Index Cond: ((case_id)::text = (aa.case_id)::text)
  • Filter: (((covid_case)::text = 'N'::text) OR (covid_case IS NULL) OR ((covid_case)::text = ''::text))