왜 쿼리가 느려지는가 — EXPLAIN ANALYZE 읽는 법

EXPLAIN 과 EXPLAIN ANALYZE 는 다른 도구다

EXPLAIN은 옵티마이저가 세운 계획을 보여줄 뿐, 실제로 쿼리를 실행하지 않습니다. EXPLAIN ANALYZE는 실행까지 합니다. 이 차이 때문에 초보자가 자주 저지르는 실수가 있습니다 — EXPLAIN만 찍어보고 "인덱스를 탄다"고 안심하는 것입니다. 계획상 인덱스를 타더라도 실제 실행에서는 추정 행 수가 크게 틀려서 다른 노드가 병목이 되는 경우가 흔합니다. 운영 DB에서 EXPLAIN ANALYZE를 돌릴 때는 실제로 쿼리가 실행된다는 점을 기억하세요. INSERT/UPDATE/DELETE에 무심코 썼다가 데이터를 실제로 바꿔버리는 사고가 나옵니다. 이런 경우엔 트랜잭션으로 감싸고 롤백하는 습관을 들이는 게 안전합니다.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

실행계획을 아래에서 위로 읽는다

실행계획은 트리 구조입니다. 들여쓰기가 가장 깊은 노드가 가장 먼저 실행되고, 그 결과가 위로 흘러 올라갑니다. 저는 실행계획을 읽을 때 항상 가장 안쪽 노드부터 봅니다. 바깥쪽 Nested Loop 나 Hash Join 노드는 안쪽 스캔이 얼마나 많은 행을 던져주느냐에 따라 완전히 다른 이야기가 되기 때문입니다.

Hash Join  (cost=1.09..891.23 rows=42 width=64) (actual time=0.045..12.301 rows=38 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0.00..820.00 rows=15000 width=24) (actual time=0.008..8.112 rows=15000 loops=1)
  ->  Hash  (cost=1.05..1.05 rows=4 width=48) (actual time=0.020..0.021 rows=4 loops=1)
        ->  Seq Scan on customers c  (cost=0.00..1.05 rows=4 width=48) (actual time=0.006..0.010 rows=4 loops=1)

여기서 눈여겨볼 숫자는 rows=15000(추정)과 actual rows=15000(실제)이 아니라, orders 테이블 전체를 Seq Scan 으로 훑었다는 사실입니다. customer_id 에 인덱스가 있어도 옵티마이저가 안 쓴 이유는 뒤에 나옵니다 — 지금은 계획을 읽는 법에 집중합시다.

cost 는 시간이 아니다

cost=1.09..891.23 같은 숫자를 밀리초로 착각하는 사람이 많습니다. 아닙니다. cost 는 seq_page_cost, random_page_cost 같은 설정값을 기준으로 한 상대적인 추정 단위입니다. 실제 걸린 시간은 actual time=... 쪽을 봐야 합니다. 두 숫자가 있는 이유는 명확합니다 — cost 는 옵티마이저가 계획을 고를 때 쓰는 예측치이고, actual time 은 그 예측이 맞았는지 검증하는 실측치입니다. 이 둘의 차이가 크면(추정 rows 와 실제 rows 가 몇 배씩 벌어지면) 십중팔구 통계가 낡았거나 옵티마이저가 컬럼 간 상관관계를 모른다는 신호입니다. 이 얘기는 9장에서 통계를 다룰 때 다시 짚겠습니다.

Seq Scan 이 항상 나쁜 것은 아니다

여기서 오해하지 말아야 할 게 하나 있습니다. Seq Scan을 보면 반사적으로 "인덱스가 없구나, 빨리 만들어야지" 라고 생각하는 분들이 많은데, 작은 테이블이나 전체 행의 상당 비율을 읽어야 하는 쿼리에서는 순차 스캔이 인덱스 스캔보다 실제로 더 빠릅니다. 디스크(혹은 캐시)에서 연속된 블록을 읽는 것과, 인덱스를 탄 뒤 힙 페이지를 랜덤하게 오가며 읽는 것 중 어느 쪽이 유리한지는 선택하는 행의 비율에 달려 있습니다. 대략 테이블의 5~10% 이상을 읽어야 하는 쿼리라면, 인덱스가 있어도 옵티마이저가 순차 스캔을 고르는 게 정상입니다. 이 책 전체에서 반복해서 확인하게 될 원칙이니 지금 기억해 두세요: 인덱스는 "있으면 무조건 좋은 것"이 아니라 "행의 일부만 골라낼 때 유리한 것" 입니다.

flowchart LR
  A["조건절이 전체 행 중 몇 %를 선택하는가"] -->|"낮음 - 소수만 선택"| B["Index Scan 유리"]
  A -->|"높음 - 대부분 선택"| C["Seq Scan 유리"]

다음 장부터는 이 B-Tree 인덱스가 내부적으로 어떻게 생겼는지, 왜 등호 조건에서는 유리하고 특정 범위 조건에서는 불리한지를 구조 레벨에서 뜯어봅니다.