explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AUVl

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 1,328.479 ↑ 1.0 1 1

Result (cost=157,372.17..157,372.18 rows=1 width=0) (actual time=1,328.479..1,328.479 rows=1 loops=1)

  • Output: CASE WHEN (NOT $2) THEN 502677 ELSE $5 END
  • Buffers: shared hit=124,947
2.          

CTE services_pairs

3. 0.025 0.025 ↑ 1.0 1 1

Index Scan using sr_service_pk on public.sr_service (cost=0.43..8.59 rows=1 width=130) (actual time=0.021..0.025 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 = 502,677)
  • Buffers: shared hit=4
4.          

Initplan (for Result)

5. 0.164 1,322.692 ↑ 1,371.0 1 1

Hash Join (cost=146,782.09..292,717.08 rows=1,371 width=0) (actual time=1,322.692..1,322.692 rows=1 loops=1)

  • Hash Cond: (sr.srv_type_id = s.id)
  • Buffers: shared hit=108,539
6. 2.341 14.468 ↑ 342.8 4 1

Nested Loop (cost=1.00..145,917.14 rows=1,371 width=4) (actual time=14.462..14.468 rows=4 loops=1)

  • Output: sr.srv_type_id
  • Buffers: shared hit=16,392
7. 1.241 1.241 ↑ 3.3 5,443 1

Index Only Scan using sr_res_group_responsible_id_id_ix on public.sr_res_group rg (cost=0.57..930.46 rows=17,969 width=4) (actual time=0.034..1.241 rows=5,443 loops=1)

  • Output: rg.responsible_id, rg.id
  • Index Cond: (rg.responsible_id = 11,951)
  • Heap Fetches: 1
  • Buffers: shared hit=27
8. 10.886 10.886 ↓ 0.0 0 5,443

Index Scan using sr_res_group_service_group_id_idx on public.sr_res_group_service sr (cost=0.43..7.88 rows=19 width=8) (actual time=0.002..0.002 rows=0 loops=5,443)

  • 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.group_id = rg.id)
  • Buffers: shared hit=16,365
9. 0.034 1,308.060 ↑ 141.7 107 1

Hash (cost=146,591.53..146,591.53 rows=15,165 width=4) (actual time=1,308.060..1,308.060 rows=107 loops=1)

  • Output: s.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 132kB
  • Buffers: shared hit=92,147
10. 0.007 1,308.026 ↑ 141.7 107 1

Subquery Scan on s (cost=146,401.97..146,591.53 rows=15,165 width=4) (actual time=1,308.008..1,308.026 rows=107 loops=1)

  • Output: s.id
  • Buffers: shared hit=92,147
11. 0.118 1,308.019 ↑ 141.7 107 1

Sort (cost=146,401.97..146,439.88 rows=15,165 width=4) (actual time=1,308.008..1,308.019 rows=107 loops=1)

  • Output: s_1.id, (NULL::character varying(255)), (NULL::character varying(500))
  • Sort Key: s_1.id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=92,147
12. 1,154.030 1,307.901 ↑ 141.7 107 1

Nested Loop (cost=0.00..145,348.88 rows=15,165 width=4) (actual time=26.746..1,307.901 rows=107 loops=1)

  • Output: s_1.id, NULL::character varying(255), NULL::character varying(500)
  • Join Filter: ((btrim((s_1.code)::text) = btrim((sp.second_service_code)::text)) OR (btrim((s_1.name)::text) = btrim(sp.second_service_name)))
  • Rows Removed by Join Filter: 1,520,162
  • Buffers: shared hit=92,144
13. 0.028 0.028 ↑ 1.0 1 1

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

  • Output: sp.second_service_code, sp.second_service_name
  • Buffers: shared hit=4
14. 153.843 153.843 ↓ 1.0 1,520,269 1

