PostgreSQL 느린 쿼리 튜닝 - auto_explain+pg_stat_statements

서버가 느려졌는데 애플리케이션 로그에는 딱히 단서가 없고, 운영 환경이라 EXPLAIN (ANALYZE)를 함부로 돌리기도 부담스러운 상황이 자주 있습니다. 이럴 때 가장 실전적인 조합이 pg_stat_statements로 “어떤 쿼리가 문제인지”를 식별하고, auto_explain으로 “왜 느린지(실제 실행계획)”를 로그로 남기는 방식입니다.

이 글에서는 두 확장을 함께 써서 느린 쿼리를 선별 → 실행계획 수집 → 원인 분류 → 튜닝 적용 → 효과 검증까지 한 번에 이어지는 운영용 워크플로를 정리합니다. (RDS를 포함한 관리형 환경에서도 적용 가능한 포인트를 함께 다룹니다.)

> 참고: 성능 이슈가 autovacuum 폭주와 섞여 보이는 경우도 많습니다. CPU 100%와 쿼리 지연이 동시에 나타난다면 아래 글도 함께 확인해보세요. > - PostgreSQL RDS autovacuum 폭주로 CPU 100% 해결

왜 auto_explain + pg_stat_statements 조합이 강력한가

pg_stat_statements: “상위 N개 문제 쿼리”를 통계로 잡는다

pg_stat_statements는 쿼리문(정규화된 형태)별로 호출 횟수, 총 실행시간, 평균/최대 시간, 블록 I/O 등을 누적합니다. 즉, 운영에서 다음 질문에 즉답이 가능합니다.

• 총 시간을 가장 많이 잡아먹는 쿼리는?
• 평균이 느린 쿼리는?
• 호출이 많아서(저비용이지만) 누적 비용이 큰 쿼리는?
• shared/local block hit/read 비율이 이상한 쿼리는?

다만 여기에는 실행계획(플랜) 이 없습니다. “무엇이 문제인지”는 알지만 “왜 문제인지”는 모릅니다.

auto_explain: “느린 실행계획”을 로그로 자동 수집한다

auto_explain은 특정 기준(예: 200ms 이상)으로 느린 쿼리의 실행계획을 서버 로그에 자동으로 남깁니다. 운영에서 재현이 어려운 쿼리도, 조건만 맞으면 플랜이 쌓입니다.

하지만 auto_explain만 쓰면 이런 문제가 있습니다.

• 로그가 너무 많아질 수 있음(특히 파라미터 바인딩이 다양하면)
• 어떤 쿼리가 “가장 큰 문제”인지 우선순위 판단이 어려움

그래서 통계(식별) + 플랜(원인) 을 결합하는 것이 핵심입니다.

1) pg_stat_statements 설치 및 기본 설정

확장 활성화

DB에서 확장을 켭니다.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

postgresql.conf 핵심 파라미터

pg_stat_statements는 shared_preload_libraries가 필요합니다.

shared_preload_libraries = 'pg_stat_statements,auto_explain'

# pg_stat_statements
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = off
pg_stat_statements.save = on

• track = all: 함수 내부 쿼리까지 포함해 관측 범위를 넓힙니다.
• track_utility: VACUUM, EXPLAIN 같은 유틸 쿼리까지 추적할지. 보통은 꺼둡니다.
• max: 정규화 쿼리 엔트리 수. 너무 작으면 상위 쿼리가 덮어써집니다.

> RDS라면 파라미터 그룹에서 shared_preload_libraries를 설정하고 재부팅이 필요합니다.

자주 쓰는 상위 쿼리 조회 쿼리

(1) 총 실행시간 상위

SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

• 총 시간(total_exec_time) 은 “지금 당장 서비스에 영향을 주는” 쿼리를 찾기 좋습니다.

(2) 평균 실행시간 상위(콜 수 필터링)

SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  query
FROM pg_stat_statements
WHERE calls >= 50
ORDER BY mean_exec_time DESC
LIMIT 20;

• 평균이 높은데 표준편차도 큰 쿼리는 파라미터/데이터 분포에 따라 플랜이 흔들리는 경우가 많습니다.

(3) I/O 비중이 큰 쿼리(캐시 미스)

SELECT
  calls,
  shared_blks_read,
  shared_blks_hit,
  round(
    (shared_blks_read::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0)) * 100,
    2
  ) AS read_pct,
  query
FROM pg_stat_statements
WHERE (shared_blks_hit + shared_blks_read) > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

• read_pct가 높으면 디스크 읽기 의존이 크다는 뜻이며, 인덱스/조인 전략/워크메모리/테이블 팽창 등 다양한 원인이 후보가 됩니다.

