explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JPIP

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 1,141,986.960 ↑ 1.0 50 1

Limit (cost=7,275,078.80..7,275,078.93 rows=50 width=3,993) (actual time=1,141,986.919..1,141,986.960 rows=50 loops=1)

  • Output: item.bill_id, item.id, ((SubPlan 7)), ((SubPlan 8)), ((SubPlan 1)), ((SubPlan 2)), (CASE WHEN ((c_1.case_type_code)::text = '2'::text) THEN (((s.hsp_department_code)::text || ', '::text) || (s.hsp_department_name)::text) ELSE (((pim_department.code)::text || ', '::text) || (pim_department.name)::text) END), item.tariff, (COALESCE(s.tariff_code, ''::character varying)), (round(item.price, 2)), ((s.srv_rendered_quantity)::integer), s.patient_id, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), item.service_id, s.service_name, fin_bill_patients.pat_birth_dt, ((COALESCE(((fin_bill_policy.series)::text || ' '::text), ''::text) || (COALESCE(fin_bill_policy.number, ''::character varying))::text)), (COALESCE(fin_bill_policy.issuer_short_name, ''::character varying)), s.case_id, c_1.uid, ((SubPlan 9)), s.step_admission_date, (COALESCE(((SubPlan 4)), ((SubPlan 10)))), (COALESCE(((SubPlan 6)), ((SubPlan 11)), ''::text)), ((c_1.region_data -> 'position_name'::text)), (((((((((n.surname)::text || ' '::text) || upper("left"((n.name)::text, 1))) || '.'::text) || COALESCE(((' '::text || upper("left"((n.patr_name)::text, 1))) || '.'::text), ''::text))) || ', '::text) || (pim_position.name)::text)), item.is_deleted, (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), (COALESCE(status.code, ''::character varying)), item.correctional_bill_id, item.comment, (CASE WHEN (((s.region_data -> 'item_type'::text)) = 'dentist'::text) THEN (round(fbg.quantity, 2)) ELSE NULL::numeric END), (CASE WHEN ((c.care_regimen_code)::text = ANY ('{3,4,5}'::text[])) THEN ((c.close_date - c.open_date) + 1) WHEN ((c.care_regimen_code)::text = '2'::text) THEN COALESCE(NULLIF((c.close_date - c.open_date), 0), 1) ELSE NULL::integer END), (CASE WHEN (SubPlan 3) THEN ((('Услуга добавлена в счет '::text || (add_case.add_date)::text) || ' пользователем '::text) || (add_case.user_login)::text) ELSE NULL::text END), (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, bill.generated, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), ((((pi.surname)::text || ' '::text) || (pi.name)::text)), f.code, ((c_1.region_data -> '_bedn_prof_record'::text)), ((((COALESCE(mpm.code, ''::character varying))::text || ' '::text) || (COALESCE(mpm.name, ''::character varying))::text)), ((SubPlan 12))
  • Buffers: shared hit=6941247 read=164418 dirtied=147
  • I/O Timings: read=734596.575
2. 350.270 1,141,986.948 ↑ 263.5 50 1

Sort (cost=7,275,078.80..7,275,111.74 rows=13,176 width=3,993) (actual time=1,141,986.917..1,141,986.948 rows=50 loops=1)

  • Output: item.bill_id, item.id, ((SubPlan 7)), ((SubPlan 8)), ((SubPlan 1)), ((SubPlan 2)), (CASE WHEN ((c_1.case_type_code)::text = '2'::text) THEN (((s.hsp_department_code)::text || ', '::text) || (s.hsp_department_name)::text) ELSE (((pim_department.code)::text || ', '::text) || (pim_department.name)::text) END), item.tariff, (COALESCE(s.tariff_code, ''::character varying)), (round(item.price, 2)), ((s.srv_rendered_quantity)::integer), s.patient_id, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), item.service_id, s.service_name, fin_bill_patients.pat_birth_dt, ((COALESCE(((fin_bill_policy.series)::text || ' '::text), ''::text) || (COALESCE(fin_bill_policy.number, ''::character varying))::text)), (COALESCE(fin_bill_policy.issuer_short_name, ''::character varying)), s.case_id, c_1.uid, ((SubPlan 9)), s.step_admission_date, (COALESCE(((SubPlan 4)), ((SubPlan 10)))), (COALESCE(((SubPlan 6)), ((SubPlan 11)), ''::text)), ((c_1.region_data -> 'position_name'::text)), (((((((((n.surname)::text || ' '::text) || upper("left"((n.name)::text, 1))) || '.'::text) || COALESCE(((' '::text || upper("left"((n.patr_name)::text, 1))) || '.'::text), ''::text))) || ', '::text) || (pim_position.name)::text)), item.is_deleted, (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), (COALESCE(status.code, ''::character varying)), item.correctional_bill_id, item.comment, (CASE WHEN (((s.region_data -> 'item_type'::text)) = 'dentist'::text) THEN (round(fbg.quantity, 2)) ELSE NULL::numeric END), (CASE WHEN ((c.care_regimen_code)::text = ANY ('{3,4,5}'::text[])) THEN ((c.close_date - c.open_date) + 1) WHEN ((c.care_regimen_code)::text = '2'::text) THEN COALESCE(NULLIF((c.close_date - c.open_date), 0), 1) ELSE NULL::integer END), (CASE WHEN (SubPlan 3) THEN ((('Услуга добавлена в счет '::text || (add_case.add_date)::text) || ' пользователем '::text) || (add_case.user_login)::text) ELSE NULL::text END), (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, bill.generated, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), ((((pi.surname)::text || ' '::text) || (pi.name)::text)), f.code, ((c_1.region_data -> '_bedn_prof_record'::text)), ((((COALESCE(mpm.code, ''::character varying))::text || ' '::text) || (COALESCE(mpm.name, ''::character varying))::text)), ((SubPlan 12))
  • Sort Key: s.service_name, item.id
  • Sort Method: top-N heapsort Memory: 81kB
  • Buffers: shared hit=6941247 read=164418 dirtied=147
  • I/O Timings: read=734596.575
