# PostgreSQL 느린 쿼리 최적화 완벽 가이드: EXPLAIN ANALYZE로 프로덕션 성능 10배 개선하기

프로덕션 장애 시나리오: 화요일 오후 3시, 쿼리 응답 시간 45초

평온한 어느 오후, 고객 지원팀으로부터 긴급 연락이 왔습니다. “대시보드가 로딩되지 않고 계속 멈춰 있습니다. 고객들이 대량으로 불만을 접수하고 있습니다.” 모니터링 대시보드를 확인하니 PostgreSQL 데이터베이스의 CPU 사용률이 **98%**에 도달해 있었습니다.

로그를 확인해보니, 평소 200ms 걸리던 특정 쿼리가 45초까지 증가했습니다. 해당 쿼리는 사용자 대시보드에서 최근 주문 내역을 조회하는 핵심 쿼리였고, 동시에 1,500명의 사용자가 접속하면서 데이터베이스가 감당할 수 없는 부하를 받고 있었습니다.

피해 규모:

• 서비스 장애: API 응답 시간 45초 (정상: 200ms)
• 데이터베이스 CPU: 98% 사용률 (커넥션 풀 고갈)
• 사용자 영향: 약 8,500명의 사용자가 타임아웃 경험
• 매출 손실: 약 $15,200 (주문 취소 및 이탈)
• 복구 시간: 긴급 인덱스 추가까지 2시간 17분
• 성능 개선: 쿼리 응답 시간 45초 → 95ms (475배 개선)

이 글에서는 PostgreSQL 느린 쿼리를 EXPLAIN ANALYZE로 분석하고 최적화하는 실전 기법을 다룹니다.

EXPLAIN ANALYZE란?

EXPLAIN ANALYZE는 PostgreSQL에서 쿼리 실행 계획과 실제 성능 지표를 확인하는 핵심 도구입니다.

EXPLAIN vs EXPLAIN ANALYZE 차이

-- EXPLAIN: 쿼리를 실행하지 않고 계획만 확인
EXPLAIN
SELECT * FROM orders WHERE user_id = 12345;

-- EXPLAIN ANALYZE: 실제로 쿼리를 실행하여 성능 측정
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345;

주요 차이점:

구분	EXPLAIN	EXPLAIN ANALYZE
실행 여부	실행 안 함	실제 실행
비용 추정	예상 비용만 표시	실제 비용 측정
시간 측정	없음	실제 실행 시간
행 수	예상 행 수	실제 처리된 행 수
부작용	없음	INSERT/UPDATE/DELETE 실제 실행됨

[주의] EXPLAIN ANALYZE는 실제로 쿼리를 실행합니다. 데이터 조작 쿼리(DML)를 테스트할 때는 반드시 트랜잭션으로 감싸야 합니다.

-- 위험! 실제로 데이터가 삽입됩니다!
EXPLAIN ANALYZE
INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');

-- 안전: 트랜잭션으로 감싸고 롤백
BEGIN;
EXPLAIN ANALYZE
INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');
ROLLBACK;

느린 쿼리 발견하기

방법 1: pg_stat_statements 활성화

가장 강력한 방법: PostgreSQL 확장 모듈로 모든 쿼리 통계를 수집합니다.

설정 (postgresql.conf):

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

재시작 후 활성화:

-- 확장 설치
CREATE EXTENSION pg_stat_statements;

-- 가장 느린 쿼리 10개 조회
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

결과 예시:

query                                   | calls | total_exec_time | mean_exec_time | max_exec_time | rows
----------------------------------------+-------+-----------------+----------------+---------------+------
SELECT * FROM orders WHERE...           | 1520  | 68400.23        | 45.00          | 62.34         | 1520
SELECT u.*, o.* FROM users u...         | 8942  | 35600.12        | 3.98           | 12.45         | 89420

분석:

• 첫 번째 쿼리: 평균 45초, 최대 62초 → 긴급 최적화 필요
• 두 번째 쿼리: 평균 4초, 호출 횟수 많음 → 캐싱 고려

방법 2: auto_explain (프로덕션 자동 로깅)

느린 쿼리 자동 탐지 및 로깅

설정 (postgresql.conf):

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000 # 1초 이상 쿼리 로깅
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = on
auto_explain.log_format = json # JSON 형식으로 저장

로그 확인:

# PostgreSQL 로그 파일에서 느린 쿼리 검색
tail -f /var/log/postgresql/postgresql-15-main.log | grep "duration"

EXPLAIN ANALYZE 출력 읽기

예시 쿼리: 사용자 주문 조회

