Quick Comparison

-- 복합 인덱스: 자주 함께 필터링·정렬되는 열을 묶는다
CREATE INDEX idx_orders_user_created_at
ON orders (user_id, created_at DESC);

-- partial index: 자주 조회되는 조건을 인덱스 자체에 새긴다
CREATE INDEX idx_orders_paid_only
ON orders (created_at DESC)
WHERE status = 'paid';

-- covering index: 결과 열까지 인덱스에 포함해 heap 접근을 없앤다
CREATE INDEX idx_orders_covering
ON orders (user_id, created_at DESC)
INCLUDE (amount, status);

복합 B-tree는 선행 열에 equality 조건이 있는 query에서 가장 예측 가능하고, PostgreSQL 18은 뒤 열 조건만으로도 분포가 맞으면 skip scan을 선택할 수 있습니다. partial index는 query predicate가 index predicate를 함의할 때만 후보가 되며, INCLUDE는 index-only scan의 필요조건일 뿐 실행을 보장하지 않습니다. 실제 index 선택은 EXPLAIN (ANALYZE, BUFFERS)로 확인합니다.

갈리는 기준

복합 인덱스의 열 순서는 쿼리 패턴이 결정한다

복합 인덱스에서 열 순서는 B-tree 구조 탐색 순서와 직결됩니다. (user_id, created_at)은 WHERE user_id = ? 또는 WHERE user_id = ? AND created_at > ?에서 가장 효율적으로 범위를 줄입니다. PostgreSQL 18에서는 created_at 단독 조건도 선행 user_id의 distinct 값이 충분히 적을 때 skip scan 후보가 될 수 있지만, 일반적인 대체 index로 기대하면 안 됩니다. 선택도·정렬·query 빈도를 기준으로 열 순서를 정하고 실제 plan을 확인합니다.

-- user_id로 필터링 후 created_at DESC 정렬 → 인덱스 순서와 일치
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
-- 실행 계획이 이 index를 택하는지는 통계와 비용에 따라 달라진다

복합, partial, covering index는 각각 다른 병목을 줄인다

복합 인덱스는 여러 열을 함께 찾거나 정렬할 때, partial index는 일부 행만 자주 읽을 때, covering index는 heap 접근을 줄이고 싶을 때 선택합니다. 셋 다 "고급 인덱스"지만 해결하려는 병목이 다르므로 먼저 쿼리 패턴을 분리해서 봐야 합니다.

-- 여러 열을 함께 찾음
CREATE INDEX idx_orders_user_created_at
ON orders (user_id, created_at DESC);

-- 일부 행만 자주 찾음
CREATE INDEX idx_orders_paid_only
ON orders (created_at DESC)
WHERE status = 'paid';

partial index는 인덱스 크기와 유지 비용을 동시에 줄인다

partial index는 WHERE 조건을 인덱스 정의에 포함해 해당 조건을 만족하는 행만 인덱싱합니다. 결과적으로 인덱스 파일과 유지 비용을 줄일 수 있습니다. 다만 planner가 query predicate가 index predicate를 함의한다고 증명할 수 있어야 이 인덱스를 후보로 고려합니다. 형태가 비슷하다는 사실만으로는 충분하지 않고, 일반화된 prepared plan의 parameter 조건은 이 증명을 하지 못할 수 있습니다.

-- 이 쿼리는 idx_orders_paid_only를 사용할 수 있다
SELECT * FROM orders
WHERE status = 'paid' AND created_at > NOW() - INTERVAL '30 days';

-- 이 쿼리는 status 조건이 없으므로 partial index를 사용하지 못한다
SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '30 days';

covering index는 heap 접근을 제거해 index-only scan을 가능하게 한다

일반 인덱스 스캔은 인덱스에서 행 위치를 찾은 뒤 heap(실제 테이블 데이터)에 다시 접근해 SELECT 컬럼 값을 읽습니다. INCLUDE 절로 결과에 필요한 컬럼을 인덱스에 추가하면, heap 접근 없이 인덱스만으로 쿼리를 완료하는 index-only scan이 가능해집니다. 단, INCLUDE 컬럼은 검색 조건에는 사용되지 않으며 인덱스 크기를 키우므로, 자주 조회되는 컬럼에만 선별적으로 추가해야 합니다. MVCC 가시성 확인을 위해 visibility map이 최신 상태여야 index-only scan이 실제로 작동하는 점도 알아야 합니다.

-- EXPLAIN에서 "Index Only Scan"이 보이면 heap 접근 없이 완료된 것
EXPLAIN ANALYZE
SELECT user_id, created_at, amount, status
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

실행 계획이 인덱스를 안 쓴다면 통계·선택도·함수 변환을 확인한다

옵티마이저가 인덱스를 무시하고 Seq Scan을 선택하는 경우는 여러 이유가 있습니다. 반환 행 비율이 높거나, 일반 인덱스 열을 함수로 감쌌거나(created_at::date = CURRENT_DATE), 통계가 오래됐거나, 인덱스와 query 타입이 불일치할 때입니다. ANALYZE 명령으로 통계를 갱신하고 EXPLAIN (ANALYZE, BUFFERS)로 실제 버퍼 I/O까지 확인하는 것이 인덱스 설계 검증의 기본입니다.

-- 통계 갱신 후 실행 계획 재확인
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE user_id = 42 AND created_at > NOW() - INTERVAL '7 days';

선택 기준

주의할 점

CREATE INDEX idx_orders_created_user
ON orders (created_at, user_id);

실제 쿼리가 WHERE user_id = ? ORDER BY created_at DESC 형태라면, 열 순서를 이렇게 뒤집으면 기대한 만큼 도움이 되지 않을 수 있습니다. 복합 인덱스는 "같은 열이 들어가느냐"보다 "순서가 맞느냐"가 중요합니다.

CREATE INDEX idx_orders_paid_only
ON orders (created_at DESC)
WHERE status = 'paid';

SELECT *
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC;

partial index는 쿼리 조건이 인덱스 조건과 실제로 맞아떨어져야 효과가 납니다. 컬럼명이나 조건식이 조금만 달라도 옵티마이저는 이 인덱스를 못 고를 수 있으니, "비슷해 보이는 조건"을 기대하기보다 실제 WHERE 절이 무엇인지 먼저 확인해야 합니다.