explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q9Oe

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 7,946.150 ↓ 0.0 0 1

Limit (cost=7,331.91..7,331.91 rows=1 width=402) (actual time=7,946.150..7,946.150 rows=0 loops=1)

  • Output: mhc.id, (CASE WHEN ((mhc.name IS NULL) OR (mhc.surname IS NULL) OR (mhc.patr_name IS NULL)) THEN (((((COALESCE(pi.surname, ''::character varying))::text || ' '::text) || (COALESCE(pi.name, ''::character varying))::text) || ' '::text) || (COALESCE(pi.patr_name, ''::character varying))::text) ELSE concat_ws(' '::text, mhc.surname, mhc.name, mhc.patr_name) END), pi.id, (NULL::unknown), (NULL::unknown), pi.id, pi.birth_dt, (CASE WHEN (mhc.address_id IS NULL) THEN (mhc.address_text)::text ELSE (((COALESCE((SubPlan 1), ''::character varying))::text || ' / '::text) || (COALESCE(mhc.contact, ''::character varying))::text) END), (to_char(ma.registration_dt, 'DD.MM.YYYY HH24:MI'::text)), ma.registration_dt, mhcc.id, mhcc.name, mhcre.id, mhcre.name, ((((mhcc.name)::text || ' '::text) || (mhcre.name)::text)), mhc.complaints, ((COALESCE(ss.bdatetime, ma.bdatetime))::date), (CASE WHEN ((mhc.result_id = 1) AND ('2020-01-31'::date > (ss.bdatetime)::date)) THEN 'text-warning'::text WHEN (mhc.result_id = 2) THEN 'text-info'::text ELSE ''::text END), ss.is_house_call, srg.id, ($1), ($2), ($3), ($4), ($5), (true), ma.id, ma.srv_rendered_id, ssr.org_id, ssr.customer_id, ssr.res_group_id, srg.is_system, ma.service_id, (CASE WHEN (ssr.is_rendered AND NULL::boolean) THEN false ELSE true END), (NULL::unknown), (CASE WHEN (mhc.result_id = 2) THEN true ELSE false END)
  • Buffers: shared hit=2678 read=106
  • I/O Timings: read=7932.297
2.          

Initplan (for Limit)

3. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.md_profile mp (cost=0.00..10.21 rows=1 width=4) (never executed)

  • Output: mp.id
  • Filter: ((mp.code)::text = '68'::text)
4. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.mc_case_type mct (cost=0.00..1.07 rows=1 width=4) (never executed)

  • Output: mct.id
  • Filter: ((mct.code)::text = '1'::text)
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.mc_case_init_goal mcig (cost=0.00..2.41 rows=1 width=4) (never executed)

  • Output: mcig.id
  • Filter: ((mcig.code)::text = '1'::text)
6. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.mc_care_regimen mcr (cost=0.00..1.09 rows=1 width=4) (never executed)

  • Output: mcr.id
  • Filter: (mcr.code = 1)
7. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.fin_funding_source_type ffst (cost=0.00..1.10 rows=1 width=4) (never executed)

  • Output: ffst.id
  • Filter: ((ffst.code)::text = 'OMS'::text)
8. 0.138 7,946.143 ↓ 0.0 0 1

