explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A3ru

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 240.081 ↑ 1.0 1 1

Result (cost=9,727.74..9,727.75 rows=1 width=0) (actual time=240.080..240.081 rows=1 loops=1)

  • Output: CASE WHEN (NOT $6) THEN 725437 ELSE $9 END
  • Buffers: shared hit=161270
2.          

CTE services_pairs

3. 0.126 0.126 ↑ 1.0 1 1

Index Scan using sr_service_pk on public.sr_service (cost=0.42..8.58 rows=1 width=122) (actual time=0.124..0.126 rows=1 loops=1)

  • Output: CASE WHEN ("position"((sr_service.name)::text, 'овторн'::text) <> 0) THEN sr_service.code WHEN ((sr_service.code)::text ~ '^(?:[a-zA-Zа-яА-Я]?[0-9]+[.]{1}[0-9]+[.]{1}[0-9]+)$'::text) THEN (concat(split_part((sr_service.code)::text, '.'::text, 1), '.', split_part((sr_service.code)::text, '.'::text, 2), '.', lpad(((((split_part((sr_service.code)::text, '.'::text, 3))::bigint + 1))::character varying)::text, length(split_part((sr_service.code)::text, '.'::text, 3)), '0'::text)))::character varying WHEN (((sr_service.code)::text ~ '^(?:[a-zA-Zа-яА-Я]?[0-9]+)$'::text) AND (length((concat(regexp_replace((sr_service.code)::text, '[^a-zA-Zа-яА-Я]'::text, ''::text, 'g'::text), ((regexp_replace((sr_service.code)::text, '[^0-9]'::text, ''::text, 'g'::text))::bigint + 1)))::text) = length((sr_service.code)::text))) THEN (concat(regexp_replace((sr_service.code)::text, '[^a-zA-Zа-яА-Я]'::text, ''::text, 'g'::text), ((regexp_replace((sr_service.code)::text, '[^0-9]'::text, ''::text, 'g'::text))::bigint + 1)))::character varying WHEN (((sr_service.code)::text ~ '^(?:[a-zA-Zа-яА-Я]?[0-9]+)$'::text) AND (length((concat(regexp_replace((sr_service.code)::text, '[^a-zA-Zа-яА-Я]'::text, ''::text, 'g'::text), ((regexp_replace((sr_service.code)::text, '[^0-9]'::text, ''::text, 'g'::text))::bigint + 1)))::text) <> length((sr_service.code)::text))) THEN (concat(regexp_replace((sr_service.code)::text, '[^a-zA-Zа-яА-Я]'::text, ''::text, 'g'::text), lpad(((((regexp_replace((sr_service.code)::text, '[^0-9]'::text, ''::text, 'g'::text))::bigint + 1))::character varying)::text, (length((sr_service.code)::text) - length(regexp_replace((sr_service.code)::text, '[^a-zA-Zа-яА-Я]'::text, ''::text, 'g'::text))), '0'::text)))::character varying ELSE NULL::character varying END, CASE WHEN ("position"((sr_service.name)::text, 'ервичн'::text) <> 0) THEN regexp_replace((sr_service.name)::text, 'ервичн'::text, 'овторн'::text) ELSE NULL::text END
  • Index Cond: (sr_service.id = 725437)
  • Buffers: shared hit=4
4.          

CTE services

5. 0.074 0.832 ↑ 270.9 16 1

Sort (cost=9,578.63..9,589.47 rows=4,335 width=126) (actual time=0.819..0.832 rows=16 loops=1)

  • Output: s.id, s.code, s.name
  • Sort Key: s.id DESC
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=58
6. 0.037 0.758 ↑ 76.1 57 1

Nested Loop (cost=231.74..9,316.76 rows=4,335 width=126) (actual time=0.177..0.758 rows=57 loops=1)

  • Output: s.id, s.code, s.name
  • Buffers: shared hit=58
7. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on services_pairs sp (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.005 rows=1 loops=1)

  • Output: sp.second_service_code, sp.second_service_name
