6.6 부분/표현식 인덱스
Partial index(부분 인덱스)와 expression index(표현식 인덱스)는 별개의 인덱스 타입이 아니라, 어떤 인덱스 타입에도 적용 가능한 옵션입니다. 적절히 쓰면 인덱스 크기를 수십 분의 1로 줄이고, 잘못된 컬럼 추상화로 인한 인덱스 미사용을 해결합니다.
Partial index — 조건이 붙은 인덱스
WHERE 절로 인덱스에 포함될 row를 제한합니다.
-- 전체 orders에 status 인덱스를 만들면 paid가 90% — 매우 큼
CREATE INDEX idx_orders_status_pending
ON orders(user_id)
WHERE status = 'pending';
-- 활용
SELECT * FROM orders
WHERE user_id = 42 AND status = 'pending';옵티마이저는 쿼리의 WHERE 조건이 인덱스의 WHERE를 포함하면 인덱스 활용 가능 판단합니다.
강점
- 인덱스가 작아 캐시·디스크 모두 효율적
- INSERT/UPDATE 시 대상 row가 인덱스에 안 들어가면 갱신 비용 0
- 자주 쿼리하는 hot subset에 집중
흔한 사용 사례
| 패턴 | 인덱스 |
|---|---|
| 활성 row만 자주 봄 | WHERE deleted_at IS NULL |
| 미처리 작업만 검색 | WHERE status IN ('pending','running') |
| 최근 데이터만 분석 | WHERE created_at > now() - interval '30 days' (volatile 함수 안 됨, 주의 아래) |
| 특정 tenant만 | WHERE tenant_id = 7 |
함정 — now() 같은 volatile 함수
-- 작동 안 함
CREATE INDEX bad_idx ON events(id)
WHERE created_at > now() - interval '30 days';now()는 호출 시점마다 달라지므로 PostgreSQL은 인덱스 정의 시점의 값을 고정으로 봅니다. 30일 지나면 인덱스가 과거 기준으로 굳어 있습니다. 정기 재생성 필요합니다.
대안: 절대 시점 기준의 partial index를 정기적으로 재생성하거나, 파티셔닝(Part VII)으로 해결합니다.
Expression index — 함수 결과를 인덱싱
WHERE에 함수 호출이 들어가면 일반 인덱스가 동작하지 않습니다.
-- 작동 안 함
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE LOWER(email) = 'a@b.com'; -- seq scan이유: 옵티마이저는 LOWER(email)의 결과 분포를 모름. 인덱스의 entry는 원본 email이라 매칭 불가능합니다.
해결: 표현식 자체를 인덱싱.
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- 같은 식의 쿼리만 인덱스 활용
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';쿼리의 식과 인덱스의 식이 완전히 일치해야 활용됩니다.
흔한 사용 사례
| 패턴 | 인덱스 |
|---|---|
| 대소문자 무시 검색 | LOWER(col), UPPER(col) |
| JSON 경로 검색 | (payload->>'user_id')::int |
| 함수 계산 결과 | date_trunc('day', created_at) |
| 도메인 추출 | split_part(email, '@', 2) |
| 정규식 정규화 | regexp_replace(phone, '[^0-9]', '', 'g') |
JSONB와의 조합 (6.4 참고)
-- 자주 쓰는 경로
CREATE INDEX idx_events_user_city
ON events ((payload->'user'->>'city'));
SELECT * FROM events WHERE payload->'user'->>'city' = 'Seoul';JSONB 컬럼 전체에 GIN을 두는 것과는 다른 패턴입니다. GIN = 다양한 containment 쿼리, 표현식 B-tree = 특정 경로의 등호·범위 쿼리.
Partial + expression 결합
CREATE INDEX idx_active_users_email_lower
ON users(LOWER(email))
WHERE deleted_at IS NULL;활성 사용자의 lower(email)만 인덱스에 — 크기 작고 정확합니다.
EXPLAIN으로 활용 확인
EXPLAIN SELECT * FROM users
WHERE LOWER(email) = 'a@b.com' AND deleted_at IS NULL;
-- 잘 잡힐 때
Index Scan using idx_active_users_email_lower
Index Cond: (lower(email) = 'a@b.com'::text)표현식이 다르거나 partial 조건이 안 맞으면 seq scan으로 빠집니다.
함수의 immutability
표현식 인덱스에 들어가는 함수는 immutable이어야 안전합니다. 입력이 같으면 출력이 항상 같다는 보장.
| 카테고리 | 예시 | 인덱스 안전 |
|---|---|---|
| IMMUTABLE | LOWER, length, ` | |
| STABLE | current_user, now() (트랜잭션 내 고정) | 위험 |
| VOLATILE | random(), clock_timestamp() | 사용 금지 |
사용자 함수를 표현식 인덱스에 쓸 때는 명시적으로 IMMUTABLE 선언:
CREATE FUNCTION canonical_phone(raw text) RETURNS text
AS $$ SELECT regexp_replace($1, '[^0-9]', '', 'g'); $$
LANGUAGE sql IMMUTABLE;
CREATE INDEX idx_users_phone ON users(canonical_phone(phone));Partial unique constraint
unique 인덱스에도 partial을 결합할 수 있다 — 조건이 만족하는 row 안에서만 unique.
-- 활성 사용자 안에서만 email unique
CREATE UNIQUE INDEX idx_active_email_unique
ON users(email)
WHERE deleted_at IS NULL;soft-delete + 같은 email로 재가입 허용 패턴에 표준입니다.
인덱스 결합 후보 점검
-- 어떤 인덱스가 자주 쓰이고 어떤 게 노는지
SELECT relname, indexrelname, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;idx_scan = 0이고 크기가 큰 인덱스는 partial로 줄이거나 삭제 후보입니다.
정리
- Partial index =
WHERE절로 인덱스 대상 row 제한 → 작고 효율적 - Expression index = 함수 결과를 인덱싱 →
WHERE LOWER(col) = …같은 쿼리 가속 - 둘은 함께 쓸 수 있고, unique 제약과도 결합 가능
- 표현식의 함수는 IMMUTABLE이어야 함
now()같은 volatile은 partial WHERE에 쓰면 안 됨idx_scan = 0의 큰 인덱스는 partial 변환 또는 삭제 후보
다음 절(6.7)에서는 인덱스가 시간이 지나며 자라는 문제 — BLOAT과 REINDEX CONCURRENTLY — 를 봅니다.