[DB] EXPLAIN 읽기: scan, join, sort, cost

EXPLAIN, EXPLAIN ANALYZE, query plan, Seq Scan, Index Scan, Hash Join, Nested Loop, Merge Join

정의

EXPLAIN = 쿼리 실행 계획 표시. EXPLAIN ANALYZE = 실제로 실행 + 실제 시간 측정.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.name;

Scan 종류

flowchart TD
    Scan{Scan 종류}
    Scan --> Seq["Seq Scan<br/>전체 테이블 순회"]
    Scan --> Idx["Index Scan<br/>인덱스 → 테이블"]
    Scan --> IdxOnly["Index Only Scan<br/>인덱스에서만"]
    Scan --> Bitmap["Bitmap Scan<br/>bitmap 으로 페이지 모음"]

Scan	의미	적합
Seq Scan	전체 테이블	작은 테이블, 대부분 row 반환
Index Scan	인덱스 → row	선택적 (적은 row)
Index Only Scan	인덱스 only (heap 안 봄)	covering index
Bitmap Index Scan	여러 인덱스 결합 + bitmap	다 인덱스 조합
Bitmap Heap Scan	bitmap → 페이지 fetch	위와 짝
Tid Scan	직접 tid 접근	극히 드뭄

Join 종류

Join	동작	적합
Nested Loop	for outer { for inner }	작은 inner + 인덱스
Hash Join	hash 빌드 → 룩업	큰 양쪽, 등가 조건
Merge Join	정렬된 양쪽 머지	양쪽 정렬됨, 범위 조건

위 애니메이션은 Grace Hash Join (메모리 부족 시 분할 hash). 대용량 join 의 일반 직관.

EXPLAIN ANALYZE 출력 읽기

Aggregate  (cost=1024.50..1024.51 rows=1 width=8) (actual time=15.234..15.236 rows=1 loops=1)
  ->  Hash Join  (cost=12.50..1020.50 rows=1600 width=4) (actual time=0.234..14.123 rows=1542 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=0.00..900.00 rows=50000 width=8) (actual time=0.012..5.234 rows=49832 loops=1)
        ->  Hash  (cost=10.00..10.00 rows=200 width=8) (actual time=0.190..0.190 rows=215 loops=1)
              ->  Index Scan using idx_users_created on users u  (cost=0.42..10.00 rows=200 width=8) (actual time=0.012..0.123 rows=215 loops=1)
                    Index Cond: (created_at > '2026-01-01'::date)
Planning Time: 0.345 ms
Execution Time: 15.456 ms

필드	의미
cost=A..B	A = 시작 비용, B = 총 비용 (페이지 수 단위)
rows=N	예상 row 수
width=N	평균 row 바이트
actual time=A..B	실제 시작/종료 ms
rows=actual	실제 row 수
loops=N	이 노드가 N번 실행
Planning Time	optimizer 시간
Execution Time	실행 시간

자주 보는 안티 패턴

1. Seq Scan on 큰 테이블

Seq Scan on big_table  (rows=10000000)
  Filter: (status = 'pending')
  Rows Removed by Filter: 9990000

→ 인덱스 추가 또는 partial index (WHERE status = 'pending').

2. Nested Loop on 큰 양쪽

Nested Loop  (loops=100000)
  ->  Index Scan on table_a
  ->  Index Scan on table_b

→ 한쪽이 수만 row 면 Hash Join 으로 가야 함. set enable_nestloop = off 로 테스트.

3. Sort + Disk

Sort  (Sort Method: external merge  Disk: 1234kB)

→ work_mem 부족. session 또는 query 단위 증가.

외부 정렬 (disk spill) 동작 직관. 임시 파일이 디스크에 쓰임.

4. Bitmap Heap Scan + 작은 rows

Bitmap Heap Scan  (rows=10)
  ->  Bitmap Index Scan

→ 좋은 패턴. 인덱스가 적절히 작동.

자주 쓰는 옵션

EXPLAIN (
  ANALYZE,      -- 실제 실행
  BUFFERS,      -- shared/local/temp buffer hits
  VERBOSE,      -- 컬럼 / 함수 상세
  COSTS OFF,    -- cost 숨김 (clean diff)
  FORMAT JSON   -- 도구 분석
) ...

시각화 도구