EXPLAIN ANALYZE
SELECT
  o.id,
  o.total_amount,
  o.created_at,
  u.name,
  u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2025-01-01'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 100;

출력 (느린 버전 - 인덱스 없음):

QUERY PLAN
-----------------------------------------------------------------------------------
Limit (cost=125234.56..125234.81 rows=100 width=120)
  (actual time=45023.456..45023.789 rows=100 loops=1)
  -> Sort (cost=125234.56..128456.78 rows=1288889 width=120)
       (actual time=45023.454..45023.512 rows=100 loops=1)
       Sort Key: o.created_at DESC
       Sort Method: top-N heapsort Memory: 35kB
       -> Hash Join (cost=12456.00..98765.43 rows=1288889 width=120)
            (actual time=234.567..44876.123 rows=1289543 loops=1)
            Hash Cond: (o.user_id = u.id)
            -> Seq Scan on orders o (cost=0.00..65432.10 rows=1288889 width=80)
                 (actual time=0.045..42345.678 rows=1289543 loops=1)
                 Filter: ((created_at >= '2025-01-01'::date) AND (status = 'completed'::text))
                 Rows Removed by Filter: 3456789
            -> Hash (cost=10000.00..10000.00 rows=196480 width=40)
                 (actual time=123.456..123.456 rows=196480 loops=1)
                 Buckets: 262144 Batches: 1 Memory Usage: 15360kB
                 -> Seq Scan on users u (cost=0.00..10000.00 rows=196480 width=40)
                      (actual time=0.012..56.789 rows=196480 loops=1)
Planning Time: 2.345 ms
Execution Time: 45023.890 ms

핵심 지표 분석

1. actual time (실제 실행 시간) actual time=45023.456..45023.789

• 45023.456ms = 45초 (전체 쿼리 실행 시간)

2. Seq Scan (Sequential Scan = 전체 테이블 스캔) Seq Scan on orders o

• [문제] 전체 테이블을 처음부터 끝까지 스캔
• rows=1289543: 약 130만 행 스캔
• 42초 소요 (전체 시간의 대부분 차지)

3. Rows Removed by Filter Rows Removed by Filter: 3456789

• 346만 행을 스캔했지만, 필터링으로 대부분 버려졌습니다.
• 비효율: 필요 없는 행까지 모두 읽었습니다.

인덱스로 최적화하기

Sequential Scan → Index Scan 전환

문제 진단: orders 테이블에 created_at, status 컬럼에 대한 인덱스가 없습니다.

해결책: 복합 인덱스 생성

-- 복합 인덱스 생성 (created_at, status, user_id 순서 중요)
CREATE INDEX CONCURRENTLY idx_orders_created_status_user
ON orders (created_at DESC, status, user_id)
INCLUDE (total_amount);

-- CONCURRENTLY: 프로덕션 환경에서 락 없이 인덱스 생성
-- DESC: 내림차순 인덱스 (ORDER BY created_at DESC와 일치)
-- INCLUDE: 커버링 인덱스 (total_amount도 인덱스에 포함)

최적화 후 EXPLAIN ANALYZE:

QUERY PLAN
-----------------------------------------------------------------------------------
Limit (cost=0.85..345.67 rows=100 width=120)
  (actual time=0.123..95.456 rows=100 loops=1)
  -> Nested Loop (cost=0.85..4456.78 rows=1288889 width=120)
       (actual time=0.122..95.234 rows=100 loops=1)
       -> Index Scan using idx_orders_created_status_user on orders o
            (cost=0.43..2345.67 rows=1288889 width=80)
            (actual time=0.045..78.123 rows=100 loops=1)
            Index Cond: ((created_at >= '2025-01-01'::date) AND (status = 'completed'::text))
       -> Index Scan using users_pkey on users u
            (cost=0.42..8.44 rows=1 width=40)
            (actual time=0.012..0.012 rows=1 loops=100)
            Index Cond: (id = o.user_id)
Planning Time: 1.234 ms
Execution Time: 95.567 ms

성능 개선:

• 45,023ms → 95ms (475배 개선)
• Seq Scan → Index Scan 전환 성공

복합 인덱스 설계 전략

인덱스 컬럼 순서의 중요성

[잘못된 순서]

-- 순서 1: status, created_at
CREATE INDEX idx_bad ON orders (status, created_at DESC);

-- 문제: WHERE created_at >= '2025-01-01'을 먼저 필터링할 수 없음
-- status는 'completed', 'pending' 등 카디널리티(선택도)가 낮음

[올바른 순서]

