핵심 정리

EXPLAIN ANALYZE
SELECT *
FROM posts
WHERE user_id = 10;

읽는 법

EXPLAIN은 계획을 보고, ANALYZE는 실제 실행 결과를 함께 보여준다

EXPLAIN만 쓰면 PostgreSQL 옵티마이저가 통계를 기반으로 예측한 실행 계획(rows, cost 추정값)을 보여줍니다. ANALYZE를 붙이면 쿼리를 실제로 실행하고 actual time과 actual rows를 함께 출력합니다. 두 값의 차이가 크다면 통계가 오래됐거나 옵티마이저 추정이 잘못됐다는 신호입니다. BUFFERS 옵션을 추가하면 캐시 히트/미스까지 확인할 수 있습니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.title, u.name
FROM posts AS p
JOIN users AS u ON u.id = p.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '7 days';

EXPLAIN과 EXPLAIN ANALYZE는 보는 대상이 다르다

EXPLAIN은 "PostgreSQL이 이렇게 실행할 것이라고 예상한다"를 보여주고, EXPLAIN ANALYZE는 "실제로 이렇게 실행됐다"를 보여줍니다. 먼저 실제 실행이 안전한 쿼리인지부터 판단하고, 그다음 어떤 수준까지 볼지 정하는 편이 좋습니다.

EXPLAIN
SELECT * FROM posts WHERE user_id = 10;

EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 10;

Seq Scan vs Index Scan: 옵티마이저가 선택하는 이유를 읽어야 한다

Seq Scan(순차 스캔)은 테이블 전체 블록을 처음부터 끝까지 읽습니다. 반환 행 비율이 높거나 테이블이 작으면 Seq Scan이 Index Scan보다 빠를 수 있습니다. Index Scan은 인덱스 B-tree를 탐색해 조건에 맞는 행 위치를 찾고 heap에서 실제 데이터를 읽습니다. Index Only Scan은 필요한 컬럼이 인덱스에 모두 있고 visibility map 조건도 맞을 때 heap 접근 없이 끝날 수 있습니다. 인덱스가 있는데 Seq Scan이 나왔다면, 반환 비율이 높거나 통계 추정이 그렇게 판단했거나, 조건식 때문에 인덱스를 활용하기 어려운 경우를 먼저 의심하는 편이 맞습니다.

-- 실행 계획 예시 읽기
-- Seq Scan on posts  (cost=0.00..450.00 rows=20000 width=200)
--                    (actual time=0.05..12.3 rows=19500 loops=1)
-- → 추정 20000행, 실제 19500행 → 통계가 비교적 정확

EXPLAIN, EXPLAIN ANALYZE, BUFFERS는 깊이가 다르다

계획 모양만 보고 싶으면 EXPLAIN, 실제 시간이 궁금하면 EXPLAIN ANALYZE, 캐시와 디스크 읽기까지 보고 싶으면 BUFFERS를 붙입니다. 처음부터 가장 무거운 옵션을 붙이기보다 "실행이 안전한가"와 "어디까지 봐야 답이 나오는가"를 먼저 정하는 편이 좋습니다.

EXPLAIN
SELECT * FROM orders WHERE user_id = 42;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

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

cost와 actual time의 간극이 성능 문제의 단서다

cost는 옵티마이저의 추정 비용(디스크 I/O, CPU 연산 가중치 합산)이며 단위는 임의적입니다. actual time은 밀리초 단위의 실제 경과 시간입니다. 추정 rows가 10인데 actual rows가 10000이라면, 옵티마이저가 선택성을 크게 과소평가한 것입니다. 이때는 먼저 ANALYZE로 통계를 최신화하고, 그다음 열 상관관계나 조건식 구조 때문에 추정이 흔들리는지 검토하는 순서가 안전합니다.

-- 통계 갱신으로 추정 정확도 높이기
ANALYZE posts;

-- 복합 열 상관관계 통계 추가 (PostgreSQL 10+)
CREATE STATISTICS stat_posts_user_status ON user_id, status FROM posts;
ANALYZE posts;

rows 추정과 actual rows 차이는 다음 액션을 정하는 신호다

실행 계획을 읽을 때는 "빠르다/느리다"보다 먼저 "예측이 맞았는가"를 보는 편이 좋습니다. 추정 rows와 actual rows가 비슷하면 통계와 선택성 판단은 대체로 맞고, 쿼리 구조나 인덱스가 다음 검토 대상입니다. 반대로 차이가 크면 오래된 통계뿐 아니라 열 상관관계, 조건식 재작성 필요, 표현식 인덱스 부재 같은 원인도 함께 의심해야 합니다.

-- 추정 5행, 실제 50000행이면 선택성 판단이 크게 틀린 것
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'paid'
  AND created_at >= CURRENT_DATE - INTERVAL '30 days';

EXPLAIN ANALYZE는 DML도 실제로 실행한다는 점을 잊어선 안 된다

EXPLAIN ANALYZE에 SELECT가 아닌 INSERT, UPDATE, DELETE를 붙이면 쿼리가 실제로 실행됩니다. 실행 계획만 보고 싶었는데 데이터가 변경될 수 있습니다. DML의 실행 계획을 안전하게 확인하려면 트랜잭션 안에서 실행하고 ROLLBACK으로 되돌리는 방법을 사용합니다.

BEGIN;
EXPLAIN ANALYZE
UPDATE posts SET published = true WHERE user_id = 42;
ROLLBACK;  -- 변경 사항을 되돌림

체크포인트

주의할 점

EXPLAIN ANALYZE
DELETE FROM sessions
WHERE expires_at < NOW();

이 쿼리는 계획만 보여주는 게 아니라 만료 세션을 실제로 삭제합니다. DML 분석은 항상 롤백 가능한 트랜잭션 안에서 보는 편이 안전합니다.

실행 계획의 내부 노드 하나가 actual time=0.1..0.2처럼 작아 보여도, 전체 쿼리는 상위 노드 반복(loops) 때문에 오래 걸릴 수 있습니다. 특정 줄의 숫자만 보고 빠르다고 결론내리기보다, 상위 노드의 rows, loops, 전체 실행 시간을 함께 읽는 습관이 필요합니다.

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE DATE(created_at) = CURRENT_DATE;

인덱스가 있는데도 Seq Scan이 나왔다고 바로 인덱스가 망가졌다고 보기보다, 먼저 컬럼에 함수가 씌워졌는지부터 확인해야 합니다. 이런 경우는 실행 계획 문제라기보다 쿼리 형태 문제인 경우가 많습니다.