[PostgreSQL] 복합 인덱스 컬럼 순서 — (user_id, status)가 status 단독 조회엔 안 먹히는 이유

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

부제: (user_id, status) 복합 인덱스를 걸었는데, WHERE status=... 만 쓰는 쿼리는 여전히 Seq Scan일 때

CREATE INDEX idx_m5_us ON m5_orders(user_id, status);

-- Q1  선두 컬럼만:            인덱스 O
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM m5_orders WHERE user_id = 12345;
-- Q2  선두+두번째:            인덱스 O (둘 다 조건으로)
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM m5_orders WHERE user_id = 12345 AND status = 'done';
-- Q3  두번째 컬럼만:          인덱스 X → Seq Scan
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM m5_orders WHERE status = 'pending';

-- Q1  user_id 단독: Bitmap Index Scan, Index Cond: (user_id = 12345)      0.167 ms
-- Q2  둘 다:        Index Cond: ((user_id = 12345) AND (status='done'))   0.024 ms
-- Q3  status 단독:
 Seq Scan on m5_orders (actual time=0.014..80.363 rows=200000 loops=1)
   Filter: (status = 'pending'::text)
   Rows Removed by Filter: 1800000
 Execution Time: 85.518 ms

복합 인덱스는 사전(dictionary) 정렬과 같다. (user_id, status)는 먼저 user_id로 줄을 세우고, 그 안에서 status로 다시 세운다. 그래서 선두 컬럼(user_id)이 조건에 있어야 사전을 펼칠 지점을 찾는다. Q1은 user_id만으로도 인덱스를 타고(선두 O), Q2는 user_id로 좁힌 뒤 그 안에서 status까지 좁힌다(둘 다 Index Cond). 반면 Q3는 선두 user_id 조건이 없다 — 성(姓)을 모른 채 이름만으로 전화번호부를 찾는 격이라 인덱스를 펼칠 수 없어 Seq Scan으로 200만 행을 통째로 훑는다(85 ms). 규칙: 자주 단독으로 검색되는 컬럼을 선두에 둔다. 등호 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 두는 것도 같은 이유다(범위가 앞에 오면 그 뒤 컬럼의 정렬이 깨져 못 좁힌다).

이렇게도 쓴다

status를 단독으로도 검색해야 한다면 순서를 뒤집은 인덱스를 따로 둔다. (조합: 반대 순서)

CREATE INDEX idx_m5_su ON m5_orders(status, user_id);

 

등호는 앞, 범위는 뒤 — created_at 범위 조회가 잦다면 이 순서로. (원칙: equality→range)

CREATE INDEX idx_m5_uc ON m5_orders(user_id, created_at);
-- WHERE user_id=1 AND created_at >= '2024-06-01' 에 최적

 

선두 컬럼만 쓸 거면 두 번째 컬럼은 INCLUDE로 얹어 인덱스만 읽게 한다. (조합: covering)

CREATE INDEX idx_m5_u_inc ON m5_orders(user_id) INCLUDE (amount);

 

정말 인덱스를 탔는지, 어느 조건이 Index Cond로 갔는지 확인한다. (조합: EXPLAIN)

EXPLAIN (COSTS OFF) SELECT * FROM m5_orders WHERE user_id=1 AND status='done';

 

컬럼별 선택도(고유값 수)를 보고 어떤 컬럼을 선두로 둘지 정한다. (조합: pg_stats)

SELECT attname, n_distinct FROM pg_stats WHERE tablename='m5_orders';

 

중복이 된 (user_id) 단일 인덱스는 복합 인덱스가 대신하므로 지운다. (조합: 중복 정리)

DROP INDEX idx_orders_user;  -- (user_id,status)가 선두로 커버