explain.depesz.com

PostgreSQL's explain analyze made readable

Result: davK

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,015.845 ↑ 1.0 30 1

Limit (cost=2,706,468.75..2,706,468.82 rows=30 width=127) (actual time=1,015.832..1,015.845 rows=30 loops=1)

  • Output: md.id, md.code, ((((md.code)::text || ' '::text) || (md.name)::text)), ($7), (CASE WHEN $8 THEN (SubPlan 6) ELSE (SubPlan 7) END), (CASE WHEN $11 THEN (SubPlan 9) ELSE (SubPlan 10) END), (CASE WHEN $14 THEN (SubPlan 13) ELSE (SubPlan 15) END), (1805409), md.is_injury, (198248), ((SubPlan 17))
  • Buffers: shared hit=772577
2.          

CTE t

3. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=318.49..318.70 rows=21 width=14) (never executed)

  • Output: md1.code, md2.code, mnrd.nosol_registr_id
  • Group Key: md1.code, md2.code, mnrd.nosol_registr_id
4. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..318.33 rows=21 width=14) (never executed)

  • Output: md1.code, md2.code, mnrd.nosol_registr_id
5. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..159.77 rows=21 width=13) (never executed)

  • Output: mnrd.nosol_registr_id, mnrd.final_diagnosis_id, md1.code
6. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.md_nosol_registr_diagnosis mnrd (cost=0.00..1.21 rows=21 width=12) (never executed)

  • Output: mnrd.id, mnrd.nosol_registr_id, mnrd.init_diagnosis_id, mnrd.final_diagnosis_id, mnrd.aud_who, mnrd.aud_when, mnrd.aud_source, mnrd.aud_who_create, mnrd.aud_when_create, mnrd.aud_source_create
7. 0.000 0.000 ↓ 0.0 0

Index Scan using md_diagnosis_pk on public.md_diagnosis md1 (cost=0.29..7.54 rows=1 width=9) (never executed)

  • Output: md1.id, md1.code, md1.name, md1.parent_id, md1.is_injury, md1.level, md1.is_leaf, md1.e_code, md1.oms, md1.stomat, md1.aud_who, md1.aud_when, md1.aud_source, md1.aud_who_create, md1.aud_when_create, md1.aud_source_create, md1.from_dt, md1.to_dt
  • Index Cond: (md1.id = mnrd.init_diagnosis_id)
8. 0.000 0.000 ↓ 0.0 0

Index Scan using md_diagnosis_pk on public.md_diagnosis md2 (cost=0.29..7.54 rows=1 width=9) (never executed)

  • Output: md2.id, md2.code, md2.name, md2.parent_id, md2.is_injury, md2.level, md2.is_leaf, md2.e_code, md2.oms, md2.stomat, md2.aud_who, md2.aud_when, md2.aud_source, md2.aud_who_create, md2.aud_when_create, md2.aud_source_create, md2.from_dt, md2.to_dt
  • Index Cond: (md2.id = mnrd.final_diagnosis_id)
9.          

CTE tt

10. 0.032 0.413 ↓ 1.2 25 1

HashAggregate (cost=318.49..318.70 rows=21 width=14) (actual time=0.404..0.413 rows=25 loops=1)

  • Output: md1_1.code, md2_1.code, mnrd_1.register_id
  • Group Key: md1_1.code, md2_1.code, mnrd_1.register_id
  • Buffers: shared hit=157
11. 0.016 0.381 ↓ 1.2 26 1

Nested Loop (cost=0.57..318.33 rows=21 width=14) (actual time=0.026..0.381 rows=26 loops=1)

  • Output: md1_1.code, md2_1.code, mnrd_1.register_id
  • Buffers: shared hit=157
12. 0.033 0.209 ↓ 1.2 26 1

Nested Loop (cost=0.29..159.77 rows=21 width=13) (actual time=0.019..0.209 rows=26 loops=1)

  • Output: mnrd_1.register_id, mnrd_1.diagnosis_end_id, md1_1.code
  • Buffers: shared hit=79
13. 0.020 0.020 ↓ 1.2 26 1

Seq Scan on d_accounting.register_diagnosis mnrd_1 (cost=0.00..1.21 rows=21 width=12) (actual time=0.005..0.020 rows=26 loops=1)

  • Output: mnrd_1.register_id, mnrd_1.diagnosis_begin_id, mnrd_1.diagnosis_end_id
  • Buffers: shared hit=1
