문제 진단 및 원인 분석
update_incident_aggregate_shift_location_service가 8~10초의 지연을 보였습니다. EXPLAIN 분석 결과, attendant_shifts 테이블은 reference_date 필터링 시 PRIMARY KEY만 사용했습니다. EXPLAIN ANALYZE를 통해 다음 원인을 파악했습니다.
* 복합 인덱스 규칙 미준수: (employee_id, reference_date) 복합 인덱스가 있었으나, 쿼리가 employee_id를 먼저 사용하지 않아 인덱스가 비활성화되었습니다.
* 비효율적인 데이터 스캔: 인덱스 미활용으로 수백만 건의 전체 테이블 스캔이 발생, 단 1행 반환에 9.4초 이상 소요되는 비효율을 초래했습니다.
해결책 및 결과
attendant_shifts 테이블 쿼리 시 employee_id를 먼저 필터링한 후 reference_date를 필터링하도록 로직을 수정했습니다. 이는 Active Record 스코프에 by_employee_id 필터를 추가하는 한 줄의 코드 변경으로 구현되었습니다.
수정 후 EXPLAIN 결과, attendant_shifts가 (employee_id, reference_date) 복합 인덱스를 올바르게 활용했습니다. EXPLAIN ANALYZE 결과, 쿼리 실행 시간은 9.4초에서 0.003초로 99.9% 감소했으며, 처리 비용도 현저히 줄었습니다.