explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e20M

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

Limit (cost=661.79..7,077.09 rows=30 width=127) (actual time=1.109..5.253 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=2008 read=2
  • I/O Timings: read=0.032
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.031 0.413 ↑ 1.1 19 1

HashAggregate (cost=318.49..318.70 rows=21 width=14) (actual time=0.409..0.413 rows=19 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.012 0.382 ↓ 1.2 26 1

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

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

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

  • Output: mnrd_1.register_id, mnrd_1.diagnosis_end_id, md1_1.code
  • Buffers: shared hit=79
13. 0.017 0.017 ↓ 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.007..0.017 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.182 0.182 ↑ 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.007 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.146 ↑ 1.0 1 1

Limit (cost=24.02..24.02 rows=1 width=112) (actual time=0.145..0.146 rows=1 loops=1)

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

Sort (cost=24.02..24.02 rows=3 width=112) (actual time=0.143..0.143 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.016 0.124 ↑ 1.5 2 1

Nested Loop Left Join (cost=4.71..24.00 rows=3 width=112) (actual time=0.116..0.124 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.040 0.040 ↑ 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.039..0.040 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.019 0.068 ↑ 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.064..0.068 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.049 0.049 ↑ 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.049..0.049 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.149 0.149 ↑ 1.0 1 1

CTE Scan on set (cost=0.00..0.02 rows=1 width=1) (actual time=0.148..0.149 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.001 0.001 ↑ 1.0 1 1

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

  • Output: set_3.st
28. 0.440 5.240 ↑ 421.9 30 1

Index Scan using md_diagnosis_code_is_leaf_idx on public.md_diagnosis md (cost=0.29..2,706,402.33 rows=12,656 width=127) (actual time=1.107..5.240 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=2008 read=2
  • I/O Timings: read=0.032
29.          

SubPlan (for Index Scan)

30. 0.750 0.750 ↑ 2.0 1 30

CTE Scan on tt (cost=0.00..0.53 rows=2 width=0) (actual time=0.025..0.025 rows=1 loops=30)

  • Filter: (((md.code)::text >= (tt.bcode)::text) AND ((md.code)::text <= (tt.ecode)::text))
  • Rows Removed by Filter: 18
  • Buffers: shared hit=157
31. 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))
32. 0.030 0.330 ↑ 1.0 1 30

Limit (cost=0.00..0.26 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=30)

  • Output: tt_1.reg_id
33. 0.300 0.300 ↑ 2.0 1 30

CTE Scan on tt tt_1 (cost=0.00..0.53 rows=2 width=4) (actual time=0.010..0.010 rows=1 loops=30)

  • 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: 18
34. 0.000 0.000 ↓ 0.0 0

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

  • Output: t_1.reg_id
35. 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))
36. 0.000 0.480 ↑ 1.0 1 30

Limit (cost=0.26..1.52 rows=1 width=107) (actual time=0.016..0.016 rows=1 loops=30)

  • Output: r.name
  • Buffers: shared hit=30
37.          

Initplan (for Limit)

38. 0.000 0.300 ↑ 1.0 1 30

Limit (cost=0.00..0.26 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=30)

  • Output: tt_2.reg_id
39. 0.300 0.300 ↑ 2.0 1 30

CTE Scan on tt tt_2 (cost=0.00..0.53 rows=2 width=4) (actual time=0.010..0.010 rows=1 loops=30)

  • 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: 18
40. 0.420 0.420 ↑ 1.0 1 30

Seq Scan on d_accounting.register r (cost=0.00..1.26 rows=1 width=107) (actual time=0.014..0.014 rows=1 loops=30)

  • Output: r.name
  • Filter: (r.id = $16)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=30
41. 0.000 0.000 ↓ 0.0 0

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

  • Output: mdnr.name
42.          

Initplan (for Limit)

43. 0.000 0.000 ↓ 0.0 0

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

  • Output: t_2.reg_id
44. 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))
45. 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)
46. 0.210 3.240 ↑ 1.0 1 30

Seq Scan on public.mc_repeat_count mrc (cost=208.59..209.62 rows=1 width=21) (actual time=0.108..0.108 rows=1 loops=30)

  • 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=1800
47.          

Initplan (for Seq Scan)

48. 0.030 3.030 ↑ 1.0 1 30

Aggregate (cost=208.58..208.59 rows=1 width=4) (actual time=0.101..0.101 rows=1 loops=30)

  • Output: count(mccount.id)
  • Buffers: shared hit=1770
49. 0.000 3.000 ↓ 0.0 0 30

Nested Loop (cost=1.00..208.57 rows=1 width=4) (actual time=0.100..0.100 rows=0 loops=30)

  • Output: mccount.id
  • Buffers: shared hit=1770
50. 3.000 3.000 ↓ 0.0 0 30

Index Scan using idx_mc_case on public.mc_case mccount (cost=0.44..199.98 rows=1 width=8) (actual time=0.100..0.100 rows=0 loops=30)

  • 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=1770
51. 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.765 ms
Execution time : 5.968 ms