-- 순서 2: created_at, status
CREATE INDEX idx_good ON orders (created_at DESC, status);

-- 장점:
-- 1. created_at 범위 검색 효율적
-- 2. status 추가 필터링도 인덱스에서 가능
-- 3. ORDER BY created_at DESC도 인덱스 사용

인덱스 순서 결정 규칙:

1. 등호 조건 (=) 먼저
2. 범위 조건 (>, <, BETWEEN) 다음
3. ORDER BY 마지막

커버링 인덱스 (Covering Index)

인덱스에 SELECT 컬럼까지 포함하여 테이블 접근 제거

-- 쿼리: name, email도 조회
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

-- 커버링 인덱스: 테이블 접근 불필요
CREATE INDEX idx_users_status_created_covering
ON users (status, created_at DESC)
INCLUDE (name, email); -- SELECT 컬럼 포함

효과: Index Only Scan이 발생하여 힙(Heap) 테이블 접근이 사라집니다.

쿼리 재작성으로 최적화하기

1. EXISTS vs IN 성능 비교

[느림] IN 서브쿼리:

SELECT *
FROM users u
WHERE u.id IN (
  SELECT DISTINCT user_id
  FROM orders
  WHERE created_at >= '2025-01-01'
);

[빠름] EXISTS: EXISTS는 조건을 만족하는 첫 번째 행을 찾으면 즉시 중단합니다.

SELECT *
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
    AND o.created_at >= '2025-01-01'
);

2. NOT EXISTS vs NOT IN 함정

[위험] NOT IN은 NULL 처리 문제 서브쿼리 결과에 NULL이 하나라도 있으면 전체 결과가 0개가 되는 치명적인 문제가 있습니다.

[안전] NOT EXISTS 사용 항상 NOT EXISTS를 사용하는 것이 안전하고 성능도 좋습니다.

3. JOIN vs 서브쿼리

[느림] SELECT 절 서브쿼리: 각 행마다 서브쿼리가 실행되는 N+1 문제가 발생합니다.

[빠름] JOIN 사용: 한 번의 JOIN으로 집계하는 것이 훨씬 효율적입니다.

4. LIMIT + OFFSET의 함정

[느림] OFFSET이 큰 경우: OFFSET 1000000은 앞의 100만 개 행을 읽고 버리는 작업을 수행합니다.

[빠름] Keyset Pagination: 마지막으로 조회한 값을 기억했다가 WHERE 조건으로 사용하는 방식입니다.

SELECT * FROM orders
WHERE created_at < '2025-01-15 10:30:00' -- 이전 페이지의 마지막 값
ORDER BY created_at DESC
LIMIT 100;

VACUUM과 ANALYZE: 통계 업데이트

PostgreSQL은 통계 정보를 기반으로 실행 계획을 수립합니다. 통계가 오래되면 옵티마이저가 잘못된 판단을 내릴 수 있습니다.

VACUUM vs VACUUM ANALYZE 차이

명령어	기능	실행 시간	권장 빈도
VACUUM	죽은 행(dead tuples) 제거	느림	매일
VACUUM ANALYZE	죽은 행 제거 + 통계 업데이트	더 느림	주 1회
ANALYZE	통계만 업데이트	빠름	매일 또는 대량 작업 후

프로덕션 권장 설정 (postgresql.conf):

autovacuum = on
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
autovacuum_max_workers = 3

2025년 최신 도구

1. pev2 (EXPLAIN 시각화)

무료 웹 기반 EXPLAIN 시각화 도구입니다. EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 결과를 붙여넣으면 직관적인 그래프로 보여줍니다.

2. pganalyze (프로덕션 모니터링)

엔터프라이즈급 모니터링 SaaS로, 실시간 쿼리 성능 분석과 자동 인덱스 추천 기능을 제공합니다.

마치며

PostgreSQL 성능 문제는 대부분 EXPLAIN ANALYZE를 통한 분석과 올바른 인덱싱으로 해결할 수 있습니다.

핵심 요약:

1. EXPLAIN ANALYZE 필수: 모든 최적화의 시작입니다.
2. Sequential Scan 제거: Index Scan으로 전환하세요.
3. 복합 인덱스 설계: 컬럼 순서(등호 → 범위 → 정렬)를 지키세요.
4. 커버링 인덱스: INCLUDE를 적극 활용하세요.
5. VACUUM ANALYZE: 통계 정보를 항상 최신으로 유지하세요.

성능 최적화는 한 번에 끝나지 않습니다. 정기적인 모니터링과 점검으로 여러분의 데이터베이스를 건강하게 유지하세요.