[PostgreSQL] 커버링 인덱스로 힙 안 건드리기 — INCLUDE + index-only scan + 가시성맵/VACUUM + DESC NULLS LAST

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

부제: 상품 목록에서 category로 걸고 name, price만 뽑는데 매번 힙 랜덤 I/O가 병목이라, 조회 컬럼을 인덱스에 실었더니 기대만큼 안 빨라질 때

상품 목록 API가 WHERE category=? 로 걸고 name, price 두 컬럼만 반환한다. 인덱스는 category에 걸려 있는데, 인덱스에서 행 위치만 찾고 실제 name, price를 읽으려고 매번 힙(테이블 본체)을 랜덤하게 다시 방문한다. 이 힙 페치가 병목이다. 조회하는 컬럼을 아예 인덱스에 실어서 힙을 건드리지 않게 만들어보자. 그런데 커버링 인덱스를 걸고도 안 빨라지는 함정이 하나 있다.

1단계 — INCLUDE로 조회 컬럼을 인덱스에 싣는다

검색 키는 category 하나지만, name과 price를 페이로드로 함께 저장한다. INCLUDE에 들어간 컬럼은 정렬 키가 아니라 "실려만 가는" 컬럼이라, 인덱스 정렬 규칙이나 유일성 판정에는 관여하지 않는다.

CREATE INDEX cov_cat_incl ON cov (category) INCLUDE (name, price);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT name, price FROM cov WHERE category = 'cat42';

50만 행 테이블(방금 적재, VACUUM 전)에서 실제 플랜은 이렇다.

                             QUERY PLAN
---------------------------------------------------------------------
 Bitmap Heap Scan on cov (actual rows=50 loops=1)
   Recheck Cond: (category = 'cat42'::text)
   Heap Blocks: exact=50
   ->  Bitmap Index Scan on cov_cat_incl (actual rows=50 loops=1)
         Index Cond: (category = 'cat42'::text)
(5 rows)

커버링 인덱스를 만들었는데 Index Only Scan이 아니라 Bitmap Heap Scan이 떴다. 힙을 여전히 50블록 읽는다(Heap Blocks: exact=50). "왜 커버링을 걸었는데 힙을 또 보나?"

2단계 — 억지로 index-only scan을 시켜 Heap Fetches를 확인한다

플래너를 잠깐 몰아세워 index-only scan을 강제하면 원인이 드러난다.

SET enable_bitmapscan = off;
SET enable_seqscan = off;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT name, price FROM cov WHERE category = 'cat42';
RESET enable_bitmapscan; RESET enable_seqscan;

                                QUERY PLAN
--------------------------------------------------------------------------
 Index Only Scan using cov_cat_incl on cov (actual rows=50 loops=1)
   Index Cond: (category = 'cat42'::text)
   Heap Fetches: 50
(3 rows)

Heap Fetches: 50. index-only scan인데도 50번 힙을 방문했다. 인덱스에 name, price가 다 있는데 왜? index-only scan은 가시성 맵(visibility map)의 all-visible 비트가 켜진 힙 페이지에 대해서만 힙을 건너뛴다. 방금 대량 적재한 페이지들은 아직 이 비트가 꺼져 있어서, "이 행이 지금 트랜잭션에 보여도 되는지" 확인하려고 힙을 도로 방문한다. 플래너도 이걸 알기 때문에(테이블의 all-visible 비율이 0), 1단계에서 index-only scan 대신 bitmap heap scan을 골랐던 것이다.

3단계 — VACUUM이 가시성 맵을 세운다

VACUUM을 돌려 all-visible 비트를 세워주면 index-only scan이 진짜 index-only가 된다.

VACUUM cov;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT name, price FROM cov WHERE category = 'cat42';

                                QUERY PLAN
--------------------------------------------------------------------------
 Index Only Scan using cov_cat_incl on cov (actual rows=50 loops=1)
   Index Cond: (category = 'cat42'::text)
   Heap Fetches: 0
(3 rows)

이제 enable_* 를 건드리지 않은 자연 상태에서도 플래너가 스스로 Index Only Scan을 고르고, Heap Fetches: 0이다. 힙을 한 번도 안 봤다. 커버링 인덱스의 완성 조건은 "INCLUDE로 컬럼을 싣는 것" 절반, "VACUUM으로 가시성 맵을 세우는 것" 나머지 절반이다. 갱신이 잦은 테이블에서 커버링이 기대만큼 안 빠르면 EXPLAIN ANALYZE의 Heap Fetches를 먼저 보면 된다.

