explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lyQk

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 441.404 ↑ 1.0 1 1

Result (cost=21,700.94..21,700.95 rows=1 width=0) (actual time=441.403..441.404 rows=1 loops=1)

  • Output: $2
  • Buffers: shared hit=21,783
2.          

CTE services_pairs

3. 0.089 0.089 ↑ 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.089..0.089 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 = 919,534)
  • Buffers: shared hit=4
4.          

Initplan (for Result)

5. 0.039 441.400 ↑ 4.0 1 1

Nested Loop (cost=4,934.54..71,965.79 rows=4 width=0) (actual time=441.400..441.400 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=21,783
6. 0.093 0.093 ↑ 1.0 1 1

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

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

Nested Loop (cost=4,934.54..71,954.54 rows=449 width=122) (actual time=441.263..441.268 rows=2 loops=1)

  • Output: s.code, s.name
  • Buffers: shared hit=21,779
8. 274.713 441.243 ↑ 224.5 2 1

Hash Join (cost=4,934.12..70,247.48 rows=449 width=4) (actual time=441.242..441.243 rows=2 loops=1)

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

Seq Scan on public.sr_res_group_service sr (cost=0.00..56,531.72 rows=2,340,572 width=8) (actual time=0.005..156.454 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=17,925
10. 1.657 10.076 ↑ 2.6 5,149 1

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

  • Output: rg.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 310kB
  • Buffers: shared hit=3,846
11. 8.419 8.419 ↑ 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.034..8.419 rows=5,149 loops=1)

  • Output: rg.id
  • Index Cond: (rg.responsible_id = 78,020)
  • Heap Fetches: 3,766
  • Buffers: shared hit=3,846
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 : 2.027 ms
Execution time : 441.627 ms