커버링 인덱스와 특수 인덱스

개념

일반 secondary 인덱스는 키만 가지고 있어서 조회할 컬럼이 더 있으면 테이블까지 한 번 더 점프해야 한다. 이 마지막 단계를 제거하는 게 커버링 인덱스고, 결과로 발생하는 실행 방식이 Index-Only Scan이다. 여기에 더해 B+tree로 풀리지 않는 도메인(배열, JSONB, 지리, 유사도)을 위한 특수 인덱스들이 따로 있다.

커버링 인덱스와 Index-Only Scan

문제 — Heap Fetch의 숨은 비용

CREATE INDEX idx_email ON users(email);

SELECT name, age FROM users WHERE email = 'x@y.com';

실제 흐름 (힙 테이블 기준).

1. email 인덱스 B+tree 탐색 → 리프에서 "Page 42의 슬롯 3"을 알아냄
2. email 인덱스는 email 값만 가지고 있어서 name, age를 모름
3. Page 42로 점프해서 실제 행을 읽음 — 이게 Heap Fetch (또는 Table Access)

인덱스로 위치를 찾았는데 다시 테이블 페이지를 한 번 더 찌른다. 반환할 컬럼 수만큼, 또는 결과 행 수만큼 이 점프가 반복된다.

해법 — 인덱스에 반환 컬럼까지 넣기

CREATE INDEX idx_cover ON users(email, name, age);

SELECT name, age FROM users WHERE email = 'x@y.com';

이제 인덱스 리프에 email, name, age가 다 있어서 테이블 안 감. 이걸 Index-Only Scan이라 부르고, 이 인덱스를 쿼리를 "커버한다"고 해서 Covering Index라고 부른다.

효과

• Heap Fetch 제거 → I/O 절반 이하
• 인덱스 페이지가 더 컴팩트해서 버퍼 풀 효율 좋음
• 리스트/피드 API 응답의 p99 레이턴시에 직접 영향

보통 2~10배 빨라진다. 대용량 테이블에서 자주 호출되는 조회에 결정적이다.

트레이드오프

공짜가 아니다. 커버링 인덱스가 붙을수록:

• 인덱스 디스크 공간 증가
• 인덱스 리프 페이지에 키가 적게 들어감 → 트리 깊이 증가 → 탐색 약간 느려짐
• INSERT/UPDATE 비용 증가 (인덱스도 그만큼 써야 함)

쿼리마다 필요한 컬럼이 다르므로 커버링 인덱스를 모든 쿼리에 만들 수는 없다. 자주 호출되는 핫한 쿼리 몇 개만 선별해서 적용한다.

INCLUDE 절

Postgres / SQL Server에 있는 문법. 탐색 키와 리프에 얹을 컬럼을 분리한다.

CREATE INDEX idx_email ON users(email) INCLUDE (name, age);

• email — B+tree 내부 노드에서 탐색에 쓰이는 키
• name, age — 리프에만 저장. 정렬·탐색엔 참여 안 하고 반환용으로만

이점: 탐색 구조는 얇게 유지하면서 커버링 효과는 얻음.

MySQL InnoDB엔 INCLUDE 없다. 대신 InnoDB는 secondary 인덱스 리프에 PK가 자동 포함되어 있어서, PK만 반환하는 쿼리는 자연스럽게 Index-Only가 된다.

Postgres MVCC 함정

Postgres에서 EXPLAIN에 Index Only Scan이 찍혀도 항상 진짜 index-only가 아니다. MVCC 때문이다.

• Postgres 인덱스는 "이 행이 현재 트랜잭션에 보이는지(visibility)" 정보를 안 가짐
• 그 정보는 테이블의 xmin/xmax에 있음
• 대신 Visibility Map(_vm 파일)에 "이 페이지는 모두에게 visible" 플래그가 있으면 테이블 접근을 생략 가능
• VM이 최신 상태가 아니면 해당 페이지는 테이블을 찔러서 확인해야 함