8. 0.588 0.716 ↑ 76.1 57 1

Bitmap Heap Scan on public.sr_service s (cost=231.74..9,273.39 rows=4,335 width=126) (actual time=0.169..0.716 rows=57 loops=1)

  • Output: s.id, s.code, s.cul, s.is_death, s.duration, s.is_fictitious, s.is_independent, s.is_multuplicity, s.name, s.terms, s.org_id, s.category_id, s.duration_unit_id, s.prototype_id, s.type_id, s.from_dt, s.to_dt, s.is_repeated, s.is_inherit_protocol, s.is_expendable_materials, s.is_actual_cul, s.is_paraclinical, s.is_complex, s.accounting_id, s.is_fact, s.is_autocopy_diagnoses, s.is_stomat, s.srg_dif_type_id, s.srg_opr_kind_id, s.is_need_anatomic_zone, s.aud_who, s.aud_when, s.aud_source, s.aud_who_create, s.aud_when_create, s.aud_source_create, s.is_filling, s.is_extraction, s.is_need_close_date, s.is_vmp, s.short_name
  • Recheck Cond: ((btrim((s.code)::text) = btrim((sp.second_service_code)::text)) OR (btrim((s.name)::text) = btrim(sp.second_service_name)))
  • Heap Blocks: exact=55
  • Buffers: shared hit=58
9. 0.016 0.128 ↓ 0.0 0 1

BitmapOr (cost=231.74..231.74 rows=4,346 width=0) (actual time=0.128..0.128 rows=0 loops=1)

  • Buffers: shared hit=3
10. 0.112 0.112 ↑ 38.1 57 1

Bitmap Index Scan on sr_service_btrim_code (cost=0.00..52.72 rows=2,173 width=0) (actual time=0.112..0.112 rows=57 loops=1)

  • Index Cond: (btrim((s.code)::text) = btrim((sp.second_service_code)::text))
  • Buffers: shared hit=3
11. 0.000 0.000 ↓ 0.0 0 1

