느린 쿼리는 추측보다 실행 계획으로 진단한다

PostgreSQL에서 조회가 느릴 때 가장 먼저 해야 할 일은 인덱스를 무작정 추가하는 것이 아니라, 실제 실행 계획을 확인하는 일입니다. 같은 SQL이라도 조건값의 분포, 반환 행 수, 통계 정보, 조인 순서에 따라 최적의 접근 방식이 달라집니다. 특히 운영 데이터가 늘어난 뒤에만 느려지는 쿼리는 개발 환경에서 단순히 실행 시간만 재서는 원인을 찾기 어렵습니다.

진단 대상은 사용자가 자주 호출하는 목록 조회, 관리자 검색, 정산 배치, API의 상세 연관 데이터 조회처럼 요청 경로가 분명한 쿼리부터 잡는 것이 좋습니다. 애플리케이션 로그에서 SQL과 바인딩 값을 확보하되, 개인정보나 민감한 값은 마스킹한 뒤 재현 가능한 조건으로 바꿉니다.

EXPLAIN ANALYZE 결과에서 볼 항목

EXPLAIN은 PostgreSQL이 선택한 계획을, ANALYZE는 그 계획을 실제로 실행했을 때의 측정값을 보여 줍니다. 운영 DB에서 ANALYZE를 실행하면 쿼리가 실제 수행되므로, 큰 UPDATE나 무거운 조회는 복제본 또는 트래픽이 낮은 시간에 점검해야 합니다. BUFFERS 옵션까지 지정하면 디스크와 공유 버퍼 사용량도 함께 확인할 수 있습니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, ordered_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
  AND ordered_at >= TIMESTAMPTZ '2026-08-01 00:00:00+09'
ORDER BY ordered_at DESC
LIMIT 50;

결과에서는 실제 시간과 행 수가 먼저입니다. 계획의 rows와 actual rows 차이가 매우 크다면 통계 정보가 오래됐거나 특정 값의 편향을 PostgreSQL이 제대로 추정하지 못한 경우를 의심합니다. Seq Scan은 테이블 전체를 읽는 방식이므로 항상 나쁜 것은 아니지만, 수백만 행 중 소수만 찾는 화면 조회라면 원인을 검토해야 합니다. Index Scan 또는 Bitmap Heap Scan이 선택되었더라도 Heap Fetches와 shared read가 많으면 인덱스만으로 해결되지 않을 수 있습니다.

조건과 정렬 순서에 맞는 복합 인덱스 만들기

복합 인덱스의 열 순서는 SQL의 사용 방식에 맞춰야 합니다. 일반적으로 동등 조건으로 자주 제한되는 tenant_id와 status를 앞에 두고, 범위 조건 및 정렬에 쓰는 ordered_at을 뒤에 둡니다. 아래 인덱스는 위 목록 조회에서 필터와 최신순 정렬, LIMIT 50을 함께 지원하도록 설계한 예입니다.

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_ordered_at
ON orders (tenant_id, status, ordered_at DESC)
INCLUDE (id, customer_id, total_amount);

CONCURRENTLY는 운영 테이블에 인덱스를 만들 때 쓰기 작업을 장시간 막지 않기 위해 사용합니다. 다만 트랜잭션 블록 안에서는 실행할 수 없고, 생성 시간이 더 걸립니다. INCLUDE 열은 검색·정렬 키에는 필요 없지만 결과에 자주 반환되는 열을 넣어 index-only scan 가능성을 높입니다. 테이블의 가시성 맵 상태에 따라 실제 테이블 접근이 여전히 발생할 수 있으므로, 인덱스 생성 뒤에도 실행 계획으로 효과를 검증해야 합니다.

인덱스가 있어도 느린 대표적인 이유

  • WHERE 절에서 ordered_at::date처럼 열에 함수를 적용하면 일반 B-tree 인덱스를 활용하기 어렵습니다. 날짜 범위 조건으로 바꾸거나 표현식 인덱스를 별도로 검토합니다.
  • LIKE '%검색어%'는 앞부분 와일드카드 때문에 일반 인덱스 효율이 낮습니다. 부분 문자열 검색이 핵심이면 pg_trgm과 GIN 또는 GiST 인덱스를 검토합니다.
  • SELECT *로 큰 TEXT, JSONB 열까지 가져오면 인덱스가 있어도 네트워크와 테이블 접근 비용이 커집니다. 목록 화면은 필요한 열만 선택합니다.
  • OFFSET이 큰 페이지네이션은 앞 행을 계속 건너뜁니다. 마지막 정렬 키를 기준으로 다음 페이지를 찾는 keyset pagination이 대량 데이터에 유리합니다.

배포 전후 검증 절차

인덱스는 쓰기 비용과 저장 공간을 함께 늘립니다. 따라서 실제 사용 쿼리 한두 개만 빨라지는 인덱스를 무분별하게 쌓지 않아야 합니다. 인덱스 생성 후에는 같은 조건값으로 EXPLAIN ANALYZE를 다시 실행하고, 실행 시간·읽은 블록 수·반환 행 수를 이전 결과와 비교합니다. 통계가 부정확해 보이면 대상 테이블에 ANALYZE를 수행하고, 데이터 분포가 매우 치우친 열은 통계 목표치를 조정할지 검토합니다.

ANALYZE orders;

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

점검 체크리스트

  • 느린 SQL을 실제 조건값으로 재현하고 EXPLAIN ANALYZE로 추측을 줄입니다.
  • 필터, 정렬, LIMIT 순서에 맞춰 복합 인덱스를 설계합니다.
  • 인덱스 생성 뒤 실행 시간뿐 아니라 버퍼 읽기와 행 수 추정 오차도 비교합니다.
  • 대량 OFFSET, 불필요한 SELECT *, 열에 적용한 함수가 없는지 함께 점검합니다.
  • 인덱스 사용 통계와 쓰기 부하를 주기적으로 확인해 불필요한 인덱스를 관리합니다.