3. 749.519 1,141,636.678 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=44.01..7,274,641.11 rows=13,176 width=3,993) (actual time=590.724..1,141,636.678 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, ((SubPlan 7)), ((SubPlan 8)), (SubPlan 1), (SubPlan 2), CASE WHEN ((c_1.case_type_code)::text = '2'::text) THEN (((s.hsp_department_code)::text || ', '::text) || (s.hsp_department_name)::text) ELSE (((pim_department.code)::text || ', '::text) || (pim_department.name)::text) END, item.tariff, (COALESCE(s.tariff_code, ''::character varying)), round(item.price, 2), (s.srv_rendered_quantity)::integer, s.patient_id, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), item.service_id, s.service_name, fin_bill_patients.pat_birth_dt, (COALESCE(((fin_bill_policy.series)::text || ' '::text), ''::text) || (COALESCE(fin_bill_policy.number, ''::character varying))::text), COALESCE(fin_bill_policy.issuer_short_name, ''::character varying), s.case_id, c_1.uid, ((SubPlan 9)), s.step_admission_date, COALESCE(((SubPlan 4)), ((SubPlan 10))), COALESCE(((SubPlan 6)), ((SubPlan 11)), ''::text), ((c_1.region_data -> 'position_name'::text)), ((((((((n.surname)::text || ' '::text) || upper("left"((n.name)::text, 1))) || '.'::text) || COALESCE(((' '::text || upper("left"((n.patr_name)::text, 1))) || '.'::text), ''::text))) || ', '::text) || (pim_position.name)::text), item.is_deleted, (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), COALESCE(status.code, ''::character varying), item.correctional_bill_id, item.comment, CASE WHEN (((s.region_data -> 'item_type'::text)) = 'dentist'::text) THEN (round(fbg.quantity, 2)) ELSE NULL::numeric END, CASE WHEN ((c.care_regimen_code)::text = ANY ('{3,4,5}'::text[])) THEN ((c.close_date - c.open_date) + 1) WHEN ((c.care_regimen_code)::text = '2'::text) THEN COALESCE(NULLIF((c.close_date - c.open_date), 0), 1) ELSE NULL::integer END, CASE WHEN (SubPlan 3) THEN ((('Услуга добавлена в счет '::text || (add_case.add_date)::text) || ' пользователем '::text) || (add_case.user_login)::text) ELSE NULL::text END, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, bill.generated, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), ((((pi.surname)::text || ' '::text) || (pi.name)::text)), f.code, ((c_1.region_data -> '_bedn_prof_record'::text)), ((((COALESCE(mpm.code, ''::character varying))::text || ' '::text) || (COALESCE(mpm.name, ''::character varying))::text)), ((SubPlan 12))
  • Buffers: shared hit=6941241 read=164418 dirtied=147
  • I/O Timings: read=734596.575
4. 144.615 1,134,016.217 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=44.01..7,216,403.19 rows=13,176 width=3,962) (actual time=410.949..1,134,016.217 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), add_case.add_date, add_case.user_login, ((SubPlan 7)), ((SubPlan 8)), c_1.case_type_code, c_1.uid, ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((c_1.region_data -> 'position_name'::text)), ((c_1.region_data -> '_bedn_prof_record'::text)), ((SubPlan 12)), c_1.payment_method_code, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), fin_bill_patients.pat_birth_dt, fin_bill_policy.series, fin_bill_policy.number, fin_bill_policy.issuer_short_name, pim_position.name, pim_department.code, pim_department.name, ((((((n.surname)::text || ' '::text) || upper("left"((n.name)::text, 1))) || '.'::text) || COALESCE(((' '::text || upper("left"((n.patr_name)::text, 1))) || '.'::text), ''::text))), ((((pi.surname)::text || ' '::text) || (pi.name)::text)), f.code
  • Buffers: shared hit=6673500 read=164254 dirtied=147
  • I/O Timings: read=733507.627
5. 148.243 1,132,638.356 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=44.01..7,081,579.77 rows=13,176 width=3,962) (actual time=410.805..1,132,638.356 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), add_case.add_date, add_case.user_login, ((SubPlan 7)), ((SubPlan 8)), c_1.case_type_code, c_1.uid, ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((c_1.region_data -> 'position_name'::text)), ((c_1.region_data -> '_bedn_prof_record'::text)), ((SubPlan 12)), c_1.last_profile_code, c_1.payment_method_code, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), fin_bill_patients.pat_birth_dt, fin_bill_policy.series, fin_bill_policy.number, fin_bill_policy.issuer_short_name, pim_position.name, pim_department.code, pim_department.name, ((((((n.surname)::text || ' '::text) || upper("left"((n.name)::text, 1))) || '.'::text) || COALESCE(((' '::text || upper("left"((n.patr_name)::text, 1))) || '.'::text), ''::text))), ((((pi.surname)::text || ' '::text) || (pi.name)::text))
  • Buffers: shared hit=6568145 read=164254 dirtied=147
  • I/O Timings: read=733507.627
6. 84.596 507,821.651 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=5.28..6,365,275.88 rows=13,176 width=3,930) (actual time=342.291..507,821.651 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), add_case.add_date, add_case.user_login, ((SubPlan 7)), ((SubPlan 8)), c_1.case_type_code, c_1.uid, ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((c_1.region_data -> 'position_name'::text)), ((c_1.region_data -> '_bedn_prof_record'::text)), ((SubPlan 12)), c_1.last_profile_code, c_1.payment_method_code, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), fin_bill_patients.pat_birth_dt, fin_bill_policy.series, fin_bill_policy.number, fin_bill_policy.issuer_short_name, pim_position.name, pim_department.code, pim_department.name, ((((((n.surname)::text || ' '::text) || upper("left"((n.name)::text, 1))) || '.'::text) || COALESCE(((' '::text || upper("left"((n.patr_name)::text, 1))) || '.'::text), ''::text)))
  • Buffers: shared hit=2039499 read=112670 dirtied=133
  • I/O Timings: read=491446.137
7. 75.379 505,446.741 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=4.55..6,143,721.44 rows=13,176 width=3,903) (actual time=330.033..505,446.741 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), s.doctor_code, add_case.add_date, add_case.user_login, ((SubPlan 7)), ((SubPlan 8)), c_1.case_type_code, c_1.uid, ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((c_1.region_data -> 'position_name'::text)), ((c_1.region_data -> '_bedn_prof_record'::text)), ((SubPlan 12)), c_1.last_profile_code, c_1.payment_method_code, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), fin_bill_patients.pat_birth_dt, fin_bill_policy.series, fin_bill_policy.number, fin_bill_policy.issuer_short_name, pim_position.name, pim_department.code, pim_department.name
  • Buffers: shared hit=1834253 read=112518 dirtied=133
  • I/O Timings: read=490411.662
