EXPLAIN과 쿼리 튜닝

한 줄 정의

EXPLAIN은 PostgreSQL이 어떤 경로로 쿼리를 실행할지 보여 주고, EXPLAIN ANALYZE는 그 계획을 실제 실행해 추정과 측정의 차이를 보여 준다.

튜닝은 무조건 인덱스를 추가하는 일이 아니다. 느린 쿼리를 실제 데이터 분포로 측정하고, 가장 많은 행·시간·I/O가 생기는 노드를 찾은 뒤 원인을 바꾸고 다시 측정하는 실험이다.

실습: 보스 처치 이벤트 로그 만들기

아래 SQL은 PostgreSQL에서 20만 이벤트를 만든다. player_id 42의 BOSS_KILL을 최근 7일에서 찾는 운영자 조회를 튜닝한다. generate_series 때문에 실행 시간이 걸릴 수 있으므로 개인 실습 DB에서 사용한다.

DROP TABLE IF EXISTS game_events, players;
CREATE TABLE players (
  player_id bigint PRIMARY KEY,
  nickname text NOT NULL
);
CREATE TABLE game_events (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  player_id bigint NOT NULL REFERENCES players(player_id),
  event_type text NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO players
SELECT id, 'player-' || id FROM generate_series(1, 1000) AS id;

INSERT INTO game_events (player_id, event_type, occurred_at, payload)
SELECT 1 + (n % 1000),
       CASE WHEN n % 20 = 0 THEN 'BOSS_KILL' ELSE 'QUEST_CLEAR' END,
       timestamptz '2026-08-11 00:00+09' - (n % 30) * interval '1 day'
         - (n % 86400) * interval '1 second',
       jsonb_build_object('zone', 1 + n % 10)
FROM generate_series(1, 200000) AS n;

ANALYZE players;
ANALYZE game_events;

EXPLAIN, ANALYZE, BUFFERS의 차이

EXPLAIN은 쿼리를 실행하지 않고 추정 계획과 비용을 출력한다. 쓰기 쿼리를 안전하게 먼저 볼 때 적합하다.
ANALYZE 옵션은 실제 실행 시간·행 수·반복 횟수를 측정한다. INSERT·UPDATE·DELETE도 실제로 수행하므로 필요하면 BEGIN 뒤 측정하고 ROLLBACK한다.
BUFFERS는 shared hit·read·dirtied·written 같은 블록 사용량을 보여 준다. hit는 공유 버퍼에서 찾았다는 뜻이지 I/O 비용이 0이라는 뜻은 아니다.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT event_id, occurred_at, payload
FROM game_events
WHERE player_id = 42
  AND event_type = 'BOSS_KILL'
  AND occurred_at >= timestamptz '2026-08-04 00:00+09'
ORDER BY occurred_at DESC
LIMIT 20;

Limit  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
  -> Sort  (... actual ... rows=... loops=1)
       Sort Key: occurred_at DESC
       -> Seq Scan on game_events
            Filter: ((player_id = 42) AND (event_type = 'BOSS_KILL') AND ...)
            Rows Removed by Filter: ...
            Buffers: shared hit=... read=...
Planning Time: ... ms
Execution Time: ... ms

계획·행 수·시간·버퍼 값은 PostgreSQL 버전, 하드웨어, 캐시, 통계와 데이터 분포마다 달라진다.

실행 계획은 안쪽에서 바깥쪽으로 읽는다

들여쓰기된 자식 노드가 먼저 행을 만들고 부모 노드가 이를 소비한다. 위 계획은 Seq Scan으로 테이블을 읽고 조건에서 대부분 버린 뒤, 남은 행을 Sort하고 Limit한다. cost의 두 값은 시작 비용과 전체 비용이며 밀리초가 아닌 옵티마이저의 상대 단위다. actual time의 두 값도 첫 행과 전체 행을 내는 시간이다.

actual rows는 loop당 평균값으로 표시되므로 전체 처리량을 추정할 때 loops를 곱한다. 노드 한 줄만 보지 말고 Rows Removed by Filter, Sort Method, Heap Fetches, Buffers도 함께 본다.

Seq·Index·Bitmap Scan 선택

Sequential Scan

Seq Scan은 테이블 블록을 순서대로 읽는다. 인덱스가 없거나 대부분의 행이 필요하면 임의 접근보다 싸다. 작은 테이블의 Seq Scan은 정상이며, 이름만 보고 병목이라고 단정하면 안 된다.

Index Scan과 Index Only Scan

Index Scan은 인덱스에서 후보 위치를 찾고 테이블 heap에서 필요한 컬럼과 가시성을 확인한다. Index Only Scan은 쿼리에 필요한 값이 인덱스에 있고 가시성 맵 조건도 맞으면 heap 접근을 줄인다. 이름이 Index Only여도 Heap Fetches가 0인지 확인한다.

Bitmap Index Scan과 Bitmap Heap Scan

후보가 한두 행보다 많지만 전체 테이블보다는 적을 때 PostgreSQL은 인덱스로 heap 페이지 위치 비트맵을 만들고 페이지 순서로 묶어 읽을 수 있다. BitmapAnd·BitmapOr로 여러 인덱스를 결합하기도 한다. 범위가 넓어지면 Index Scan보다 유리하고, 너무 넓으면 Seq Scan이 유리할 수 있다.

복합 인덱스로 필터와 정렬을 함께 해결한다

조회는 player_id와 event_type을 동등 비교하고 occurred_at 범위와 내림차순 정렬을 사용한다. 같은 순서의 B-tree 복합 인덱스는 후보를 좁히면서 최신 순서로 읽어 Sort 없이 LIMIT 20에서 일찍 멈출 수 있다.

CREATE INDEX game_events_player_type_time_idx
ON game_events (player_id, event_type, occurred_at DESC);

ANALYZE game_events;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT event_id, occurred_at, payload
FROM game_events
WHERE player_id = 42
  AND event_type = 'BOSS_KILL'
  AND occurred_at >= timestamptz '2026-08-04 00:00+09'
ORDER BY occurred_at DESC
LIMIT 20;

Limit  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
  -> Index Scan using game_events_player_type_time_idx on game_events
       Index Cond: ((player_id = 42) AND (event_type = 'BOSS_KILL')
                    AND (occurred_at >= ...))
       Buffers: shared hit=... read=...
Planning Time: ... ms
Execution Time: ... ms

예시 형태일 뿐이다. 실습 데이터의 분포상 player_id 42에 BOSS_KILL이 없거나 적을 수 있고, 실제 노드와 수치는 환경마다 달라진다.

개선 여부는 Index Scan이라는 이름이 아니라 실행 시간과 읽은 버퍼, 제거한 행, 동시 쓰기 비용을 함께 비교해 판단한다. 인덱스는 읽기를 돕지만 INSERT·UPDATE와 저장 공간, VACUUM 부담을 늘린다. 운영 환경에서는 CREATE INDEX CONCURRENTLY를 고려하되 트랜잭션 블록 안에서 실행할 수 없고 더 오래 걸리며 실패한 invalid 인덱스를 확인해야 한다.

JOIN 노드는 입력 크기와 접근 경로로 읽는다

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.nickname, count(*) AS kills
FROM game_events AS e
JOIN players AS p ON p.player_id = e.player_id
WHERE e.event_type = 'BOSS_KILL'
GROUP BY p.player_id, p.nickname
ORDER BY kills DESC
LIMIT 10;

Nested Loop는 작은 바깥 입력마다 인덱스로 안쪽을 찾을 때 강하다. Hash Join은 작은 쪽의 동등 키 해시를 만들고 큰 쪽을 탐색하며, 해시가 메모리를 넘으면 Batches와 임시 I/O가 늘 수 있다. Merge Join은 양쪽이 키 순서로 정렬됐거나 인덱스 순서를 쓸 때 큰 입력을 한 번씩 훑는다. PostgreSQL은 통계 기반 비용이 가장 낮다고 추정한 경로를 선택하므로 알고리즘을 억지로 고정하기 전에 추정 행 수와 접근 경로를 고친다.

estimated rows와 actual rows의 오차

옵티마이저는 실행 전에 실제 결과를 모르므로 테이블 통계로 선택도를 추정한다. 추정 오차 배수는 0으로 나누지 않도록 다음처럼 대칭적으로 볼 수 있다.

ErrorFactor=\max\left(\frac{A}{E},\frac{E}{A}\right),\quad A=actual\ rows,\ E=estimated\ rows

A나 E가 0인 경계는 별도로 표시한다. 오차가 여러 노드를 거치며 커지면 작은 결과를 예상해 Nested Loop를 골랐는데 실제로는 수백만 번 반복하는 식의 잘못된 선택이 생긴다. 가장 아래쪽에서 오차가 처음 크게 벌어지는 노드를 찾는다.

통계와 ANALYZE

ANALYZE는 일부 행을 표본으로 읽어 값 분포 통계를 갱신한다. 대량 적재 직후나 분포가 크게 바뀐 뒤에는 자동 분석을 기다리지 않고 실행할 수 있다. event_type과 player_id가 서로 강하게 연관되면 각 컬럼의 독립 통계만으로 결합 선택도를 틀릴 수 있어 확장 통계를 고려한다.

ANALYZE game_events;

CREATE STATISTICS game_events_player_type_stats (dependencies, mcv)
ON player_id, event_type FROM game_events;
ANALYZE game_events;

-- 통계 객체는 쿼리를 빠르게 실행하는 인덱스가 아니라
-- 옵티마이저의 행 수 추정을 돕는 메타데이터다.

sargability: 컬럼을 검색 가능한 형태로 둔다

sargable 조건은 인덱스의 정렬 구조로 검색 범위를 만들 수 있는 조건이다. occurred_at 컬럼에 함수를 씌우면 일반 B-tree 인덱스로 시간 범위를 바로 찾기 어렵다.

-- 개선 전: 컬럼을 date로 변환
SELECT count(*) FROM game_events
WHERE occurred_at::date = date '2026-08-10';

-- 개선 후: 원래 컬럼의 반열린 범위
SELECT count(*) FROM game_events
WHERE occurred_at >= timestamptz '2026-08-10 00:00+09'
  AND occurred_at <  timestamptz '2026-08-11 00:00+09';

CREATE INDEX game_events_occurred_at_idx ON game_events (occurred_at);

반열린 범위는 23:59:59.999 같은 임의의 끝값을 만들지 않아 정밀도가 바뀌어도 안전하다. timestamptz의 날짜 경계는 서비스 시간대에 따라 달라지므로 명시적 오프셋이나 정해진 시간대 정책을 사용한다. 표현식 인덱스로 함수 결과를 색인할 수도 있지만 쿼리 표현식 일치와 유지 비용을 검토해야 한다.

측정할 때 피해야 할 함정

첫 실행과 두 번째 실행은 캐시 상태가 다르다. 여러 번 측정하고 BUFFERS의 hit와 read를 함께 기록한다.
개발용 20만 행 계획을 운영의 수십억 행에 그대로 일반화하지 않는다. 데이터량과 치우침을 비슷하게 만든다.
EXPLAIN ANALYZE가 실제 쿼리를 실행한다는 점을 잊지 않는다. 쓰기와 잠금 쿼리는 안전한 환경에서 측정한다.
인덱스 스캔을 만들기 위해 enable_seqscan을 운영 설정에서 끄지 않는다. 진단 실험과 영구 해결책을 구분한다.
평균 지연만 보지 않고 실제 서비스의 p95·p99, CPU, I/O, WAL, 복제 지연과 쓰기 비용을 함께 확인한다.

튜닝 순서

재현 가능한 SQL과 파라미터를 고정한다. EXPLAIN (ANALYZE, BUFFERS)로 기준선을 저장한다. 가장 아래에서 추정이 틀리거나 행을 많이 버리는 노드를 찾는다. 통계·조건·인덱스·스키마 중 원인에 맞는 하나를 바꾼다. 같은 조건으로 재측정하고 읽기 이득과 쓰기 비용을 비교한다. 이 순서를 반복해야 “빨라진 것 같다”가 아니라 근거가 남는다.

참고 자료

PostgreSQL 공식 문서 — Using EXPLAIN (2026-08-11 확인)
PostgreSQL 공식 문서 — EXPLAIN SQL 명령 (2026-08-11 확인)
PostgreSQL 공식 문서 — Planner Statistics (2026-08-11 확인)
PostgreSQL 공식 문서 — Multivariate Statistics Examples (2026-08-11 확인)
PostgreSQL 공식 문서 — Index Types (2026-08-11 확인)
PostgreSQL 공식 문서 — Indexes on Expressions (2026-08-11 확인)

댓글을 불러오는 중…