[PostgreSQL] 부분 인덱스(partial index)로 소수 행만 인덱싱한다

설치·접속: PostgreSQL 설치와 접속

부제: 주문 50만 건 중 status='pending'은 5%뿐인데, 그 대기 건만 자주 조회할 때

-- pending 행만, created_at 기준으로 인덱스를 만든다
CREATE INDEX idx_partial_pending
  ON demo_orders(created_at) WHERE status='pending';

SELECT * FROM demo_orders WHERE status='pending'
  ORDER BY created_at DESC LIMIT 20;

-- 인덱스 크기: 전체 status 인덱스 3408kB  vs  부분 인덱스 568kB
 Limit (actual time=0.011..0.024 rows=20 loops=1)
   Buffers: shared hit=20 read=2
   ->  Index Scan Backward using idx_partial_pending on demo_orders (actual time=0.010..0.022 rows=20 loops=1)
 Execution Time: 0.029 ms
-- 인덱스 없이 같은 조건: Parallel Seq Scan, 3712 buffers, 14.4 ms

전체 50만 건 중 pending은 2.5만 건(5%)뿐인데 나머지 95%까지 인덱스에 담을 이유가 없다. WHERE status='pending' 조건을 붙이면 그 행만 인덱스에 들어가 크기가 3408kB → 568kB로 6분의 1이 된다. 작아진 만큼 캐시에 잘 얹히고 갱신 비용도 준다. 조회할 때 쿼리의 WHERE가 인덱스의 조건을 포함(implies)하면 플래너가 이 인덱스를 골라 14ms짜리 seq scan을 0.03ms로 끝낸다. 함정은 인덱스 조건과 쿼리 조건이 어긋나면(예: status='shipped') 이 인덱스를 못 쓴다는 것. 조건 컬럼은 값이 거의 안 바뀌는 상태 플래그(활성/삭제/대기)에 잘 맞는다.

이렇게도 쓴다

soft delete 환경에서 살아있는 행만 인덱싱한다. (deleted_at IS NULL)

CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;

 

부분 인덱스로 부분 UNIQUE 제약을 건다. 취소되지 않은 주문만 유니크. (조합: UNIQUE)

CREATE UNIQUE INDEX uq_active_slug ON products(name) WHERE price IS NOT NULL;

 

여러 상태를 IN으로 묶어 미처리 건만 인덱싱한다.

CREATE INDEX idx_todo ON demo_orders(created_at)
  WHERE status IN ('pending','shipped');

 

부분 + 다중 컬럼을 결합해 특정 세그먼트 조회를 좁힌다. (조합: 복합 인덱스)

CREATE INDEX idx_pending_by_user ON demo_orders(user_id, created_at)
  WHERE status='pending';

 

플래너가 실제로 부분 인덱스를 고르는지 확인한다. (조합: EXPLAIN)

EXPLAIN (COSTS OFF) SELECT * FROM demo_orders
  WHERE status='pending' ORDER BY created_at DESC LIMIT 20;

 

만든 인덱스 크기를 사람이 읽는 단위로 확인한다. (조합: pg_relation_size)

SELECT pg_size_pretty(pg_relation_size('idx_partial_pending'));