8. 91.079 505,048.369 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=4.27..6,034,097.12 rows=13,176 width=3,846) (actual time=329.952..505,048.369 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), s.doctor_code, add_case.add_date, add_case.user_login, ((SubPlan 7)), ((SubPlan 8)), c_1.case_type_code, c_1.uid, ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((c_1.region_data -> 'position_name'::text)), ((c_1.region_data -> '_bedn_prof_record'::text)), ((SubPlan 12)), c_1.last_profile_code, c_1.payment_method_code, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), fin_bill_patients.pat_birth_dt, fin_bill_policy.series, fin_bill_policy.number, fin_bill_policy.issuer_short_name, pim_position.name, pim_position.department_id
  • Buffers: shared hit=1746249 read=112514 dirtied=133
  • I/O Timings: read=490411.600
9. 99.476 504,516.845 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=3.85..5,922,628.16 rows=13,176 width=3,850) (actual time=320.632..504,516.845 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), s.service_id, s.doctor_code, add_case.add_date, add_case.user_login, ((SubPlan 7)), ((SubPlan 8)), c_1.case_type_code, c_1.uid, ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((c_1.region_data -> 'position_name'::text)), ((c_1.region_data -> '_bedn_prof_record'::text)), ((SubPlan 12)), c_1.last_profile_code, c_1.payment_method_code, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), fin_bill_patients.pat_birth_dt, fin_bill_policy.series, fin_bill_policy.number, fin_bill_policy.issuer_short_name, pim_position.name, pim_position.department_id
  • Buffers: shared hit=1639197 read=112505 dirtied=133
  • I/O Timings: read=490380.092
10. 84.962 504,094.376 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=3.56..5,812,937.96 rows=13,176 width=3,800) (actual time=320.237..504,094.376 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), s.position_id, s.service_id, s.doctor_code, add_case.add_date, add_case.user_login, ((SubPlan 7)), ((SubPlan 8)), c_1.case_type_code, c_1.uid, ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((c_1.region_data -> 'position_name'::text)), ((c_1.region_data -> '_bedn_prof_record'::text)), ((SubPlan 12)), c_1.last_profile_code, c_1.payment_method_code, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), fin_bill_patients.pat_birth_dt, fin_bill_policy.series, fin_bill_policy.number, fin_bill_policy.issuer_short_name
  • Buffers: shared hit=1551221 read=112473 dirtied=133
  • I/O Timings: read=490367.660
11. 100.937 502,658.716 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=3.12..5,701,271.36 rows=13,176 width=3,747) (actual time=291.857..502,658.716 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), s.position_id, s.service_id, s.doctor_code, add_case.add_date, add_case.user_login, ((SubPlan 7)), ((SubPlan 8)), c_1.case_type_code, c_1.uid, ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((c_1.region_data -> 'position_name'::text)), ((c_1.region_data -> '_bedn_prof_record'::text)), ((SubPlan 12)), c_1.active_policy_id, c_1.last_profile_code, c_1.payment_method_code, ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), fin_bill_patients.pat_birth_dt
  • Buffers: shared hit=1434456 read=112022 dirtied=133
  • I/O Timings: read=489509.823
12. 108.340 498,916.767 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=2.56..5,587,826.00 rows=13,176 width=3,718) (actual time=227.145..498,916.767 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), s.id_pac, s.position_id, s.service_id, s.doctor_code, add_case.add_date, add_case.user_login, ((SubPlan 7)), ((SubPlan 8)), c_1.case_type_code, c_1.uid, ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((c_1.region_data -> 'position_name'::text)), ((c_1.region_data -> '_bedn_prof_record'::text)), ((SubPlan 12)), c_1.active_policy_id, c_1.last_profile_code, c_1.payment_method_code
  • Buffers: shared hit=1288319 read=111342 dirtied=133
  • I/O Timings: read=486789.999
13. 635.955 495,960.216 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=2.13..5,274,731.30 rows=13,176 width=1,026) (actual time=226.862..495,960.216 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), s.id_pac, s.position_id, s.service_id, s.doctor_code, add_case.add_date, add_case.user_login
  • Join Filter: (s.case_id = add_case.case_id)
  • Rows Removed by Join Filter: 2495855
  • Buffers: shared hit=930672 read=111342 dirtied=133
  • I/O Timings: read=486789.999
14. 101.534 495,030.631 ↓ 2.2 29,363 1

Nested Loop Left Join (cost=2.13..5,258,917.10 rows=13,176 width=1,011) (actual time=226.703..495,030.631 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code, s.hsp_department_code, s.hsp_department_name, (COALESCE(s.tariff_code, ''::character varying)), s.srv_rendered_quantity, s.patient_id, s.service_name, s.case_id, s.step_admission_date, ((SubPlan 4)), ((SubPlan 6)), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), ((s.region_data -> 'item_type'::text)), (round(fbg.quantity, 2)), c.care_regimen_code, c.close_date, c.open_date, (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), s.id_pac, s.position_id, s.service_id, s.doctor_code
  • Buffers: shared hit=930671 read=111341 dirtied=133
  • I/O Timings: read=486789.974
15. 116.619 5,095.531 ↓ 2.2 29,363 1

Nested Loop (cost=0.56..11,990.95 rows=13,176 width=110) (actual time=58.236..5,095.531 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, bill.generated, status.code
  • Join Filter: (item.status_id = status.id)
  • Rows Removed by Join Filter: 88089
  • Buffers: shared hit=420 read=819
  • I/O Timings: read=4784.025
16. 50.656 4,920.186 ↓ 2.2 29,363 1

Nested Loop (cost=0.56..11,199.34 rows=13,176 width=103) (actual time=58.203..4,920.186 rows=29,363 loops=1)

  • Output: item.bill_id, item.id, item.tariff, item.price, item.service_id, item.is_deleted, item.correctional_bill_id, item.comment, item.status_id, bill.generated
  • Buffers: shared hit=419 read=819
  • I/O Timings: read=4784.025
17. 1.888 1.888 ↑ 1.0 1 1

Seq Scan on public.fin_bill bill (cost=0.00..168.03 rows=1 width=8) (actual time=1.604..1.888 rows=1 loops=1)

  • Output: bill.id, bill.comment, bill.date, bill.package_number, bill.status_id, bill.number, bill.generated, bill.task_id, bill.aud_who, bill.aud_when, bill.aud_source, bill.aud_who_create, bill.aud_when_create, bill.aud_source_create
  • Filter: (bill.id = 6287)
  • Rows Removed by Filter: 4067
  • Buffers: shared hit=108
18. 4,867.642 4,867.642 ↓ 2.2 29,363 1

Index Scan using fin_bill_spec_item_bill_id_idx on public.fin_bill_spec_item item (cost=0.56..10,899.56 rows=13,176 width=99) (actual time=56.585..4,867.642 rows=29,363 loops=1)

  • Output: item.id, item.price, item.tariff, item.bill_id, item.service_id, item.number, item.is_deleted, item.comment, item.doc_id, item.status_id, item.correctional_bill_id
  • Index Cond: (item.bill_id = 6287)
  • Buffers: shared hit=311 read=819
  • I/O Timings: read=4784.025
19. 58.716 58.726 ↑ 1.0 4 29,363

Materialize (cost=0.00..1.06 rows=4 width=15) (actual time=0.000..0.002 rows=4 loops=29,363)

  • Output: status.code, status.id
  • Buffers: shared hit=1
20. 0.010 0.010 ↑ 1.0 4 1

Seq Scan on public.fin_bill_payment_status status (cost=0.00..1.04 rows=4 width=15) (actual time=0.006..0.010 rows=4 loops=1)

  • Output: status.code, status.id
  • Buffers: shared hit=1
21. 88.089 489,833.566 ↑ 1.0 1 29,363

Limit (cost=1.56..398.20 rows=1 width=1,035) (actual time=16.682..16.682 rows=1 loops=29,363)

  • Output: s.srv_rendered_quantity, s.service_id, s.service_name, s.case_id, s.step_admission_date, s.position_id, s.doctor_code, s.id_pac, s.hsp_department_name, s.hsp_department_code, c.close_date, c.open_date, c.care_regimen_code, ((SubPlan 4)), (NULL::integer), (round(fbg.quantity, 2)), (NULL::integer), ((s.region_data -> 'item_type'::text)), (CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END), ((SubPlan 6)), s.patient_id, s.step_outcome_date, ((s.region_data -> 'csg_id'::text)), ((fbg.region_data -> 'f_ksg_code'::text)), (CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END), (COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false)), (NULL::character varying), (COALESCE(s.tariff_code, ''::character varying)), (NULL::integer)
  • Buffers: shared hit=930251 read=110522 dirtied=133
  • I/O Timings: read=482005.949
