인덱스 온리 스캔이 안 되는 이유

필요한 컬럼을 전부 인덱스에 넣었어요. 테이블 본체를 안 읽어도 되니 훨씬 빨라져야 하는데, EXPLAIN 을 보면 여전히 테이블을 읽고 있어요.

어떤 때는 인덱스 온리 스캔이 뜨는데도 안 빨라져요. 계획 이름은 맞는데 실제 시간은 그대로예요.

그리고 이상한 패턴이 있어요. 대량 갱신 직후에는 느리다가, 한참 지나면 저절로 빨라져요. 아무것도 안 고쳤는데요.

이 편의 답은 그 '한참' 동안 무슨 일이 일어나는지에 있어요. 그리고 그걸 알면, 왜 쓰기가 많은 테이블에서는 이 최적화를 믿으면 안 되는지도 같이 설명됩니다.

계획 이름이 맞다고 최적화가 걸린 게 아니라는 걸 보여 주는 대표적인 자리예요.

왜 그런가

인덱스 온리 스캔은 필요한 값이 전부 인덱스 안에 있을 때, 테이블 본체를 읽지 않고 끝내는 방식이에요.

그런데 한 가지 문제가 있어요. 인덱스에는 그 행이 지금 살아 있는지에 대한 정보가 없어요. MVCC 에서 행의 가시성 정보는 테이블 쪽에 있거든요. 그래서 원칙적으로는 인덱스만 봐서는 그 값을 돌려줘도 되는지 알 수 없어요.

PostgreSQL 은 이걸 가시성 맵으로 풀어요. 페이지 단위로 "이 페이지의 모든 행은 모두에게 보인다"는 표시를 유지해요. 인덱스 온리 스캔은 이 표시를 확인하고, 표시가 있으면 테이블을 안 읽어요.

이 표시는 VACUUM 이 붙여요. 그래서 청소가 안 돌았거나 최근에 대량으로 바뀐 테이블은 표시가 없고, 결국 테이블을 다시 읽게 돼요. 계획 이름은 인덱스 온리 스캔인데 실제로는 본체를 읽는 상태예요.

"한참 지나면 저절로 빨라진다"의 정체가 이거예요. autovacuum 이 돌면서 가시성 맵을 갱신한 겁니다.

MySQL 의 InnoDB 는 구조가 달라요. 보조 인덱스가 기본키 값을 들고 있어서, 필요한 컬럼이 인덱스와 기본키 안에 다 있으면 커버링 인덱스로 처리돼요. 가시성 맵 같은 장치는 없고 대신 언두 로그를 봐야 하는 경우가 있습니다.

코드로 보면

필요한 값을 인덱스 안에 다 넣는다:

· 조회에 쓰는 컬럼이 인덱스 밖에 있으면 본체를 읽는다
✕ CREATE INDEX idx ON orders (user_id);
✕ SELECT user_id, status FROM orders WHERE user_id = 1;
✕ → status 를 가지러 테이블로 간다

· 1) 인덱스에 포함시킨다
✓ CREATE INDEX idx ON orders (user_id, status);

· 2) 정렬·조건에 안 쓰이면 포함 컬럼으로 (인덱스가 덜 커진다)
✓ CREATE INDEX idx ON orders (user_id) INCLUDE (status);

· 3) 그리고 가시성 맵이 있어야 실제로 효과가 난다
✓ VACUUM (ANALYZE) orders;
✓ SELECT relname, last_vacuum, last_autovacuum
✓ FROM pg_stat_user_tables WHERE relname='orders';

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

인덱스 온리 스캔이 실제로 이득을 주려면 조건이 여럿 맞아야 해요.

첫째, 필요한 컬럼이 전부 인덱스에 있어야 해요. SELECT 목록만이 아니라 WHERE, ORDER BY, GROUP BY 에 쓰이는 컬럼 전부요. 하나라도 밖에 있으면 본체를 읽어야 합니다.

둘째, 가시성 맵이 최신이어야 해요. 이건 VACUUM 이 관리하니 청소가 잘 도는지가 성능에 직결돼요. 쓰기가 많은 테이블에서는 이 방식의 이득이 들쭉날쭉해집니다.

셋째, 인덱스가 커지는 비용을 감당해야 해요. 컬럼을 넣을수록 인덱스가 커지고, 커지면 캐시에 덜 들어가고 쓰기도 느려져요. 본체를 안 읽어서 아낀 것보다 인덱스가 커져서 잃은 게 클 수도 있어요.

여기서 포함 컬럼이 유용해요. 조건이나 정렬에 안 쓰이고 값만 필요한 컬럼은 인덱스의 정렬 구조에 참여시키지 않고 잎 노드에만 값을 얹을 수 있어요. 트리 구조가 덜 커지고 유니크 인덱스에도 컬럼을 얹을 수 있습니다.

그리고 이 방식이 특히 강한 자리가 있어요. 집계예요. 개수를 세거나 합계를 구하는 쿼리는 본체의 다른 컬럼이 필요 없으니, 인덱스만으로 끝낼 수 있으면 읽는 양이 크게 줄어요. 뒤에서 다룰 COUNT 문제와 이어지는 얘기예요.

반대로 이득이 작은 경우도 알아 두세요. 결과 행이 몇 개 안 되면 본체를 몇 번 더 읽는 비용이 어차피 작아요. 인덱스에 컬럼을 욱여넣을 이유가 없습니다. 이 방식은 많은 행을 훑어야 할 때 값어치가 나와요.

처방

1. 자주 도는 쿼리가 쓰는 컬럼을 확인하고, 전부 인덱스에 들어가는지 보세요. SELECT 목록만이 아니라 조건·정렬까지요.
2. 조건과 정렬에 안 쓰이는 컬럼은 포함 컬럼으로 넣으세요. 인덱스 트리가 덜 커집니다.
3. 청소가 잘 도는지 확인하세요. 가시성 맵이 낡으면 계획 이름만 인덱스 온리 스캔이고 실제로는 본체를 읽어요.
4. 쓰기가 많은 테이블에서는 이 방식에 기대지 마세요. 가시성 맵이 계속 무효화돼서 이득이 불안정합니다.
5. 인덱스 크기 증가를 재세요. 컬럼을 넣기 전과 후의 인덱스 크기를 비교하고, 쓰기 성능 변화도 같이 보세요.
6. 집계 쿼리를 우선 대상으로 삼으세요. 이 방식의 이득이 가장 큰 자리예요.
7. EXPLAIN 에서 실제로 본체를 읽었는지 확인하세요. 계획 이름이 아니라 힙 접근 횟수를 봐야 합니다.
8. 커버링 인덱스는 특정 쿼리에 맞춰 만드는 거라는 걸 기억하세요. 그 쿼리가 사라지면 큰 인덱스만 남습니다. 만들 때 어떤 쿼리를 위한 것인지 주석이나 문서에 남겨 두세요.

지금 확인해 보세요

정말 본체를 안 읽었나:

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, status FROM orders WHERE user_id = 1;

✓ Index Only Scan using idx_orders_cover
✕ Heap Fetches: 48000   ← 이게 크면 실제로는 본체를 읽었다
✓ Heap Fetches: 0       ← 이게 목표다

· 가시성 맵을 갱신하는 건 VACUUM 이다
SELECT relname, last_vacuum, last_autovacuum, n_dead_tup
  FROM pg_stat_user_tables WHERE relname = 'orders';

· 인덱스가 얼마나 커졌나 — 이득과 견줘 본다
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes WHERE relname = 'orders';

핵심 한 줄인덱스만 읽어도 되려면, 청소가 그렇다고 말해 줘야 한다.