explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WuFc

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

Limit (cost=2,706,468.75..2,706,468.82 rows=30 width=127) (actual time=1,060.983..1,061.000 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.037 0.407 ↓ 1.2 25 1

HashAggregate (cost=318.49..318.70 rows=21 width=14) (actual time=0.399..0.407 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.023 0.370 ↓ 1.2 26 1

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

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

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

  • Output: mnrd_1.register_id, mnrd_1.diagnosis_end_id, md1_1.code
  • Buffers: shared hit=79
13. 0.029 0.029 ↓ 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.006..0.029 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.130 0.130 ↑ 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.005 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.022 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.019 0.121 ↑ 1.5 2 1

Nested Loop Left Join (cost=4.71..24.00 rows=3 width=112) (actual time=0.113..0.121 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.044 0.044 ↑ 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.043..0.044 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.010 0.058 ↑ 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.055..0.058 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.048 0.048 ↑ 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.048..0.048 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.002 0.002 ↑ 1.0 1 1

CTE Scan on set set_1 (cost=0.00..0.02 rows=1 width=1) (actual time=0.001..0.002 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. 10.800 1,060.997 ↑ 421.9 30 1

Sort (cost=2,705,807.24..2,705,838.88 rows=12,656 width=127) (actual time=1,060.981..1,060.997 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. 50.373 1,050.197 ↑ 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.903..1,050.197 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. 50.624 759.360 ↑ 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.059..0.060 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 708.736 ↑ 1.0 1 12,656

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

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

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

  • Output: mccount.id
  • Buffers: shared hit=746704
51. 696.080 696.080 ↓ 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.055..0.055 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.305 ms
Execution time : 1,061.740 ms