# PostgreSQL EXPLAIN ANALYZE 완벽 가이드: 느린 쿼리의 범인을 찾아라
Table of Contents
“인덱스도 걸었는데 왜 Full Table Scan을 하죠?”
PostgreSQL을 사용하다 보면 누구나 한 번쯤 마주치는 상황입니다. 분명히 인덱스를 생성했는데 쿼리가 느리고, 왜 그런지 알 수 없어 답답한 경험. 이 문제를 해결하는 열쇠가 바로 EXPLAIN ANALYZE입니다.
EXPLAIN ANALYZE는 PostgreSQL이 쿼리를 어떻게 실행하는지 상세하게 보여주는 진단 도구입니다. 이것을 제대로 읽을 수 있다면, 느린 쿼리의 원인을 정확히 파악하고 최적화할 수 있습니다.
EXPLAIN vs EXPLAIN ANALYZE
먼저 두 명령어의 차이를 명확히 알아야 합니다.
-- 실행 계획만 보여줌 (실제 실행 X)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- 실제 실행 후 실행 계획 + 실제 소요 시간 표시
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';| 명령어 | 실제 실행 | 실제 시간 표시 | 프로덕션 안전성 |
|---|---|---|---|
| EXPLAIN | X | X | 안전 |
| EXPLAIN ANALYZE | O | O | SELECT만 안전 |
:::warning EXPLAIN ANALYZE는 쿼리를 실제로 실행합니다. UPDATE, DELETE 쿼리에 사용할 때는 반드시 트랜잭션으로 감싸야 합니다. :::
-- 안전한 방법
BEGIN;
EXPLAIN ANALYZE UPDATE users SET name = 'test' WHERE id = 1;
ROLLBACK;실행 계획 읽는 법
실행 계획을 처음 보면 암호처럼 느껴질 수 있습니다. 하나씩 해석해 봅시다.
기본 구조
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;출력 예시:
Seq Scan on orders (cost=0.00..25461.00 rows=100 width=72) (actual time=0.031..142.315 rows=87 loops=1)
Filter: (user_id = 12345)
Rows Removed by Filter: 999913
Planning Time: 0.082 ms
Execution Time: 142.347 ms각 부분의 의미:
- Seq Scan on orders: 테이블 전체를 순차적으로 스캔
- cost=0.00..25461.00: 예상 비용 (시작..총비용)
- rows=100: 예상 반환 행 수
- width=72: 행당 예상 바이트 수
- actual time=0.031..142.315: 실제 소요 시간 (ms)
- rows=87: 실제 반환 행 수
- loops=1: 실행 횟수
- Rows Removed by Filter: 필터로 제거된 행 수
여기서 Rows Removed by Filter: 999913이 핵심입니다. 100만 개 중 87개만 필요한데 99만 9천 개를 읽고 버렸다는 뜻입니다.
예상 vs 실제 차이가 큰 경우
Index Scan using idx_orders_user on orders (cost=0.42..8.44 rows=1 width=72) (actual time=0.025..0.089 rows=87 loops=1)예상 rows=1인데 실제 rows=87이면 통계 정보가 오래된 것입니다.
-- 테이블 통계 업데이트
ANALYZE orders;주요 Scan 타입 이해하기
PostgreSQL은 데이터를 읽는 여러 방법을 가지고 있습니다.
1. Seq Scan (Sequential Scan)
테이블 전체를 처음부터 끝까지 읽습니다.
Seq Scan on users (cost=0.00..18334.00 rows=1000000 width=37)언제 발생하나?
- 인덱스가 없을 때
- 반환 데이터가 전체의 5-10% 이상일 때 (인덱스보다 효율적일 수 있음)
- 인덱스 컬럼에 함수 적용 시
-- 인덱스 무시됨 (함수 적용)
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 인덱스 사용됨
SELECT * FROM users WHERE email = 'test@example.com';2. Index Scan
인덱스를 통해 테이블 데이터를 읽습니다.
Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=37)
Index Cond: (email = 'test@example.com'::text)가장 일반적이고 효율적인 방식입니다.
3. Index Only Scan
인덱스만으로 쿼리를 완료합니다 (테이블 접근 X).
Index Only Scan using idx_users_email_created on users (cost=0.42..4.44 rows=1 width=12)
Index Cond: (email = 'test@example.com'::text)
Heap Fetches: 0Heap Fetches: 0이 핵심입니다. 테이블(Heap)에 전혀 접근하지 않았다는 뜻입니다.
-- Index Only Scan을 위한 covering index
CREATE INDEX idx_users_email_created ON users(email) INCLUDE (created_at);
-- 이 쿼리는 Index Only Scan 사용
SELECT email, created_at FROM users WHERE email = 'test@example.com';4. Bitmap Index Scan
여러 조건을 결합하거나 많은 행을 반환할 때 사용합니다.
Bitmap Heap Scan on orders (cost=97.12..8721.86 rows=5000 width=72)
Recheck Cond: (status = 'pending')
-> Bitmap Index Scan on idx_orders_status (cost=0.00..95.87 rows=5000 width=0)
Index Cond: (status = 'pending')동작 방식:
- 인덱스에서 조건에 맞는 행의 위치를 비트맵으로 수집
- 비트맵을 정렬하여 테이블을 순차적으로 접근
- 랜덤 I/O를 줄여 대량 데이터에 효율적
Join 방식 이해하기
1. Nested Loop Join
Nested Loop (cost=0.42..16.47 rows=1 width=109)
-> Index Scan using idx_orders_id on orders (cost=0.42..8.44 rows=1 width=72)
Index Cond: (id = 12345)
-> Index Scan using idx_users_id on users (cost=0.42..8.02 rows=1 width=37)
Index Cond: (id = orders.user_id)- 외부 테이블의 각 행마다 내부 테이블을 검색
- 소량의 데이터 조인에 효율적
- O(n*m) 복잡도이지만 인덱스가 있으면 매우 빠름
2. Hash Join
Hash Join (cost=1234.00..5678.00 rows=10000 width=109)
Hash Cond: (orders.user_id = users.id)
-> Seq Scan on orders (cost=0.00..3456.00 rows=100000 width=72)
-> Hash (cost=1000.00..1000.00 rows=10000 width=37)
-> Seq Scan on users (cost=0.00..1000.00 rows=10000 width=37)- 작은 테이블로 해시 테이블 생성
- 큰 테이블을 스캔하며 해시 테이블 조회
- 대량의 데이터 조인에 효율적
- 메모리 사용량 주의 필요
3. Merge Join
Merge Join (cost=1234.00..5678.00 rows=10000 width=109)
Merge Cond: (orders.user_id = users.id)
-> Sort (cost=...)
Sort Key: orders.user_id
-> Seq Scan on orders
-> Sort (cost=...)
Sort Key: users.id
-> Seq Scan on users- 양쪽 테이블을 정렬 후 병합
- 이미 정렬된 데이터에 효율적
- 인덱스가 있으면 정렬 단계 생략 가능
성능 문제 진단 패턴
패턴 1: 예상과 실제 행 수 차이
Index Scan on orders (rows=1) (actual rows=50000)원인: 통계 정보 부정확 해결:
ANALYZE orders;
-- 또는 더 정밀한 통계 수집
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000;
ANALYZE orders;패턴 2: 높은 Rows Removed by Filter
Seq Scan on orders (actual rows=100)
Rows Removed by Filter: 999900원인: 적절한 인덱스 부재 해결:
CREATE INDEX idx_orders_user_id ON orders(user_id);패턴 3: Sort 병목
Sort (cost=154987.47..157487.47 rows=1000000 width=37) (actual time=2456.123..3012.456 rows=1000000 loops=1)
Sort Key: created_at
Sort Method: external merge Disk: 45678kB원인: work_mem 부족으로 디스크 정렬 발생
해결:
-- 세션 단위로 work_mem 증가
SET work_mem = '256MB';
-- 또는 인덱스로 정렬 회피
CREATE INDEX idx_orders_created ON orders(created_at DESC);패턴 4: 느린 Nested Loop
Nested Loop (actual time=0.025..15234.567 rows=100000 loops=1)
-> Seq Scan on large_table (actual rows=100000)
-> Index Scan on another_table (actual loops=100000)원인: 외부 테이블이 너무 커서 반복 횟수 과다 해결:
-- Hash Join 유도
SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT...;
SET enable_nestloop = on;실전 최적화 예제
사례 1: 복합 조건 쿼리
문제 쿼리:
SELECT * FROM orders
WHERE user_id = 12345
AND status = 'completed'
AND created_at > '2024-01-01';실행 계획 (문제):
Seq Scan on orders (actual time=0.023..234.567 rows=50)
Filter: ((user_id = 12345) AND (status = 'completed') AND (created_at > '2024-01-01'))
Rows Removed by Filter: 999950해결: 복합 인덱스 생성 (선택도 순서 고려)
-- user_id가 가장 선택적 -> 첫 번째 위치
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at);사례 2: LIKE 패턴 검색
문제 쿼리:
SELECT * FROM products WHERE name LIKE '%phone%';실행 계획 (문제):
Seq Scan on products (actual time=0.015..456.789 rows=1000)
Filter: (name ~~ '%phone%'::text)해결: Full-text search 또는 pg_trgm 확장
-- pg_trgm 확장 설치
CREATE EXTENSION pg_trgm;
-- GIN 인덱스 생성
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);
-- 쿼리 변경
SELECT * FROM products WHERE name ILIKE '%phone%';사례 3: 대량 데이터 집계
문제 쿼리:
SELECT user_id, COUNT(*), SUM(amount)
FROM orders
WHERE created_at > '2024-01-01'
GROUP BY user_id
ORDER BY SUM(amount) DESC
LIMIT 100;해결: 부분 인덱스(Partial Index) + 커버링 인덱스
-- 조건에 맞는 부분 인덱스
CREATE INDEX idx_orders_recent ON orders(user_id, amount)
WHERE created_at > '2024-01-01';유용한 EXPLAIN 옵션
BUFFERS: I/O 통계
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 12345;출력:
Index Scan using idx_orders_user on orders (actual time=0.025..0.089 rows=87 loops=1)
Index Cond: (user_id = 12345)
Buffers: shared hit=12 read=3- shared hit: 캐시(메모리)에서 읽은 블록 수
- shared read: 디스크에서 읽은 블록 수
hit이 높으면 좋고, read가 높으면 캐시가 부족하거나 비효율적인 쿼리일 수 있습니다.
FORMAT JSON: 상세 정보
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders WHERE user_id = 12345;JSON 형식은 프로그래밍으로 파싱하거나 시각화 도구(pev2 등)에서 사용할 때 유용합니다.
정리
EXPLAIN ANALYZE는 PostgreSQL 성능 최적화의 필수 도구입니다. 핵심 포인트를 정리하면:
- 실행 계획을 읽는 습관을 들이세요. 느린 쿼리가 있으면 먼저
EXPLAIN ANALYZE를 실행하세요. - 예상 vs 실제 차이에 주목하세요. 큰 차이는 통계 정보 갱신(
ANALYZE)이 필요하다는 신호입니다. - Rows Removed by Filter가 크면 인덱스 추가를 검토하세요.
- Sort Method: external merge Disk가 보이면
work_mem을 늘리거나 인덱스로 정렬을 대체하세요. - Buffers 정보로 I/O 패턴을 파악하세요.
쿼리 최적화는 반복적인 과정입니다. EXPLAIN ANALYZE로 현재 상태를 파악하고, 가설을 세우고, 인덱스나 설정을 변경하고, 다시 측정하는 사이클을 거치면 됩니다.
