TestForge Blog
← 전체 포스트
#backend #postgresql #database #performance #index

PostgreSQL 인덱스 튜닝 가이드 - EXPLAIN ANALYZE로 느린 쿼리를 어떻게 줄일까

PostgreSQL 성능 문제는 인덱스를 많이 만드는 것으로 해결되지 않습니다. 이 글에서는 EXPLAIN ANALYZE 읽는 법, Seq Scan과 Index Scan 판단, 복합 인덱스 순서, 부분 인덱스, 정렬과 페이징 쿼리 튜닝까지 실무에서 바로 쓰는 기준을 정리합니다.

TestForge Team ·

인덱스 튜닝이 어려운 이유

느린 쿼리를 보면 많은 팀이 먼저 “인덱스를 하나 더 만들자”로 접근합니다.
하지만 PostgreSQL에서는 잘못된 인덱스가 오히려 쓰기 비용과 저장 공간만 늘릴 수 있습니다.

핵심은 두 가지입니다.

  • 실제 실행 계획을 먼저 본다
  • 어떤 조건과 정렬을 줄이려는지 명확히 안다

EXPLAIN과 EXPLAIN ANALYZE의 차이

  • EXPLAIN: 옵티마이저가 예상한 계획
  • EXPLAIN ANALYZE: 실제 실행 결과와 시간

실무에서는 거의 항상 EXPLAIN ANALYZE를 봐야 합니다.
예상 비용이 아니라 실제 행 수와 시간이 중요하기 때문입니다.

먼저 봐야 할 포인트

실행 계획에서 아래를 먼저 확인합니다.

  • Seq Scan이 발생하는가
  • 예상 rows와 실제 rows 차이가 큰가
  • Sort가 큰 비용을 쓰는가
  • Nested Loop가 비정상적으로 반복되는가
  • Bitmap Heap Scan이 필요한 상황인가

이 다섯 가지만 제대로 봐도 대부분의 병목 방향을 잡을 수 있습니다.

Seq Scan이 항상 나쁜 것은 아니다

테이블 전체를 많이 읽는 쿼리라면 Seq Scan이 더 빠를 수 있습니다.

문제가 되는 경우:

  • 조건절이 선택도가 높은데도 Seq Scan 발생
  • 큰 테이블에서 LIMIT 쿼리인데도 전체 스캔
  • 정렬/필터가 자주 반복되는데 인덱스 부재

즉, Seq Scan 자체보다 “왜 이 쿼리에서 그런 선택이 나왔는지”가 중요합니다.

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

예를 들어 쿼리가 아래와 같다고 가정합니다.

SELECT *
FROM orders
WHERE tenant_id = 10
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

이 경우 인덱스 후보는 보통 아래처럼 잡습니다.

CREATE INDEX idx_orders_tenant_status_created_at
ON orders (tenant_id, status, created_at DESC);

이유:

  • 먼저 필터링 조건
  • 그 다음 정렬 조건

복합 인덱스에서 컬럼 순서는 쿼리 패턴과 직접 연결됩니다.

부분 인덱스가 매우 강력한 경우

조건이 특정 값에 집중되는 경우 전체 인덱스보다 부분 인덱스가 훨씬 낫습니다.

예:

CREATE INDEX idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'PAID';

이 방식은:

  • 인덱스 크기를 줄이고
  • 쓰기 비용을 줄이며
  • 자주 조회하는 조건에 최적화할 수 있습니다

페이징 쿼리는 OFFSET이 커질수록 느려진다

아래 쿼리는 초반엔 괜찮아 보여도 뒤로 갈수록 급격히 느려질 수 있습니다.

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 50 OFFSET 10000;

이 경우 Keyset Pagination으로 바꾸는 것이 보통 더 낫습니다.

SELECT *
FROM orders
WHERE created_at < :cursor
ORDER BY created_at DESC
LIMIT 50;

정렬 컬럼 인덱스와 함께 쓰면 대규모 테이블에서도 안정적입니다.

정렬 비용이 큰 쿼리는 인덱스로 줄일 수 있다

쿼리 병목이 필터가 아니라 Sort에 있는 경우도 많습니다.

특히:

  • 최신순 목록
  • 상태별 최근 이벤트
  • 사용자별 최근 활동

이런 쿼리는 필터와 정렬을 함께 반영한 인덱스가 매우 효과적입니다.

커버링 인덱스도 고려할 수 있다

PostgreSQL에서는 INCLUDE를 활용해 추가 컬럼을 포함시킬 수 있습니다.

CREATE INDEX idx_orders_lookup
ON orders (tenant_id, created_at DESC)
INCLUDE (status, total_amount);

이 방식은 일부 조회에서 Heap 접근을 줄여줄 수 있습니다.

하지만 남용하면 인덱스가 커져서 오히려 손해일 수 있습니다.

통계가 잘못되면 좋은 인덱스도 못 탄다

예상 rows와 실제 rows 차이가 큰 경우 PostgreSQL 통계 문제를 의심해야 합니다.

확인할 것:

  • ANALYZE가 충분히 수행되는가
  • 데이터 분포가 심하게 치우쳐 있는가
  • 조인 컬럼 통계가 정확한가

대용량 테이블에서는 자동 analyze만 믿지 말고 관찰이 필요합니다.

자주 하는 실수

  • 같은 의미의 인덱스를 중복 생성
  • 읽기 성능만 보고 쓰기 비용을 무시
  • low-cardinality 컬럼 단독 인덱스를 남발
  • 실제 쿼리 패턴 확인 없이 복합 인덱스 순서 결정
  • ORM이 만든 쿼리를 보지 않고 추측만으로 튜닝

실무 튜닝 순서

  1. 느린 쿼리 식별
  2. EXPLAIN ANALYZE 수집
  3. 필터/정렬/조인 조건 분해
  4. 가장 자주 실행되는 패턴 중심으로 인덱스 설계
  5. 배포 후 읽기/쓰기 영향 함께 측정

마무리

PostgreSQL 인덱스 튜닝은 “인덱스를 더 만드는 작업”이 아니라 “실행 계획을 이해하고 가장 비싼 작업을 줄이는 작업”입니다.

좋은 튜닝은 보통 화려한 기법보다도:

  • 실제 쿼리를 보고
  • 실행 계획을 읽고
  • 패턴에 맞는 인덱스를 적게, 정확하게 추가하는 것

에서 시작합니다.