4단계 — DESC NULLS LAST 인덱스로 Sort 노드까지 없앤다

목록을 최신순(updated_at DESC)으로 정렬하되 값이 없는 행(예약 발행 등 NULL)은 맨 뒤로 보내고 싶다. 맞는 정렬 인덱스가 없으면 매번 Sort가 붙는다.

EXPLAIN (COSTS OFF)
SELECT id, updated_at FROM cov ORDER BY updated_at DESC NULLS LAST LIMIT 20;

                     QUERY PLAN
----------------------------------------------------
 Limit
   ->  Gather Merge
         Workers Planned: 2
         ->  Sort
               Sort Key: updated_at DESC NULLS LAST
               ->  Parallel Seq Scan on cov
(6 rows)

50만 행을 전부 훑고 Sort까지 한 뒤 20개만 남긴다. 인덱스를 ORDER BY와 방향·NULL 위치까지 똑같이 만들어주면 Sort가 통째로 사라진다.

CREATE INDEX cov_upd_desc ON cov (updated_at DESC NULLS LAST);

EXPLAIN (COSTS OFF)
SELECT id, updated_at FROM cov ORDER BY updated_at DESC NULLS LAST LIMIT 20;

                    QUERY PLAN
--------------------------------------------------
 Limit
   ->  Index Scan using cov_upd_desc on cov
(2 rows)

Sort 노드가 없다. 인덱스 앞쪽 20개만 읽고 끝난다. 주의할 점은 방향/NULL 위치가 어긋나면 이 인덱스를 못 쓴다는 것이다. 기본 DESC는 NULLS FIRST라, 인덱스는 DESC NULLS LAST인데 쿼리가 그냥 DESC면 다시 Sort가 붙는다.

EXPLAIN (COSTS OFF)
SELECT id, updated_at FROM cov ORDER BY updated_at DESC LIMIT 20;

                  QUERY PLAN
-----------------------------------------------
 Limit
   ->  Gather Merge
         Workers Planned: 2
         ->  Sort
               Sort Key: updated_at DESC
               ->  Parallel Seq Scan on cov
(6 rows)

결론 — 언제 이 조합을 쓰나

읽기가 쓰기보다 압도적으로 잦은 조회 경로에서, (1) INCLUDE로 조회 컬럼을 실어 힙 접근을 없애고, (2) VACUUM/autovacuum이 가시성 맵을 세워 index-only scan을 실제로 성립시키고, (3) 정렬까지 인덱스 순서로 흡수해 LIMIT n을 스캔 없이 끝내는 세 겹이 맞물릴 때 효과가 크다. "커버링 걸었는데 왜 안 빨라?"의 답은 대개 Heap Fetches와 VACUUM이다.

이렇게도 쓴다

커버링이 정말 걸렸는지 Heap Fetches로 검증한다. (조합: EXPLAIN ANALYZE)

EXPLAIN (ANALYZE, BUFFERS)
SELECT name, price FROM cov WHERE category = 'cat42';
-- Heap Fetches: 0 이어야 진짜 index-only

 

가시성 맵 커버리지를 직접 들여다본다. (조합: pg_visibility)

SELECT count(*) AS all_visible_pages
FROM pg_visibility_map('cov') WHERE all_visible;

 

복합 인덱스 (x, y) 대신 INCLUDE를 쓰는 이유 — 유일성은 키에만, 정렬 부담 없이 페이로드만 싣는다.

-- 검색은 category로만, name/price는 실려만 감
CREATE INDEX ON cov (category) INCLUDE (name, price);
-- vs 복합 인덱스: name/price도 정렬 키가 되어 인덱스가 커지고 갱신 부담↑
CREATE INDEX ON cov (category, name, price);

 

혼합 정렬도 인덱스로 흡수한다. (조합: 복합 인덱스 + ORDER BY)

-- ORDER BY category ASC, updated_at DESC 를 Sort 없이
CREATE INDEX ON cov (category ASC, updated_at DESC NULLS LAST);

 

INCLUDE 컬럼 밖을 참조하면 index-only가 깨진다 — 경계 확인.

-- payload는 인덱스에 없으므로 힙을 봐야 함 → Index Only Scan 불가
EXPLAIN SELECT name, id FROM cov WHERE category = 'cat42';

언제 다른 도구로: 조회 컬럼이 넓거나(대용량 text/jsonb) 자주 바뀌면 INCLUDE가 인덱스를 뚱뚱하게 만들고 갱신 비용만 키운다. 이럴 땐 커버링을 포기하고 일반 인덱스 + 힙 접근이 낫다.