explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aPk8

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 40,207.543 ↓ 0.0 0 1

GroupAggregate (cost=57,236.24..57,236.61 rows=5 width=115) (actual time=40,207.543..40,207.543 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=6 read=8,139
  • I/O Timings: read=39,638.291
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.025 40,207.540 ↓ 0.0 0 1

Sort (cost=57,234.86..57,234.87 rows=5 width=115) (actual time=40,207.540..40,207.540 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=6 read=8,139
  • I/O Timings: read=39,638.291
5. 0.001 40,207.515 ↓ 0.0 0 1

Nested Loop Left Join (cost=276.15..57,234.80 rows=5 width=115) (actual time=40,207.515..40,207.515 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
  • Buffers: shared hit=6 read=8,139
  • I/O Timings: read=39,638.291
6. 0.001 40,207.514 ↓ 0.0 0 1

Nested Loop Left Join (cost=275.72..57,161.14 rows=5 width=100) (actual time=40,207.514..40,207.514 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, s.code, s.e_code, r.code, d.code, d.name
  • Buffers: shared hit=6 read=8,139
  • I/O Timings: read=39,638.291
7. 0.001 40,207.513 ↓ 0.0 0 1

Nested Loop Left Join (cost=275.44..57,122.90 rows=5 width=43) (actual time=40,207.513..40,207.513 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.department_id, s.code, s.e_code, r.code
  • Buffers: shared hit=6 read=8,139
  • I/O Timings: read=39,638.291
8. 0.000 40,207.512 ↓ 0.0 0 1

Nested Loop Left Join (cost=275.29..57,095.36 rows=5 width=43) (actual time=40,207.512..40,207.512 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=6 read=8,139
  • I/O Timings: read=39,638.291
9. 0.001 40,207.512 ↓ 0.0 0 1

Nested Loop (cost=275.29..57,068.08 rows=5 width=37) (actual time=40,207.512..40,207.512 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=6 read=8,139
  • I/O Timings: read=39,638.291
10. 0.003 40,207.511 ↓ 0.0 0 1

Nested Loop (cost=275.00..57,029.27 rows=5 width=25) (actual time=40,207.511..40,207.511 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=6 read=8,139
  • I/O Timings: read=39,638.291
11. 0.000 40,207.508 ↓ 0.0 0 1

Nested Loop (cost=274.71..56,990.46 rows=5 width=18) (actual time=40,207.508..40,207.508 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, p.employee_id, p.code, p.position_id
  • Buffers: shared hit=6 read=8,139
  • I/O Timings: read=39,638.291
12. 0.001 40,207.508 ↓ 0.0 0 1

Nested Loop (cost=274.42..56,951.56 rows=5 width=12) (actual time=40,207.508..40,207.508 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, er.employee_position_id
  • Buffers: shared hit=6 read=8,139
  • I/O Timings: read=39,638.291
13. 0.011 40,207.507 ↓ 0.0 0 1

Hash Join (cost=274.13..56,921.53 rows=5 width=12) (actual time=40,207.507..40,207.507 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, gr.resource_id
  • Hash Cond: (gr.role_id = sr_res_role.id)
  • Buffers: shared hit=6 read=8,139
  • I/O Timings: read=39,638.291
14. 0.001 40,207.463 ↓ 0.0 0 1

Nested Loop (cost=272.54..56,919.89 rows=5 width=16) (actual time=40,207.463..40,207.463 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, gr.role_id, gr.resource_id
  • Buffers: shared hit=5 read=8,139
  • I/O Timings: read=39,638.291
15. 0.003 40,207.462 ↓ 0.0 0 1

Nested Loop (cost=271.98..56,813.92 rows=3 width=16) (actual time=40,207.462..40,207.462 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, f.res_group_id, g.id
  • Buffers: shared hit=5 read=8,139
  • I/O Timings: read=39,638.291
16. 40,019.945 40,207.459 ↓ 0.0 0 1

Bitmap Heap Scan on billing.fin_bill_steps f (cost=271.41..56,109.07 rows=142 width=12) (actual time=40,207.459..40,207.459 rows=0 loops=1)

  • Output: f.bill_id, f.srv_rendered_id, f.res_group_id
  • Recheck Cond: (f.bill_id = 2,301)
  • Filter: ((NULLIF(btrim((f.doctor_code_regional)::text), ''::text) IS NULL) OR (NULLIF(btrim((f.speciality_code_arr[1])::text), ''::text) IS NULL))
  • Rows Removed by Filter: 9,603
  • Heap Blocks: exact=8,115
  • Buffers: shared hit=5 read=8,139
  • I/O Timings: read=39,638.291
17. 187.514 187.514 ↑ 1.5 9,603 1

Bitmap Index Scan on fin_bill_steps_bill_id_spec_item_id_id_idx (cost=0.00..271.37 rows=14,258 width=0) (actual time=187.514..187.514 rows=9,603 loops=1)

  • Index Cond: (f.bill_id = 2,301)
  • Buffers: shared hit=1 read=28
  • I/O Timings: read=184.026
18. 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.95 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
19. 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)
20. 0.008 0.033 ↑ 1.0 19 1

Hash (cost=1.35..1.35 rows=19 width=4) (actual time=0.033..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
  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
21. 0.025 0.025 ↑ 1.0 19 1

Seq Scan on public.sr_res_role (cost=0.00..1.35 rows=19 width=4) (actual time=0.010..0.025 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
22. 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
23. 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)
24. 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)
25. 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)
26. 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
27. 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
28. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_position_role_pk on public.pim_position_role r (cost=0.15..5.50 rows=1 width=8) (never executed)

  • Output: r.id, r.code, r.name, r.category_id, r.e_code, r.parent_id, r.aud_who, r.aud_when, r.aud_source, r.aud_who_create, r.aud_when_create, r.aud_source_create, r.from_dt, r.to_dt
  • Index Cond: (r.id = t.role_id)
29. 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)
30. 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)
Planning time : 11.807 ms
Execution time : 40,208.036 ms