PostgreSQL 슬로우 쿼리 원인 찾기: EXPLAIN ANALYZE로 실행 계획 읽는 실전 가이드

느린 쿼리는 추측하지 말고 실제 실행 계획부터 확인한다

PostgreSQL에서 목록 조회나 통계 API가 느려졌을 때 인덱스를 먼저 추가하면 문제를 가릴 수 있습니다. 데이터 분포, 조건의 선택도, 조인 순서, 정렬과 집계 비용에 따라 인덱스가 있어도 효율적인 계획이 선택되지 않을 수 있기 때문입니다. 운영 쿼리 개선의 시작점은 애플리케이션 로그의 실행 시간과 PostgreSQL이 실제로 수행한 작업을 함께 보는 것입니다.

기본 도구는 EXPLAIN ANALYZE입니다. EXPLAIN은 옵티마이저가 예상한 계획만 보여 주고, ANALYZE를 붙이면 실제 실행 시간과 실제 행 수를 보여 줍니다. 예상 행 수와 실제 행 수의 차이, 각 노드의 loops, 버퍼 사용량을 보면 병목 위치를 좁힐 수 있습니다. 단, ANALYZE는 대상 SQL을 실제로 실행하므로 UPDATE나 DELETE에는 트랜잭션을 열어 ROLLBACK하는 방식으로 먼저 확인해야 합니다.

재현 가능한 방식으로 계획 수집하기

다음 예시는 최근 30일간 결제 완료 주문을 고객별로 집계하는 조회입니다. 운영 환경에서는 민감한 값과 사용자 정보가 포함된 결과를 그대로 공유하지 말고, 바인딩 값과 행 수를 기록한 뒤 복제 환경 또는 읽기 전용 연결에서 분석하는 것이 안전합니다. BUFFERS 옵션은 디스크와 공유 버퍼의 접근량을 보여 주므로 CPU 연산 문제와 I/O 문제를 구분하는 데 유용합니다.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT customer_id, count(*) AS order_count, sum(total_amount) AS total_sales
FROM orders
WHERE status = 'PAID'
  AND paid_at >= now() - interval '30 days'
GROUP BY customer_id
ORDER BY total_sales DESC
LIMIT 50;

결과의 맨 위에는 전체 실행 시간과 Planning Time이 표시됩니다. Planning Time보다 Execution Time이 길다면 대개 데이터 접근, 조인, 정렬, 집계가 원인입니다. 반대로 짧은 쿼리인데 Planning Time이 비정상적으로 크다면 복잡한 동적 SQL이나 지나치게 많은 파티션, 준비된 문장 사용 방식을 점검합니다.

실행 계획에서 먼저 읽을 항목

• actual time, rows, loops: 각 노드가 실제로 반환한 행과 반복 횟수입니다. 노드의 총 비용은 실행 시간에 loops를 고려해 판단합니다. 안쪽 노드가 수천 번 반복되면 작은 스캔도 전체적으로 매우 비싸질 수 있습니다.
• rows 추정 차이: 계획의 rows와 actual rows가 크게 다르면 통계 정보가 오래되었거나 조건 컬럼의 상관관계를 옵티마이저가 모르는 경우가 많습니다. 잘못된 추정은 조인 순서와 조인 방식 선택까지 바꿉니다.
• Seq Scan: 순차 스캔 자체는 오류가 아닙니다. 테이블의 큰 비율을 읽는 쿼리는 임의 I/O가 많은 인덱스 스캔보다 순차 스캔이 빠를 수 있습니다. 필터 후 남는 행이 적은데도 큰 테이블 전체를 읽는 경우를 우선 조사합니다.
• Sort와 Hash: Sort Method의 external merge는 작업 메모리를 넘어 임시 파일을 사용했다는 뜻입니다. Hash 노드의 batch 증가도 메모리 부족 신호입니다. 쿼리별로 무작정 work_mem을 높이지 말고 동시 실행 수까지 계산해야 합니다.
• Buffers: shared read가 많으면 캐시에 없던 페이지를 읽은 것입니다. temp read/write가 크면 정렬 또는 해시가 디스크를 사용한 것이며, 쿼리 구조와 메모리 설정을 함께 검토해야 합니다.

