PostgreSQL 느린 쿼리 분석: EXPLAIN ANALYZE를 읽는 순서

AI Editter

PostgreSQL 쿼리가 느리다고 EXPLAIN에서 Seq Scan부터 없애려 하면 오히려 돌아갈 수 있습니다. 작은 테이블이나 많은 행을 읽는 조회에서는 순차 스캔이 합리적일 수 있습니다. 먼저 잠금을 기다리는지, 실제 실행이 느린지, 예상 행 수가 틀렸는지를 구분하세요. 이 글은 PostgreSQL 16 문서를 기준으로 실행 계획을 읽는 순서를 정리합니다.

1. 실행 계획 전에 현재 대기부터 확인한다

평소 빠른 쿼리가 갑자기 멈췄다면 잠금이나 외부 자원 대기가 원인일 수 있습니다. 승인된 모니터링 권한으로 아래처럼 조회합니다. 권한에 따라 다른 세션 정보가 제한될 수 있으며, 쿼리 원문에는 개인정보가 포함될 수 있어 예시에서는 제외했습니다.

SELECT pid, state, wait_event_type, wait_event,
       clock_timestamp() - query_start AS elapsed,
       pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE datname = current_database()
  AND state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

wait_event_type=Lock이고 blockers가 있다면 차단 세션의 트랜잭션과 업무부터 조사합니다. 임의로 세션을 종료하면 롤백이나 서비스 오류가 발생할 수 있습니다. idle in transaction의 경과 시간도 현재 쿼리 실행 시간으로 단정하면 안 됩니다. 필드 의미는 pg_stat_activity 설명에서 확인할 수 있습니다.

2. EXPLAIN과 EXPLAIN ANALYZE의 차이를 알고 실행한다

EXPLAIN은 계획을 보여 주고 EXPLAIN ANALYZE는 쿼리를 실제 실행해 측정합니다. UPDATE·DELETE뿐 아니라 함수가 포함된 SELECT도 부작용 가능성을 확인해야 합니다. 큰 운영 쿼리를 분석한다고 같은 쿼리를 여러 번 돌리면 부하를 더할 수 있습니다. EXPLAIN 공식 문서의 실행·측정 특성을 먼저 확인하세요.

다음은 검증된 읽기 전용 SELECT를 짧은 제한으로 조사하는 예시입니다. 테이블·열·조건은 실제 쿼리로 바꾸며, 변경 쿼리에는 이 템플릿을 사용하지 않습니다.

BEGIN READ ONLY;
SET LOCAL statement_timeout = '10s';
SET LOCAL lock_timeout = '1s';
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT order_id, created_at
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMP '2026-10-01 00:00:00'
  AND created_at <  TIMESTAMP '2026-10-02 00:00:00';
ROLLBACK;

시간 제한으로 취소되면 완전한 분석 결과가 나오지 않을 수 있습니다. 그것만으로 제한을 풀지 말고 복제된 테스트 환경이나 더 작은 검증 범위를 사용합니다. TIMING OFF는 노드별 시간 측정 부담을 줄이지만 행 수·버퍼 통계 등은 볼 수 있습니다.

3. 비용 숫자보다 rows와 loops를 먼저 대조한다

다음은 읽는 법을 설명하기 위한 가상 계획 일부이며 실제 성능 측정값이 아닙니다.

Index Scan ... (cost=0.42..12.00 rows=1 width=16)
  (actual rows=500 loops=200)

예상은 호출당 1행인데 실제는 평균 500행입니다. loops가 200이면 해당 노드는 반복 실행됐고 총 처리 행은 대략 10만 행으로 볼 수 있습니다. 이런 오차가 Nested Loop 안에 있으면 작은 작업으로 예상한 부분이 크게 늘어납니다. actual rows는 loops가 여러 번일 때 평균이라는 점을 놓치지 마세요. Using EXPLAIN의 반복 노드 해석을 기준으로 합니다.

처음부터 모든 노드를 튜닝하지 말고 예상과 실제가 크게 갈라지는 가장 아래쪽 노드부터 찾습니다. 데이터 분포가 바뀌었는지, 조건 열의 상관관계가 있는지, 통계가 오래됐는지 확인합니다. ANALYZE는 통계 수집 작업이므로 큰 테이블은 운영 부하를 고려해 수행합니다.

4. 인덱스가 있는데도 느린 세 가지 경우

예를 들어 날짜별 조회에서 created_at::date = ...를 범위 조건으로 바꾸는 후보를 검토할 수 있습니다. 다만 timestamp와 timestamptz의 시간대 의미가 다르므로 결과가 동일한지 먼저 확인해야 합니다. 인덱스를 강제로 사용하도록 planner 옵션을 바꾸는 것은 원인 확인용 실험과 운영 설정을 구분해야 합니다. 표현식·복합 인덱스는 표현식 인덱스와 복합 인덱스 문서를 참고하세요.

5. BUFFERS와 결과의 정확성을 함께 비교한다

shared hit는 공유 버퍼에서 찾은 블록, shared read는 공유 버퍼로 읽어 들인 블록입니다. read를 곧바로 물리 디스크 I/O 횟수로 해석하면 OS 캐시 영향을 놓칩니다. 정렬에서 디스크를 사용하거나 임시 파일이 늘었다면 행 수·정렬 범위·메모리 설정을 함께 보세요. work_mem을 전역으로 크게 올리면 여러 세션과 노드에서 메모리 사용이 겹칠 수 있습니다.

수정 전후에는 같은 파라미터와 데이터 범위로 결과 행 수·주요 합계를 먼저 비교합니다. 그다음 실행 시간, 버퍼, 반복 횟수를 봅니다. 첫 실행과 캐시가 데워진 실행도 구분해 기록하세요. “인덱스를 탔다”보다 “결과가 같고 목표 시간 안에 안정적으로 끝났다”가 실제 튜닝의 완료 기준입니다.

JOIN을 바꾼 뒤 금액이 달라졌다면 JOIN으로 합계가 늘어나는 재현 예제로 관계의 다중성부터 확인하세요.