EXPLAIN을 추정이 아니라 실제로 읽기—cost·rows와 EXPLAIN ANALYZE로 간극을 확인하는 법을 정리한 글입니다.
느린 쿼리를 마주하면 많은 분들이 인덱스부터 추가합니다. 저도 오래 그랬죠. 하지만 인덱스를 던지기 전에 플래너가 무슨 생각으로 그 계획을 짰는지 먼저 읽어야 한다는 걸 여러 번 헛발질한 뒤에야 배웠습니다. 오늘은 EXPLAIN을 막연한 추정으로 흘려 보지 않고, EXPLAIN ANALYZE로 추정과 실제의 간극을 짚어 원인을 찾는 방법을 제 경험과 함께 정리해 보겠습니다.
플래너가 보여 주는 추정을 먼저 읽습니다
그냥 EXPLAIN만 붙이면 쿼리를 실행하지 않고 플래너의 계획, 즉 "이렇게 돌리면 이 정도 비용이 들 것 같다"는 추정만 돌려줍니다. 가장 먼저 눈에 들어오는 숫자가 cost, rows, width죠. 예를 들어 아래처럼 나옵니다.
Seq Scan on orders (cost=0.00..18334.00 rows=1000000 width=64)- cost=0.00..18334.00 : 앞이 startup cost, 뒤가 total cost입니다. startup은 첫 행을 내보내기까지의 비용, total은 마지막 행까지의 누적 비용이죠. 단위는 초가 아니라 순차 페이지 읽기 1회를 1.0으로 잡는 내부 단위라, cost는 절대 시간이 아니라 계획들끼리 비교하는 상대 점수로 봐야 합니다.
- rows=1000000 : 이 노드가 내보낼 것으로 예상하는 행 수입니다. 통계 정보(pg_statistic)를 근거로 뽑은 추정치죠.
- width=64 : 한 행의 평균 바이트 폭입니다. 정렬이나 hash에서 메모리를 얼마나 쓸지 가늠하는 데 쓰입니다.
여기서 꼭 익혀 둘 것이 읽는 방향입니다. 실행 계획은 노드 트리이고, 들여쓰기가 깊은 안쪽 노드부터 실행되어 바깥으로 흘러 올라갑니다. 즉 위에서 아래로 읽는 게 아니라 가장 안쪽 노드부터 바깥으로 읽어야 하죠.
Nested Loop (cost=0.42..250.11 rows=12 width=72)
-> Index Scan using idx_users_active on users u (cost=0.42..8.44 rows=1 width=40)
Index Cond: (email = 'a@b.com')
-> Index Scan using idx_orders_uid on orders o (cost=0.42..241.55 rows=12 width=32)
Index Cond: (user_id = u.id)이 계획은 안쪽의 users를 먼저 Index Scan으로 한 건 찾고, 그 결과(1건)를 바깥 Nested Loop이 받아 orders를 user_id로 다시 훑는 순서입니다. Nested Loop의 total cost가 안쪽 노드들의 cost를 품고 있다는 점만 잡아도 계획의 뼈대가 보이기 시작하죠.
처음 EXPLAIN을 익힐 때 저는 위에서부터 실행되는 줄 알고 맨 위 Nested Loop을 제일 먼저 도는 작업이라 오해했습니다. 실제로는 정반대라, 읽는 순서를 손에 익히는 게 첫 관문이었죠.
EXPLAIN ANALYZE로 추정과 실제를 맞대 봅니다
EXPLAIN이 플래너의 상상이라면, EXPLAIN ANALYZE는 쿼리를 실제로 실행한 뒤 계측값을 붙여 줍니다(그래서 UPDATE·DELETE에 붙일 땐 트랜잭션으로 감싸 ROLLBACK하는 습관이 필요하죠). 이때 새로 등장하는 숫자가 actual time, actual rows, loops입니다.
Index Scan using idx_orders_uid on orders o
(cost=0.42..241.55 rows=12 width=32)
(actual time=0.018..0.045 rows=9 loops=1)읽는 요령을 정리하면 이렇습니다.
| 항목 | 의미 | 볼 때 주의점 |
|---|---|---|
| actual time | 첫 행..마지막 행까지 실제 소요(ms) | loops당 평균값입니다 |
| actual rows | 실제로 나온 행 수 | loops당 평균이라 총합은 rows×loops |
| loops | 이 노드가 반복 실행된 횟수 | Nested Loop 안쪽에서 특히 커집니다 |
핵심은 loops를 놓치지 않는 것입니다. 안쪽 노드의 actual rows가 3이고 loops가 50만이면, 실제 처리된 행은 3이 아니라 150만이죠. actual time도 "한 번 도는 데 걸린 평균"이라 loops를 곱해야 그 노드의 총시간이 나옵니다. 이 함정 탓에 "노드 하나가 0.05ms인데 왜 쿼리는 3초냐"는 착시가 자주 생깁니다.
여기에 EXPLAIN (ANALYZE, BUFFERS)를 쓰면 실제 I/O가 보입니다. shared hit은 버퍼 캐시에서 바로 읽은 페이지, read는 디스크에서 읽어 온 페이지입니다. 캐시 히트만 잔뜩이면 CPU·플랜 문제이고, read가 크면 물리 I/O가 병목이라는 뜻이죠. 저는 느린 쿼리를 볼 때 거의 습관적으로 BUFFERS를 켭니다. shared read=142000 같은 줄 하나가 "이건 캐시가 아니라 디스크를 긁고 있다"는 단서를 주니까요.
그리고 이 모든 것 중에서 가장 강력한 신호는 추정 rows와 실제 rows의 간극입니다. 플래너의 계획은 결국 추정 rows 위에 세워집니다. 추정이 정확하면 대체로 좋은 플랜이 나오고, 추정이 어긋나면 조인 방식(Nested Loop vs Hash Join)이나 인덱스 선택이 통째로 틀어집니다. 그래서 저는 EXPLAIN ANALYZE를 볼 때 cost보다 먼저 각 노드에서 rows=추정 옆의 actual rows=실제를 나란히 훑습니다. 둘의 비율이 10배, 100배 벌어지는 노드가 있다면 거기가 바로 문제의 진원지죠.
추정 10, 실제 100만이었던 그날
가장 기억에 남는 사건은 재작년 커머스 주문 통계 API였습니다. 특정 판매자의 정산 대상 주문을 뽑는 쿼리가 평소 40ms 안팎이었는데, 어느 날부터 한 판매자에서만 6초가 넘게 걸린다는 제보가 들어왔죠. 대략 이런 쿼리였습니다.
SELECT o.id, o.amount, s.settled_at
FROM orders o
JOIN settlements s ON s.order_id = o.id
WHERE o.seller_id = 4821
AND o.status = 'DONE'
AND o.created_at >= '2025-05-01';EXPLAIN ANALYZE (BUFFERS)를 걸어 보니 문제 노드가 한눈에 드러났습니다.
Nested Loop (... rows=10 ...) (actual ... rows=1030412 loops=1)
-> Index Scan on orders o
(... rows=10 ...) (actual ... rows=1030412 loops=1)
Index Cond: (seller_id = 4821)
Filter: (status = 'DONE' AND created_at >= '2025-05-01')
-> Index Scan on settlements s (... loops=1030412)추정은 rows=10인데 실제는 rows=1030412였습니다. 플래너는 이 판매자의 주문을 10건쯤으로 보고 Nested Loop을 골랐는데, 실제로는 103만 건이 나오니 settlements 인덱스를 103만 번 반복(loops=1030412)해 훑고 있었던 것이죠. 안쪽 한 번은 빨라도 백만 번을 돌면 6초가 됩니다.
원인은 통계였습니다. 이 판매자는 원래 소규모였는데 대형 셀러로 바뀌며 주문이 폭증했고, autovacuum의 analyze 임계치를 넘기기 전이라 pg_statistic이 옛날 분포(=주문 거의 없음)를 그대로 들고 있었죠. 그래서 seller_id 4821의 카디널리티를 크게 과소추정한 겁니다.
조치는 두 갈래로 했습니다. 급한 불은 ANALYZE orders;로 통계를 즉시 갱신해 껐습니다. 갱신 직후 다시 걸어 보니 추정 rows가 10에서 98만 수준으로 현실을 따라왔고, 플래너가 스스로 Nested Loop을 버리고 Hash Join으로 갈아탔죠. 6.2초였던 쿼리가 210ms로 떨어졌습니다. 근본 대책으로는 해당 테이블의 autovacuum_analyze_scale_factor를 0.1에서 0.02로 낮춰, 대형 셀러가 생겨도 통계가 더 자주 갱신되도록 손봤습니다. 덤으로 (seller_id, status, created_at) 복합 인덱스를 만들어 status·created_at 필터가 Index Cond로 흡수되게 하니, Filter로 흘려버리던 행도 인덱스에서 걸러졌습니다.
그날의 교훈은 분명합니다. cost 숫자를 노려보며 인덱스를 추가하기 전에, 각 노드의 추정 rows와 실제 rows를 나란히 놓고 어디서 100배가 벌어지는지부터 찾아야 한다는 것이죠. 플래너는 거짓말을 하지 않습니다. 다만 낡은 통계를 근거로 성실하게 틀린 계획을 짤 뿐입니다. EXPLAIN ANALYZE로 실제와 맞대 보는 습관 하나가, 저에게는 수많은 새벽 호출을 줄여 준 값진 도구가 되었습니다.