explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JILt

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 404.080 ↑ 1.0 1 1

Result (cost=21,701.43..21,701.44 rows=1 width=0) (actual time=404.079..404.080 rows=1 loops=1)

  • Output: (NOT $2)
  • Buffers: shared hit=21783
2.          

CTE services_pairs

3. 0.077 0.077 ↑ 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.077..0.077 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 = 919534)
  • Buffers: shared hit=4
4.          

Initplan (for Result)

5. 0.020 404.077 ↑ 4.0 1 1

Nested Loop (cost=4,934.54..71,967.76 rows=4 width=0) (actual time=404.077..404.077 rows=1 loops=1)

  • Join Filter: ((btrim((s.code)::text) = btrim((sp.second_service_code)::text)) OR (btrim((s.name)::text) = btrim(sp.second_service_name)))
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=21783
6. 0.081 0.081 ↑ 1.0 1 1

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

  • Output: sp.second_service_code, sp.second_service_name
  • Buffers: shared hit=4
7. 0.007 403.976 ↑ 224.5 2 1

Nested Loop (cost=4,934.54..71,956.52 rows=449 width=122) (actual time=403.971..403.976 rows=2 loops=1)

  • Output: s.code, s.name
  • Buffers: shared hit=21779
8. 252.774 403.951 ↑ 224.5 2 1

Hash Join (cost=4,934.12..70,249.45 rows=449 width=4) (actual time=403.950..403.951 rows=2 loops=1)

  • Output: sr.srv_type_id
  • Hash Cond: (sr.group_id = rg.id)
  • Buffers: shared hit=21771
9. 141.636 141.636 ↑ 1.8 1,283,788 1

Seq Scan on public.sr_res_group_service sr (cost=0.00..56,533.43 rows=2,340,643 width=8) (actual time=0.004..141.636 rows=1,283,788 loops=1)

  • 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
  • Buffers: shared hit=17925
10. 1.676 9.541 ↑ 2.6 5,149 1

Hash (cost=4,769.56..4,769.56 rows=13,165 width=4) (actual time=9.541..9.541 rows=5,149 loops=1)

  • Output: rg.id
  • Buckets: 16384 Batches: 1 Memory Usage: 310kB
  • Buffers: shared hit=3846
11. 7.865 7.865 ↑ 2.6 5,149 1

Index Only Scan using sr_res_group_responsible_id_id_ix on public.sr_res_group rg (cost=0.57..4,769.56 rows=13,165 width=4) (actual time=0.037..7.865 rows=5,149 loops=1)

  • Output: rg.id
  • Index Cond: (rg.responsible_id = 78020)
  • Heap Fetches: 3766
  • Buffers: shared hit=3846
12. 0.018 0.018 ↑ 1.0 1 2

Index Scan using sr_service_pk on public.sr_service s (cost=0.42..3.79 rows=1 width=126) (actual time=0.009..0.009 rows=1 loops=2)

  • 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
  • Index Cond: (s.id = sr.srv_type_id)
  • Buffers: shared hit=8
Planning time : 1.786 ms
Execution time : 404.297 ms