Sort (cost=7,316.02..7,316.03 rows=1 width=402) (actual time=7,946.143..7,946.143 rows=0 loops=1)

  • Output: mhc.id, (CASE WHEN ((mhc.name IS NULL) OR (mhc.surname IS NULL) OR (mhc.patr_name IS NULL)) THEN (((((COALESCE(pi.surname, ''::character varying))::text || ' '::text) || (COALESCE(pi.name, ''::character varying))::text) || ' '::text) || (COALESCE(pi.patr_name, ''::character varying))::text) ELSE concat_ws(' '::text, mhc.surname, mhc.name, mhc.patr_name) END), pi.id, (NULL::unknown), (NULL::unknown), pi.id, pi.birth_dt, (CASE WHEN (mhc.address_id IS NULL) THEN (mhc.address_text)::text ELSE (((COALESCE((SubPlan 1), ''::character varying))::text || ' / '::text) || (COALESCE(mhc.contact, ''::character varying))::text) END), (to_char(ma.registration_dt, 'DD.MM.YYYY HH24:MI'::text)), ma.registration_dt, mhcc.id, mhcc.name, mhcre.id, mhcre.name, ((((mhcc.name)::text || ' '::text) || (mhcre.name)::text)), mhc.complaints, ((COALESCE(ss.bdatetime, ma.bdatetime))::date), (CASE WHEN ((mhc.result_id = 1) AND ('2020-01-31'::date > (ss.bdatetime)::date)) THEN 'text-warning'::text WHEN (mhc.result_id = 2) THEN 'text-info'::text ELSE ''::text END), ss.is_house_call, srg.id, ($1), ($2), ($3), ($4), ($5), (true), ma.id, ma.srv_rendered_id, ssr.org_id, ssr.customer_id, ssr.res_group_id, srg.is_system, ma.service_id, (CASE WHEN (ssr.is_rendered AND NULL::boolean) THEN false ELSE true END), (NULL::unknown), (CASE WHEN (mhc.result_id = 2) THEN true ELSE false END)
  • Sort Key: ma.registration_dt
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2678 read=106
  • I/O Timings: read=7932.297
9. 0.001 7,946.005 ↓ 0.0 0 1

Nested Loop Left Join (cost=4,595.96..7,316.01 rows=1 width=402) (actual time=7,946.005..7,946.005 rows=0 loops=1)

  • Output: mhc.id, CASE WHEN ((mhc.name IS NULL) OR (mhc.surname IS NULL) OR (mhc.patr_name IS NULL)) THEN (((((COALESCE(pi.surname, ''::character varying))::text || ' '::text) || (COALESCE(pi.name, ''::character varying))::text) || ' '::text) || (COALESCE(pi.patr_name, ''::character varying))::text) ELSE concat_ws(' '::text, mhc.surname, mhc.name, mhc.patr_name) END, pi.id, NULL::unknown, NULL::unknown, pi.id, pi.birth_dt, CASE WHEN (mhc.address_id IS NULL) THEN (mhc.address_text)::text ELSE (((COALESCE((SubPlan 1), ''::character varying))::text || ' / '::text) || (COALESCE(mhc.contact, ''::character varying))::text) END, to_char(ma.registration_dt, 'DD.MM.YYYY HH24:MI'::text), ma.registration_dt, mhcc.id, mhcc.name, mhcre.id, mhcre.name, (((mhcc.name)::text || ' '::text) || (mhcre.name)::text), mhc.complaints, (COALESCE(ss.bdatetime, ma.bdatetime))::date, CASE WHEN ((mhc.result_id = 1) AND ('2020-01-31'::date > (ss.bdatetime)::date)) THEN 'text-warning'::text WHEN (mhc.result_id = 2) THEN 'text-info'::text ELSE ''::text END, ss.is_house_call, srg.id, $1, $2, $3, $4, $5, true, ma.id, ma.srv_rendered_id, ssr.org_id, ssr.customer_id, ssr.res_group_id, srg.is_system, ma.service_id, CASE WHEN (ssr.is_rendered AND NULL::boolean) THEN false ELSE true END, NULL::unknown, CASE WHEN (mhc.result_id = 2) THEN true ELSE false END
  • Join Filter: (mhc.call_reason_id = mhcre.id)
  • Buffers: shared hit=2675 read=106
  • I/O Timings: read=7932.297
10. 0.001 7,946.004 ↓ 0.0 0 1