Seq Scan on public.sr_service s_1 (cost=0.00..107,342.53 rows=1,520,253 width=134) (actual time=0.004..153.843 rows=1,520,269 loops=1)

  • Output: s_1.id, s_1.code, s_1.cul, s_1.is_death, s_1.duration, s_1.is_fictitious, s_1.is_independent, s_1.is_multuplicity, s_1.name, s_1.terms, s_1.org_id, s_1.category_id, s_1.duration_unit_id, s_1.prototype_id, s_1.type_id, s_1.from_dt, s_1.to_dt, s_1.is_repeated, s_1.is_inherit_protocol, s_1.is_expendable_materials, s_1.is_actual_cul, s_1.is_paraclinical, s_1.is_complex, s_1.accounting_id, s_1.is_autocopy_diagnoses, s_1.is_stomat, s_1.srg_dif_type_id, s_1.srg_opr_kind_id, s_1.is_need_anatomic_zone, s_1.aud_who, s_1.aud_when, s_1.aud_source, s_1.aud_who_create, s_1.aud_when_create, s_1.aud_source_create, s_1.is_filling, s_1.is_extraction, s_1.short_name, s_1.is_need_close_date, s_1.is_vmp, s_1.is_blocked_for_waitlist
  • Buffers: shared hit=92,140
15. 0.000 5.782 ↑ 1.0 1 1

Limit (cost=1.43..10,475.04 rows=1 width=4) (actual time=5.782..5.782 rows=1 loops=1)

  • Output: s_2.id
  • Buffers: shared hit=16,408
16. 0.020 5.782 ↑ 14.0 1 1

Nested Loop (cost=1.43..146,632.01 rows=14 width=4) (actual time=5.782..5.782 rows=1 loops=1)

  • Output: s_2.id
  • Join Filter: ((btrim((s_2.code)::text) = btrim((sp_1.second_service_code)::text)) OR (btrim((s_2.name)::text) = btrim(sp_1.second_service_name)))
  • Rows Removed by Join Filter: 3
  • Buffers: shared hit=16,408
17. 0.000 0.000 ↑ 1.0 1 1

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

  • Output: sp_1.second_service_code, sp_1.second_service_name
18. 0.003 5.762 ↑ 342.8 4 1

Nested Loop (cost=1.43..146,597.72 rows=1,371 width=134) (actual time=5.748..5.762 rows=4 loops=1)

  • Output: s_2.id, s_2.code, s_2.name
  • Buffers: shared hit=16,408
19. 0.000 5.743 ↑ 342.8 4 1

Nested Loop (cost=1.00..145,917.14 rows=1,371 width=4) (actual time=5.741..5.743 rows=4 loops=1)

  • Output: sr_1.srv_type_id
  • Buffers: shared hit=16,392
20. 0.470 0.470 ↑ 3.3 5,443 1

Index Only Scan using sr_res_group_responsible_id_id_ix on public.sr_res_group rg_1 (cost=0.57..930.46 rows=17,969 width=4) (actual time=0.016..0.470 rows=5,443 loops=1)

  • Output: rg_1.responsible_id, rg_1.id
  • Index Cond: (rg_1.responsible_id = 11,951)
  • Heap Fetches: 1
  • Buffers: shared hit=27
21. 5.443 5.443 ↓ 0.0 0 5,443

Index Scan using sr_res_group_service_group_id_idx on public.sr_res_group_service sr_1 (cost=0.43..7.88 rows=19 width=8) (actual time=0.001..0.001 rows=0 loops=5,443)

  • 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.group_id = rg_1.id)
  • Buffers: shared hit=16,365
22. 0.016 0.016 ↑ 1.0 1 4

Index Scan using sr_service_pk on public.sr_service s_2 (cost=0.43..0.49 rows=1 width=134) (actual time=0.004..0.004 rows=1 loops=4)

  • Output: s_2.id, s_2.code, s_2.cul, s_2.is_death, s_2.duration, s_2.is_fictitious, s_2.is_independent, s_2.is_multuplicity, s_2.name, s_2.terms, s_2.org_id, s_2.category_id, s_2.duration_unit_id, s_2.prototype_id, s_2.type_id, s_2.from_dt, s_2.to_dt, s_2.is_repeated, s_2.is_inherit_protocol, s_2.is_expendable_materials, s_2.is_actual_cul, s_2.is_paraclinical, s_2.is_complex, s_2.accounting_id, s_2.is_autocopy_diagnoses, s_2.is_stomat, s_2.srg_dif_type_id, s_2.srg_opr_kind_id, s_2.is_need_anatomic_zone, s_2.aud_who, s_2.aud_when, s_2.aud_source, s_2.aud_who_create, s_2.aud_when_create, s_2.aud_source_create, s_2.is_filling, s_2.is_extraction, s_2.short_name, s_2.is_need_close_date, s_2.is_vmp, s_2.is_blocked_for_waitlist
  • Index Cond: (s_2.id = sr_1.srv_type_id)
  • Buffers: shared hit=16
Planning time : 5.751 ms
Execution time : 1,329.011 ms