15.2 슬로우 쿼리 진단 워크플로우

"쿼리가 갑자기 느려졌다"는 사고는 매우 흔합니다. 당황하지 않고 30분 안에 원인을 좁히려면 손에 익은 순서가 필요합니다.

5단계 워크플로우

1단계 — 범위 식별

어떤 쿼리

SELECT round(mean_exec_time::numeric, 1) AS mean_ms,
       calls,
       round(total_exec_time::numeric / 1000, 1) AS total_sec,
       LEFT(query, 80) AS query
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC
 LIMIT 10;

total_exec_time이 전체 부하의 큰 부분을 차지하는 쿼리부터 봅니다. pg_stat_statements는 마지막 reset 이후의 누적 통계라 "최근 1시간"처럼 시간 창으로 걸러 주는 컬럼은 없습니다. 사고 구간만 좁혀 보려면 구간 시작에 pg_stat_statements_reset()으로 초기화한 뒤 다시 조회합니다. PostgreSQL 17부터는 각 항목에 stats_since(통계 수집이 시작된 시점) 컬럼이 있어 항목이 언제부터 집계됐는지 구분할 수 있습니다.

언제부터

pg_stat_statements의 추세를 Grafana나 외부 모니터링으로 그래프를 그립니다. 갑작스러운 spike가 나타난 시점이 곧 사고 시점입니다.

추세가 없으면 application 로그·alert 시간으로 추정합니다.

얼마나 느려졌나

• 평균 30ms → 800ms = 27배
• p95·p99 latency 추세 비교
• application 측정 vs DB 측정 비교 (네트워크인지 DB인지 구분)

2단계 — plan 캡처

auto_explain 로그

# 사고 시간대 PG 로그
grep -A 50 "duration:" /var/lib/pgsql/17/data/log/postgresql-*.log | less

auto_explain이 켜져 있으면(10.6) 사고 시점 실제 plan이 로그에 남습니다.

수동 EXPLAIN

운영 트래픽 받는 쿼리는 재현하기 어렵습니다. 가능한 한:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT json)
SELECT ...;

UPDATE/DELETE는 트랜잭션으로 감싸 ROLLBACK.

plan 시각화

depesz·dalibo(5.4)에 붙이면 느린 노드를 빠르게 찾습니다.

3단계 — 원인 가설

5.4·5.5에서 본 4가지 우선 신호입니다.

신호	가설	대응
actual rows >> estimated rows	통계 미스	ANALYZE, CREATE STATISTICS
Seq Scan 큰 테이블	인덱스 누락	CREATE INDEX
Sort Method: external merge	work_mem 부족	SET work_mem
Hash Batches > 1	같음	같음
loops 큰 Nested Loop	잘못된 조인	hash join 유도
Buffers: shared read >>	cache miss	hot 쿼리 인덱스·shared_buffers
wait_event = LWLock	공유 자원 경합	동시 부하 점검

추가 진단

-- 락 대기?
SELECT pid, query, pg_blocking_pids(pid)
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- 통계 신선도
SELECT relname, n_mod_since_analyze, last_analyze, last_autoanalyze
  FROM pg_stat_user_tables
 WHERE relname IN ('orders', 'users');

-- 인덱스 BLOAT
-- (6.7의 pgstattuple 쿼리)

-- 시스템 부하
-- top, iostat, vmstat

4단계 — 변경 (staging 먼저)

13.1의 순서대로 싼 도구부터:

1. ANALYZE
2. EXPLAIN 다시
3. 인덱스 추가
4. 쿼리 재작성
5. work_mem 등 파라미터
6. 파티셔닝·아키텍처

운영 환경에서는 staging 또는 standby에서 먼저 검증합니다.

5단계 — 검증 + 문서화

SELECT pg_stat_statements_reset();
-- (운영 1시간 또는 적절한 기간)
-- 다시 측정

변경 효과:

측정	변경 전	변경 후
mean_exec_time	800ms	30ms
total_exec_time	100h/day	5h/day
p95	1500ms	50ms

문서:

• 사고 일자·시간
• 영향 받은 쿼리·테이블
• 가설 + 검증 과정
• 변경 사항·날짜
• 효과 측정

다음 비슷한 사고에 절차서가 됩니다.

자주 보는 시나리오

Scenario A — autovacuum이 못 따라옴

plan: rows=10 → actual rows=1000000
last_autovacuum: 1주일 전
n_dead_tup: 50% of n_live_tup

대응:

ANALYZE big_table;
-- + autovacuum_*_scale_factor를 0.02로
ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.02);

Scenario B — 인덱스 누락

Seq Scan on orders, rows=10M, Filter: (status = 'pending')
Rows Removed by Filter: 9M

대응:

CREATE INDEX CONCURRENTLY ON orders(user_id) WHERE status = 'pending';
ANALYZE orders;

Scenario C — 갑작스러운 plan 변경

어제까지 Index Scan, 오늘 Seq Scan

원인 후보:

• ANALYZE 후 통계 변화 (selectivity 다름)
• 데이터 분포 큰 변화
• 새 데이터 INSERT 후

대응:

• pg_stats로 분포 확인
• pg_hint_plan으로 임시 강제 + 통계 개선

Scenario D — work_mem 부족

Sort Method: external merge  Disk: 256MB

대응:

SET LOCAL work_mem = '512MB';
-- 또는 분석 사용자에 ALTER ROLE ... SET work_mem

Scenario E — 락 대기

pg_stat_activity의 다른 backend가 같은 row UPDATE 중

대응:

-- 블로커 식별
SELECT pid, query FROM pg_stat_activity
 WHERE pid IN (SELECT unnest(pg_blocking_pids(<slow_pid>)));

-- 필요시 종료
SELECT pg_terminate_backend(<blocker_pid>);

Scenario F — 트래픽 폭증

TPS는 평소 1000, 사고 시 5000
모든 쿼리가 균등하게 느림

원인: connections·CPU·디스크 한계.

대응:

• pgBouncer pool 점검
• application 캐시 추가
• read replica 분리

운영 알람 임계

지표	WARN
query mean p95	평소 대비 3x
pg_stat_activity.active 수	max_connections × 80%
dead_tup > live_tup × 0.2	큰 테이블
disk %util	80%
connection waiting	pgBouncer cl_waiting > 0

정리

슬로우 쿼리 진단은 범위 식별에서 시작해 plan 캡처, 원인 가설, 변경, 검증의 다섯 단계를 따릅니다. 이 과정에서 auto_explain과 pg_stat_statements가 가장 큰 도움이 됩니다. plan을 볼 때는 rows 격차, 큰 테이블 Seq Scan, Sort spill, 과도한 loops 네 가지를 우선 신호로 삼습니다. 변경은 staging에서 검증한 뒤 운영에 반영하고, 시나리오별 대응은 절차서로 누적해 둡니다.