14. 0.156 0.156 ↑ 1.0 1 26

Index Scan using md_diagnosis_pk on public.md_diagnosis md1_1 (cost=0.29..7.54 rows=1 width=9) (actual time=0.006..0.006 rows=1 loops=26)

  • Output: md1_1.id, md1_1.code, md1_1.name, md1_1.parent_id, md1_1.is_injury, md1_1.level, md1_1.is_leaf, md1_1.e_code, md1_1.oms, md1_1.stomat, md1_1.aud_who, md1_1.aud_when, md1_1.aud_source, md1_1.aud_who_create, md1_1.aud_when_create, md1_1.aud_source_create, md1_1.from_dt, md1_1.to_dt
  • Index Cond: (md1_1.id = mnrd_1.diagnosis_begin_id)
  • Buffers: shared hit=78
15. 0.156 0.156 ↑ 1.0 1 26

Index Scan using md_diagnosis_pk on public.md_diagnosis md2_1 (cost=0.29..7.54 rows=1 width=9) (actual time=0.005..0.006 rows=1 loops=26)

  • Output: md2_1.id, md2_1.code, md2_1.name, md2_1.parent_id, md2_1.is_injury, md2_1.level, md2_1.is_leaf, md2_1.e_code, md2_1.oms, md2_1.stomat, md2_1.aud_who, md2_1.aud_when, md2_1.aud_source, md2_1.aud_who_create, md2_1.aud_when_create, md2_1.aud_source_create, md2_1.from_dt, md2_1.to_dt
  • Index Cond: (md2_1.id = mnrd_1.diagnosis_end_id)
  • Buffers: shared hit=78
16.          

CTE set

17. 0.003 0.158 ↑ 1.0 1 1

Limit (cost=24.02..24.02 rows=1 width=112) (actual time=0.157..0.158 rows=1 loops=1)

  • Output: ((COALESCE(s_value.value, setting.default_value))::boolean), s_value.scope_id
  • Buffers: shared hit=8
18. 0.014 0.155 ↑ 3.0 1 1

Sort (cost=24.02..24.02 rows=3 width=112) (actual time=0.155..0.155 rows=1 loops=1)

  • Output: ((COALESCE(s_value.value, setting.default_value))::boolean), s_value.scope_id
  • Sort Key: s_value.scope_id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=8
19. 0.017 0.141 ↑ 1.5 2 1

Nested Loop Left Join (cost=4.71..24.00 rows=3 width=112) (actual time=0.133..0.141 rows=2 loops=1)

  • Output: (COALESCE(s_value.value, setting.default_value))::boolean, s_value.scope_id
  • Join Filter: ((setting.id)::text = (s_value.setting_id)::text)
  • Buffers: shared hit=8
20. 0.042 0.042 ↑ 1.0 1 1

Index Scan using cmn_setting_pkey on public.cmn_setting setting (cost=0.28..8.29 rows=1 width=121) (actual time=0.040..0.042 rows=1 loops=1)

  • Output: setting.code, setting.default_value, setting.description, setting.is_mondatory, setting.name, setting.view_order, setting.domain_id, setting.module_id, setting.scopes_version, setting.default_value_version, setting.name_version, setting.id, setting.group_id, setting.aud_who, setting.aud_when, setting.aud_source, setting.aud_who_create, setting.aud_when_create, setting.aud_source_create
  • Index Cond: ((setting.id)::text = 'cz.atria.lsd.md.components.jsp.DispensarySettings.lsdAdnN2oDispensaryWithoutIntersection'::text)
  • Buffers: shared hit=3
21. 0.011 0.082 ↑ 1.5 2 1

Bitmap Heap Scan on public.cmn_setting_value s_value (cost=4.43..15.65 rows=3 width=174) (actual time=0.079..0.082 rows=2 loops=1)

  • Output: s_value.scope_id, s_value.setting_id, s_value.value, s_value.aud_who, s_value.aud_when, s_value.aud_source, s_value.aud_who_create, s_value.aud_when_create, s_value.aud_source_create
  • Recheck Cond: ((s_value.setting_id)::text = 'cz.atria.lsd.md.components.jsp.DispensarySettings.lsdAdnN2oDispensaryWithoutIntersection'::text)
  • Heap Blocks: exact=2
  • Buffers: shared hit=5
