본문으로 건너뛰기
PostgreSQL EXPLAIN ANALYZE query optimization guide cover
백엔드 개발 / · 약 4분

PostgreSQL EXPLAIN ANALYZE 완벽 가이드: 느린 쿼리의 범인을 찾아라

#backend #sql #optimization

“인덱스도 걸었는데 왜 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';
명령어실제 실행실제 시간 표시프로덕션 안전성
EXPLAINXX안전
EXPLAIN ANALYZEOOSELECT만 안전
-- 안전한 방법
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: 0

Heap 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')

동작 방식:

  1. 인덱스에서 조건에 맞는 행의 위치를 비트맵으로 수집
  2. 비트맵을 정렬하여 테이블을 순차적으로 접근
  3. 랜덤 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 성능 최적화의 필수 도구입니다. 핵심 포인트를 정리하면:

  1. 실행 계획을 읽는 습관을 들이세요. 느린 쿼리가 있으면 먼저 EXPLAIN ANALYZE를 실행하세요.
  2. 예상 vs 실제 차이에 주목하세요. 큰 차이는 통계 정보 갱신(ANALYZE)이 필요하다는 신호입니다.
  3. Rows Removed by Filter가 크면 인덱스 추가를 검토하세요.
  4. Sort Method: external merge Disk가 보이면 work_mem을 늘리거나 인덱스로 정렬을 대체하세요.
  5. Buffers 정보로 I/O 패턴을 파악하세요.

쿼리 최적화는 반복적인 과정입니다. EXPLAIN ANALYZE로 현재 상태를 파악하고, 가설을 세우고, 인덱스나 설정을 변경하고, 다시 측정하는 사이클을 거치면 됩니다.

My avatar

글을 마치며

이 글이 도움이 되었기를 바랍니다. 궁금한 점이나 의견이 있다면 댓글로 남겨주세요.

더 많은 기술 인사이트와 개발 경험을 공유하고 있으니, 다른 포스트도 확인해보세요.

유럽살며 여행하며 코딩하는 노마드의 여정을 함께 나누며, 함께 성장하는 개발자 커뮤니티를 만들어가요! 🚀

관련 포스트
Circuit Breaker 패턴 개념도
· 약 8분

Circuit Breaker 패턴: 마이크로서비스가 도미노처럼 무너지는 것을 막는 법

외부 API 장애가 우리 서비스까지 전파되는 '연쇄 장애(Cascading Failure)'를 겪어보셨나요? 서킷 브레이커는 시스템의 퓨즈 역할을 합니다. Resilience4j 사용법부터 OPEN, CLOSED, HALF-OPEN 상태 전이의 원리, 그리고 적절한 타임아웃 설정 전략까지 상세히 알아봅니다.

backend system-design microservices +4
읽기