CS학습2026.10.067 min read

Execution Plan: 옵티마이저가 선택한 쿼리 실행 경로를 읽는 법

실행 계획(Execution Plan)은 데이터베이스 옵티마이저가 SQL을 어떤 순서와 방식으로 처리할지 결정한 결과를 트리 형태로 보여준다. 스캔 방식, 조인 알고리즘, 비용 추정치와 실제 실행 결과를 비교하면 느린 쿼리의 원인을 데이터에 근거해 찾을 수 있다. PostgreSQL의 EXPLAIN과 EXPLAIN ANALYZE를 기준으로 읽는 방법과 주의

By 송민성

1. 개념

실행 계획(Execution Plan)은 DBMS의 쿼리 옵티마이저(Query Optimizer)가 SQL 문을 처리하기 위해 선택한 연산의 순서와 방식을 트리 구조로 표현한 것이다. SQL은 "무엇을 가져올지"만 선언하는 선언형(declarative) 언어이므로, "어떻게 가져올지"는 옵티마이저가 정한다. 실행 계획은 그 결정을 사람이 확인할 수 있게 드러낸 결과물이다.

PostgreSQL에서는 EXPLAIN 명령으로 계획을 볼 수 있다.

EXPLAIN: 쿼리를 실행하지 않고 추정한 계획만 출력한다.
EXPLAIN ANALYZE: 쿼리를 실제로 실행하고, 추정치와 실제 측정값을 함께 출력한다.

2. 왜 사용하는가

쿼리가 느릴 때 SQL 문장만 봐서는 원인을 알기 어렵다. 같은 SQL이라도 데이터 분포, 인덱스 유무, 통계 정보에 따라 전혀 다른 방식으로 실행되기 때문이다. 실행 계획을 보면 다음을 확인할 수 있다.

인덱스를 탔는지, 테이블 전체를 읽었는지
조인이 어떤 알고리즘으로, 어떤 순서로 수행되는지
옵티마이저의 행 수 추정이 실제와 얼마나 어긋났는지
정렬이나 해시 연산이 메모리를 넘어 디스크를 쓰는지

추측 대신 측정값으로 튜닝 방향을 정할 수 있다는 점이 핵심이다.

3. 동작 원리

3.1 옵티마이저의 처리 흐름

파싱(Parsing): SQL을 구문 트리로 변환한다.
재작성(Rewriting): 뷰 전개 등 규칙 기반 변환을 적용한다.
계획 생성(Planning): 가능한 후보 계획들을 만들고, 각각의 비용(cost)을 추정한다.
최소 비용 계획 선택: 추정 비용이 가장 낮은 계획을 실행기(Executor)에 넘긴다.

비용 추정은 테이블 통계(pg_statistic, ANALYZE로 갱신)와 비용 상수(seq_page_cost, random_page_cost, cpu_tuple_cost 등)를 기반으로 한다. 비용은 실제 시간(ms)이 아니라 임의의 상대 단위다.

3.2 주요 스캔 방식

Seq Scan: 테이블 전체를 순차적으로 읽는다.
Index Scan: 인덱스로 위치를 찾은 뒤 테이블 힙(heap)에서 행을 읽는다.
Index Only Scan: 필요한 컬럼이 모두 인덱스에 있고 가시성 맵(visibility map)으로 확인 가능하면 힙 접근을 생략한다.
Bitmap Heap Scan: 인덱스에서 조건에 맞는 위치를 비트맵으로 모은 뒤, 힙을 페이지 순서대로 읽는다.

3.3 주요 조인 알고리즘

Nested Loop: 바깥 테이블의 각 행마다 안쪽 테이블을 탐색한다. 바깥 행 수가 적고 안쪽에 인덱스가 있을 때 유리하다.
Hash Join: 한쪽 테이블로 해시 테이블을 만들고 다른 쪽을 탐색한다. 등호(=) 조건 조인에서 쓰인다.
Merge Join: 양쪽을 조인 키로 정렬한 뒤 병합한다. 이미 정렬되어 있거나 인덱스로 정렬 순서를 얻을 수 있을 때 유리하다.

3.4 출력 읽는 법

text

Index Scan using idx_orders_user on orders  (cost=0.43..8.45 rows=1 width=40) (actual time=0.021..0.022 rows=1 loops=1)

cost=시작비용..전체비용: 첫 행을 반환하기까지의 비용과 모든 행을 반환하기까지의 비용
rows: 추정 행 수
width: 행 하나의 추정 평균 바이트 수
actual time: 첫 행까지 / 전체 소요 시간(ms), loops가 1보다 크면 이 값은 1회당 평균이다
rows(actual): 실제 반환 행 수

계획은 트리이며, 들여쓰기가 깊은 노드(리프)가 먼저 실행되어 결과를 부모 노드로 올려보낸다.

4. 코드 예제

테이블을 만들고 인덱스 유무에 따른 계획 변화를 확인한다.

sql

-- 준비: 사용자 10,000명, 주문 200,000건
CREATE TABLE users (
    id    integer PRIMARY KEY,
    name  text NOT NULL
);

CREATE TABLE orders (
    id         integer PRIMARY KEY,
    user_id    integer NOT NULL,
    amount     integer NOT NULL,
    created_at timestamptz NOT NULL
);

