SELECT에 컬럼 하나를 더했더니 느려졌던 경험과 커버링 인덱스를 index-only scan 관점에서 정리한 글입니다.
잘 태운 쿼리가 밀리초 단위로 끝나다가, 어느 날 배포 이후 몇 초씩 걸리기 시작했습니다. diff를 보니 바뀐 건 딱 한 줄, SELECT 목록에 컬럼 하나가 추가된 것뿐이었죠. WHERE도 ORDER BY도 그대로였는데 왜 느려졌을까요. 범인은 조용히 깨진 index-only scan이었습니다. 오늘은 커버링 인덱스라는 최적화가 왜 그렇게 빠른지, 또 왜 그렇게 쉽게 무너지는지를 실제 사고 기록과 함께 정리해 보겠습니다.
index-only scan은 왜 그렇게 빠른가
일반적인 index scan은 두 단계로 움직입니다. 먼저 B-tree 인덱스를 타고 내려가 조건에 맞는 엔트리를 찾고, 그 엔트리에 적힌 물리적 위치(PostgreSQL이라면 ctid)를 들고 실제 테이블(heap)로 다시 찾아갑니다. 이 두 번째 방문이 heap fetch죠. 문제는 인덱스 순서와 heap의 물리적 순서가 대체로 다르다는 점입니다. 그래서 heap 접근은 흩어진 페이지를 random I/O로 긁게 되고, 조회 행이 많아질수록 비용이 눈덩이처럼 불어납니다.
index-only scan은 이 두 번째 단계를 통째로 생략합니다. 쿼리가 필요로 하는 모든 컬럼이 인덱스 엔트리에 이미 들어 있다면 heap까지 갈 이유가 없죠. 인덱스만 읽고 바로 반환하면 됩니다. 인덱스는 heap보다 페이지 수도 작아, random I/O가 대거 사라지니 빠를 수밖에 없습니다.
그런데 걸림돌이 하나 있습니다. PostgreSQL은 MVCC 구조라 각 행의 가시성(visibility) 정보가 heap의 tuple header에 들어 있는데, 인덱스 엔트리에는 이 정보가 없습니다. heap을 안 가고 어떻게 "보여줘도 되는 행인지"를 판단할까요. 여기서 등장하는 것이 visibility map입니다.
- visibility map은 heap의 페이지마다 1비트를 두어, "이 페이지의 모든 tuple이 모든 트랜잭션에 확정적으로 보인다"는 상태(all-visible)를 표시합니다.
- index-only scan은 엔트리를 읽을 때 이 map을 확인해, all-visible이면 heap 없이 인덱스 값만으로 행을 반환합니다.
- all-visible이 아니면 heap을 방문해 가시성을 확인하며, 이 방문 횟수가 EXPLAIN에 찍히는
Heap Fetches입니다.
여기에 중요한 실무 포인트가 있습니다. visibility map은 VACUUM이 갱신하므로, 대량 INSERT나 UPDATE 직후에는 페이지가 all-visible로 표시되지 않아 index-only scan을 태워도 Heap Fetches가 잔뜩 발생할 수 있죠. 저는 벤치마크 첫 실행이 유독 느려 한참 헤맸는데, 원인이 갓 적재한 데이터의 visibility map 미갱신이었습니다.
커버링 인덱스를 설계하는 법
커버링 인덱스(covering index)란 특정 쿼리가 필요로 하는 컬럼을 인덱스가 전부 "덮는(cover)" 상태, 즉 그 쿼리에 대해 index-only scan이 성립하는 인덱스입니다. 설계의 핵심은 하나의 쿼리를 놓고 세 종류의 컬럼을 빠짐없이 담는 것이죠.
- 필터 컬럼: WHERE, JOIN 조건에 등장. 인덱스 앞쪽 key 컬럼에 둡니다.
- 정렬 컬럼: ORDER BY 컬럼. key 순서를 정렬과 맞춰 두면 별도 sort 없이 인덱스 순서를 씁니다.
- 반환 컬럼: SELECT 목록에만 등장. 필터·정렬엔 안 쓰이지만 결과로 필요한 값들입니다.
문제는 세 번째 부류죠. 반환만 하면 되는 컬럼을 key 컬럼에 넣으면 B-tree의 모든 내부 노드에까지 값이 복제되어 인덱스가 쓸데없이 커집니다. PostgreSQL 11부터 지원하는 INCLUDE 컬럼이 이 지점을 정확히 해결합니다.
-- 필터/정렬은 key 컬럼, 반환만 필요한 값은 INCLUDE로
CREATE INDEX idx_orders_cover
ON orders (customer_id, created_at DESC) -- 탐색·정렬용 key 컬럼
INCLUDE (status, total_amount); -- 반환 전용, leaf에만 저장INCLUDE 컬럼은 leaf 페이지에만 저장되고 내부 노드에는 올라가지 않습니다. 덕분에 탐색 트리는 날씬하게 유지되면서, leaf에 도달했을 때 반환에 필요한 값은 전부 손에 쥐게 되죠. WHERE customer_id = ? ORDER BY created_at DESC 같은 조회가 이 인덱스 하나로 필터·정렬·반환이 모두 끝나 index-only scan이 됩니다.
다만 커버링 인덱스는 공짜가 아니라, 넓힐수록 대가가 따릅니다.
| 항목 | 좁은 인덱스 | 넓은 커버링 인덱스 |
|---|---|---|
| 조회 속도 | heap fetch 발생 | index-only로 빠름 |
| 인덱스 크기 | 작음 | 컬럼 수만큼 증가 |
| 쓰기 비용 | 낮음 | INSERT/UPDATE마다 갱신 부담 |
| 캐시 효율 | 좋음 | 커지면 buffer 압박 |
컬럼을 자꾸 INCLUDE에 밀어 넣으면 쓰기마다 큰 인덱스를 갱신해야 하고, shared_buffers에서 다른 인덱스가 밀려나 캐시 히트율이 떨어집니다. 저는 "이 쿼리 하나 빠르게 하겠다"고 컬럼 여덟 개짜리 커버링 인덱스를 만들었다가, UPDATE 지연이 눈에 띄게 올라가 결국 세 개로 줄인 적이 있습니다. 진짜 hot한 쿼리에만, 꼭 필요한 컬럼만 덮는 것이 원칙이라고 생각해요.
깨지기 쉬운 최적화
2025년 가을, 주문 목록 API 이야기입니다. 고객별 최근 주문 20건을 내려주는 엔드포인트로, 위 (customer_id, created_at DESC) INCLUDE (status, total_amount) 인덱스를 태워 p95가 8ms 안팎이었습니다. EXPLAIN을 찍으면 Index Only Scan에 Heap Fetches: 0, 교과서 같았죠.
사건은 사소한 기능 추가에서 시작됐습니다. 목록에 결제 수단 아이콘을 붙이자는 요청에, 동료가 SELECT에 payment_method 컬럼을 딱 한 줄 추가했죠.
SELECT status, total_amount, payment_method -- payment_method 한 줄 추가
FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC
LIMIT 20;배포 다음 날 이 API의 p95가 8ms에서 갑자기 300ms를 넘겼습니다. 지표만 보면 대형 장애 같았지만 diff는 저 한 줄뿐이었죠. EXPLAIN (ANALYZE, BUFFERS)를 걸자 원인이 드러났습니다.
-- 문제 발생 후: Index Only Scan 이 아니라 일반 Index Scan
Index Scan using idx_orders_cover on orders
Buffers: shared hit=41 read=1893 -- random read 폭증
actual time=0.3..312.5payment_method는 인덱스가 덮지 않는 컬럼이었습니다. 그래서 플래너는 index-only scan을 못 쓰고, 그 값을 가져오려 매 행마다 heap을 방문하는 일반 Index Scan으로 되돌아간 것이죠. 20건이라 작아 보여도 heap 페이지가 흩어져 random read가 폭증했습니다. 0이던 heap 접근이 되살아난, 전형적인 커버링 깨짐이었습니다.
해결은 간단합니다. 깨진 커버링을 다시 덮어 주면 되죠. payment_method를 INCLUDE에 추가했습니다.
DROP INDEX idx_orders_cover;
CREATE INDEX idx_orders_cover
ON orders (customer_id, created_at DESC)
INCLUDE (status, total_amount, payment_method); -- 반환 컬럼 보강인덱스를 다시 만들고 VACUUM으로 visibility map을 정리한 뒤 EXPLAIN을 찍으니 Index Only Scan, Heap Fetches: 0이 돌아왔고 p95도 8ms대로 내려앉았습니다. 수정은 반나절도 안 걸렸지만 배운 것은 오래 남았습니다.
이 사고 이후 저는 커버링에 의존하는 쿼리에 "이 인덱스가 SELECT를 전부 덮고 있음, 컬럼 추가 시 INCLUDE도 같이 손볼 것"이라고 주석으로 못을 박아 둡니다. 성능이 중요한 엔드포인트는 CI에서 EXPLAIN을 떠 Heap Fetches가 0을 벗어나면 경고가 뜨게 해 두었죠. 커버링 인덱스는 강력하지만 SELECT 한 줄에도 조용히 무너집니다. 무엇이 그 속도를 떠받치는지 EXPLAIN으로 늘 확인하는 습관이 가장 든든한 안전장치라고 생각합니다.