Nested Loop Left Join (cost=4,595.96..7,314.55 rows=1 width=371) (actual time=7,946.004..7,946.004 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, srg.id, srg.is_system, ma.registration_dt, ma.bdatetime, ma.id, ma.srv_rendered_id, ma.service_id, mhc.id, mhc.name, mhc.surname, mhc.patr_name, mhc.address_id, mhc.address_text, mhc.contact, mhc.complaints, mhc.result_id, mhc.call_reason_id, mhcc.id, mhcc.name, ssr.org_id, ssr.customer_id, ssr.res_group_id, ssr.is_rendered, pi.surname, pi.name, pi.patr_name, pi.id, pi.birth_dt
  • Buffers: shared hit=2675 read=106
  • I/O Timings: read=7932.297
11. 0.002 7,946.003 ↓ 0.0 0 1

Nested Loop Left Join (cost=4,595.53..7,314.09 rows=1 width=318) (actual time=7,946.003..7,946.003 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, srg.id, srg.is_system, ma.registration_dt, ma.bdatetime, ma.id, ma.srv_rendered_id, ma.service_id, ma.customer_id, mhc.id, mhc.name, mhc.surname, mhc.patr_name, mhc.address_id, mhc.address_text, mhc.contact, mhc.complaints, mhc.result_id, mhc.call_reason_id, mhcc.id, mhcc.name, ssr.org_id, ssr.customer_id, ssr.res_group_id, ssr.is_rendered
  • Join Filter: (mhc.category_id = mhcc.id)
  • Buffers: shared hit=2675 read=106
  • I/O Timings: read=7932.297
12. 0.001 7,946.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=4,595.53..7,312.93 rows=1 width=290) (actual time=7,946.001..7,946.001 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, srg.id, srg.is_system, ma.registration_dt, ma.bdatetime, ma.id, ma.srv_rendered_id, ma.service_id, ma.customer_id, mhc.id, mhc.name, mhc.surname, mhc.patr_name, mhc.address_id, mhc.address_text, mhc.contact, mhc.complaints, mhc.result_id, mhc.category_id, mhc.call_reason_id, ssr.org_id, ssr.customer_id, ssr.res_group_id, ssr.is_rendered
  • Buffers: shared hit=2675 read=106
  • I/O Timings: read=7932.297
13. 0.001 7,946.000 ↓ 0.0 0 1

Nested Loop Left Join (cost=4,595.24..7,312.52 rows=1 width=63) (actual time=7,946.000..7,946.000 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, srg.id, srg.is_system, ma.registration_dt, ma.bdatetime, ma.id, ma.srv_rendered_id, ma.service_id, ma.customer_id, mha.house_call_id, ssr.org_id, ssr.customer_id, ssr.res_group_id, ssr.is_rendered
  • Filter: CASE WHEN (ssr.is_rendered AND NULL::boolean) THEN false ELSE true END
  • Buffers: shared hit=2675 read=106
  • I/O Timings: read=7932.297
14. 0.001 7,945.999 ↓ 0.0 0 1

Nested Loop (cost=4,594.67..7,310.96 rows=2 width=50) (actual time=7,945.999..7,945.999 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, srg.id, srg.is_system, ma.registration_dt, ma.bdatetime, ma.id, ma.srv_rendered_id, ma.service_id, ma.customer_id, mha.house_call_id
  • Buffers: shared hit=2675 read=106
  • I/O Timings: read=7932.297
15. 0.013 7,945.998 ↓ 0.0 0 1

Nested Loop (cost=4,594.24..7,310.00 rows=2 width=54) (actual time=7,945.998..7,945.998 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, srg.id, srg.is_system, sst.ticket_id, ma.registration_dt, ma.bdatetime, ma.id, ma.srv_rendered_id, ma.service_id, ma.customer_id, mha.house_call_id
  • Buffers: shared hit=2675 read=106
  • I/O Timings: read=7932.297
16. 4.050 4.050 ↑ 1.0 1 1

Index Scan using sr_res_group_responsible_id_id_ix on public.sr_res_group srg (cost=0.57..8.59 rows=1 width=5) (actual time=4.048..4.050 rows=1 loops=1)

  • Output: srg.id, srg.bdate, srg.edate, srg.is_system, srg.name, srg.department_id, srg.org_id, srg.responsible_id, srg.is_available_in_electronic_queue, srg.label_id, srg.ignore_regclinic_check, srg.note, srg.blocked, srg.block_comment, srg.template_res_group_id, srg.aud_who, srg.aud_when, srg.aud_source, srg.aud_who_create, srg.aud_when_create, srg.aud_source_create, srg.emergency, srg.ignore_districts, srg.service_template_id
  • Index Cond: ((srg.responsible_id = 73230) AND (srg.id = 58580925))
  • Buffers: shared hit=4 read=1
  • I/O Timings: read=3.964
17. 0.064 7,941.935 ↓ 0.0 0 1

Hash Right Join (cost=4,593.67..7,301.39 rows=2 width=53) (actual time=7,941.935..7,941.935 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, strg.res_group_id, sst.ticket_id, ma.registration_dt, ma.bdatetime, ma.id, ma.srv_rendered_id, ma.service_id, ma.customer_id, mha.house_call_id
  • Hash Cond: (mha.appointment_id = ma.id)
  • Buffers: shared hit=2671 read=105
  • I/O Timings: read=7928.333
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.md_hcall_assignment mha (cost=0.00..2,350.52 rows=95,252 width=8) (never executed)

  • Output: mha.id, mha.house_call_id, mha.appointment_id, mha.date, mha.reassign_reason_id, mha.comment, mha.aud_who, mha.aud_when, mha.aud_source, mha.aud_who_create, mha.aud_when_create, mha.aud_source_create, mha.department_id
19. 0.002 7,941.871 ↓ 0.0 0 1

Hash (cost=4,593.64..4,593.64 rows=2 width=49) (actual time=7,941.871..7,941.871 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, strg.res_group_id, sst.ticket_id, ma.registration_dt, ma.bdatetime, ma.id, ma.srv_rendered_id, ma.service_id, ma.customer_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2668 read=105
  • I/O Timings: read=7928.333
20. 0.002 7,941.869 ↓ 0.0 0 1

Nested Loop (cost=2.42..4,593.64 rows=2 width=49) (actual time=7,941.869..7,941.869 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, strg.res_group_id, sst.ticket_id, ma.registration_dt, ma.bdatetime, ma.id, ma.srv_rendered_id, ma.service_id, ma.customer_id
  • Join Filter: (((COALESCE(ss.bdatetime, ma.bdatetime))::date >= '2020-01-31'::date) AND ((COALESCE(ss.bdatetime, ma.bdatetime))::date <= '2020-01-31'::date))
  • Buffers: shared hit=2668 read=105
  • I/O Timings: read=7928.333
21. 0.002 7,941.867 ↓ 0.0 0 1

Nested Loop (cost=1.85..4,581.57 rows=17 width=17) (actual time=7,941.867..7,941.867 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, strg.res_group_id, sst.ticket_id
  • Buffers: shared hit=2668 read=105
  • I/O Timings: read=7928.333
22. 0.626 7,941.865 ↓ 0.0 0 1

Nested Loop (cost=1.42..4,492.90 rows=28 width=17) (actual time=7,941.865..7,941.865 rows=0 loops=1)

  • Output: ss.bdatetime, ss.is_house_call, ss.id, strg.res_group_id
  • Buffers: shared hit=2668 read=105
  • I/O Timings: read=7928.333
23. 0.325 1.671 ↑ 1.6 304 1

Nested Loop (cost=0.85..492.38 rows=480 width=8) (actual time=0.053..1.671 rows=304 loops=1)

  • Output: ss1.id, strg.res_group_id
  • Buffers: shared hit=205
24. 0.139 0.139 ↑ 1.6 17 1

Index Scan using idx_sr_timetable_res_group on public.sr_timetable_res_group strg (cost=0.42..76.47 rows=27 width=8) (actual time=0.027..0.139 rows=17 loops=1)

  • Output: strg.id, strg.res_group_id, strg.aud_who, strg.aud_when, strg.aud_source, strg.aud_who_create, strg.aud_when_create, strg.aud_source_create
  • Index Cond: (strg.res_group_id = 58580925)
  • Buffers: shared hit=18
25. 1.207 1.207 ↑ 1.3 18 17

Index Scan using sr_shift_timetable_id_idx on public.sr_shift ss1 (cost=0.43..15.16 rows=24 width=8) (actual time=0.018..0.071 rows=18 loops=17)

  • Output: ss1.id, ss1.bdatetime, ss1.edatetime, ss1.schedule_id, ss1.time_type_id, ss1.timetable_id, ss1.schedule_kind_id, ss1.aud_who, ss1.aud_when, ss1.aud_source, ss1.aud_who_create, ss1.aud_when_create, ss1.aud_source_create
  • Index Cond: (ss1.timetable_id = strg.id)
  • Buffers: shared hit=187
26. 7,939.568 7,939.568 ↓ 0.0 0 304

Index Scan using sr_session_shift_id_idx on public.sr_session ss (cost=0.56..8.32 rows=1 width=17) (actual time=26.117..26.117 rows=0 loops=304)

  • Output: ss.id, ss.bdatetime, ss.edatetime, ss.time_type_id, ss.shift_id, ss.is_house_call, ss.aud_who, ss.aud_when, ss.aud_source, ss.aud_who_create, ss.aud_when_create, ss.aud_source_create, ss.eboard_prohibited, ss.session_number, ss.ticket_counter
  • Index Cond: (ss.shift_id = ss1.id)
  • Filter: ss.is_house_call
  • Rows Removed by Filter: 17
  • Buffers: shared hit=2463 read=105
  • I/O Timings: read=7928.333
27. 0.000 0.000 ↓ 0.0 0

Index Only Scan using sr_session_ticket_pk on public.sr_session_ticket sst (cost=0.44..3.15 rows=2 width=8) (never executed)

  • Output: sst.session_id, sst.ticket_id
  • Index Cond: (sst.session_id = ss.id)
  • Heap Fetches: 0
28. 0.000 0.000 ↓ 0.0 0

Index Scan using md_appointment_pk on public.md_appointment ma (cost=0.56..0.69 rows=1 width=32) (never executed)

  • Output: ma.id, ma.srv_rendered_id, ma.executor_id, ma.bdatetime, ma.customer_id, ma.funding_id, ma.state_id, ma.service_id, ma.cancel_reason_id, ma.care_regimen_id, ma.care_level_id, ma.referral_id, ma.source_id, ma.registrator_id, ma.registration_dt, ma.edatetime, ma.is_urgent, ma.notif_contact, ma.price_list_id, ma.message, ma.queue_state_id, ma.queue_change_dt, ma.ticket_number, ma.price, ma.init_goal_id, ma.organization_id, ma.is_house_call, ma.comment, ma.aud_who, ma.aud_when, ma.aud_source, ma.aud_who_create, ma.aud_when_create, ma.aud_source_create, ma.reject_comment, ma.reject_reason_id, ma.way_id, ma.is_cito, ma.event_id, ma.is_send_message, ma.comment_send_message, ma.cancel_dt, ma.answer_portal_code
  • Index Cond: (ma.id = sst.ticket_id)
29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using sr_ticket_pk on public.sr_ticket st (cost=0.44..0.47 rows=1 width=4) (never executed)

  • Output: st.id
  • Index Cond: (st.id = sst.ticket_id)
  • Heap Fetches: 0
30. 0.000 0.000 ↓ 0.0 0

Index Scan using sr_srv_rendered_pk on public.sr_srv_rendered ssr (cost=0.57..0.77 rows=1 width=17) (never executed)

  • Output: ssr.id, ssr.bdate, ssr.comment, ssr.cost, ssr.duration, ssr.edate, ssr.is_rendered, ssr.quantity, ssr.total_cost, ssr.contract_id, ssr.customer_id, ssr.funding_id, ssr.res_group_id, ssr.service_id, ssr.duration_measure_unit_id, ssr.begin_time, ssr.prototype_id, ssr.org_id, ssr.price_list_id, ssr.cul, ssr.parent_id, ssr.payment_status_id, ssr.root_service_id, ssr.is_wholly_rendered, ssr.planned_date, ssr.planned_time, ssr.tooth_number, ssr.is_refused, ssr.aud_who, ssr.aud_when, ssr.aud_source, ssr.aud_who_create, ssr.aud_when_create, ssr.aud_source_create, ssr.is_amalgam_filling, ssr.is_mobile_medical_teams, ssr.close_date, ssr.complex_srv_id, ssr.for_upload, ssr.uid, ssr.is_need_consulting
  • Index Cond: (ma.srv_rendered_id = ssr.id)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using md_house_call_pk on public.md_house_call mhc (cost=0.29..0.40 rows=1 width=231) (never executed)

  • Output: mhc.id, mhc.address_id, mhc.entryphone, mhc.district_id, mhc.category_id, mhc.call_reason_id, mhc.temperature, mhc.complaints, mhc.contact, mhc.is_executor_assigned, mhc.result_id, mhc.deviation_reason_id, mhc.name, mhc.patr_name, mhc.surname, mhc.address_text, mhc.aud_who, mhc.aud_when, mhc.aud_source, mhc.aud_who_create, mhc.aud_when_create, mhc.aud_source_create, mhc.house_call_number, mhc.emergency, mhc.actual_appointment_id, mhc.actual_assignment_id, mhc.date, mhc.organization_id, mhc.patient_id, mhc.executor_assign_date, mhc.source_id, mhc.call_reason_text, mhc.age, mhc.age_measure_id, mhc.gender_id, mhc.result_comment
  • Index Cond: (mhc.id = mha.house_call_id)
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.md_house_call_category mhcc (cost=0.00..1.07 rows=7 width=32) (never executed)

  • Output: mhcc.id, mhcc.name, mhcc.code, mhcc.e_code, mhcc.aud_who, mhcc.aud_when, mhcc.aud_source, mhcc.aud_who_create, mhcc.aud_when_create, mhcc.aud_source_create, mhcc.from_dt, mhcc.to_dt, mhcc.close_without_visit
33. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_individual_pk on public.pim_individual pi (cost=0.43..0.45 rows=1 width=57) (never executed)

  • Output: pi.id, pi.birth_dt, pi.death_dt, pi.has_citizenship, pi.name, pi.patr_name, pi.surname, pi.gender_id, pi.nationality_id, pi.list_identity_doc, pi.list_oms_doc, pi.list_job_org, pi.list_reg_name, pi.list_snils, pi.list_uid, pi.aud_who, pi.aud_when, pi.aud_source, pi.aud_who_create, pi.aud_when_create, pi.aud_source_create, pi.birth_place, pi.age_year, pi.age_month, pi.age_day, pi.list_main_contact, pi.is_only_birth_year
  • Index Cond: (ma.customer_id = pi.id)
34. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.md_house_call_reason mhcre (cost=0.00..1.07 rows=7 width=35) (never executed)

  • Output: mhcre.id, mhcre.name, mhcre.code, mhcre.e_code, mhcre.aud_who, mhcre.aud_when, mhcre.aud_source, mhcre.aud_who_create, mhcre.aud_when_create, mhcre.aud_source_create, mhcre.from_dt, mhcre.to_dt
35.          

SubPlan (for Nested Loop Left Join)

36. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.26 rows=1 width=0) (never executed)

  • Output: adr__get_element_as_text(mhc.address_id, '(4,s,0)(6,s,0)(7,s,0)(8,s,0)'::text)
Planning time : 65.909 ms
Execution time : 7,947.352 ms