Composite Index 전략을 통한 쿼리 성능 60배 개선 및 응답 시간 250ms 달성

Context

단순히 인덱스를 추가하는 관습적 접근으로 인한 Write 부하 증가 및 스토리지 낭비 발생. Low Cardinality 컬럼에 대한 단일 인덱스 적용 시 Query Planner가 Seq Scan을 선택하여 성능 저하가 나타나는 구조적 한계 직면.

Technical Solution

• pg_stat_statements 및 EXPLAIN ANALYZE를 통한 고비용 쿼리의 Bottleneck 지점 식별
• Cardinality 분석을 통한 Index Scan 효율성 검토 및 Seq Scan 발생 원인 파악
• Low Cardinality 컬럼을 Composite Index의 마지막 순서로 배치하여 필터링 효율 최적화
• (shift_id, planned_start_date, status) 순의 Composite B-tree Index 설계를 통한 데이터 접근 경로 단축
• Index 추가에 따른 Write Operation 비용과 Query 속도 개선 간의 Trade-off 분석 수행

Impact

• 특정 SELECT 쿼리 응답 시간 15초에서 250ms로 단축
• 불필요한 인덱스 10개 제거 시 Write Operations per second 30% 성능 회복

Key Takeaway

인덱스는 단순한 가속 도구가 아닌 시스템 전체의 Write Load와 Storage를 결정하는 아키텍처 요소임. 특히 Low Cardinality 컬럼은 단독 인덱스보다 High Cardinality 컬럼과 조합된 Composite Index 형태로 설계하여 Selective Filter로 활용하는 전략이 필수적임.