EXPLAIN ANALYZE는 데이터베이스가 쿼리를 "실제로 어떻게 실행했는지"를 보여주는 명령이다. 예상 계획만 보는 EXPLAIN과 달리, ANALYZE를 붙이면 쿼리를 진짜 돌려보고 각 단계에 걸린 시간과 실제 행 수까지 찍어준다. 느린 쿼리를 감으로 고치지 않고 눈으로 잡는 첫걸음이다.
솔직히 나도 한동안은 쿼리가 느리면 인덱스를 일단 하나 더 걸고 봤다. 운 좋으면 빨라지고 아니면 그대로. 그러다 EXPLAIN ANALYZE를 제대로 읽기 시작하면서부터, "왜 느린지"를 먼저 보고 나서 손을 대게 됐다. 순서가 바뀌니 삽질이 확 줄었다.
일단 한 번 찍어보자
PostgreSQL 기준으로 쿼리 앞에 EXPLAIN ANALYZE를 붙이면 된다. 운영 DB에서 UPDATE/DELETE에 붙일 땐 실제로 실행되니 트랜잭션으로 감싸고 롤백하는 습관을 들이자.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;출력의 핵심은 이렇게 생겼다.
Limit (cost=0.43..8.61 rows=20 width=64) (actual time=0.03..0.05 rows=20 loops=1)
-> Index Scan Backward using idx_orders_user_created on orders
(cost=0.43..210.5 rows=512 width=64) (actual time=0.02..0.04 rows=20 loops=1)
Index Cond: (user_id = 42)
Filter: (status = 'paid')
Planning Time: 0.15 ms
Execution Time: 0.07 ms숫자를 읽는 순서
처음엔 괄호 속 숫자가 암호처럼 보인다. 내가 실제로 보는 순서는 이렇다.
- actual time — 실제 걸린 시간(ms).
cost는 옵티마이저의 상대적 추정치라 절대 시간이 아니다. 튜닝할 땐actual time과 맨 아래Execution Time을 본다. - rows 추정 vs 실제 —
rows=512(추정)인데actual ... rows=20이면 통계가 어긋난 것. 이 괴리가 크면 옵티마이저가 엉뚱한 계획을 고른다.ANALYZE 테이블명;으로 통계를 갱신하면 나아질 때가 많다. - loops — 그 노드가 몇 번 반복됐는지. actual time은 1회당 시간이라, 총 시간은
actual time × loops다. 중첩 루프에서 loops가 수천이면 그 안쪽이 범인이다.
어떤 스캔이 보이면 의심하나
노드 이름만 봐도 대충 감이 온다.
| 노드 | 뜻 | 신호 |
|---|---|---|
| Index Scan | 인덱스로 필요한 행만 | 보통 좋음 |
| Seq Scan | 테이블 전체 훑기 | 큰 테이블이면 의심 |
| Bitmap Heap Scan | 인덱스로 위치 모아 한 번에 | 중간 선택도에 적합 |
| Nested Loop | 바깥 행마다 안쪽 조회 | loops 폭발 주의 |
주의할 건, Seq Scan이 무조건 나쁜 게 아니라는 점이다. 테이블이 작거나 결과가 테이블의 상당 부분이면 옵티마이저는 일부러 전체 스캔을 고른다. 인덱스로 여기저기 흩어진 페이지를 랜덤하게 읽느니 그냥 순차로 쭉 읽는 게 더 빠르기 때문이다. 그래서 "Seq Scan이 보인다 → 인덱스 추가"는 성급하다.
내가 실제로 밟는 순서
느린 쿼리를 받으면 대략 이렇게 본다. 맨 아래 Execution Time으로 규모를 파악하고, 트리에서 actual time이 가장 큰 노드를 찾는다. 그 노드의 추정/실제 rows 괴리를 보고, 통계 문제인지 인덱스 부재인지 조인 순서 문제인지를 가른다. BUFFERS 옵션(EXPLAIN (ANALYZE, BUFFERS))을 켜면 디스크에서 읽었는지 캐시에서 읽었는지까지 나와서, "느린 게 I/O 때문인지"도 구분된다.
자주 묻는 질문
EXPLAIN과 EXPLAIN ANALYZE는 뭐가 다른가요?
EXPLAIN은 쿼리를 실행하지 않고 옵티마이저의 예상 계획만 보여줍니다. ANALYZE를 붙이면 쿼리를 실제로 실행해 걸린 시간과 실제 행 수를 함께 보여줍니다. 튜닝할 땐 실제 값이 필요하니 ANALYZE를 씁니다.
cost 숫자가 시간(ms)인가요?
아닙니다. cost는 옵티마이저가 계획을 비교하려고 쓰는 상대적 추정 단위입니다. 실제 시간은 actual time과 Execution Time을 보세요. cost가 낮다고 항상 빠른 것도 아닙니다.
운영 DB에서 EXPLAIN ANALYZE를 돌려도 되나요?
SELECT는 대체로 안전하지만 실제로 쿼리가 실행되므로 무거운 쿼리는 부하를 줄 수 있습니다. UPDATE·DELETE·INSERT는 진짜로 데이터가 바뀌니, BEGIN으로 트랜잭션을 열고 확인한 뒤 ROLLBACK 하는 방식으로 돌리세요.
추정 rows와 실제 rows 차이가 크면 어떻게 하나요?
먼저 해당 테이블에 ANALYZE를 실행해 통계를 갱신해 보세요. 그래도 크게 어긋나면 컬럼 간 상관관계 때문일 수 있어, 확장 통계(CREATE STATISTICS)나 쿼리 재작성을 검토합니다.
댓글 0