VACUUM이 주기적으로 돌아야 VM이 갱신된다. EXPLAIN (ANALYZE) 결과에 "Heap Fetches: N" 으로 얼마나 떨어지는지 찍힌다. 이 값이 높으면 커버링 인덱스 만들어도 이득이 반만 난다.

해시 인덱스

CREATE INDEX idx_email_hash ON users USING hash(email);

키를 해싱해 버킷에 저장. 등호(=) 조회가 O(1) — B+tree보다 약간 빠르다.

단점

• 범위 조회 불가 (>, <, BETWEEN)
• 정렬 불가 (ORDER BY)
• 복합 인덱스 불가

B+tree도 등호가 충분히 빠르고 범위·정렬까지 되는데 해시는 기능이 반 토막. 실무에서 거의 안 쓴다. 특수 케이스가 아니면 B+tree가 정답.

GIN (Generalized Inverted Index)

한 행이 여러 값을 가지는 컬럼을 위한 역색인.

• 배열: tags TEXT[]
• JSONB: metadata JSONB
• 전문 검색: tsvector

왜 B+tree로 안 되나

일반 B+tree는 "키 하나 → 행 하나"를 전제한다. 태그가 ['db', 'postgres', 'wal']인 글을 'postgres'로 검색하고 싶으면 B+tree 구조로는 답이 안 나온다.

구조

GIN은 역색인(inverted index) — 값 하나에서 그 값을 포함하는 행 목록으로 매핑한다.

'postgres' → [행 3, 행 7, 행 42, ...]
'wal'      → [행 7, 행 15, ...]
'db'       → [행 3, 행 15, 행 42, ...]

책 뒷면의 "찾아보기"와 같은 구조. Elasticsearch가 쓰는 원리도 이것.

CREATE INDEX idx_tags_gin ON posts USING gin(tags);

SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

트레이드오프

• 빠른 읽기 — 다중 값 검색 효율적
• 느린 쓰기 — 값 하나가 여러 역색인 항목을 갱신시킴
• 큰 인덱스 — 모든 값에 대해 역색인 유지

전문 검색(tsvector), JSONB 속성 검색, 태그 검색에 표준.

GiST (Generalized Search Tree)

B+tree로 표현하기 어려운 2차원 이상의 관계를 다룰 수 있는 일반화된 트리 구조. 거리·겹침·유사도 같은 관계를 인덱스로 다룬다.

대표 용도

• PostGIS 지리 검색 — "이 좌표 근처 5km 안의 장소"
• 범위 타입 겹침 — 시간 범위끼리 겹치는지
• pg_trgm 유사도 검색 — 오타를 허용하는 이름 검색

-- 지리 검색
CREATE INDEX idx_location ON places USING gist(location);

-- 유사도 검색 (오타 허용)
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING gist(name gist_trgm_ops);
SELECT * FROM users WHERE name % 'Jhon';  -- 'John' 찾아줌

지리·범위·검색 기능을 쓸 때만 등장. 일반 CRUD 서비스에는 안 나온다.

인덱스 타입 정리

타입	용도	대표 쿼리
B+tree	등호, 범위, 정렬	=, <, BETWEEN, ORDER BY
해시	등호만	= (실무 거의 안 씀)
GIN	다중 값 (배열, JSONB, 전문 검색)	@>, ?, @@
GiST	공간·범위·유사도	지리 검색, 범위 겹침, 오타 허용

실무에서 99%는 B+tree. GIN은 검색 기능 넣을 때, GiST는 지리·범위 타입 쓸 때 등장한다.

더 보기

• 인덱스-설계 — 복합 인덱스 설계, leftmost prefix, 카디널리티
• DB-스토리지-레이아웃 — Heap Fetch가 왜 추가 I/O인지 (힙 테이블 구조)
• B-tree와-LSM-tree — B+tree가 기본 선택지인 이유