22. 0.071 0.071 ↑ 1.5 2 1

Bitmap Index Scan on cmn_setting_value_setting_id_idx (cost=0.00..4.43 rows=3 width=0) (actual time=0.071..0.071 rows=2 loops=1)

  • Index Cond: ((s_value.setting_id)::text = 'cz.atria.lsd.md.components.jsp.DispensarySettings.lsdAdnN2oDispensaryWithoutIntersection'::text)
  • Buffers: shared hit=3
23.          

Initplan (for Limit)

24. 0.161 0.161 ↑ 1.0 1 1

CTE Scan on set (cost=0.00..0.02 rows=1 width=1) (actual time=0.160..0.161 rows=1 loops=1)

  • Output: set.st
  • Buffers: shared hit=8
25. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on set set_1 (cost=0.00..0.02 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

  • Output: set_1.st
26. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on set set_2 (cost=0.00..0.02 rows=1 width=1) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: set_2.st
27. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on set set_3 (cost=0.00..0.02 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=1)

  • Output: set_3.st
28. 10.896 1,015.839 ↑ 421.9 30 1

Sort (cost=2,705,807.24..2,705,838.88 rows=12,656 width=127) (actual time=1,015.829..1,015.839 rows=30 loops=1)

  • Output: md.id, md.code, ((((md.code)::text || ' '::text) || (md.name)::text)), ($7), (CASE WHEN $8 THEN (SubPlan 6) ELSE (SubPlan 7) END), (CASE WHEN $11 THEN (SubPlan 9) ELSE (SubPlan 10) END), (CASE WHEN $14 THEN (SubPlan 13) ELSE (SubPlan 15) END), (1805409), md.is_injury, (198248), ((SubPlan 17))
  • Sort Key: md.code
  • Sort Method: top-N heapsort Memory: 36kB
  • Buffers: shared hit=772577
29. 43.087 1,004.943 ↑ 1.0 12,656 1

Seq Scan on public.md_diagnosis md (cost=0.00..2,705,433.46 rows=12,656 width=127) (actual time=0.917..1,004.943 rows=12,656 loops=1)

  • Output: md.id, md.code, (((md.code)::text || ' '::text) || (md.name)::text), $7, CASE WHEN $8 THEN (SubPlan 6) ELSE (SubPlan 7) END, CASE WHEN $11 THEN (SubPlan 9) ELSE (SubPlan 10) END, CASE WHEN $14 THEN (SubPlan 13) ELSE (SubPlan 15) END, 1805409, md.is_injury, 198248, (SubPlan 17)
  • Filter: md.is_leaf
  • Rows Removed by Filter: 2271
  • Buffers: shared hit=772577
30.          

SubPlan (for Seq Scan)

31. 63.280 63.280 ↑ 2.0 1 12,656

CTE Scan on tt (cost=0.00..0.53 rows=2 width=0) (actual time=0.005..0.005 rows=1 loops=12,656)

  • Filter: (((md.code)::text >= (tt.bcode)::text) AND ((md.code)::text <= (tt.ecode)::text))
  • Rows Removed by Filter: 11
  • Buffers: shared hit=157
32. 0.000 0.000 ↓ 0.0 0

CTE Scan on t (cost=0.00..0.53 rows=2 width=0) (never executed)

  • Filter: (((md.code)::text >= (t.bcode)::text) AND ((md.code)::text <= (t.ecode)::text))
33. 0.000 63.280 ↑ 1.0 1 12,656

Limit (cost=0.00..0.26 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=12,656)

  • Output: tt_1.reg_id
34. 63.280 63.280 ↑ 2.0 1 12,656

CTE Scan on tt tt_1 (cost=0.00..0.53 rows=2 width=4) (actual time=0.005..0.005 rows=1 loops=12,656)

  • Output: tt_1.reg_id
  • Filter: (((md.code)::text >= (tt_1.bcode)::text) AND ((md.code)::text <= (tt_1.ecode)::text))
  • Rows Removed by Filter: 11
35. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.26 rows=1 width=4) (never executed)

  • Output: t_1.reg_id
