explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 23IW

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 0.135 ↓ 0.0 0 1

GroupAggregate (cost=1,570.21..1,570.59 rows=5 width=115) (actual time=0.135..0.135 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, (COALESCE((p.employee_id)::text, ''::text)), (COALESCE(NULLIF(btrim((p.code)::text), ''::text), btrim((e.number)::text), ''::text)), (ARRAY[COALESCE(btrim((s.code)::text), ''::text), COALESCE(btrim((s.e_code)::text), ''::text)]), (COALESCE(btrim((r.code)::text), ''::text)), p.position_id, (COALESCE(btrim((d.code)::text), ''::text)), (COALESCE(btrim((d.name)::text), ''::text)), regexp_replace(replace(replace((array_agg(COALESCE(btrim((i.code)::text), ''::text) ORDER BY i.issue_dt DESC NULLS LAST))[1], '-'::text, ''::text), ' '::text, ''::text), '(...)(...)(...)(..)'::text, '\1-\2-\3 \4'::text)
  • Group Key: f.bill_id, f.srv_rendered_id, (COALESCE((p.employee_id)::text, ''::text)), (COALESCE(NULLIF(btrim((p.code)::text), ''::text), btrim((e.number)::text), ''::text)), (ARRAY[COALESCE(btrim((s.code)::text), ''::text), COALESCE(btrim((s.e_code)::text), ''::text)]), (COALESCE(btrim((r.code)::text), ''::text)), p.position_id, (COALESCE(btrim((d.code)::text), ''::text)), (COALESCE(btrim((d.name)::text), ''::text))
  • Buffers: shared hit=3
2.          

Initplan (for GroupAggregate)

3. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.pim_code_type (cost=0.00..1.38 rows=1 width=4) (never executed)

  • Output: pim_code_type.id
  • Filter: ((pim_code_type.code)::text = 'SNILS'::text)
4. 0.040 0.131 ↓ 0.0 0 1

Sort (cost=1,568.84..1,568.85 rows=5 width=115) (actual time=0.131..0.131 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, (COALESCE((p.employee_id)::text, ''::text)), (COALESCE(NULLIF(btrim((p.code)::text), ''::text), btrim((e.number)::text), ''::text)), (ARRAY[COALESCE(btrim((s.code)::text), ''::text), COALESCE(btrim((s.e_code)::text), ''::text)]), (COALESCE(btrim((r.code)::text), ''::text)), p.position_id, (COALESCE(btrim((d.code)::text), ''::text)), (COALESCE(btrim((d.name)::text), ''::text)), i.code, i.issue_dt
  • Sort Key: f.bill_id, f.srv_rendered_id, (COALESCE((p.employee_id)::text, ''::text)), (COALESCE(NULLIF(btrim((p.code)::text), ''::text), btrim((e.number)::text), ''::text)), (ARRAY[COALESCE(btrim((s.code)::text), ''::text), COALESCE(btrim((s.e_code)::text), ''::text)]), (COALESCE(btrim((r.code)::text), ''::text)), p.position_id, (COALESCE(btrim((d.code)::text), ''::text)), (COALESCE(btrim((d.name)::text), ''::text))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
5. 0.002 0.091 ↓ 0.0 0 1

Hash Left Join (cost=16.96..1,568.78 rows=5 width=115) (actual time=0.091..0.091 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, COALESCE((p.employee_id)::text, ''::text), COALESCE(NULLIF(btrim((p.code)::text), ''::text), btrim((e.number)::text), ''::text), ARRAY[COALESCE(btrim((s.code)::text), ''::text), COALESCE(btrim((s.e_code)::text), ''::text)], COALESCE(btrim((r.code)::text), ''::text), p.position_id, COALESCE(btrim((d.code)::text), ''::text), COALESCE(btrim((d.name)::text), ''::text), i.code, i.issue_dt
  • Hash Cond: (t.role_id = r.id)
  • Buffers: shared hit=3
6. 0.000 0.089 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.29..1,554.91 rows=5 width=115) (actual time=0.089..0.089 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, p.employee_id, p.code, p.position_id, e.number, t.role_id, s.code, s.e_code, d.code, d.name, i.code, i.issue_dt
  • Buffers: shared hit=3
7. 0.000 0.089 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.86..1,481.38 rows=5 width=100) (actual time=0.089..0.089 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, p.employee_id, p.code, p.position_id, e.number, e.individual_id, t.role_id, s.code, s.e_code, d.code, d.name
  • Buffers: shared hit=3
8. 0.001 0.089 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.58..1,443.14 rows=5 width=43) (actual time=0.089..0.089 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, p.employee_id, p.code, p.position_id, e.number, e.individual_id, t.role_id, t.department_id, s.code, s.e_code
  • Join Filter: (s.id = t.speciality_id)
  • Buffers: shared hit=3
9. 0.001 0.088 ↓ 0.0 0 1

Nested Loop (cost=2.58..1,415.86 rows=5 width=37) (actual time=0.088..0.088 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, p.employee_id, p.code, p.position_id, e.number, e.individual_id, t.speciality_id, t.role_id, t.department_id
  • Buffers: shared hit=3
10. 0.000 0.087 ↓ 0.0 0 1

Nested Loop (cost=2.29..1,377.05 rows=5 width=25) (actual time=0.087..0.087 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, p.employee_id, p.code, p.position_id, e.number, e.individual_id
  • Buffers: shared hit=3
11. 0.000 0.087 ↓ 0.0 0 1

Nested Loop (cost=2.00..1,338.24 rows=5 width=18) (actual time=0.087..0.087 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, p.employee_id, p.code, p.position_id
  • Buffers: shared hit=3
12. 0.001 0.087 ↓ 0.0 0 1

Nested Loop (cost=1.71..1,299.34 rows=5 width=12) (actual time=0.087..0.087 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, er.employee_position_id
  • Buffers: shared hit=3
13. 0.015 0.086 ↓ 0.0 0 1

Nested Loop (cost=1.42..1,269.31 rows=5 width=12) (actual time=0.086..0.086 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, gr.resource_id
  • Join Filter: (gr.role_id = sr_res_role.id)
  • Buffers: shared hit=3
14. 0.033 0.033 ↑ 1.0 19 1

Seq Scan on public.sr_res_role (cost=0.00..1.35 rows=19 width=4) (actual time=0.016..0.033 rows=19 loops=1)

  • Output: sr_res_role.id, sr_res_role.name, sr_res_role.kind_id, sr_res_role.code, sr_res_role.resource_kind_id, sr_res_role.finder_id, sr_res_role.e_code, sr_res_role.aud_who, sr_res_role.aud_when, sr_res_role.aud_source, sr_res_role.aud_who_create, sr_res_role.aud_when_create, sr_res_role.aud_source_create, sr_res_role.from_dt, sr_res_role.to_dt, sr_res_role.is_default_doctor_role, sr_res_role.is_commission_role
  • Filter: (sr_res_role.resource_kind_id = 1)
  • Rows Removed by Filter: 9
  • Buffers: shared hit=1
15. 0.004 0.038 ↓ 0.0 0 19

Materialize (cost=1.42..1,266.54 rows=5 width=16) (actual time=0.002..0.002 rows=0 loops=19)

  • Output: f.bill_id, f.srv_rendered_id, gr.role_id, gr.resource_id
  • Buffers: shared hit=2
16. 0.000 0.034 ↓ 0.0 0 1

Nested Loop (cost=1.42..1,266.52 rows=5 width=16) (actual time=0.034..0.034 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, gr.role_id, gr.resource_id
  • Buffers: shared hit=2
17. 0.001 0.034 ↓ 0.0 0 1

Nested Loop (cost=0.85..1,160.55 rows=3 width=16) (actual time=0.034..0.034 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, f.res_group_id, g.id
  • Buffers: shared hit=2
18. 0.033 0.033 ↓ 0.0 0 1

Index Scan using billing_fin_bill_steps_bill_idx on billing.fin_bill_steps f (cost=0.28..514.87 rows=130 width=12) (actual time=0.033..0.033 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, f.res_group_id
  • Index Cond: (f.bill_id = 2301)
  • Buffers: shared hit=2
19. 0.000 0.000 ↓ 0.0 0

Index Only Scan using sr_res_group_responsible_id_id_ix on public.sr_res_group g (cost=0.57..4.96 rows=1 width=4) (never executed)

  • Output: g.responsible_id, g.id
  • Index Cond: ((g.responsible_id IS NULL) AND (g.id = f.res_group_id))
  • Heap Fetches: 0
20. 0.000 0.000 ↓ 0.0 0

Index Scan using sr_res_group_relationship__group_id_ix on public.sr_res_group_relationship gr (cost=0.57..32.94 rows=238 width=12) (never executed)

  • Output: gr.id, gr.bdatetime, gr.edatetime, gr.resource_id, gr.group_id, gr.role_id, gr.is_disabled, gr.aud_who, gr.aud_when, gr.aud_source, gr.aud_who_create, gr.aud_when_create, gr.aud_source_create, gr._is_system
  • Index Cond: (gr.group_id = g.id)
21. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pim_employee_position_resource_unq on public.pim_employee_position_resource er (cost=0.29..6.00 rows=1 width=8) (never executed)

  • Output: er.id, er.employee_position_id
  • Index Cond: (er.id = gr.resource_id)
  • Heap Fetches: 0
22. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_employee_position_pk on public.pim_employee_position p (cost=0.29..7.77 rows=1 width=14) (never executed)

  • Output: p.id, p.dismissal_order_code, p.start_date, p.hiring_order_code, p.rate, p.end_date, p.dismissal_reason_id, p.employee_id, p.employment_type_id, p.hiring_type_id, p.position_id, p.position_type_id, p.unit_id, p.code, p.aud_who, p.aud_when, p.aud_source, p.aud_who_create, p.aud_when_create, p.aud_source_create, p.pref_prescription, p.extra_payment, p.target_training, p.leaving_reason_id
  • Index Cond: (p.id = er.employee_position_id)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_employee_pk on public.pim_employee e (cost=0.29..7.75 rows=1 width=11) (never executed)

  • Output: e.id, e.note, e.number, e.photo, e.callup_subject_id, e.individual_id, e.organization_id, e.is_dismissed, e.employment_dt, e.dismissal_dt, e.aud_who, e.aud_when, e.aud_source, e.aud_who_create, e.aud_when_create, e.aud_source_create, e.accreditation_id
  • Index Cond: (e.id = p.employee_id)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_position_pk on public.pim_position t (cost=0.29..7.75 rows=1 width=16) (never executed)

  • Output: t.id, t.code, t.start_date, t.name, t.end_date, t.department_id, t.organization_id, t.role_id, t.speciality_id, t.rate, t.aud_who, t.aud_when, t.aud_source, t.aud_who_create, t.aud_when_create, t.aud_source_create, t.pref_prescription, t.payment_fond, t.staff_event1, t.staff_event2, t.note, t.fact_employment, t.external_rate, t.billing_code
  • Index Cond: (t.id = p.position_id)
25. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..13.79 rows=186 width=14) (never executed)

  • Output: s.code, s.e_code, s.id
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.pim_speciality s (cost=0.00..12.86 rows=186 width=14) (never executed)

  • Output: s.code, s.e_code, s.id
27. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_department_pk on public.pim_department d (cost=0.28..7.64 rows=1 width=65) (never executed)

  • Output: d.id, d.is_available_diagnosis, d.code, d.from_dt, d.name, d.to_dt, d.accounting_center_id, d.type_id, d.funding_id, d.org_id, d.parent_id, d.sphere_id, d.is_payment, d.unit_id, d.kind_id, d.e_code, d.scope_id, d.aud_who, d.aud_when, d.aud_source, d.aud_who_create, d.aud_when_create, d.aud_source_create, d.is_separate, d.is_social_significant, d.age_group_id, d.is_branch_type, d.visits_per_shift, d.departures_per_shift, d.visits_per_day, d.necropsies_per_day, d.clinical_trials_per_shift, d.brigades_amount, d.ose, d.ose_reject, d.at_home, d.longitude, d.latitude, d.amb_reception_exists, d.cadastral_number, d.hospital_mode_id, d.type_profile_id
  • Index Cond: (d.id = t.department_id)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_indiv_code_indiv_id_idx on public.pim_indiv_code i (cost=0.43..14.70 rows=1 width=23) (never executed)

  • Output: i.id, i.code, i.issue_dt, i.type_id, i.indiv_id, i.sha256, i.aud_who, i.aud_when, i.aud_source, i.aud_who_create, i.aud_when_create, i.aud_source_create
  • Index Cond: (i.indiv_id = e.individual_id)
  • Filter: (i.type_id = $0)
29. 0.000 0.000 ↓ 0.0 0

Hash (cost=9.41..9.41 rows=341 width=8) (never executed)

  • Output: r.code, r.id
30. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.pim_position_role r (cost=0.00..9.41 rows=341 width=8) (never executed)

  • Output: r.code, r.id
Planning time : 14.392 ms
Execution time : 0.580 ms