백엔드 데이터 인프라 40편 — EXPLAIN 쿼리 계획 읽기

이 글은 백엔드 데이터 인프라 시리즈 70편 중 40편이에요. "이 쿼리 왜 느린가" 의 답이 EXPLAIN. PG(PostgreSQL 약칭) 운영 깊이의 핵심 도구.

EXPLAIN 기본

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

결과:

                          QUERY PLAN
─────────────────────────────────────────────────────────
 Index Scan using idx_users_email on users
   (cost=0.42..8.44 rows=1 width=128)
   Index Cond: (email = 'alice@example.com'::text)

읽는 법은 단순하다. Index Scan 이 사용 방법이고, cost=0.42..8.44 는 추정 비용을 시작 비용과 총 비용으로 나눠 보여준다. rows=1 은 추정 결과 행 수, width=128 은 행 평균 크기다.

EXPLAIN ANALYZE — 실제 실행

EXPLAIN ANALYZE SELECT ...;

ANALYZE = 실제 실행 + 실측 추가.

 Index Scan using idx_users_email on users
   (cost=0.42..8.44 rows=1 width=128)
   (actual time=0.025..0.026 rows=1 loops=1)
   Index Cond: (email = 'alice@example.com')
 Planning Time: 0.150 ms
 Execution Time: 0.045 ms

actual time 이 실측이다. cost 와 비교해서 차이가 크면 통계가 부정확하다는 신호.

옵션 추가

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

운영 진단 표준 = EXPLAIN (ANALYZE, BUFFERS) ....

스캔 노드 4가지

Seq Scan          → 풀스캔 (인덱스 미사용)
Index Scan        → 인덱스 + 테이블 접근
Index Only Scan   → 인덱스만으로 (Heap Fetches: 0 일 때 최고)
Bitmap Index Scan → 여러 인덱스·범위

Seq Scan — 풀스캔

Seq Scan on users (cost=0.00..18334.00 rows=100 width=...)
  Filter: (email = '...')
  Rows Removed by Filter: 999900

Rows Removed 가 크면 — 인덱스 박을 곳.

Index Scan

Index Scan using idx_users_email on users
  Index Cond: (email = '...')

Index Cond 가 인덱스에서 찾는 조건이다. 여기에 Filter 가 추가로 붙으면 인덱스로 좁힌 뒤 추가 필터가 도는 형태.

Index Only Scan — 최고

Index Only Scan using idx_users_email on users
  Index Cond: (email = '...')
  Heap Fetches: 0

Heap Fetches(테이블 본체 접근 횟수) 가 0 이면 테이블 안 봐도 답이 나온다는 뜻. INCLUDE 인덱스(필요한 컬럼을 인덱스 페이지에 함께 저장하는 방식) 또는 "필요한 컬럼 모두 인덱스에" 가 핵심.

Bitmap Index Scan

Bitmap Heap Scan on users
  ->  BitmapOr
        ->  Bitmap Index Scan on idx_users_city
        ->  Bitmap Index Scan on idx_users_country

여러 인덱스 결합. "city=Seoul OR country=Korea" 같은 OR 검색.

JOIN 노드 3가지

Nested Loop  → 한쪽 행마다 다른 쪽 스캔 (작은 테이블)
Hash Join    → 해시 테이블 + 룩업 (중간)
Merge Join   → 양쪽 정렬 후 병합 (인덱스 정렬)

Nested Loop

Nested Loop  (cost=...)
  ->  Index Scan on users  (rows=10)
  ->  Index Scan on orders
        Index Cond: (user_id = users.id)

사용자 10명 × 각자 orders 인덱스 검색. 작은 외부 + 인덱스 내부 = 매우 빠름.

Hash Join

Hash Join
  Hash Cond: (users.id = orders.user_id)
  ->  Seq Scan on orders
  ->  Hash
        ->  Seq Scan on users

users 로 해시 테이블 만들고 — orders 스캔. 큰 테이블 + 작은 빌드 시 효율.

Merge Join

Merge Join
  Merge Cond: (users.id = orders.user_id)
  ->  Index Scan using idx_users_id
  ->  Index Scan using idx_orders_user_id

양쪽 인덱스 정렬 = 한 번에 병합. 매우 빠름.

PG 계획자가 자동 선택. "왜 Nested Loop 가 아닌 Hash 인가" 같은 의문 = 통계·비용 추정 차이.

정렬·집계 노드

Sort  (cost=... )
  Sort Key: created_at DESC
  Sort Method: quicksort  Memory: 25kB
  ->  ...

Sort Method = quicksort (메모리) · external merge (디스크). 디스크 정렬 = 느림. work_mem(정렬·해시 작업용 메모리 한도) 늘리기.

HashAggregate  (cost=...)
  Group Key: category
  ->  ...

GROUP BY 의 해시 집계. 큰 그룹 = 메모리 부담.

Aggregate  (cost=...)
  ->  ...

집계 함수 (COUNT·SUM 등). 단순한 경우.

Limit 노드

Limit
  ->  Sort
        Sort Key: created_at DESC
        ->  Seq Scan

LIMIT 가 잘 박히면 — Limit ▸ Index Scan 형태로 정렬 X. 인덱스 ORDER BY 방향 활용.

CTE·서브쿼리

CTE Scan on cte_name
  ->  ... (CTE 정의)

CTE(Common Table Expression, WITH 절 임시 결과)는 PG 12+ 부터 "인라인 자동" (옵션). 옛 동작 (CTE materialize) 이 필요하면 WITH ... AS MATERIALIZED.

비용·실측 분석

비용 의미

cost=START..TOTAL  rows=N  width=W

