PostgreSQL의 부분 인덱스

Source: Dev.to

개요

Partial 인덱스는 특정 접근 패턴을 목표로 하는 정교한 인덱스입니다. 테이블의 모든 행을 인덱싱하는 대신, 조건에 맞는 행만 인덱싱하므로 더 작고 빠르며 올바른 사용 사례에 대해 더 효율적입니다.

Partial 인덱스 작동 방식

Partial 인덱스는 다음과 같은 쿼리에 가장 적합합니다.

• 먼저 필터링하고 여러 행을 스캔함
• 의미 있는 데이터 하위 집합을 목표로 함
• 인덱스 임계값에 걸려 플래너가 인덱스를 완전히 무시할 수 있는 경우

샘플 데이터베이스를 이용한 시연

예제는 MySQL 샘플 데이터베이스를 PostgreSQL로 변환한 것입니다. salaries 테이블은 약 300만 행을 포함합니다.

CREATE TABLE "employees"."salaries" (
    "id" int4 GENERATED ALWAYS AS IDENTITY,
    "emp_no" int4,
    "salary" int4,
    "from_date" date,
    "to_date" date,
    PRIMARY KEY ("id")
);

기본 인덱스 쿼리

SELECT COUNT(*)
FROM salaries
WHERE salary > 100000
  AND to_date = '9999-01-01';

이 데이터셋에서 9999-01-01은 현재 급여를 의미합니다. 쿼리 수행 시간은 **140 ms(콜드)**와 40 ms(핫) 정도였습니다.

Partial 인덱스 추가

인덱스를 추가하기 전에 몇 개의 행이 필터링될지 생각해 보세요. 여기서는 300만 행 중 247 000개만 현재(활성)이며, 큰 감소를 보입니다.

CREATE INDEX idx_salaries_salary_todate_partial
ON salaries (salary)
WHERE to_date = '9999-01-01';

Partial 인덱스를 사용하면 동일한 쿼리가 ≈16 ms(콜드·핫 모두) 안에 실행됩니다.

Partial 인덱스가 빛을 발하는 경우

대규모 결과 집합과 “30 % 규칙”

결과 집합이 전체 행의 약 30 %를 초과하면 플래너가 범위 쿼리를 무시할 수 있습니다. 예시:

SELECT COUNT(*)
FROM salaries
WHERE salary > 50000
  AND to_date = '9999-01-01';

• salary > 100000 → 17 000 행 반환 → 인덱스 사용.
• salary > 50000 → 215 000 행 반환 (활성 행의 ≈87 %) → 표준 인덱스 무시, 테이블 스캔 사용, 핫 시간 ≈120 ms.

Partial 인덱스는 시작 행 수를 줄여 주므로, 더 넓은 필터를 사용하더라도 쿼리 시간이 40 ms 정도로 유지됩니다.

하위 집합에 대한 반복 쿼리

전형적인 시나리오는 is_processed 불리언을 가진 큐입니다.

CREATE INDEX idx_unprocessed_queue
ON queue (created_at)
WHERE is_processed = false;

미처리 행이 전체의 작은 비율이라면 인덱스는 작고 빠르게 유지됩니다.

인덱스 크기 이점

• 전체 급여 인덱스: 58 MB
• Partial 급여 인덱스: 7 MB

작은 인덱스는 버퍼 캐시에 더 잘 들어가 메모리에 남아 있을 확률이 높아집니다.

효과가 미미한 상황

컬럼이 이미 고유 인덱스로 지정돼 있다면, 추가 조건에 대한 Partial 인덱스는 성능 향상이 거의 없습니다.

SELECT id
FROM users
WHERE email = 'test@test.com' AND is_active = true;

email이 고유하므로 플래너는 is_active와 무관하게 고유 인덱스를 사용합니다. 다만 매우 큰 테이블에서는 Partial 인덱스가 인덱스 부풀림을 줄이는 데 도움이 될 수 있습니다.

요약

Partial 인덱스는 특정 접근 패턴에 대한 쿼리 시간을 줄이는 강력한 도구입니다. 가장 효과적인 경우는 다음과 같습니다.

• 쿼리가 동일한 조건을 지속적으로 필터링할 때
• 해당 조건이 상당히 많은 행을 제외할 때
• 결과 인덱스가 전체 인덱스에 비해 크게 작을 때

가장 흔히 사용하는 쿼리를 분석하고 필터링될 데이터 양을 추정해 보세요. 숫자가 유망하다면 Partial 인덱스를 도입하는 것이 좋은 선택이 될 수 있습니다.