22. 5,109.162 489,745.477 ↑ 1.0 1 29,363

Nested Loop (cost=1.56..398.20 rows=1 width=1,035) (actual time=16.679..16.679 rows=1 loops=29,363)

  • Output: s.srv_rendered_quantity, s.service_id, s.service_name, s.case_id, s.step_admission_date, s.position_id, s.doctor_code, s.id_pac, s.hsp_department_name, s.hsp_department_code, c.close_date, c.open_date, c.care_regimen_code, (SubPlan 4), NULL::integer, round(fbg.quantity, 2), NULL::integer, (s.region_data -> 'item_type'::text), CASE WHEN (s.patient_id = (SubPlan 5)) THEN 'Да'::text ELSE 'Нет'::text END, (SubPlan 6), s.patient_id, s.step_outcome_date, (s.region_data -> 'csg_id'::text), (fbg.region_data -> 'f_ksg_code'::text), CASE WHEN ((s.region_data -> 'prikrep'::text) = 'true'::text) THEN 'Да'::text ELSE 'Нет'::text END, COALESCE(((s.region_data -> 'is_exceeded'::text))::boolean, false), NULL::character varying, COALESCE(s.tariff_code, ''::character varying), NULL::integer
  • Buffers: shared hit=930251 read=110522 dirtied=133
  • I/O Timings: read=482005.949
23. 293.630 136,684.765 ↑ 1.0 1 29,363

Nested Loop (cost=1.00..17.05 rows=1 width=981) (actual time=4.655..4.655 rows=1 loops=29,363)

  • Output: s.srv_rendered_quantity, s.service_id, s.service_name, s.case_id, s.step_admission_date, s.position_id, s.doctor_code, s.id_pac, s.hsp_department_name, s.hsp_department_code, s.step_id, s.region_data, s.patient_id, s.step_main_diagnosis_id, s.step_outcome_date, s.tariff_code, c.close_date, c.open_date, c.care_regimen_code
  • Buffers: shared hit=228327 read=35944 dirtied=115
  • I/O Timings: read=135010.762
24. 118,479.705 118,479.705 ↑ 1.0 1 29,363

Index Scan using fin_bill_steps_srv_rendered_id_bill_id_idx on billing.fin_bill_steps s (cost=0.56..8.58 rows=1 width=975) (actual time=4.035..4.035 rows=1 loops=29,363)

  • Output: s.srv_rendered_quantity, s.service_id, s.service_name, s.case_id, s.step_admission_date, s.position_id, s.doctor_code, s.id_pac, s.hsp_department_name, s.hsp_department_code, s.step_id, s.region_data, s.patient_id, s.step_main_diagnosis_id, s.step_outcome_date, s.tariff_code, s.bill_id
  • Index Cond: ((s.srv_rendered_id = item.service_id) AND (s.bill_id = item.bill_id))
  • Buffers: shared hit=117053 read=29766 dirtied=71
  • I/O Timings: read=117690.036
25. 17,911.430 17,911.430 ↑ 1.0 1 29,363

Index Scan using fin_bill_cases_case_id_bill_id_idx on billing.fin_bill_cases c (cost=0.44..8.46 rows=1 width=18) (actual time=0.610..0.610 rows=1 loops=29,363)

  • Output: c.close_date, c.open_date, c.care_regimen_code, c.case_id, c.bill_id
  • Index Cond: ((c.case_id = s.case_id) AND (c.bill_id = item.bill_id))
  • Buffers: shared hit=111274 read=6178 dirtied=44
  • I/O Timings: read=17320.726
26. 201,811.899 201,811.899 ↑ 49.0 1 29,363

Index Scan using fin_bill_generate_step_id_bill_id_idx on billing.fin_bill_generate fbg (cost=0.56..336.68 rows=49 width=58) (actual time=6.873..6.873 rows=1 loops=29,363)

  • Output: fbg.quantity, fbg.region_data, fbg.step_id
  • Index Cond: (fbg.step_id = s.step_id)
  • Filter: (NOT fbg.is_sifted)
  • Buffers: shared hit=113155 read=33668
  • I/O Timings: read=200952.501
27.          

SubPlan (for Nested Loop)

28. 264.267 49,682.196 ↑ 1.0 1 29,363

Limit (cost=8.47..9.72 rows=1 width=23) (actual time=1.692..1.692 rows=1 loops=29,363)

  • Output: n_1.name
  • Buffers: shared hit=121389 read=16098
  • I/O Timings: read=47846.595
29. 566.042 49,417.929 ↑ 1.0 1 29,363

Hash Join (cost=8.47..9.72 rows=1 width=23) (actual time=1.683..1.683 rows=1 loops=29,363)

  • Output: n_1.name
  • Hash Cond: (n_1.id = t.outcome_id)
  • Buffers: shared hit=121389 read=16098
  • I/O Timings: read=47846.595
30. 138.670 138.670 ↑ 1.4 13 19,810

