[DB] PK가 있는데 왜 쿼리가 느릴까 - 복합인덱스 선두컬럼

인덱스 설계는 단순히 "어떤 컬럼을 포함시킬까"의 문제가 아니다. 어떤 순서로 정렬할까가 더 중요하다. 복합 PK가 잡혀있다고 해서 그 안에 들어있는 컬럼으로 조건을 걸기만 하면 빨라질 거라고 생각했는데, 그게 아니었다.

이번 글에서는 PK가 있음에도 쿼리가 풀스캔에 가깝게 동작했던 사례를 정리하고, 복합 B-tree 인덱스의 선두 컬럼(leading column) 원리를 짚어본다.

---

0. 문제 상황

리포팅 화면 하나가 느렸다. 예시로 지점별·연월별 매출 통계를 보여주는 페이지인데, 한 지점 데이터가 5천 건 수준이 되면 응답이 200ms 이상으로 늘어졌다.

테이블 구조는 대략 이렇다(예시).

CREATE TABLE analytics.sales_monthly_stats (
    sales_id    bigint      NOT NULL,
    year_mm     varchar(6)  NOT NULL,   -- '202403' 형식
    branch_cd   varchar(10) NOT NULL,
    amount      numeric(18,2),
    -- ... 컬럼 다수
    CONSTRAINT sales_monthly_stats_pk
        PRIMARY KEY (sales_id, year_mm, branch_cd)
);

 

가장 많이 호출되는 쿼리는 다음과 같다.

SELECT count(*)
FROM analytics.sales_monthly_stats
WHERE branch_cd = 'B001'
  AND year_mm   = '202403';

 

branch_cd도 year_mm도 PK 안에 포함되어 있다. 그러니 인덱스를 잘 타고 있을 거라고 처음엔 그렇게 생각했다.

---

1. 1차 진단 - "PK가 있으니까 빠르겠지"

처음에는 애플리케이션 쪽을 의심했다. JOIN을 줄이고, N+1을 풀고, 클라이언트 렌더링 코드도 손봤다. 그런데도 쿼리 단독으로 200ms가 나왔다.

쿼리를 직접 의심해봐도 WHERE 조건의 두 컬럼이 모두 PK 안에 있으니 풀스캔은 아닐 것 같았다. 확인 차 EXPLAIN을 떠 봤다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM analytics.sales_monthly_stats
WHERE branch_cd = 'B001'
  AND year_mm   = '202403';

Aggregate  (cost=64614.63..64614.64 rows=1 width=8)
           (actual time=217.564..217.566 rows=1 loops=1)
  Buffers: shared hit=19054
  ->  Index Only Scan using sales_monthly_stats_pk on sales_monthly_stats
        (cost=0.43..64613.38 rows=497 width=0)
        (actual time=176.794..217.527 rows=438 loops=1)
        Index Cond: ((year_mm = '202403'::text) AND (branch_cd = 'B001'::text))
        Heap Fetches: 438
        Buffers: shared hit=19054
Planning Time: 0.248 ms
Execution Time: 217.621 ms

Index Only Scan using sales_monthly_stats_pk 라는 줄이 보인다. 인덱스를 타긴 탔다. 그런데 몇 가지 숫자가 이상했다.

438행을 돌려주는데 Buffers가 19,054
예상 비용도 cost=64613

 

PostgreSQL의 한 buffer는 8KB이다. 19,054 × 8KB ≈ 149MB. 결국 438행을 위해 약 149MB 분량의 인덱스 페이지를 읽었다는 뜻이다. 한 행당 약 43개 buffer이다. 결과 행수에 비해 압도적으로 많다. 인덱스를 타고는 있는데, 정상적으로 활용되고 있지는 않다는 신호이다.

---

2. 2차 분석 - 왜 PK가 안 통하는가

여기서 잠시 막혔다. "인덱스를 탔다고 나오는데 왜 풀스캔처럼 동작하지?"

핵심은 B-tree 복합 인덱스가 선두 컬럼부터 정렬되어 있다는 점이다. 알고 있는 사실이지만, "PK 안에 컬럼이 있으면 그래도 활용된다" 라는 직관과 충돌하면서 잠깐 헷갈렸다.

 

① 복합 인덱스는 다단 정렬된 책

복합 인덱스 (성, 이름, 중간이름) 이라면 책은 아래 순서로 정렬되어 있다.

 

페이지	성	이름	중간이름
1	강	사람	-
2	강	휴먼	-
3	김	피플	-
4	김	동웅	-
5	김	동웅	영
...	...	...	...

• "김 동웅" 검색 → 성 → 이름 순으로 따라가면 한 번에 도달한다.
• "동웅" (이름)만으로 검색 → 책 전체를 1페이지부터 끝까지 훑어야 한다.
• "동웅 영" (이름+중간이름)만으로 검색 → 마찬가지로 풀스캔이다.

선두 컬럼이 WHERE 절에 없으면 인덱스의 "정렬 이점"을 쓸 수 없다. 나머지 컬럼이 인덱스에 포함되어 있더라도 결국 책 전체를 펼쳐놓고 필터링하는 것과 같다.

 

② 우리 쿼리에 대입

PK 선두: sales_id   ← WHERE 절에 없음
WHERE  : branch_cd, year_mm   ← PK의 2,3번째 컬럼