Bitmap Index Scan on sr_service_btrim_name (cost=0.00..176.85 rows=2,173 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (btrim((s.name)::text) = btrim(sp.second_service_name))
12.          

Initplan (for Result)

13. 4.796 155.671 ↑ 2,817.0 1 1

Nested Loop Semi Join (cost=1.00..179,860.24 rows=2,817 width=0) (actual time=155.671..155.671 rows=1 loops=1)

  • Buffers: shared hit=80666
14. 7.286 82.571 ↑ 2.6 17,076 1

Nested Loop (cost=0.43..56,258.05 rows=44,747 width=4) (actual time=1.038..82.571 rows=17,076 loops=1)

  • Output: sr.group_id
  • Buffers: shared hit=12345
15. 0.075 1.077 ↑ 2.7 16 1

Nested Loop (cost=0.00..195.09 rows=43 width=4) (actual time=0.986..1.077 rows=16 loops=1)

  • Output: s_1.id
  • Join Filter: ((btrim((s_1.code)::text) = btrim((sp_1.second_service_code)::text)) OR (btrim((s_1.name)::text) = btrim(sp_1.second_service_name)))
  • Buffers: shared hit=62
16. 0.149 0.149 ↑ 1.0 1 1

CTE Scan on services_pairs sp_1 (cost=0.00..0.02 rows=1 width=64) (actual time=0.149..0.149 rows=1 loops=1)

  • Output: sp_1.second_service_code, sp_1.second_service_name
  • Buffers: shared hit=4
17. 0.853 0.853 ↑ 270.9 16 1

CTE Scan on services s_1 (cost=0.00..86.70 rows=4,335 width=1,036) (actual time=0.820..0.853 rows=16 loops=1)

  • Output: s_1.id, s_1.code, s_1.name
  • Buffers: shared hit=58
18. 74.208 74.208 ↓ 1.0 1,067 16

Index Scan using sr_res_group_service_srv_type_id_idx on public.sr_res_group_service sr (cost=0.43..1,293.44 rows=1,035 width=8) (actual time=0.029..4.638 rows=1,067 loops=16)

  • Output: sr.id, sr.bdatetime, sr.edatetime, sr.power, sr.group_id, sr.srv_type_id, sr.moderation_required, sr.patient_multiple_appointment_per_day, sr.referral_required, sr.default_service, sr.aud_who, sr.aud_when, sr.aud_source, sr.aud_who_create, sr.aud_when_create, sr.aud_source_create
  • Index Cond: (sr.srv_type_id = s_1.id)
  • Buffers: shared hit=12283
19. 68.304 68.304 ↓ 0.0 0 17,076

Index Only Scan using sr_res_group_responsible_id_id_ix on public.sr_res_group rg (cost=0.57..2.76 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=17,076)

  • Output: rg.responsible_id, rg.id
  • Index Cond: ((rg.responsible_id = 58710) AND (rg.id = sr.group_id))
  • Heap Fetches: 0
  • Buffers: shared hit=68321
20. 0.004 84.398 ↑ 1.0 1 1

Limit (cost=1.00..64.85 rows=1 width=4) (actual time=84.397..84.398 rows=1 loops=1)

  • Output: s_2.id
  • Buffers: shared hit=80604
21. 7.117 84.394 ↑ 2,817.0 1 1

Nested Loop Semi Join (cost=1.00..179,860.24 rows=2,817 width=4) (actual time=84.394..84.394 rows=1 loops=1)

  • Output: s_2.id
  • Buffers: shared hit=80604
22. 4.742 26.049 ↑ 2.6 17,076 1

Nested Loop (cost=0.43..56,258.05 rows=44,747 width=8) (actual time=0.041..26.049 rows=17,076 loops=1)

  • Output: s_2.id, sr_1.group_id
  • Buffers: shared hit=12283
23. 0.061 0.075 ↑ 2.7 16 1

Nested Loop (cost=0.00..195.09 rows=43 width=4) (actual time=0.026..0.075 rows=16 loops=1)

  • Output: s_2.id
  • Join Filter: ((btrim((s_2.code)::text) = btrim((sp_2.second_service_code)::text)) OR (btrim((s_2.name)::text) = btrim(sp_2.second_service_name)))
24. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on services_pairs sp_2 (cost=0.00..0.02 rows=1 width=64) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: sp_2.second_service_code, sp_2.second_service_name
25. 0.012 0.012 ↑ 270.9 16 1

CTE Scan on services s_2 (cost=0.00..86.70 rows=4,335 width=1,036) (actual time=0.001..0.012 rows=16 loops=1)

  • Output: s_2.id, s_2.code, s_2.name
26. 21.232 21.232 ↓ 1.0 1,067 16

Index Scan using sr_res_group_service_srv_type_id_idx on public.sr_res_group_service sr_1 (cost=0.43..1,293.44 rows=1,035 width=8) (actual time=0.010..1.327 rows=1,067 loops=16)

  • Output: sr_1.id, sr_1.bdatetime, sr_1.edatetime, sr_1.power, sr_1.group_id, sr_1.srv_type_id, sr_1.moderation_required, sr_1.patient_multiple_appointment_per_day, sr_1.referral_required, sr_1.default_service, sr_1.aud_who, sr_1.aud_when, sr_1.aud_source, sr_1.aud_who_create, sr_1.aud_when_create, sr_1.aud_source_create
  • Index Cond: (sr_1.srv_type_id = s_2.id)
  • Buffers: shared hit=12283
27. 51.228 51.228 ↓ 0.0 0 17,076

Index Only Scan using sr_res_group_responsible_id_id_ix on public.sr_res_group rg_1 (cost=0.57..2.76 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=17,076)

  • Output: rg_1.responsible_id, rg_1.id
  • Index Cond: ((rg_1.responsible_id = 58710) AND (rg_1.id = sr_1.group_id))
  • Heap Fetches: 0
  • Buffers: shared hit=68321
Planning time : 5.176 ms
Execution time : 240.465 ms