Seq Scan on public.mc_step_care_result n_1 (cost=0.00..1.18 rows=18 width=27) (actual time=0.006..0.007 rows=13 loops=19,810)

  • Output: n_1.id, n_1.code, n_1.name, n_1.e_code, n_1.aud_who, n_1.aud_when, n_1.aud_source, n_1.aud_who_create, n_1.aud_when_create, n_1.aud_source_create, n_1.from_dt, n_1.to_dt, n_1.billing_code
  • Buffers: shared hit=19810
31. 146.815 48,713.217 ↑ 1.0 1 29,363

Hash (cost=8.46..8.46 rows=1 width=4) (actual time=1.659..1.659 rows=1 loops=29,363)

  • Output: t.outcome_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=101579 read=16098
  • I/O Timings: read=47846.595
32. 48,566.402 48,566.402 ↑ 1.0 1 29,363

Index Scan using mc_step_pk on public.mc_step t (cost=0.44..8.46 rows=1 width=4) (actual time=1.652..1.654 rows=1 loops=29,363)

  • Output: t.outcome_id
  • Index Cond: (t.id = s.step_id)
  • Buffers: shared hit=101579 read=16098
  • I/O Timings: read=47846.595
33. 29.363 42,576.350 ↓ 0.0 0 29,363

Limit (cost=1.27..17.33 rows=1 width=4) (actual time=1.450..1.450 rows=0 loops=29,363)

  • Output: pci.patient_id
  • Buffers: shared hit=171370 read=10723 dirtied=18
  • I/O Timings: read=41543.220
34. 112.001 42,546.987 ↓ 0.0 0 29,363

Nested Loop (cost=1.27..33.39 rows=2 width=4) (actual time=1.449..1.449 rows=0 loops=29,363)

  • Output: pci.patient_id
  • Buffers: shared hit=171370 read=10723 dirtied=18
  • I/O Timings: read=41543.220
35. 8,838.263 8,838.263 ↓ 0.0 0 29,363

Index Scan using pci_dispensary_patient_id_idx on public.pci_dispensary pci (cost=0.42..16.47 rows=2 width=4) (actual time=0.295..0.301 rows=0 loops=29,363)

  • Output: pci.id, pci.reg_in_dt, pci.reg_out_dt, pci.diagnosis_id, pci.reg_in_doctor_id, pci.reg_out_doctor_id, pci.med_case_in_id, pci.med_case_out_id, pci.patient_id, pci.reg_out_reason_id, pci.dispensary_group_id, pci.srv_rendered_id, pci.nosol_registr_id, pci.reg_stage_id, pci.clinic_id, pci.aud_who, pci.aud_when, pci.aud_source, pci.aud_who_create, pci.aud_when_create, pci.aud_source_create, pci.is_integrated, pci.periodicity_id
  • Index Cond: (pci.patient_id = s.patient_id)
  • Filter: (pci.reg_out_dt IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=97953 read=2631 dirtied=18
  • I/O Timings: read=8447.572
36. 46.423 33,596.723 ↑ 1.0 1 11,743

Materialize (cost=0.85..16.90 rows=1 width=0) (actual time=2.861..2.861 rows=1 loops=11,743)

  • Buffers: shared hit=73417 read=8092
  • I/O Timings: read=33095.648
37. 82.897 33,550.300 ↑ 1.0 1 11,690

Nested Loop (cost=0.85..16.90 rows=1 width=0) (actual time=2.870..2.870 rows=1 loops=11,690)

  • Buffers: shared hit=73417 read=8092
  • I/O Timings: read=33095.648
38. 33,339.880 33,339.880 ↑ 1.0 1 11,690

Index Scan using mc_diagnosis_pk on public.mc_diagnosis mc (cost=0.56..8.58 rows=1 width=4) (actual time=2.852..2.852 rows=1 loops=11,690)

  • Output: mc.diagnos_id
  • Index Cond: (mc.id = s.step_main_diagnosis_id)
  • Buffers: shared hit=49903 read=8090
  • I/O Timings: read=33095.612
39. 127.523 127.523 ↑ 1.0 1 11,593

Index Only Scan using md_diagnosis_pk on public.md_diagnosis md (cost=0.29..8.30 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=11,593)

  • Output: md.id
  • Index Cond: (md.id = mc.diagnos_id)
  • Heap Fetches: 228
  • Buffers: shared hit=23514 read=2
  • I/O Timings: read=0.036
40. 29.363 53,881.105 ↑ 1.0 1 29,363

Limit (cost=0.85..16.90 rows=1 width=122) (actual time=1.835..1.835 rows=1 loops=29,363)

  • Output: ((((d.code)::text || ' '::text) || (d.name)::text))
  • Buffers: shared hit=221634 read=12665
  • I/O Timings: read=52839.052
41. 266.082 53,851.742 ↑ 1.0 1 29,363

Nested Loop (cost=0.85..16.90 rows=1 width=122) (actual time=1.834..1.834 rows=1 loops=29,363)

  • Output: (((d.code)::text || ' '::text) || (d.name)::text)
  • Buffers: shared hit=221634 read=12665
  • I/O Timings: read=52839.052
42. 53,264.482 53,264.482 ↑ 1.0 1 29,363

Index Scan using mc_diagnosis_pk on public.mc_diagnosis m (cost=0.56..8.58 rows=1 width=4) (actual time=1.814..1.814 rows=1 loops=29,363)

  • Output: m.diagnos_id
  • Index Cond: (m.id = s.step_main_diagnosis_id)
  • Buffers: shared hit=133413 read=12655
  • I/O Timings: read=52787.512
43. 321.178 321.178 ↑ 1.0 1 29,198

Index Scan using md_diagnosis_pk on public.md_diagnosis d (cost=0.29..8.30 rows=1 width=126) (actual time=0.011..0.011 rows=1 loops=29,198)

  • Output: d.id, d.code, d.name, d.parent_id, d.is_injury, d.level, d.is_leaf, d.e_code, d.oms, d.stomat, d.aud_who, d.aud_when, d.aud_source, d.aud_who_create, d.aud_when_create, d.aud_source_create, d.from_dt, d.to_dt
  • Index Cond: (d.id = m.diagnos_id)
  • Buffers: shared hit=88221 read=10
  • I/O Timings: read=51.540
44. 293.560 293.630 ↓ 1.1 85 29,363

Materialize (cost=0.00..3.20 rows=80 width=19) (actual time=0.001..0.010 rows=85 loops=29,363)

  • Output: add_case.add_date, add_case.user_login, add_case.case_id
  • Buffers: shared hit=1 read=1
  • I/O Timings: read=0.025
45. 0.070 0.070 ↓ 1.1 85 1

Seq Scan on public.fin_bill_add_case add_case (cost=0.00..2.80 rows=80 width=19) (actual time=0.011..0.070 rows=85 loops=1)

  • Output: add_case.add_date, add_case.user_login, add_case.case_id
  • Buffers: shared hit=1 read=1
  • I/O Timings: read=0.025
46. 146.815 2,848.211 ↑ 1.0 1 29,363

Limit (cost=0.44..23.74 rows=1 width=377) (actual time=0.097..0.097 rows=1 loops=29,363)

  • Output: c_1.uid, c_1.active_policy_id, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), ((SubPlan 7)), ((SubPlan 8)), ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), (NULL::integer), c_1.case_type_code, c_1.last_profile_code, ((c_1.region_data -> '_bedn_prof_record'::text)), c_1.payment_method_code, (NULL::integer), (NULL::text), (NULL::text), ((c_1.region_data -> 'position_name'::text)), ((SubPlan 12)), (NULL::character varying(255))
  • Buffers: shared hit=357647
