[PostgreSQL] 커버링 인덱스(INCLUDE)로 테이블을 아예 안 읽는다

설치·접속: PostgreSQL 설치와 접속

부제: user_id 범위로 amount만 뽑는 조회가 잦은데, 인덱스는 탔지만 매번 테이블(heap)까지 다녀와 느릴 때

-- 검색 키는 user_id, 반환할 amount는 INCLUDE로 인덱스에 얹는다
CREATE INDEX idx_orders_uid_incl ON demo_orders(user_id) INCLUDE (amount);

SELECT user_id, amount FROM demo_orders WHERE user_id BETWEEN 100 AND 200;

-- 일반 인덱스 (user_id): 인덱스로 위치만 찾고 테이블 889블록을 또 읽음 → 891 buffers, 0.91 ms
 Index Only Scan using idx_orders_uid_incl on demo_orders (actual time=0.006..0.081 rows=1015 loops=1)
   Index Cond: ((user_id >= 100) AND (user_id <= 200))
   Heap Fetches: 0
   Buffers: shared hit=3 read=5
 Execution Time: 0.122 ms

일반 인덱스는 user_id로 행의 위치만 알려주고, 정작 amount 값을 읽으려면 테이블(heap)로 다시 가야 한다. 매칭 행이 많으면 이 heap 방문이 병목(891 buffers). INCLUDE (amount)로 amount를 인덱스 리프에 얹어두면 인덱스만 읽고 끝나는 Index Only Scan이 되고, Heap Fetches: 0이 그 증거다 — 891 buffers가 8 buffers로 준다. INCLUDE 컬럼은 검색/정렬에는 안 쓰이고 그저 실려만 가므로, 키에 넣기엔 부적절한(정렬 의미 없는) 반환용 컬럼에 적합하다. 함정 둘: 인덱스가 커지고(여기선 15MB), Index Only Scan은 visibility map이 최신이어야(=VACUUM이 돌아야) heap 방문 없이 동작한다.

이렇게도 쓴다

여러 반환 컬럼을 한꺼번에 실어 완전 커버링을 만든다.

CREATE INDEX idx_cover ON demo_orders(user_id) INCLUDE (amount, status, created_at);

 

INCLUDE 대신 복합 키로도 커버링이 되지만, 정렬 대상이 늘어난다. (비교)

CREATE INDEX idx_multi ON demo_orders(user_id, amount);

 

Index Only Scan이 되려면 VACUUM으로 visibility map을 갱신한다. (조합: VACUUM)

VACUUM demo_orders;

 

Heap Fetches가 0인지로 커버링 성공을 확인한다. (조합: EXPLAIN ANALYZE)

EXPLAIN (ANALYZE) SELECT user_id, amount FROM demo_orders WHERE user_id=150;

 

UNIQUE 제약을 유지하면서 반환 컬럼을 얹는다. (조합: UNIQUE INCLUDE)

CREATE UNIQUE INDEX uq_ord ON demo_orders(id) INCLUDE (amount);

 

인덱스가 얼마나 커졌는지 대가를 확인한다. (조합: pg_relation_size)

SELECT pg_size_pretty(pg_relation_size('idx_orders_uid_incl'));