왜 인덱스를 타는데도 느릴까
인덱스가 있고 EXPLAIN에서 Index Scan이 찍히는데도 기대만큼 빠르지 않은 경우가 있다. 일반 Index Scan은 인덱스에서 조건에 맞는 튜플 위치(TID)를 찾은 뒤, 실제 행이 살아있는지 확인하기 위해 힙(테이블 페이지)을 다시 읽는다. 이 힙 액세스가 랜덤 I/O를 유발한다. 조회 컬럼이 전부 인덱스 안에 있는데도 힙을 매번 읽는다면, 그건 낭비다. 이 낭비를 없애는 것이 인덱스 온리 스캔(Index-Only Scan)이다.
인덱스 온리 스캔의 조건
인덱스 온리 스캔이 성립하려면 두 가지가 필요하다. 첫째, 쿼리가 참조하는 모든 컬럼이 인덱스에 포함돼야 한다. 둘째, 대상 힙 페이지가 Visibility Map(VM)에서 "all-visible"로 표시돼 있어야 한다. VM은 페이지 단위 비트맵으로, 해당 페이지의 모든 튜플이 모든 트랜잭션에 보인다는 것을 기록한다. all-visible이면 가시성 확인을 위한 힙 접근을 생략할 수 있다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, created_at
FROM orders
WHERE user_id = 1024;
-- Index Only Scan using idx_orders_user_created on orders
-- Heap Fetches: 0 <- 이 값이 0에 가까워야 이득이 크다
-- Buffers: shared hit=4
핵심 지표는 Heap Fetches다. 이 값이 크면 VM이 최신이 아니라서 결국 힙을 읽고 있다는 뜻이다.
커버링 인덱스로 컬럼 채우기
조회에 필요한 컬럼을 인덱스에 넣어야 한다. WHERE·정렬에 쓰는 컬럼은 키로, 단순 조회용 컬럼은 INCLUDE로 붙이면 인덱스 정렬 비용 없이 페이로드만 담을 수 있다.
-- user_id로 조회하고 created_at, amount를 함께 반환
CREATE INDEX idx_orders_user_incl
ON orders (user_id)
INCLUDE (created_at, amount);
Visibility Map은 누가 갱신하나
VM의 all-visible 비트는 VACUUM이 세운다. 즉 데이터가 계속 갱신되는 테이블은 VACUUM이 돌기 전까지 VM이 뒤처지고, 그동안 Heap Fetches가 늘어난다. UPDATE/DELETE가 잦은 테이블에서 인덱스 온리 스캔이 "됐다 안 됐다" 하는 이유가 이것이다. 해결은 autovacuum이 충분히 자주 돌게 만드는 것이다.
테이블 단위 autovacuum 튜닝
기본 autovacuum 임계값은 대용량 테이블에서 너무 관대하다. scale_factor가 0.2면 2천만 행 테이블은 4백만 행이 바뀌어야 VACUUM이 돈다. 핫 테이블은 값을 낮춰 자주 돌리는 게 낫다.
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_threshold = 1000,
autovacuum_vacuum_cost_limit = 2000
);
| 설정 | 기본값 | 핫 테이블 권장 |
|---|---|---|
| vacuum_scale_factor | 0.2 | 0.01~0.05 |
| vacuum_threshold | 50 | 1000 내외 |
| vacuum_cost_limit | 200 | 1000~3000 |
모니터링과 진단
VM 상태와 vacuum 이력을 직접 확인해야 감으로 튜닝하지 않는다. pg_visibility 확장으로 all-visible 페이지 비율을, pg_stat_user_tables로 마지막 vacuum 시점을 본다.
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT relname, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';
SELECT count(*) FILTER (WHERE all_visible) AS visible_pages,
count(*) AS total_pages
FROM pg_visibility('orders'::regclass);
주의점
몇 가지 함정이 있다. 첫째, INCLUDE 컬럼을 늘리면 인덱스가 커져 캐시 효율과 쓰기 비용이 나빠진다. 조회에 실제로 쓰는 컬럼만 넣는다. 둘째, 인덱스 온리 스캔은 부분 인덱스나 함수 표현식과 조합할 때 플래너가 커버 여부를 놓칠 수 있으니 EXPLAIN으로 반드시 확인한다. 셋째, autovacuum을 공격적으로 잡으면 I/O가 늘어 피크 시간에 부담이 될 수 있으므로 cost_limit과 워커 수를 함께 조정한다. 마지막으로, 대량 적재 직후에는 VM이 비어 있으니 배치 이후 수동 VACUUM으로 all-visible을 미리 세워두면 조회 성능이 안정된다.