47. 998.342 2,701.396 ↑ 1.0 1 29,363

Index Scan using fin_bill_cases_case_id_bill_id_idx on billing.fin_bill_cases c_1 (cost=0.44..23.74 rows=1 width=377) (actual time=0.092..0.092 rows=1 loops=29,363)

  • Output: c_1.uid, c_1.active_policy_id, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, (SubPlan 7), (SubPlan 8), (SubPlan 9), (SubPlan 10), (SubPlan 11), NULL::integer, c_1.case_type_code, c_1.last_profile_code, (c_1.region_data -> '_bedn_prof_record'::text), c_1.payment_method_code, NULL::integer, NULL::text, NULL::text, (c_1.region_data -> 'position_name'::text), (SubPlan 12), NULL::character varying(255)
  • Index Cond: ((c_1.case_id = s.case_id) AND (c_1.bill_id = item.bill_id))
  • Buffers: shared hit=357647
48.          

SubPlan (for Index Scan)

49. 293.630 293.630 ↑ 1.0 1 29,363

Seq Scan on public.mc_care_level (cost=0.00..1.14 rows=1 width=73) (actual time=0.009..0.010 rows=1 loops=29,363)

  • Output: mc_care_level.name
  • Filter: ((mc_care_level.code)::text = (c_1.care_level_code)::text)
  • Rows Removed by Filter: 11
  • Buffers: shared hit=29363
50. 117.452 117.452 ↑ 1.0 1 29,363

