PostgreSQL:CompositeIndex

PostgreSQL 의 복합 인덱스(Composite Index)는 두 개 이상의 컬럼을 하나의 인덱스로 묶은 것이다. B-Tree 구조에서 컬럼 순서대로 정렬되므로, 선행 컬럼(leftmost prefix)부터 순서대로 활용할 수 있다.

Usage

CREATE INDEX idx_example ON orders(customer_id, created_at);

이 인덱스는 내부적으로 다음과 같이 정렬된다:

customer_id | created_at
------------|---------------------
AAA         | 2025-01-01 09:00:00
AAA         | 2025-03-15 14:30:00
BBB         | 2025-02-10 11:00:00
BBB         | 2025-04-20 08:45:00
CCC         | 2025-01-05 16:20:00

선행 컬럼 규칙 (Leftmost Prefix Rule)

복합 인덱스는 왼쪽부터 연속된 컬럼 조합에 대해서만 인덱스를 활용할 수 있다.

-- 인덱스: (customer_id, created_at)

쿼리 조건	인덱스 활용	이유
`WHERE customer_id = ?`	Yes	선행 컬럼 단독 사용
`WHERE customer_id = ? AND created_at = ?`	Yes	전체 컬럼 매칭
`WHERE customer_id = ? AND created_at > ?`	Yes	선행 컬럼 + 후행 컬럼 범위
`WHERE created_at = ?`	No	선행 컬럼 누락
`WHERE created_at > ? AND customer_id = ?`	Yes	순서 무관 (옵티마이저가 재배치)

왜 선행 컬럼 없이는 안 되는가

B-Tree는 전화번호부와 같다. 전화번호부가 (성, 이름) 순서로 정렬되어 있을 때:

"김" 씨 성을 가진 사람 → 바로 찾을 수 있다 (선행 컬럼)
"김영희" → 바로 찾을 수 있다 (전체 매칭)
이름이 "영희"인 사람 → 전체를 다 뒤져야 한다 (선행 컬럼 누락)

UNIQUE 제약 조건과 인덱스

PostgreSQL에서 UNIQUE 제약 조건은 자동으로 인덱스를 생성한다. 따라서 동일한 컬럼에 인덱스를 별도로 만들면 중복이다.

단일 컬럼 UNIQUE

-- UNIQUE 제약 조건이 자동으로 인덱스를 생성한다
CREATE TABLE org_perms (
    name TEXT NOT NULL UNIQUE  -- 내부적으로 UNIQUE INDEX 생성
);

-- 따라서 아래는 중복이다
CREATE INDEX idx_org_perms_name ON org_perms(name);  -- 불필요

복합 UNIQUE

CREATE TABLE org_members (
    org_id  UUID NOT NULL,
    user_id UUID NOT NULL,
    UNIQUE(org_id, user_id)  -- (org_id, user_id) 복합 인덱스 자동 생성
);

별도 인덱스	필요 여부	이유
`INDEX(org_id)`	No	UNIQUE 복합 인덱스의 선행 컬럼이 커버
`INDEX(user_id)`	Yes	후행 컬럼이라 복합 인덱스로 커버 불가
`INDEX(org_id, user_id)`	No	UNIQUE 인덱스와 완전히 동일

PRIMARY KEY와 인덱스

PRIMARY KEY도 내부적으로 UNIQUE INDEX를 생성한다.

CREATE TABLE orders (
    id UUID PRIMARY KEY  -- UNIQUE INDEX 자동 생성
);

CREATE INDEX idx_orders_id ON orders(id);  -- 불필요

컬럼 순서 설계

복합 인덱스의 컬럼 순서는 쿼리 패턴에 따라 결정해야 한다.

원칙

등호(=) 조건이 먼저, 범위(>, <, BETWEEN) 조건이 나중에
카디널리티가 높은 컬럼(고유 값이 많은)이 먼저
자주 단독으로 조회되는 컬럼이 먼저 (선행 컬럼 규칙)

예시

-- 쿼리: WHERE org_id = ? AND created_at > ?
-- 좋은 순서: org_id가 등호 조건이므로 선행
CREATE INDEX idx_good ON org_members(org_id, created_at);

-- 나쁜 순서: 범위 조건이 선행하면 org_id 인덱스 활용 불가
CREATE INDEX idx_bad ON org_members(created_at, org_id);

3개 이상 컬럼 복합 인덱스

CREATE INDEX idx_triple ON table_name(a, b, c);

쿼리 조건	인덱스 활용	활용 범위
`WHERE a = ?`	Yes	a만 사용
`WHERE a = ? AND b = ?`	Yes	a, b 사용
`WHERE a = ? AND b = ? AND c = ?`	Yes	전체 사용
`WHERE b = ?`	No	선행 컬럼 a 누락
`WHERE a = ? AND c = ?`	Partial	a만 사용 (b를 건너뛸 수 없음)
`WHERE b = ? AND c = ?`	No	선행 컬럼 a 누락

인덱스 중복 확인 쿼리

현재 데이터베이스에서 중복 인덱스를 찾는 쿼리:

SELECT
    a.indexrelid::regclass AS index_a,
    b.indexrelid::regclass AS index_b,
    a.indrelid::regclass   AS table_name
FROM pg_index a
JOIN pg_index b
    ON a.indrelid = b.indrelid
    AND a.indexrelid != b.indexrelid
    AND (
        a.indkey::text = b.indkey::text
        OR a.indkey::text LIKE b.indkey::text || ' %'
    )
WHERE a.indisvalid AND b.indisvalid;