[local]|postgres@lsd=# explain(verbose, analyze, buffers, timing) select from mc_step s join mc_case c on c.id = s.case_id where s._clinic_id=89 and c.closing_step_id is null and c.closing_step_id is null order by s.admission_date desc, s.admission_time desc, s.id desc limit 16; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------- Limit (cost=258143.78..258143.82 rows=16 width=16) (actual time=127967.775..127967.778 rows=16 loops=1) Output: s.admission_date, s.admission_time, s.id Buffers: shared hit=3511653 read=276851 dirtied=506 I/O Timings: read=110987.861 -> Sort (cost=258143.78..258145.48 rows=681 width=16) (actual time=127967.774..127967.777 rows=16 loops=1) Output: s.admission_date, s.admission_time, s.id Sort Key: s.admission_date DESC, s.admission_time DESC, s.id DESC Sort Method: top-N heapsort Memory: 26kB Buffers: shared hit=3511653 read=276851 dirtied=506 I/O Timings: read=110987.861 -> Nested Loop (cost=1.13..258126.75 rows=681 width=16) (actual time=10.358..127951.428 rows=6384 loops=1) Output: s.admission_date, s.admission_time, s.id Buffers: shared hit=3511642 read=276851 dirtied=506 I/O Timings: read=110987.861 -> Index Scan using mc_case_closing_step_id_idx on public.mc_case c (cost=0.56..4304.49 rows=11838 width=4) (actual time=0.095..4257.087 rows=664547 loops=1) Output: c.id Index Cond: ((c.closing_step_id IS NULL) AND (c.closing_step_id IS NULL)) Buffers: shared hit=597053 dirtied=9 -> Index Scan using idx_mc_step on public.mc_step s (cost=0.56..21.43 rows=1 width=20) (actual time=0.185..0.185 rows=0 loops=664547) Output: s.id, s.admission_date, s.admission_time, s.death_date, s.death_time, s.outcome_date, s.outcome_time, s.death_employee_id, s.main_diagnosis_id, s.case_id, s.outcome_id, s.outcome_clinic_id, s.outcome_regimen_ id, s.regimen_id, s.res_group_id, s.result_id, s.reason_id, s.profile_id, s.mes_id, s.mes_quality_criterion_id, s.is_continue, s.is_continue_editable, s.standard_id, s.csg_id, s.vmp_type_id, s.vmp_method_id, s.deviation_reason_id, s.aud_ who, s.aud_when, s.aud_source, s.aud_who_create, s.aud_when_create, s.aud_source_create, s._clinic_id, s._patient_id, s._case_mode_id, s._is_last, s._responsible_id, s._department_id, s.hsp_bed_days_amount, s.hsp_bed_profile_id, s.hsp_co mplexity_level_id, s.hsp_days_comp_algo_id, s.hsp_department_id, s.hsp_funding_id, s.hsp_is_admission_day_counts, s.hsp_is_diag_not_eq, s.hsp_is_set_diagnosis, s.hsp_issue_planned_date, s.hsp_mes_id, s.hsp_missed_days_amount, s.hsp_plan_ department_id, s.hsp_previous_id, s.hsp_refusal_employee_id, s.plc_goal_id, s.plc_initiator_id, s.plc_is_needed, s.plc_is_sanitized, s.plc_is_viewed, s.plc_place_id, s.plc_type_id, s.plc_appointment_id, s.plc_planned_date, s.is_autopsy, s.action_allowed_result_id Index Cond: (s.case_id = c.id) Filter: (s._clinic_id = 89) Rows Removed by Filter: 1 Buffers: shared hit=2914589 read=276851 dirtied=497 I/O Timings: read=110987.861 Planning time: 11.493 ms Execution time: 127967.923 ms (27 rows) Time: 127984.797 ms [local]|postgres@lsd=# explain(verbose, analyze, buffers, timing) select from mc_step s join mc_case c on c.id = s.case_id where s._clinic_id=89 and c.closing_step_id is null and c.closing_step_id is null order by s.admission_date desc, s.admission_time desc, s.id desc limit 16; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------- Limit (cost=258143.78..258143.82 rows=16 width=16) (actual time=5669.221..5669.223 rows=16 loops=1) Output: s.admission_date, s.admission_time, s.id Buffers: shared hit=3786376 dirtied=16 -> Sort (cost=258143.78..258145.48 rows=681 width=16) (actual time=5669.220..5669.221 rows=16 loops=1) Output: s.admission_date, s.admission_time, s.id Sort Key: s.admission_date DESC, s.admission_time DESC, s.id DESC Sort Method: top-N heapsort Memory: 26kB Buffers: shared hit=3786376 dirtied=16 -> Nested Loop (cost=1.13..258126.75 rows=681 width=16) (actual time=1.595..5665.105 rows=6384 loops=1) Output: s.admission_date, s.admission_time, s.id Buffers: shared hit=3786376 dirtied=16 -> Index Scan using mc_case_closing_step_id_idx on public.mc_case c (cost=0.56..4304.49 rows=11838 width=4) (actual time=0.036..1283.290 rows=664518 loops=1) Output: c.id Index Cond: ((c.closing_step_id IS NULL) AND (c.closing_step_id IS NULL)) Buffers: shared hit=597013 dirtied=16 -> Index Scan using idx_mc_step on public.mc_step s (cost=0.56..21.43 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=664518) Output: s.id, s.admission_date, s.admission_time, s.death_date, s.death_time, s.outcome_date, s.outcome_time, s.death_employee_id, s.main_diagnosis_id, s.case_id, s.outcome_id, s.outcome_clinic_id, s.outcome_regimen_ id, s.regimen_id, s.res_group_id, s.result_id, s.reason_id, s.profile_id, s.mes_id, s.mes_quality_criterion_id, s.is_continue, s.is_continue_editable, s.standard_id, s.csg_id, s.vmp_type_id, s.vmp_method_id, s.deviation_reason_id, s.aud_ who, s.aud_when, s.aud_source, s.aud_who_create, s.aud_when_create, s.aud_source_create, s._clinic_id, s._patient_id, s._case_mode_id, s._is_last, s._responsible_id, s._department_id, s.hsp_bed_days_amount, s.hsp_bed_profile_id, s.hsp_co mplexity_level_id, s.hsp_days_comp_algo_id, s.hsp_department_id, s.hsp_funding_id, s.hsp_is_admission_day_counts, s.hsp_is_diag_not_eq, s.hsp_is_set_diagnosis, s.hsp_issue_planned_date, s.hsp_mes_id, s.hsp_missed_days_amount, s.hsp_plan_ department_id, s.hsp_previous_id, s.hsp_refusal_employee_id, s.plc_goal_id, s.plc_initiator_id, s.plc_is_needed, s.plc_is_sanitized, s.plc_is_viewed, s.plc_place_id, s.plc_type_id, s.plc_appointment_id, s.plc_planned_date, s.is_autopsy, s.action_allowed_result_id Index Cond: (s.case_id = c.id) Filter: (s._clinic_id = 89) Rows Removed by Filter: 1 Buffers: shared hit=3189363 Planning time: 1.423 ms Execution time: 5669.292 ms (23 rows) Time: 5673.175 ms [local]|postgres@lsd=# \di+ mc_case_closing_step_id_idx