빠른 비교

-- 복합 인덱스: 자주 함께 필터링·정렬되는 열을 묶는다
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 구조 탐색 순서와 직결됩니다. PostgreSQL은 인덱스의 왼쪽 열부터 순서대로 사용하므로, (user_id, created_at) 인덱스는 WHERE user_id = ? 또는 WHERE user_id = ? AND created_at > ? 쿼리에서 활용됩니다. 그러나 WHERE created_at > ? 단독으로는 이 인덱스를 사용하지 못합니다. 선택도(cardinality)가 높은 열을 앞에, 범위 조건이나 정렬에 쓰는 열을 뒤에 두는 것이 일반 원칙입니다.

-- user_id로 필터링 후 created_at DESC 정렬 → 인덱스 순서와 일치
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
-- EXPLAIN 결과: Index Scan using idx_orders_user_created_at

복합, 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는 인덱스 크기와 유지 비용을 동시에 줄인다

전체 주문 중 status = 'paid'인 행이 5%라면, 전체 테이블에 인덱스를 거는 것은 95%의 행에 대한 인덱스 유지 비용을 쓸데없이 지불하는 셈입니다. partial index는 WHERE 조건을 인덱스 정의에 포함해 해당 조건을 만족하는 행만 인덱싱합니다. 결과적으로 인덱스 파일이 작아지고, INSERT·UPDATE·DELETE 시 인덱스 갱신 빈도도 줄어 쓰기 부하가 낮아집니다. 다만 쿼리의 WHERE 조건이 인덱스 조건과 정확히 일치하거나 더 좁아야 옵티마이저가 이 인덱스를 선택합니다.

-- 이 쿼리는 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';

복합, partial, covering은 같은 인덱스라도 해결하는 병목이 다르다

복합 인덱스는 "어떤 순서로 찾고 정렬하느냐", 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';

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을 선택하는 경우는 여러 이유가 있습니다. 반환 행 비율이 너무 높거나(소형 테이블), 열에 함수가 씌워져 있거나(DATE(created_at) 대신 created_at::date), 통계가 오래됐거나, 인덱스와 쿼리 타입이 불일치할 때입니다. 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 paid = true
ORDER BY created_at DESC;

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