[local]:5432 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=1054298.43..1054298.47 rows=16 width=16) (actual time=158354.337..158354.339 rows=16 loops=1) Output: s.admission_date, s.admission_time, s.id Buffers: shared hit=2688130 read=906533 I/O Timings: read=152744.912 -> Sort (cost=1054298.43..1054300.48 rows=818 width=16) (actual time=158354.336..158354.337 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=2688130 read=906533 I/O Timings: read=152744.912 -> Nested Loop (cost=1.13..1054277.98 rows=818 width=16) (actual time=186.075..158336.684 rows=6698 loops=1) Output: s.admission_date, s.admission_time, s.id Buffers: shared hit=2688130 read=906533 I/O Timings: read=152744.912 -> Index Scan using mc_case_closing_step_id_idx on public.mc_case c (cost=0.56..4637.11 rows=14079 width=4) (actual time=29.339..26652.510 rows=671326 loops=1) Output: c.id Index Cond: ((c.closing_step_id IS NULL) AND (c.closing_step_id IS NULL)) Buffers: shared read=373616 I/O Timings: read=25667.697 -> Index Scan using idx_mc_step on public.mc_step s (cost=0.56..74.53 rows=2 width=20) (actual time=0.195..0.196 rows=0 loops=671326) 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, s.is_first_step Index Cond: (s.case_id = c.id) Filter: (s._clinic_id = 89) Rows Removed by Filter: 1 Buffers: shared hit=2688130 read=532917 I/O Timings: read=127077.215 Planning time: 35.484 ms Execution time: 158354.524 ms (28 rows) Time: 158391.053 ms [local]:5432 postgres@lsd=# \di+ mc_case_closing_step_id_idx List of relations Schema | Name | Type | Owner | Table | Size | Description --------+-----------------------------+-------+------------------+---------+--------+------------- public | mc_case_closing_step_id_idx | index | app_group_master | mc_case | 675 MB | (1 row) [local]:5432 postgres@lsd=# \di+ idx_mc_step List of relations Schema | Name | Type | Owner | Table | Size | Description --------+-------------+-------+------------------+---------+---------+------------- public | idx_mc_step | index | app_group_master | mc_step | 1060 MB | (1 row) [local]:5432 postgres@lsd=# explain(verbose, analyze, buffers, timing) select c.id 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=1054298.43..1054298.47 rows=16 width=20) (actual time=152060.935..152060.938 rows=16 loops=1) Output: c.id, s.admission_date, s.admission_time, s.id Buffers: shared hit=2688459 read=906204 I/O Timings: read=146737.935 -> Sort (cost=1054298.43..1054300.48 rows=818 width=20) (actual time=152060.933..152060.935 rows=16 loops=1) Output: c.id, 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=2688459 read=906204 I/O Timings: read=146737.935 -> Nested Loop (cost=1.13..1054277.98 rows=818 width=20) (actual time=174.538..152044.680 rows=6698 loops=1) Output: c.id, s.admission_date, s.admission_time, s.id Buffers: shared hit=2688459 read=906204 I/O Timings: read=146737.935 -> Index Scan using mc_case_closing_step_id_idx on public.mc_case c (cost=0.56..4637.11 rows=14079 width=4) (actual time=37.191..27475.594 rows=671326 loops=1) Output: c.id Index Cond: ((c.closing_step_id IS NULL) AND (c.closing_step_id IS NULL)) Buffers: shared read=373616 I/O Timings: read=26532.511 -> Index Scan using idx_mc_step on public.mc_step s (cost=0.56..74.53 rows=2 width=20) (actual time=0.185..0.185 rows=0 loops=671326) 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, s.is_first_step Index Cond: (s.case_id = c.id) Filter: (s._clinic_id = 89) Rows Removed by Filter: 1 Buffers: shared hit=2688459 read=532588 I/O Timings: read=120205.424 Planning time: 26.400 ms Execution time: 152061.029 ms (28 rows) Time: 152088.338 ms