INSERT INTO users
SELECT g, 'user_' || g FROM generate_series(1, 10000) AS g;

INSERT INTO orders
SELECT g,
       (random() * 9999)::int + 1,
       (random() * 100000)::int,
       now() - (random() * interval '365 days')
FROM generate_series(1, 200000) AS g;

ANALYZE users;
ANALYZE orders;

sql

-- 1) 인덱스 없이 조회: 보통 Seq Scan이 선택된다
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

sql

-- 2) 인덱스 생성 후 재확인: Bitmap Heap Scan 또는 Index Scan으로 바뀐다
CREATE INDEX idx_orders_user ON orders (user_id);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42;

sql

-- 3) 조인 계획 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, sum(o.amount) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id BETWEEN 1 AND 100
GROUP BY u.name;

BUFFERS 옵션은 shared hit(캐시 적중)과 read(디스크 읽기) 블록 수를 보여준다. 실제 선택되는 스캔 방식과 수치는 데이터, 설정, 버전에 따라 달라지므로 직접 실행해서 확인해야 한다.

EXPLAIN ANALYZE는 쿼리를 실제로 실행하므로, INSERT/UPDATE/DELETE는 트랜잭션으로 감싸 롤백한다.

sql

BEGIN;
EXPLAIN ANALYZE
UPDATE orders SET amount = amount + 1 WHERE user_id = 42;
ROLLBACK;

5. 시간 복잡도 또는 성능 특성

연산	일반적 비용 특성
Seq Scan	테이블 크기 N에 비례, O(N)
B-tree Index Scan	탐색은 O(log N), 이후 일치하는 행 수만큼 추가 접근
Nested Loop	바깥 행 수 × 안쪽 탐색 비용. 안쪽에 인덱스가 없으면 O(N×M)
Hash Join	해시 테이블 구축과 탐색을 합쳐 평균적으로 O(N+M), 메모리(work_mem) 초과 시 디스크 사용
Merge Join	정렬이 필요하면 정렬 비용(O(N log N))이 추가되고, 병합 자체는 O(N+M)

인덱스가 항상 빠른 것은 아니다. 조건에 맞는 행의 비율이 크면 인덱스를 통한 임의 접근(random access)이 순차 읽기보다 비싸져서, 옵티마이저가 Seq Scan을 선택하는 편이 합리적일 수 있다.

6. 실무 사용 사례

슬로우 쿼리 분석: 느린 쿼리를 EXPLAIN (ANALYZE, BUFFERS)로 돌려 가장 시간이 오래 걸리는 노드를 찾는다.
인덱스 설계 검증: 인덱스를 추가한 뒤 계획이 실제로 바뀌는지, 읽는 버퍼 수가 줄어드는지 확인한다.
통계 문제 진단: 추정 rows와 실제 rows가 수십 배 이상 차이 나면 ANALYZE 실행, 통계 목표치 조정, 확장 통계(CREATE STATISTICS) 적용을 검토한다.
ORM 쿼리 점검: ORM이 생성한 SQL을 로그로 추출해 N+1이나 불필요한 조인이 없는지 계획으로 확인한다.
배포 전 회귀 검증: 운영과 유사한 데이터량에서 핵심 쿼리의 계획이 의도대로인지 점검한다.

7. 주의할 점

EXPLAIN만으로는 추정치만 볼 수 있다. 실제 병목은 ANALYZE를 붙여야 알 수 있다.
데이터가 적은 개발 환경에서는 운영과 다른 계획이 나온다. 소량 데이터에서는 인덱스가 있어도 Seq Scan이 선택되는 경우가 흔하다.
loops가 큰 노드는 actual time에 loops를 곱해야 총 소요 시간이 된다.
비용(cost) 숫자는 서로 다른 쿼리 간에 절대 비교하는 값이 아니다. 같은 쿼리의 후보 계획을 비교하기 위한 상대 지표다.
인덱스 컬럼에 함수를 적용(WHERE date(created_at) = ...)하거나 암묵적 형변환이 일어나면 일반 인덱스를 사용하지 못할 수 있다.
enable_seqscan = off 같은 설정은 진단용으로만 쓴다. 운영 설정으로 고정하면 다른 쿼리에 부작용이 생긴다.
운영 DB에서 EXPLAIN ANALYZE는 실제로 쿼리를 실행하므로 부하와 데이터 변경에 유의한다.
다른 DBMS(MySQL, Oracle 등)는 명령어와 출력 형식, 노드 이름이 다르다. 개념은 같지만 용어를 그대로 옮기지 않는다.

8. 핵심 정리

실행 계획은 옵티마이저가 선택한 스캔 방식, 조인 방식, 연산 순서를 보여주는 트리다.
옵티마이저는 통계와 비용 모델로 후보 계획의 비용을 추정하고 가장 낮은 것을 고른다.
EXPLAIN은 추정, EXPLAIN ANALYZE는 실제 실행 결과를 보여주며, BUFFERS로 I/O까지 확인할 수 있다.
추정 행 수와 실제 행 수의 차이는 통계 문제를 알려주는 가장 좋은 단서다.
인덱스 사용 여부는 선택도(selectivity)에 따라 달라지며, Seq Scan이 항상 나쁜 것은 아니다.
튜닝은 계획을 읽고, 가설을 세우고, 변경 후 다시 계획을 비교하는 순서로 진행한다.