Seq Scan on public.md_care_providing_form (cost=0.00..1.04 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=29,363)

  • Output: md_care_providing_form.name
  • Filter: ((md_care_providing_form.code)::text = (c_1.care_providing_form_code)::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=29363
51. 88.089 264.267 ↑ 1.0 1 29,363

Limit (cost=0.00..2.41 rows=1 width=76) (actual time=0.009..0.009 rows=1 loops=29,363)

  • Output: mc_case_init_goal.name
  • Buffers: shared hit=34018
52. 176.178 176.178 ↑ 1.0 1 29,363

Seq Scan on public.mc_case_init_goal (cost=0.00..2.41 rows=1 width=76) (actual time=0.006..0.006 rows=1 loops=29,363)

  • Output: mc_case_init_goal.name
  • Filter: (mc_case_init_goal.id = c_1.init_goal_id)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=34018
53. 88.089 264.267 ↑ 1.0 1 29,363

Limit (cost=0.00..1.23 rows=1 width=23) (actual time=0.009..0.009 rows=1 loops=29,363)

  • Output: mc_step_care_result.name
  • Buffers: shared hit=29363
54. 176.178 176.178 ↑ 1.0 1 29,363

Seq Scan on public.mc_step_care_result (cost=0.00..1.23 rows=1 width=23) (actual time=0.006..0.006 rows=1 loops=29,363)

  • Output: mc_step_care_result.name
  • Filter: (mc_step_care_result.id = c_1.last_outcome_id)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=29363
55. 58.726 264.267 ↑ 1.0 1 29,363

Limit (cost=0.29..8.31 rows=1 width=122) (actual time=0.009..0.009 rows=1 loops=29,363)

  • Output: ((((md_diagnosis.code)::text || ' '::text) || (md_diagnosis.name)::text))
  • Buffers: shared hit=88725
56. 205.541 205.541 ↑ 1.0 1 29,363

Index Scan using md_diagnosis_pk on public.md_diagnosis (cost=0.29..8.31 rows=1 width=122) (actual time=0.007..0.007 rows=1 loops=29,363)

  • Output: (((md_diagnosis.code)::text || ' '::text) || (md_diagnosis.name)::text)
  • Index Cond: (md_diagnosis.id = c_1.main_diagnosis_id)
  • Buffers: shared hit=88725
57. 499.171 499.171 ↑ 1.0 1 29,363

Seq Scan on public.mc_disease_type d_1 (cost=0.00..1.16 rows=1 width=39) (actual time=0.014..0.017 rows=1 loops=29,363)

  • Output: d_1.name
  • Filter: (((d_1.code)::text = (c_1.disease_type_code)::text) AND ((d_1.to_dt >= ('now'::cstring)::date) OR (d_1.to_dt IS NULL)))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=29363
58. 88.089 3,641.012 ↑ 1.0 1 29,363

Limit (cost=0.56..8.59 rows=1 width=53) (actual time=0.124..0.124 rows=1 loops=29,363)

  • Output: ((((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text)), fin_bill_patients.pat_birth_dt, (NULL::character varying(255)), (NULL::character varying(255)), (NULL::character varying(255)), (NULL::integer)
  • Buffers: shared hit=146137 read=680
  • I/O Timings: read=2719.824
59. 3,552.923 3,552.923 ↑ 1.0 1 29,363

Index Scan using fin_bill_patients_pk on billing.fin_bill_patients (cost=0.56..8.59 rows=1 width=53) (actual time=0.121..0.121 rows=1 loops=29,363)

  • Output: (((((fin_bill_patients.pat_surname)::text || ' '::text) || (fin_bill_patients.pat_name)::text) || ' '::text) || (fin_bill_patients.pat_patr_name)::text), fin_bill_patients.pat_birth_dt, NULL::character varying(255), NULL::character varying(255), NULL::character varying(255), NULL::integer
  • Index Cond: ((fin_bill_patients.bill_id = item.bill_id) AND ((fin_bill_patients.id_pac)::text = (s.id_pac)::text))
  • Buffers: shared hit=146137 read=680
  • I/O Timings: read=2719.824
60. 88.089 1,350.698 ↑ 1.0 1 29,363

Limit (cost=0.43..8.46 rows=1 width=57) (actual time=0.046..0.046 rows=1 loops=29,363)

  • Output: fin_bill_policy.series, fin_bill_policy.number, fin_bill_policy.issuer_short_name, (NULL::integer), (NULL::integer)
  • Buffers: shared hit=116765 read=451
  • I/O Timings: read=857.837
61. 1,262.609 1,262.609 ↑ 1.0 1 29,363

Index Scan using fin_bill_policy_pk on billing.fin_bill_policy (cost=0.43..8.46 rows=1 width=57) (actual time=0.043..0.043 rows=1 loops=29,363)

  • Output: fin_bill_policy.series, fin_bill_policy.number, fin_bill_policy.issuer_short_name, NULL::integer, NULL::integer
  • Index Cond: ((fin_bill_policy.bill_id = item.bill_id) AND (fin_bill_policy.id = c_1.active_policy_id))
  • Buffers: shared hit=116765 read=451
  • I/O Timings: read=857.837
62. 58.726 322.993 ↑ 1.0 1 29,363

Limit (cost=0.29..8.30 rows=1 width=54) (actual time=0.011..0.011 rows=1 loops=29,363)

  • Output: pim_position.department_id, pim_position.name
  • Buffers: shared hit=87976 read=32
  • I/O Timings: read=12.432
63. 264.267 264.267 ↑ 1.0 1 29,363

Index Scan using pim_position_pk on public.pim_position (cost=0.29..8.30 rows=1 width=54) (actual time=0.009..0.009 rows=1 loops=29,363)

  • Output: pim_position.department_id, pim_position.name
  • Index Cond: (pim_position.id = s.position_id)
  • Buffers: shared hit=87976 read=32
  • I/O Timings: read=12.432
64. 58.726 440.445 ↑ 1.0 1 29,363

Limit (cost=0.42..8.44 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=29,363)

  • Output: (NULL::integer), (NULL::integer), (NULL::integer)
  • Buffers: shared hit=107052 read=9
  • I/O Timings: read=31.508
65. 381.719 381.719 ↑ 1.0 1 29,363

Index Only Scan using sr_service_pk on public.sr_service (cost=0.42..8.44 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=29,363)

  • Output: NULL::integer, NULL::integer, NULL::integer
  • Index Cond: (sr_service.id = s.service_id)
  • Heap Fetches: 18971
  • Buffers: shared hit=107052 read=9
  • I/O Timings: read=31.508
66. 58.726 322.993 ↑ 1.0 1 29,363

Limit (cost=0.28..8.30 rows=1 width=61) (actual time=0.010..0.011 rows=1 loops=29,363)

  • Output: pim_department.code, pim_department.name
  • Buffers: shared hit=88004 read=4
  • I/O Timings: read=0.062
67. 264.267 264.267 ↑ 1.0 1 29,363

Index Scan using pim_department_pk on public.pim_department (cost=0.28..8.30 rows=1 width=61) (actual time=0.009..0.009 rows=1 loops=29,363)

  • Output: pim_department.code, pim_department.name
  • Index Cond: (pim_department.id = pim_position.department_id)
  • Buffers: shared hit=88004 read=4
  • I/O Timings: read=0.062
68. 29.363 2,290.314 ↑ 1.0 1 29,363

Limit (cost=0.72..16.80 rows=1 width=49) (actual time=0.078..0.078 rows=1 loops=29,363)

  • Output: (NULL::integer), ((((((n.surname)::text || ' '::text) || upper("left"((n.name)::text, 1))) || '.'::text) || COALESCE(((' '::text || upper("left"((n.patr_name)::text, 1))) || '.'::text), ''::text)))
  • Buffers: shared hit=205246 read=152
  • I/O Timings: read=1034.475
69. 617.892 2,260.951 ↑ 1.0 1 29,363

Nested Loop (cost=0.72..16.80 rows=1 width=49) (actual time=0.077..0.077 rows=1 loops=29,363)

  • Output: NULL::integer, (((((n.surname)::text || ' '::text) || upper("left"((n.name)::text, 1))) || '.'::text) || COALESCE(((' '::text || upper("left"((n.patr_name)::text, 1))) || '.'::text), ''::text))
  • Buffers: shared hit=205246 read=152
  • I/O Timings: read=1034.475
70. 264.267 264.267 ↑ 1.0 1 29,363

Index Scan using pim_employee_pk on public.pim_employee e (cost=0.30..8.31 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=29,363)

  • 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 = (NULLIF((s.doctor_code)::text, ''::text))::integer)
  • Buffers: shared hit=88007 read=1
  • I/O Timings: read=0.016
71. 1,378.792 1,378.792 ↑ 1.0 1 29,336

Index Scan using pim_individual_id_birth_dt_gender_id_idx on public.pim_individual n (cost=0.43..8.45 rows=1 width=53) (actual time=0.047..0.047 rows=1 loops=29,336)

  • Output: n.id, n.birth_dt, n.death_dt, n.has_citizenship, n.name, n.patr_name, n.surname, n.gender_id, n.nationality_id, n.list_identity_doc, n.list_oms_doc, n.list_job_org, n.list_reg_name, n.list_snils, n.list_uid, n.aud_who, n.aud_when, n.aud_source, n.aud_who_create, n.aud_when_create, n.aud_source_create, n.birth_place, n.age_year, n.age_month, n.age_day, n.list_main_contact, n.is_only_birth_year
  • Index Cond: (n.id = e.individual_id)
  • Buffers: shared hit=117239 read=151
  • I/O Timings: read=1034.459
72. 29.363 624,668.462 ↓ 0.0 0 29,363

Limit (cost=38.73..54.34 rows=1 width=29) (actual time=21.274..21.274 rows=0 loops=29,363)

  • Output: ((((pi.surname)::text || ' '::text) || (pi.name)::text))
  • Buffers: shared hit=4528646 read=51584 dirtied=14
  • I/O Timings: read=242061.490
73.          

CTE cte1

74. 88.089 244,094.619 ↑ 1.0 1 29,363

Limit (cost=38.02..38.02 rows=1 width=18) (actual time=8.313..8.313 rows=1 loops=29,363)

  • Output: c_2.aud_who, c_2.aud_when
  • Buffers: shared hit=149542 read=51549 dirtied=14
  • I/O Timings: read=241863.189
75. 616.623 244,006.530 ↑ 31.0 1 29,363

Sort (cost=38.02..38.09 rows=31 width=18) (actual time=8.310..8.310 rows=1 loops=29,363)

  • Output: c_2.aud_who, c_2.aud_when
  • Sort Key: c_2.aud_when DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=149542 read=51549 dirtied=14
  • I/O Timings: read=241863.189
76. 243,389.907 243,389.907 ↑ 2.6 12 29,363

Index Scan using "public$mc_case_id_idx" on audit."public$mc_case" c_2 (cost=0.43..37.86 rows=31 width=18) (actual time=2.885..8.289 rows=12 loops=29,363)

  • Output: c_2.aud_who, c_2.aud_when
  • Index Cond: (c_2.id = $31)
  • Filter: (c_2.type = ANY ('{U,I}'::text[]))
  • Buffers: shared hit=149539 read=51549 dirtied=14
  • I/O Timings: read=241863.189
77. 72.258 624,639.099 ↓ 0.0 0 29,363

Nested Loop (cost=0.71..594.10 rows=38 width=29) (actual time=21.273..21.273 rows=0 loops=29,363)

  • Output: (((pi.surname)::text || ' '::text) || (pi.name)::text)
  • Buffers: shared hit=4528646 read=51584 dirtied=14
  • I/O Timings: read=242061.490
78. 60.118 624,286.743 ↓ 0.0 0 29,363

Nested Loop (cost=0.28..346.60 rows=38 width=4) (actual time=21.261..21.261 rows=0 loops=29,363)

  • Output: sup.party_id
  • Buffers: shared hit=4501995 read=51559 dirtied=14
  • I/O Timings: read=241864.593
79. 337,351.507 624,139.928 ↓ 0.0 0 29,363

Nested Loop (cost=0.00..332.78 rows=38 width=4) (actual time=21.256..21.256 rows=0 loops=29,363)

  • Output: su.id
  • Join Filter: ((cte1.aud_who)::text ~~* (su.login)::text)
  • Rows Removed by Join Filter: 6873
  • Buffers: shared hit=4480776 read=51549 dirtied=14
  • I/O Timings: read=241863.189
80. 244,212.071 244,212.071 ↑ 1.0 1 29,363

CTE Scan on cte1 (cost=0.00..0.02 rows=1 width=32) (actual time=8.316..8.317 rows=1 loops=29,363)

  • Output: cte1.aud_who
  • Buffers: shared hit=149542 read=51549 dirtied=14
  • I/O Timings: read=241863.189
81. 42,576.350 42,576.350 ↑ 1.1 6,873 29,363

Seq Scan on public.sec_user su (cost=0.00..238.45 rows=7,545 width=15) (actual time=0.004..1.450 rows=6,873 loops=29,363)

  • Output: su.id, su.close_dt, su.comment, su.cr_dt, su.email, su.login, su.password, su.version, su.blocked, su.scope_id, su.use_global_context, su.aud_who, su.aud_when, su.aud_source, su.aud_who_create, su.aud_when_create, su.aud_source_create
  • Buffers: shared hit=4331234
82. 86.697 86.697 ↑ 1.0 1 6,669

Index Scan using pk_sec_user_party on public.sec_user_party sup (cost=0.28..0.35 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=6,669)

  • Output: sup.id, sup.party_id, sup.aud_who, sup.aud_when, sup.aud_source, sup.aud_who_create, sup.aud_when_create, sup.aud_source_create
  • Index Cond: (sup.id = su.id)
  • Buffers: shared hit=21219 read=10
  • I/O Timings: read=1.404
83. 280.098 280.098 ↑ 1.0 1 6,669

Index Scan using pim_individual_pk on public.pim_individual pi (cost=0.43..6.50 rows=1 width=33) (actual time=0.042..0.042 rows=1 loops=6,669)

  • 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: (pi.id = sup.party_id)
  • Buffers: shared hit=26651 read=25
  • I/O Timings: read=196.897
84. 176.178 1,233.246 ↑ 1.0 1 29,363

Limit (cost=0.00..10.21 rows=1 width=3) (actual time=0.042..0.042 rows=1 loops=29,363)

  • Output: f.code, (NULL::text)
  • Buffers: shared hit=105355
85. 1,057.068 1,057.068 ↑ 1.0 1 29,363

Seq Scan on public.md_profile f (cost=0.00..10.21 rows=1 width=3) (actual time=0.036..0.036 rows=1 loops=29,363)

  • Output: f.code, NULL::text
  • Filter: ((f.code)::text = (c_1.last_profile_code)::text)
  • Rows Removed by Filter: 112
  • Buffers: shared hit=105355
86. 117.452 469.808 ↑ 1.0 1 29,363

Limit (cost=0.00..3.67 rows=1 width=134) (actual time=0.016..0.016 rows=1 loops=29,363)

  • Output: (NULL::character varying(255)), ((((COALESCE(mpm.code, ''::character varying))::text || ' '::text) || (COALESCE(mpm.name, ''::character varying))::text))
  • Buffers: shared hit=30545
87. 352.356 352.356 ↑ 1.0 1 29,363

Seq Scan on public.mc_payment_method mpm (cost=0.00..3.67 rows=1 width=134) (actual time=0.012..0.012 rows=1 loops=29,363)

  • Output: NULL::character varying(255), (((COALESCE(mpm.code, ''::character varying))::text || ' '::text) || (COALESCE(mpm.name, ''::character varying))::text)
  • Filter: ((mpm.code)::text = (c_1.payment_method_code)::text)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=30545
88.          

SubPlan (for Nested Loop Left Join)

89. 3,347.382 3,347.382 ↑ 1.0 1 29,363

Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.113..0.114 rows=1 loops=29,363)

  • Output: billing.fin_bill__get_item_warning(item.id)
  • Buffers: shared hit=88666 read=38
  • I/O Timings: read=272.495
90. 2,231.588 2,231.588 ↑ 1.0 1 29,363

Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.076..0.076 rows=1 loops=29,363)

  • Output: fin_bill__get_item_errors(item.id)
  • Buffers: shared hit=89804 read=126
  • I/O Timings: read=816.453
91. 117.452 822.164 ↓ 0.0 0 29,363

Limit (cost=0.00..0.30 rows=1 width=4) (actual time=0.028..0.028 rows=0 loops=29,363)

  • Output: fin_bill_add_case.case_id
  • Buffers: shared hit=58726
92. 704.712 704.712 ↓ 0.0 0 29,363

Seq Scan on public.fin_bill_add_case (cost=0.00..3.00 rows=10 width=4) (actual time=0.024..0.024 rows=0 loops=29,363)

  • Output: fin_bill_add_case.case_id
  • Filter: (fin_bill_add_case.bill_id = item.bill_id)
  • Rows Removed by Filter: 85
  • Buffers: shared hit=58726
Planning time : 655.014 ms
Execution time : 1,141,989.410 ms