처음으로 PostgreSQL EXPLAIN을 마주했던 날을 기억한다. 조회가 30초씩 걸리는 API가 있었고, 팀장은 "쿼리 좀 봐봐"라고 했다. 그때 나는 EXPLAIN의 존재조차 몰랐다. 그냥 쿼리를 눈으로 읽으며 "이 조건이 문제겠지?"라고 추측했다. 틀렸다. 문제는 전혀 다른 곳에 있었다.
데이터베이스 쿼리 최적화는 직관으로 하는 게 아니다. PostgreSQL 쿼리 최적화를 제대로 하려면 먼저 EXPLAIN ANALYZE가 뭘 말해주는지를 읽는 법을 배워야 한다. 그게 전부다. 나머지는 그 안에서 보인다.
EXPLAIN은 "계획"을, ANALYZE는 "실제"를 말해준다
PostgreSQL의 쿼리 실행 계획은 두 단계로 나뉜다. EXPLAIN은 실제 쿼리를 실행하지 않고 옵티마이저가 어떤 전략을 선택할지를 보여준다. EXPLAIN ANALYZE는 쿼리를 실제로 실행하고, 각 노드에서 예상 비용과 실제 소요 시간을 비교해준다.
이 둘의 차이가 중요한 이유가 있다. 옵티마이저의 예측과 실제 결과가 크게 다를 때, 거기서 문제가 시작된다. 통계(statistics)가 오래됐거나, 데이터 분포가 편향됐거나, 잘못된 인덱스를 선택하고 있을 가능성이 높다.
Before: 인덱스도 있는데 왜 느린가
실무에서 자주 마주치는 상황이다. 분명히 인덱스를 걸었는데 쿼리가 느리다. 아래처럼 생긴 테이블이 있다고 하자.
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL, -- 'pending', 'done', 'cancelled'
created_at TIMESTAMP NOT NULL
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
그리고 이런 쿼리를 실행한다:
-- 문제의 쿼리
SELECT * FROM orders
WHERE user_id = 12345
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
EXPLAIN ANALYZE를 돌리면 이런 결과가 나올 수 있다:
Limit (cost=0.00..5432.11 rows=20 width=80) (actual time=4821.332..4821.889 rows=20 loops=1)
-> Sort (cost=0.00..12873.44 rows=...) (actual time=4821.329..4821.341 rows=20 loops=1)
Sort Key: created_at DESC
Sort Method: external merge Disk: 18432kB
-> Seq Scan on orders (cost=...) (actual time=0.018..4103.332 rows=... loops=1)
Filter: ((user_id = 12345) AND (status = 'pending'))
Rows Removed by Filter: 9873422
두 가지 신호가 보인다. Seq Scan은 전체 테이블을 다 뒤지고 있다는 뜻이고, Sort Method: external merge Disk는 정렬을 디스크에서 했다는 뜻이다. 둘 다 최악의 패턴이다.
After: 복합 인덱스 하나로 해결
분리된 인덱스 두 개보다 복합 인덱스 하나가 훨씬 낫다. 특히 이 쿼리처럼 조건 + 정렬이 함께 있는 경우는 더욱 그렇다.
-- 기존 인덱스 제거
DROP INDEX idx_orders_user_id;
DROP INDEX idx_orders_status;
-- 복합 인덱스 + 정렬 컬럼 포함
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);
이제 같은 쿼리를 다시 돌리면:
Limit (cost=0.44..23.18 rows=20 width=80) (actual time=0.081..0.129 rows=20 loops=1)
-> Index Scan using idx_orders_user_status_created on orders
(actual time=0.077..0.118 rows=20 loops=1)
Index Cond: ((user_id = 12345) AND (status = 'pending'))
4821ms에서 0.129ms로 줄었다. 3만 7천 배 빨라진 것이다. 쿼리 자체를 건드린 게 아니다. EXPLAIN이 보여준 신호를 읽고, 인덱스 설계를 바꿨을 뿐이다.
PostgreSQL EXPLAIN 읽기의 실전 원칙 4가지
1. Seq Scan이 나오면 먼저 의심하라
Seq Scan이 항상 나쁜 건 아니다. 테이블이 작거나, 선택도(selectivity)가 낮은 경우엔 옵티마이저가 의도적으로 Seq Scan을 선택한다. 하지만 수백만 건의 테이블에서 Seq Scan이 나오고 Rows Removed by Filter가 크다면, 인덱스 설계를 다시 봐야 한다.
2. 예상 rows와 실제 rows의 차이를 확인하라
(cost=... rows=100 ...)와 (actual ... rows=89234 ...)가 극단적으로 다르다면, 통계가 오래된 것이다. ANALYZE orders;를 실행해서 통계를 갱신하면 옵티마이저가 더 나은 판단을 할 수 있다.
3. Sort Method: external merge Disk는 즉시 조치가 필요하다
정렬이 디스크에서 일어나고 있다는 뜻이다. work_mem을 늘리거나, 정렬 컬럼을 인덱스에 포함시키는 방식으로 해결한다. 인덱스 스캔으로 정렬이 이미 되어 있으면 Sort 노드 자체가 사라진다.
4. BUFFERS 옵션으로 캐시 히트율을 확인하라
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;를 사용하면 얼마나 많은 블록이 shared buffer에서 읽혔는지(hit)와 디스크에서 읽혔는지(read)를 볼 수 있다. 캐시 히트율이 낮다면 자주 조회되는 데이터에 대한 hot data 전략이 필요하다.
EXPLAIN은 데이터베이스가 스스로를 설명하는 방식이다
시니어 개발자와 주니어 개발자의 차이는 종종 이런 데서 드러난다. 주니어는 느린 쿼리를 보면 쿼리 자체를 고친다. 조건을 바꾸고, 서브쿼리를 없애고, LIMIT를 앞으로 옮긴다. 때로는 맞다. 하지만 시니어는 먼저 EXPLAIN ANALYZE를 돌린다. 데이터베이스에게 "너는 지금 어떻게 생각하고 있어?"라고 묻는 것이다.
PostgreSQL 슬록우 쿼리 해결의 90%는 EXPLAIN 결과를 제대로 읽는 것에서 시작핝다. 인덱스 전략도, 팊티셔닝도, 캐싱도 그 다음이다. 먼저 현실을 보는 것다. 그게 데이터베이스 최적화의 첫 번째 단계이고, 가장 중요한 단계다.
댓글