sales_id 조건이 없으니 옵티마이저는 인덱스 전체를 훑으면서 year_mm/branch_cd로 필터링만 한다. "Index Only Scan" 이라는 이름이 붙긴 하지만, 실상은 인덱스 풀스캔 + 필터이다. 이것때문에 19,054 buffers가 발생한 것이었따.

 

---

3. 해결 방안

선두 컬럼이 branch_cd인 보조 인덱스를 추가한다.

CREATE INDEX ix_sales_monthly_stats__branch_yearmm
  ON analytics.sales_monthly_stats (branch_cd, year_mm);

ANALYZE analytics.sales_monthly_stats;   -- 옵티마이저 통계 갱신

운영 환경이라면 락을 줄이기 위해 다음과 같이 작성한다.

CREATE INDEX CONCURRENTLY ix_sales_monthly_stats__branch_yearmm
  ON analytics.sales_monthly_stats (branch_cd, year_mm);

CONCURRENTLY 옵션은 테이블 쓰기를 막지 않고 인덱스를 생성한다. 대신 일반 생성보다 시간이 더 걸리고, 중간에 실패하면 INVALID 상태의 인덱스가 남을 수 있어 후처리가 필요하다.

 

① Before / After 비교

다시 EXPLAIN을 떠보면 다음과 같다.

Index Scan using ix_sales_monthly_stats__branch_yearmm on sales_monthly_stats
  (cost=0.43..2.65 rows=1 width=225)
  Index Cond: (((branch_cd)::text = 'B001'::text)
           AND ((year_mm)::text = '202403'::text))
Execution Time: 1.4 ms

Index Only Scan using sales_monthly_stats_pk (cost 64,613) 에서 Index Scan using ix_sales_monthly_stats__branch_yearmm (cost 2.65) 로 바뀌었다. 옵티마이저가 새 인덱스를 골랐다는 뜻이다.

지표 Before (PK만) After (신규 인덱스)

Plan	Index Only Scan using PK	Index Scan using 신규 인덱스
Cost	64,613	2.65
Buffers	19,054	23
Execution Time	217 ms	1.4 ms

 

응답시간은 약 150배, 메모리 IO는 800배 가까이 줄었다. cost 추정치도 약 24,000배 차이가 난다.

 

② PK는 그대로 두어도 되는가

된다. 둘은 역할이 다르다.

인덱스	선두컬럼	쓰임새
PK (sales_id, year_mm, branch_cd)	sales_id	WHERE sales_id = ? 단건 조회/UPDATE/DELETE
신규 (branch_cd, year_mm)	branch_cd	WHERE branch_cd = ? AND year_mm = ? 목록 조회

 

PK를 떼는 게 아니라 추가만 하므로 기존 동작에는 영향이 없다. 대신 쓰기 비용은 약간 늘어난다. INSERT/UPDATE 시 갱신해야 할 인덱스가 하나 더 생기기 때문이다. 2컬럼짜리 일반 B-tree 인덱스라 비용은 미미하지만, 쓰기가 매우 잦은 테이블이라면 측정해보고 결정하는 것이 안전하다.

 

---

4. 문제 정리

① "PK가 있으니까 빠를 것"이라는 가정으로 시간을 낭비했다

PK는 단순히 "있다/없다" 의 문제가 아니라 선두 컬럼이 WHERE 조건과 맞느냐 의 문제이다. PK 안에 들어있는 컬럼이라도 그것이 인덱스의 첫 번째 컬럼이 아니라면 정렬 이점을 활용할 수 없다.

 

② EXPLAIN에 Index Only Scan 이 떴다고 안심했다

계획 종류만 보지 말고 Buffers, Heap Fetches, 결과 행수를 같이 봐야 한다. 결과 행수에 비해 Buffers가 비정상적으로 크면 인덱스가 정렬 이점 없이 쓰이고 있다는 신호이다. 행당 10 buffer 이상 읽고 있다면 한 번 의심해볼 만 하다.

 

③ 컬럼 순서를 깊이 생각하지 않았다

복합 인덱스를 새로 만들 때 "어떤 컬럼을 포함시킬까" 만큼 "어떤 순서로 정렬할까" 가 중요하다. 기준은 단순하다. 자주 쓰는 WHERE 조건의 컬럼을 선두로 둔다. 등호 조건과 범위 조건이 섞여있다면 등호 조건을 먼저 둔다.

 

---

5. 결론 정리

복합 인덱스는 정렬된 책이다. 선두 컬럼이 WHERE 절에 없으면 책 전체를 펼친다. "Index Only Scan" 이라는 단어에 안심하지 말고, Buffers ÷ 결과 행수를 한번 계산해보면 인덱스가 제대로 동작하는지 빠르게 판단할 수 있다.

 

해결책 자체는 단순하다. WHERE 조건에 맞는 컬럼 순서로 보조 인덱스를 추가하면 된다. PK와 충돌하지도 않는다. PK는 단건 식별, 보조 인덱스는 목록 조회로 역할이 다르다. 작은 테이블에서는 잘 드러나지 않다가 데이터가 쌓이면 어느 순간 터지는 종류의 문제이기 때문에, 한 번 겪고 나면 다음부터는 의심 1순위에 둘 수 있다.

 

결국 인덱스를 늘리면 읽기는 빨라지지만 쓰기 비용은 늘어난다. 모든 조합에 인덱스를 거는 것이 정답은 아니고, 실제 쿼리 패턴과 테이블의 읽기/쓰기 비율을 고려해 필요한 인덱스만 선별해 추가하는 것이 핵심이다.