PostgreSQL 인덱스를 추가하기 전 EXPLAIN ANALYZE로 확인한 선택 기준

느린 쿼리를 바로 인덱싱하지 않았다

관리자 주문 목록이 특정 날짜 필터에서 2초 이상 걸렸다. created_at에 인덱스를 만들면 될 것 같았지만, 실제 조건에는 상태와 상점 ID가 함께 들어갔다. 먼저 운영 데이터를 복제한 스테이징에서 같은 파라미터로 실행 계획을 저장했다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at, total
FROM orders
WHERE shop_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

처음 계획에서는 많은 행을 읽고 정렬하고 있었다. 단일 created_at 인덱스는 정렬에는 도움을 주지만 shop_id로 범위를 충분히 줄이지 못했다.

후보를 두 개만 비교했다

후보는 (shop_id, status, created_at DESC) 복합 인덱스와, 상태가 거의 고정인 환경을 가정한 부분 인덱스로 좁혔다.

CREATE INDEX CONCURRENTLY idx_orders_shop_status_created
ON orders (shop_id, status, created_at DESC);

CREATE INDEX CONCURRENTLY idx_orders_paid_shop_created
ON orders (shop_id, created_at DESC)
WHERE status = 'paid';

인덱스를 만든 뒤에는 실행 시간이 줄었는지만 보지 않고 Buffers의 shared read와 테이블 쓰기 경로도 비교했다. 부분 인덱스는 현재 화면에는 더 작았지만, 다른 상태의 조회 요구가 생기면 다시 설계해야 한다는 비용이 있었다.

읽기 속도 외에 확인한 것

• 실제 쿼리의 WHERE 순서가 아니라 선택도와 정렬 방식
• 통계가 최신인지: ANALYZE orders
• 인덱스 추가 후 INSERT/UPDATE 시간이 늘지 않았는지
• 배포 롤백 시 CONCURRENTLY 작업이 남지 않는지
• 인덱스 이름과 생성 시간을 운영 기록에 남겼는지

대량 테이블에서는 트랜잭션 안에서 CREATE INDEX CONCURRENTLY를 실행하지 않는 것도 체크했다. 실패 시 남는 invalid 인덱스는 다음 생성 전에 확인해야 한다.

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;

나의 결론

인덱스는 “느리니까 하나 추가”가 아니라, 한 쿼리의 실행 계획을 바꾸는 데이터 구조다. 이번에는 복합 인덱스를 먼저 적용하고, 일주일 동안 실제 사용량을 관찰하기로 했다. EXPLAIN 결과 한 장과 쓰기 비용 측정이 없었다면, 빨라진 화면 뒤에 유지보수 부담을 숨겼을 것이다.