2) auto_explain로 “느린 쿼리 실행계획”을 로그에 남기기

auto_explain 설정 예시(운영 친화)

# auto_explain
auto_explain.log_min_duration = '200ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = off
auto_explain.log_verbose = on
auto_explain.log_format = json
auto_explain.log_nested_statements = on

# 세션/샘플링을 활용해 로그 폭주 방지(가능한 버전에서)
# auto_explain.sample_rate = 0.05

권장 포인트:

• log_min_duration: 처음에는 200~500ms 정도로 시작해 로그량을 보며 조정합니다.
• log_analyze=on: 실제 실행 통계를 포함합니다(운영 부담이 아주 크진 않지만 0은 아닙니다).
• log_buffers=on: 캐시 히트/리드 힌트를 얻습니다.
• log_timing=off: 타이밍 측정 오버헤드를 줄입니다.
• log_format=json: 로그 파이프라인(CloudWatch/ELK/Datadog)에서 파싱이 쉬워집니다.

> auto_explain은 “느린 쿼리만” 대상으로 하기 때문에, log_statement=all 같은 무식한 설정보다 훨씬 안전합니다.

특정 역할(role)만 추적하기

운영에서는 관리자 작업/배치와 서비스 트래픽을 분리해서 관측하는 것이 중요합니다. 예를 들어 앱 계정만 auto_explain을 켜고 싶다면:

ALTER ROLE app_user SET auto_explain.log_min_duration = '200ms';
ALTER ROLE app_user SET auto_explain.log_analyze = on;
ALTER ROLE app_user SET auto_explain.log_buffers = on;
ALTER ROLE app_user SET auto_explain.log_format = 'json';

이렇게 하면 해당 role로 접속한 세션에만 적용됩니다.

3) “통계”와 “플랜”을 연결해 튜닝 우선순위 세우기

실무에서는 다음 순서가 효율적입니다.

1. pg_stat_statements에서 총 시간 상위 5~20개를 뽑음
2. 해당 쿼리(정규화된 형태)의 특징을 분류
   • 호출이 많다(calls)
   • I/O가 많다(shared_blks_read)
   • 편차가 크다(stddev)
3. auto_explain 로그에서 해당 쿼리의 실행계획을 찾아 원인을 확정

여기서 중요한 점: pg_stat_statements.query는 정규화되어 파라미터가 $1 형태로 보일 수 있고, auto_explain 로그에는 실제 SQL이 찍히거나(설정/드라이버에 따라) 형태가 다를 수 있습니다. 그래서 로그 검색 시에는 다음을 함께 활용합니다.

• 쿼리의 고유한 FROM/JOIN 구조
• 특정 테이블명/인덱스명
• JSON 플랜의 Plan 노드 타입(Seq Scan, Nested Loop 등)

4) auto_explain 플랜에서 자주 만나는 “느린 원인” 패턴과 처방

패턴 A: Seq Scan이 터진다(필터인데 인덱스를 안 탐)

플랜에서 Seq Scan on ... + Filter:가 보이고, Rows Removed by Filter가 큰 경우입니다.

가능한 원인/대응:

• 조건 컬럼에 인덱스가 없음 → 인덱스 생성
• 조건이 함수/캐스팅으로 감싸짐 → 표현식 인덱스 또는 쿼리 수정
• 통계가 부정확 → ANALYZE / autovacuum 튜닝

예: 이메일 대소문자 무시 검색

-- 나쁜 예: lower(email) 때문에 일반 인덱스(email)를 못 씀
SELECT * FROM users WHERE lower(email) = lower($1);

-- 해결 1) 표현식 인덱스
CREATE INDEX CONCURRENTLY idx_users_lower_email ON users ((lower(email)));

-- 해결 2) citext 사용(가능한 경우)
-- CREATE EXTENSION IF NOT EXISTS citext;
-- email 컬럼을 citext로 변경 후 = 비교

패턴 B: Nested Loop 폭발(조인 순서/인덱스 부재)

Nested Loop가 보이고, 내부 테이블이 반복적으로 스캔되며 버퍼 read가 증가합니다.

대응 체크리스트:

• 조인 키에 인덱스가 있는가?
• 조인 조건이 함수/캐스팅으로 망가져 있지 않은가?
• work_mem이 너무 작아 Hash Join이 못 뜨는가?

예: 주문-주문아이템 조인

-- 예시 쿼리
SELECT o.id, sum(oi.price)
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = $1
GROUP BY o.id;

-- 핵심 인덱스(존재 여부 확인)
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_order_items_order_id ON order_items(order_id);

패턴 C: Sort/Hash가 디스크로 스필(메모리 부족)