도구	비고
explain.depesz.com	색상 코딩
pev2 (Dalibo)	tree 시각화
pg_stat_statements	누적 통계
auto_explain	느린 쿼리 자동 로깅

흔한 함정

PostgreSQL Cost Model

플래너가 계획을 선택하는 비용 단위:

파라미터	기본값	의미
seq_page_cost	1.0	순차 페이지 읽기 단위 비용
random_page_cost	4.0	랜덤 페이지 읽기 (SSD 환경 1.1 권장)
cpu_tuple_cost	0.01	row 처리 비용
cpu_index_tuple_cost	0.005	인덱스 항목 처리 비용
cpu_operator_cost	0.0025	연산자 실행 비용

통계 (Statistics)

플래너는 통계 기반으로 plan 선택. 통계가 오래되면 잘못된 plan 선택.

flowchart LR
    Table["테이블 변경<br/>INSERT / UPDATE / DELETE"]
    Table --> AutoVacuum["autovacuum analyze<br/>자동 통계 갱신"]
    AutoVacuum --> PgStats["pg_statistic<br/>pg_stats 뷰"]
    PgStats --> Planner["Query Planner<br/>Plan 선택"]

-- 수동 통계 갱신
ANALYZE users;

-- 특정 컬럼만
ANALYZE users (created_at, status);

-- 통계 확인
SELECT tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'users';

지표	의미
n_distinct	유니크 값 수 추정 (-1 = 선형 비례)
correlation	물리 순서와 논리 순서 상관 (1 = 완전 일치)
most_common_vals	가장 흔한 값 목록
histogram_bounds	균등 분포 히스토그램 경계

Parallel Query

PostgreSQL 9.6+ 에서 대형 Seq Scan / Aggregate / Join 의 병렬 실행.

flowchart TD
    Gather["Gather<br/>결과 수집 (Leader)"]
    Gather --> W1["Worker 1<br/>Partial Seq Scan"]
    Gather --> W2["Worker 2<br/>Partial Seq Scan"]
    Gather --> W3["Worker 3<br/>Partial Seq Scan"]

Gather  (cost=1000.00..5000.00 rows=50000 width=8)
  Workers Planned: 3
  ->  Parallel Seq Scan on big_table
        Filter: (status = 'active')
        Rows Removed by Filter: 1234

파라미터	의미
max_parallel_workers_per_gather	gather 당 최대 worker (default 2)
max_parallel_workers	전체 최대 병렬 worker
parallel_tuple_cost	병렬 row 전달 비용
min_parallel_table_scan_size	병렬 스캔 최소 테이블 크기

인덱스 선택 팁

-- Partial Index: 조건 좁히기
CREATE INDEX idx_pending ON orders (created_at)
WHERE status = 'pending';

-- Covering Index: Index Only Scan 유도
CREATE INDEX idx_cover ON users (created_at) INCLUDE (name, email);

-- 복합 인덱스: 카디널리티 높은 컬럼 먼저
CREATE INDEX idx_multi ON logs (user_id, created_at);
-- user_id 조건으로 범위 좁힌 후 created_at 정렬

인덱스 종류	언제 쓰나
B-Tree (기본)	등가, 범위, 정렬
GIN	배열, JSONB, 전문 검색
GiST	지리, 범위 타입
Hash	등가만 (PostgreSQL 10+)

자세히는 btree-indexing, gin-gist-hash-indexes.

EXPLAIN 실전 워크플로

flowchart TD
    Start["느린 쿼리 발견<br/>pg_stat_statements"]
    Start --> E1["EXPLAIN ANALYZE BUFFERS 실행"]
    E1 --> Check{"예상 vs 실제 rows<br/>10배 이상 차이?"}
    Check -->|"예"| Analyze["ANALYZE table_name<br/>통계 갱신"]
    Check -->|"아니오"| Scan{"Seq Scan on 큰 테이블?"}
    Analyze --> E1
    Scan -->|"예"| Index["인덱스 추가 또는<br/>Partial Index 검토"]
    Scan -->|"아니오"| Sort{"Sort Disk spill?"}
    Sort -->|"예"| WorkMem["work_mem 증가"]
    Sort -->|"아니오"| Done["최적화 완료"]

관련 위키

• btree-indexing, gin-gist-hash-indexes
• postgresql, mysql-innodb
• mvcc
• transaction-isolation-levels