36. 0.000 0.000 ↓ 0.0 0

CTE Scan on t t_1 (cost=0.00..0.53 rows=2 width=4) (never executed)

  • Output: t_1.reg_id
  • Filter: (((md.code)::text >= (t_1.bcode)::text) AND ((md.code)::text <= (t_1.ecode)::text))
37. 0.000 113.904 ↑ 1.0 1 12,656

Limit (cost=0.26..1.52 rows=1 width=107) (actual time=0.009..0.009 rows=1 loops=12,656)

  • Output: r.name
  • Buffers: shared hit=12656
38.          

Initplan (for Limit)

39. 0.000 63.280 ↑ 1.0 1 12,656

Limit (cost=0.00..0.26 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=12,656)

  • Output: tt_2.reg_id
40. 63.280 63.280 ↑ 2.0 1 12,656

CTE Scan on tt tt_2 (cost=0.00..0.53 rows=2 width=4) (actual time=0.005..0.005 rows=1 loops=12,656)

  • Output: tt_2.reg_id
  • Filter: (((md.code)::text >= (tt_2.bcode)::text) AND ((md.code)::text <= (tt_2.ecode)::text))
  • Rows Removed by Filter: 11
41. 101.248 101.248 ↑ 1.0 1 12,656

Seq Scan on d_accounting.register r (cost=0.00..1.26 rows=1 width=107) (actual time=0.008..0.008 rows=1 loops=12,656)

  • Output: r.name
  • Filter: (r.id = $16)
  • Rows Removed by Filter: 10
  • Buffers: shared hit=12656
42. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.26..1.52 rows=1 width=107) (never executed)

  • Output: mdnr.name
43.          

Initplan (for Limit)

44. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.26 rows=1 width=4) (never executed)

  • Output: t_2.reg_id
45. 0.000 0.000 ↓ 0.0 0

CTE Scan on t t_2 (cost=0.00..0.53 rows=2 width=4) (never executed)

  • Output: t_2.reg_id
  • Filter: (((md.code)::text >= (t_2.bcode)::text) AND ((md.code)::text <= (t_2.ecode)::text))
46. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.md_nosol_registr mdnr (cost=0.00..1.26 rows=1 width=107) (never executed)

  • Output: mdnr.name
  • Filter: (mdnr.id = $18)
47. 37.968 721.392 ↑ 1.0 1 12,656

Seq Scan on public.mc_repeat_count mrc (cost=208.59..209.62 rows=1 width=21) (actual time=0.057..0.057 rows=1 loops=12,656)

  • Output: row_to_json(ROW(mrc.id, mrc.name))
  • Filter: (mrc.id = CASE WHEN ($21 > 0) THEN 2 ELSE 1 END)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=759360
48.          

Initplan (for Seq Scan)

49. 12.656 683.424 ↑ 1.0 1 12,656

Aggregate (cost=208.58..208.59 rows=1 width=4) (actual time=0.054..0.054 rows=1 loops=12,656)

  • Output: count(mccount.id)
  • Buffers: shared hit=746704
50. 0.000 670.768 ↓ 0.0 0 12,656

Nested Loop (cost=1.00..208.57 rows=1 width=4) (actual time=0.053..0.053 rows=0 loops=12,656)

  • Output: mccount.id
  • Buffers: shared hit=746704
51. 670.768 670.768 ↓ 0.0 0 12,656

Index Scan using idx_mc_case on public.mc_case mccount (cost=0.44..199.98 rows=1 width=8) (actual time=0.053..0.053 rows=0 loops=12,656)

  • Output: mccount.id, mccount.main_diagnos_id
  • Index Cond: (mccount.patient_id = 198248)
  • Filter: (date_part('year'::text, now()) = date_part('year'::text, (mccount.create_date)::timestamp without time zone))
  • Rows Removed by Filter: 56
  • Buffers: shared hit=746704
52. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_diagnosis_pk on public.mc_diagnosis mdcount (cost=0.56..8.58 rows=1 width=4) (never executed)

  • Output: mdcount.id
  • Index Cond: (mdcount.id = mccount.main_diagnos_id)
  • Filter: (mdcount.diagnos_id = md.id)
Planning time : 4.492 ms
Execution time : 1,016.579 ms