플랜에 Sort Method: external merge Disk: 또는 Hash 관련 Disk:가 뜨면, 정렬/해시가 메모리를 넘겨 디스크로 떨어진 것입니다.

대응:

• 세션/쿼리 단위로 work_mem 상향(전역 상향은 위험)
• 불필요한 정렬 제거(인덱스 정렬 활용)
• GROUP BY/ORDER BY 컬럼 인덱스 검토

-- 특정 트랜잭션/세션에서만 work_mem 상향
BEGIN;
SET LOCAL work_mem = '256MB';

-- 무거운 리포트 쿼리
SELECT user_id, count(*)
FROM events
WHERE created_at >= now() - interval '7 days'
GROUP BY user_id
ORDER BY count(*) DESC
LIMIT 100;

COMMIT;

패턴 D: 플랜이 흔들린다(파라미터에 따라 최적 플랜이 달라짐)

pg_stat_statements에서 표준편차가 크고, auto_explain 플랜이 케이스별로 다르게 나오는 경우입니다.

대응:

• 데이터 분포가 극단적이면 통계를 강화
  • ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000;
  • CREATE STATISTICS ...(확장 통계)
• prepared statement가 항상 generic plan을 쓰는 문제라면 드라이버/설정 점검
  • 예: 일부 환경에서 plan_cache_mode 조정 검토

-- 다중 컬럼 상관관계가 강할 때 확장 통계
CREATE STATISTICS st_events_user_type (dependencies)
ON user_id, event_type
FROM events;

ANALYZE events;

5) 튜닝 적용 전후를 pg_stat_statements로 검증하기

튜닝은 “좋아진 것 같다”가 아니라 숫자로 확인해야 합니다. 가장 간단한 방법은 리셋 후 재측정입니다(운영에서는 시간대 주의).

-- 전체 리셋(주의: 모든 통계가 초기화)
SELECT pg_stat_statements_reset();

리셋이 부담되면 대상 쿼리만 필터링해 전후를 비교하거나, 관측 기간을 정해 스냅샷을 떠두는 방식(외부 모니터링/대시보드)을 권장합니다.

검증 시 핵심 지표:

• mean_exec_time 감소
• total_exec_time 감소(호출 수가 같거나 유사할 때)
• shared_blks_read 감소(캐시 효율/인덱스 효율 개선)
• rows 변화(불필요한 결과 스캔 감소)

6) 운영에서의 안전장치: 로그 폭주, 개인정보, 비용

로그 폭주 방지

• auto_explain.log_min_duration을 너무 낮게 시작하지 않기
• 가능하면 샘플링 사용(auto_explain.sample_rate)
• role 단위 적용으로 범위를 제한

쿼리/파라미터에 개인정보가 섞이는 문제

auto_explain 로그에 SQL 텍스트가 남으면 개인정보/토큰이 포함될 수 있습니다. 애플리케이션에서 민감 값이 직접 SQL로 들어가지 않도록(바인딩 사용), 로그 접근권한을 최소화하세요.

비용(특히 RDS/CloudWatch)

JSON 플랜 로그는 크기가 큽니다. CloudWatch ingest 비용이 빠르게 늘 수 있으니:

• 임시로만 켜고(incident 대응), 문제 해결 후 끄기
• 임계치 상향(200ms→500ms)
• 특정 role/DB에만 적용

7) 추천 워크플로(현업용 체크리스트)

1. pg_stat_statements 활성화 후 대시보드/정기 쿼리로 Top SQL 확보
2. Top SQL 중 총 시간 상위와 평균 상위를 각각 10개 선정
3. auto_explain을 200500ms로 켜고 12시간 플랜 수집
4. 플랜에서 패턴 분류(Seq Scan/Nested Loop/Spill/플랜 흔들림)
5. 인덱스/쿼리/통계/메모리 파라미터를 “작게” 적용
   • 인덱스는 CONCURRENTLY로 운영 영향 최소화
   • work_mem은 전역이 아니라 SET LOCAL 우선
6. pg_stat_statements로 전후 비교, 필요 시 재수집

마무리

pg_stat_statements는 “누가 범인인지”를 빠르게 특정하고, auto_explain은 “범행 수법(실행계획)”을 증거로 남깁니다. 둘을 함께 쓰면 재현이 어려운 운영 환경에서도 느린 쿼리를 체계적으로 줄일 수 있습니다.

특히 Top SQL을 통계로 좁힌 다음, auto_explain로 해당 쿼리의 실제 플랜을 확보하는 흐름을 습관화하면, 감으로 인덱스를 찍는 튜닝에서 벗어나 훨씬 안정적으로 성능을 끌어올릴 수 있습니다.