인덱스가 있어도 쿼리가 느린 이유

PostgreSQL에서 인덱스는 모든 조회를 자동으로 빠르게 만드는 장치가 아니다. 옵티마이저는 조건의 선택도, 테이블 통계, 반환 행 수, 정렬 방식, 조인 비용을 비교해 순차 스캔과 인덱스 스캔 중 더 저렴하다고 판단한 실행 계획을 선택한다. 수십 퍼센트의 행을 읽어야 하는 조회라면 인덱스를 따라가며 테이블을 여러 번 읽는 것보다 순차 스캔이 더 빠를 수 있다. 따라서 느린 SQL을 만나면 인덱스를 먼저 추가하기보다 실제 실행 계획과 호출 패턴부터 확인해야 한다.

특히 업무 시스템에서는 목록 화면의 조건, 정렬, 페이지 크기가 함께 성능을 결정한다. 주문 목록에서 회사별 최근 주문을 50건씩 조회하는 기능이라면 회사 ID만 인덱싱하는 것으로 부족할 수 있다. WHERE 조건과 ORDER BY, 필요한 컬럼을 한 흐름으로 보고 인덱스를 설계해야 한다.

측정 없이 인덱스를 만들지 않는다

먼저 운영 환경과 유사한 데이터 분포에서 실행 시간을 확인한다. EXPLAIN은 예상 계획을 보여 주고, ANALYZE를 함께 쓰면 실제 행 수와 시간, 버퍼 사용량을 확인할 수 있다. 민감한 운영 환경에서는 쓰기 SQL에 EXPLAIN ANALYZE를 사용하지 말고, 대표 조회 쿼리부터 점검한다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, status, created_at
FROM orders
WHERE company_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

결과에서 Seq Scan, Index Scan, Bitmap Heap Scan 여부를 확인한다. actual time과 rows는 추정치와 실제 값의 차이를 보여 준다. 예상 rows가 실제 rows와 크게 다르면 통계가 오래되었거나 데이터 분포가 한쪽으로 치우친 상태일 수 있다. Buffers의 shared read가 많으면 디스크에서 읽은 페이지가 많다는 뜻이며, 응답 지연의 원인이 될 수 있다. 계획 하나만 보고 결론 내리지 말고 자주 발생하는 회사 ID, 기간 조건, 빈 결과 조건까지 측정한다.

복합 인덱스는 조건의 순서가 핵심이다

위 조회는 company_id와 status로 범위를 줄이고 created_at으로 정렬한다. 이때 일반적으로 동등 비교 조건을 앞에 두고 정렬 또는 범위 조건을 뒤에 둔 복합 인덱스가 적합하다. 단, status 값이 PAID·CANCELLED처럼 종류가 적다면 status 하나만 앞에 둔 인덱스는 선택도가 낮아 효용이 작다. 회사별 조회가 주된 화면이라면 company_id를 선두 컬럼으로 두는 편이 자연스럽다.

CREATE INDEX CONCURRENTLY idx_orders_company_status_created_at
ON orders (company_id, status, created_at DESC);

대용량 운영 테이블에서는 CREATE INDEX 대신 CREATE INDEX CONCURRENTLY를 고려한다. 일반 생성은 테이블 쓰기를 오래 막을 수 있지만, 동시 생성은 쓰기 차단을 줄인다. 다만 동시 생성은 트랜잭션 블록 안에서 실행할 수 없고 시간이 더 걸릴 수 있으므로 배포 절차에 분리해 둔다. 인덱스 생성 후 같은 EXPLAIN (ANALYZE, BUFFERS)로 실행 계획이 실제로 바뀌었는지, 반환 행 수가 같은지 다시 확인한다.

부분 인덱스와 INCLUDE를 적용할 때

특정 상태의 데이터만 반복 조회한다면 부분 인덱스로 크기와 쓰기 비용을 줄일 수 있다. 예를 들어 결제 완료 주문만 최근 목록에 자주 나타난다면 PAID 행만 담는 인덱스를 만들 수 있다. 조건식은 애플리케이션 SQL의 조건과 논리적으로 맞아야 하며, 파라미터 형태에 따라 옵티마이저가 부분 인덱스를 항상 선택하지 않을 수 있으므로 실제 계획 검증이 필요하다.

CREATE INDEX CONCURRENTLY idx_orders_paid_company_created_at
ON orders (company_id, created_at DESC)
INCLUDE (id, order_no)
WHERE status = 'PAID';

INCLUDE 컬럼은 검색이나 정렬 키는 아니지만 인덱스에 함께 저장한다. 목록 화면이 id와 order_no만 필요하다면 테이블 본문을 다시 읽지 않는 Index Only Scan 가능성을 높일 수 있다. 다만 UPDATE가 잦은 넓은 컬럼을 무분별하게 INCLUDE하면 인덱스가 커지고 쓰기 비용이 증가한다. 또한 Index Only Scan은 가시성 맵 상태의 영향을 받으므로 VACUUM이 제대로 수행되는지도 함께 살펴봐야 한다.

성능 개선 뒤에 확인할 운영 항목

  • 실행 계획의 actual rows와 예상 rows 차이가 크면 ANALYZE 실행 주기와 통계 목표 값을 점검한다.
  • 인덱스는 INSERT, UPDATE, DELETE마다 유지 비용이 발생하므로 중복 인덱스와 사용되지 않는 인덱스를 정기적으로 검토한다.
  • OFFSET이 큰 페이지네이션은 뒤로 갈수록 많은 행을 버린다. 목록이 길면 created_at과 id를 이용한 키셋 페이지네이션을 검토한다.
  • 쿼리 조건에 함수나 형변환을 적용하면 일반 인덱스를 활용하지 못할 수 있다. 필요 시 표현식 인덱스를 별도로 검증한다.
  • 배포 전후 동일한 데이터 조건에서 응답 시간, 버퍼 읽기, CPU 사용량을 비교해 개선 효과를 기록한다.

체크리스트

느린 조회를 개선할 때는 실행 계획을 먼저 수집하고, WHERE·ORDER BY·LIMIT을 기준으로 인덱스 후보를 만든다. 복합 인덱스의 선두 컬럼은 실제 필터링 패턴과 선택도를 기준으로 정한다. 부분 인덱스와 INCLUDE는 반복되는 좁은 조회에만 적용하고, 생성 뒤에는 동일 SQL의 실제 시간과 버퍼 사용량을 재측정한다. 인덱스 수를 늘리는 것보다 업무 쿼리에 맞는 한 개의 검증된 인덱스를 유지하는 것이 안정적인 운영에 도움이 된다.