START 는 첫 행 반환까지 드는 비용으로 LIMIT 에 유리한 지표고, TOTAL 은 모든 행을 처리할 때까지의 비용이다. rows 는 추정 행 수, width 는 한 행의 평균 바이트.

실측 비교

actual time=START..TOTAL rows=N loops=L

START..TOTAL 은 실제 ms 단위 시간, rows 는 실제 결과 행 수다. 그리고 loops 가 노드 실행 횟수 — Nested Loop 안쪽 inner 가 여러 번 도는 경우 여기서 잡힌다.

loops=1000 + actual time 10ms = 노드 자체는 10ms, 총 10초.

추정 vs 실측 어긋남

rows=100 (estimated) vs rows=10000 (actual)

100배 차이 = 통계 부정확. ANALYZE table; 실행.

BUFFERS — I/O 통계

Index Scan
  Buffers: shared hit=5 read=2

hit 가 메모리 캐시에서 바로 읽어온 횟수(빠름), read 가 디스크에서 읽어온 횟수(느림)다. read 가 많으면 캐시 부족이라는 신호 — 메모리를 늘리거나 자주 안 쓰는 데이터를 분리하면 된다.

시각화 도구

복잡한 EXPLAIN 출력은 — 도구 활용:

• explain.depesz.com — 가장 유명
• explain.dalibo.com — Dalibo 시각화
• pgMustard — 분석 + 권장

JSON 출력을 붙여넣어 시각화.

실전 — 느린 쿼리 진단

1. 느린 쿼리 찾기

-- pg_stat_statements 확장
CREATE EXTENSION pg_stat_statements;

SELECT
    query,
    calls,
    total_exec_time / calls AS avg_ms,
    rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

pg_stat_statements(쿼리 실행 통계 수집 익스텐션)로 운영 환경의 "느린 쿼리 TOP" 을 뽑는다.

2. EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) <slow_query>;

3. 진단

Seq Scan 에 Rows Removed 가 크게 잡히면 인덱스를 박을 자리고, Sort Method 가 external 로 떨어졌으면 work_mem 을 늘려야 한다. Nested Loop 에 loops 가 크면 JOIN 쪽 인덱스 점검, 추정과 실측이 한참 어긋나면 ANALYZE.

4. 적용 + 검증

인덱스 추가 → 다시 EXPLAIN → 확인.

함정 5가지

(1) EXPLAIN 만 (ANALYZE 없음)

추정만 — 실제 동작 모름. ANALYZE 무조건.

(2) BUFFERS 안 봄

캐시 hit 율 모름. BUFFERS 표준.

(3) PROD 에 EXPLAIN ANALYZE 무서워서 못 함

EXPLAIN ANALYZE = 실제 쿼리 실행. UPDATE·DELETE 에 ANALYZE 박으면 — 진짜 변경. BEGIN-ROLLBACK 으로 안전.

BEGIN;
EXPLAIN ANALYZE UPDATE users SET ...;
ROLLBACK;

(4) Cost vs Time 혼동

Cost = 추정 단위. Time = 실측 ms. 비교 X.

(5) 통계 갱신 잊음

대량 DML(Data Manipulation Language, INSERT·UPDATE·DELETE 등) 후 ANALYZE 안 하면 — 계획자 잘못 판단. 주기적.

한 줄 정리 — EXPLAIN ANALYZE BUFFERS = 운영 진단 표준. 스캔 4종·JOIN 3종·정렬·집계 노드 의미. 추정 vs 실측 어긋남 = ANALYZE. UPDATE 진단 = BEGIN-ROLLBACK 안전. pg_stat_statements + 시각화 도구.

시험 직전 한 번 더 — EXPLAIN 입문자가 매번 헷갈리는 것

• EXPLAIN = 추정 계획만
• EXPLAIN ANALYZE = 실제 실행 + 실측
• BUFFERS = I/O 통계 (운영 필수)
• 스캔 = Seq·Index·Index Only·Bitmap
• Index Only + Heap Fetches: 0 = 최고
• Rows Removed by Filter = 인덱스 박을 곳
• JOIN = Nested Loop·Hash·Merge
• Nested Loop = 작은 외부 + 인덱스 내부
• Hash Join = 큰 + 작은 빌드
• Merge Join = 양쪽 정렬
• Sort Method: quicksort vs external merge
• work_mem 늘리면 = 메모리 정렬
• HashAggregate = GROUP BY
• cost = 추정 비용 단위
• actual time = 실측 ms
• loops = 노드 실행 횟수 (총 시간 = time × loops)
• 추정 vs 실측 차이 100배+ = ANALYZE
• shared hit·read = 캐시 vs 디스크
• pg_stat_statements = 느린 쿼리 통계
• UPDATE/DELETE 진단 = BEGIN-ROLLBACK
• explain.depesz.com 시각화
• 통계 부정확 = ANALYZE 수동
• 한국 백엔드 = EXPLAIN ANALYZE BUFFERS 표준
• 운영 진단 = 매주 느린 쿼리 TOP 점검

시리즈 다른 편 (앞뒤 글 모음)

이전 글:

• 35편 — 인덱스 종류 6가지 B-Tree·Hash·GIN·GiST·BRIN·SP-GiST
• 36편 — 인덱스 종합 운영 전략과 튜닝
• 37편 — MVCC 소개
• 38편 — MVCC 격리 수준과 동시성 함정
• 39편 — 전문 검색 to_tsvector to_tsquery

다음 글:

• 41편 — 성능 팁 종합
• 42편 — 운영 설치 바이너리·Docker·관리 서비스
• 43편 — postgresql.conf 핵심 설정
• 44편 — 사용자·역할 관리 CREATE ROLE GRANT
• 45편 — 데이터베이스 관리 생성·복제·드롭