인덱스는 조건과 정렬 순서에 맞춰 설계한다

예시 쿼리가 자주 실행되고 PAID 주문 비율이 전체보다 충분히 낮다면, 상태와 기간 조건을 만족시키는 부분 인덱스를 고려할 수 있습니다. 집계 값까지 포함하는 인덱스가 항상 정답은 아니지만, 필요한 열이 적고 테이블 접근이 큰 비용일 때 인덱스 전용 스캔 가능성을 높일 수 있습니다. 인덱스 생성 전후에는 반드시 같은 바인딩 값으로 실행 계획과 쓰기 비용을 비교합니다.

CREATE INDEX CONCURRENTLY idx_orders_paid_paid_at
ON orders (paid_at DESC)
INCLUDE (customer_id, total_amount)
WHERE status = 'PAID';

ANALYZE orders;

CREATE INDEX CONCURRENTLY는 일반 인덱스 생성보다 시간이 더 걸릴 수 있지만, 일반적인 쓰기 작업을 장시간 막는 위험을 줄입니다. 단일 트랜잭션 블록 안에서는 실행할 수 없고, 실패한 인덱스가 남을 수 있으므로 배포 절차와 모니터링이 필요합니다. 또한 paid_at 범위가 너무 넓거나 PAID 비율이 높다면 이 인덱스가 선택되지 않을 수 있습니다. 이것은 실패가 아니라 해당 접근 경로의 이득이 작다는 신호일 수 있습니다.

추정이 틀릴 때 통계와 SQL 형태를 점검한다

실제 행 수가 예상보다 수십 배 이상 많거나 적다면 먼저 ANALYZE 실행 시점과 autovacuum 설정을 확인합니다. 데이터가 급격히 변하는 테이블은 통계 갱신이 늦을 수 있습니다. 두 컬럼이 함께 사용되는 조건에서는 확장 통계를 만들어 상관관계를 알릴 수 있습니다. 예를 들어 지역과 상태가 강하게 연관된 주문 데이터라면 단일 컬럼 통계만으로는 결합 선택도를 정확히 예측하기 어렵습니다.

CREATE STATISTICS orders_region_status_stats
  (dependencies, ndistinct)
ON region_code, status
FROM orders;

ANALYZE orders;

함수로 감싼 조건도 계획을 악화시키기 쉽습니다. WHERE date(paid_at) = CURRENT_DATE처럼 컬럼에 함수를 적용하면 일반 인덱스를 활용하기 어렵습니다. 시간 범위 조건으로 바꾸고, 시간대 기준을 명확히 정하면 인덱스 범위 스캔과 데이터 해석 모두가 안정적입니다. LIKE 검색, 암묵적 형 변환, OR 조건도 별도로 계획을 확인해야 합니다.

개선 전후를 운영 지표로 검증한다

한 번 빠르게 나온 실행 계획만으로 배포를 결정하지 않습니다. 대표적인 바인딩 값뿐 아니라 결과가 적은 경우와 많은 경우, 캐시가 비어 있는 경우, 동시 요청이 있는 경우를 나눠 확인합니다. PostgreSQL의 pg_stat_statements를 사용하면 평균 시간뿐 아니라 호출 횟수, 총 시간, 행 수 변화도 추적할 수 있습니다. p95 응답 시간, 데이터베이스 CPU, 읽기 I/O, 임시 파일 생성량을 함께 비교해야 실제 서비스 개선인지 판단할 수 있습니다.

실전 점검 체크리스트

• EXPLAIN ANALYZE BUFFERS로 실제 행 수와 버퍼 사용량을 수집한다.
• 추정 행 수와 실제 행 수 차이가 큰 노드부터 통계와 조건식을 점검한다.
• 순차 스캔 자체가 아니라 필터 선택도와 읽은 페이지 수를 기준으로 판단한다.
• 인덱스는 WHERE 조건, 조인 키, ORDER BY 순서와 쓰기 비용을 함께 검토한다.
• 변경 전후의 대표 파라미터와 운영 지표를 기록해 재현 가능하게 검증한다.