안 쓰는 인덱스 찾는 법 — pg_stat_user_indexes 로 지워도 되는지 판단하기

조회가 느릴 때마다 인덱스를 하나씩 추가해 왔어요. 어느새 테이블 하나에 인덱스가 열두 개예요.

조회는 눈에 띄게 빨라지지 않았는데 INSERT 가 확실히 느려졌어요. 디스크도 계속 늘고요. 인덱스 크기를 재 보니 테이블 본체보다 인덱스 합계가 더 큽니다.

이건 어느 날 터지는 장애가 아니라 서서히 무거워지는 종류예요. 그래서 원인으로 지목되는 일이 거의 없어요. "트래픽이 늘어서 그런가 보다"로 넘어가고, 서버를 늘리는 걸로 대응하게 됩니다.

인덱스는 추가할 때만 논의되고 제거는 아무도 안 하는 자산이에요. 들어오기만 하고 나가지 않으니 쌓일 수밖에 없어요.

왜 그런가

인덱스는 읽기를 사고 쓰기를 파는 거래예요. 이 거래에 값이 붙어 있다는 걸 자주 잊어요.

행 하나를 INSERT 하면 그 테이블의 모든 인덱스가 함께 갱신돼요. 인덱스가 열두 개면 쓰기 한 번에 열세 번의 구조 변경이 일어나요. UPDATE 는 더 미묘해요. 바뀐 컬럼을 포함하는 인덱스만 갱신되는 게 원칙이지만, 새 버전이 다른 페이지에 쓰이면 모든 인덱스가 새 위치를 가리키도록 갱신돼야 해요.

메모리도 나눠 써요. 인덱스는 테이블과 같은 버퍼 풀 안에 있어요. 안 쓰는 인덱스가 캐시를 차지하면 정작 필요한 데이터가 밀려나요. 디스크가 넉넉해도 이 비용은 남습니다.

그리고 옵티마이저의 일도 늘어요. 후보 인덱스가 많을수록 계획을 고르는 시간이 길어지고, 통계가 어긋나면 잘못된 인덱스를 고를 확률도 올라가요.

그리고 청소 비용도 커져요. VACUUM 은 테이블만 훑는 게 아니라 인덱스도 정리해야 하거든요. 인덱스가 많을수록 청소 한 번이 오래 걸리고, 그만큼 뒤처지기 쉬워져요.

정리하면 안 쓰는 인덱스는 이득이 0 이고 비용은 넷이에요. 쓰기 갱신, 캐시 점유, 계획 수립, 청소 시간. 세금이라는 표현이 정확한 이유예요.

코드로 보면

한 번도 안 쓰인 인덱스부터:

코드

SELECT schemaname, relname, indexrelname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
✕ WHERE idx_scan = 0
 ORDER BY pg_relation_size(indexrelid) DESC;

· 지우기 전에 반드시 통계 기간을 확인한다
✓ SELECT datname, stats_reset FROM pg_stat_database
✓ WHERE datname = current_database();
✕ stats_reset 이 어제면 이 목록은 의미가 없다

· 바로 지우지 말고 먼저 숨긴다
✓ UPDATE pg_index SET indisvalid = false   -- PG: 신중히
✓ ALTER TABLE t ALTER INDEX idx INVISIBLE; -- MySQL 8.0

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

주의할 점이 있어요. 한 번의 스냅샷만 보고 지우면 위험해요.

월말 정산, 분기 리포트, 연말 배치처럼 가끔만 도는 쿼리가 쓰는 인덱스는 평소에 idx_scan 이 0 으로 보여요. 통계를 리셋한 지 얼마 안 됐어도 마찬가지고요. 그래서 pg_stat_database 의 stats_reset 을 먼저 확인해서 통계가 충분히 오래 쌓였는지 봐야 해요. 최소 한 달, 정산 주기가 있는 서비스면 한 분기가 안전해요.

PostgreSQL 16 부터는 pg_stat_user_indexes 에 last_idx_scan 이 생겨서 마지막 사용 시각까지 볼 수 있어요. 횟수만 보는 것보다 훨씬 판단이 쉬워요.

복제본도 봐야 해요. 통계는 인스턴스별로 쌓여요. 주 서버에서 idx_scan 이 0 이어도 읽기 복제본에서 그 인덱스로 리포트를 돌고 있을 수 있어요. 복제본에서 따로 확인해야 합니다.

중복 인덱스는 더 확실한 후보예요. 복합 인덱스 (a, b) 가 있으면 (a) 단독 인덱스는 대개 불필요해요. 복합 인덱스의 왼쪽 접두사가 단독 인덱스 역할을 하니까요. 다만 (a) 가 더 작아서 인덱스 온리 스캔에 유리한 경우가 있어 항상은 아니에요. 크기 차이가 크지 않다면 지우는 게 맞습니다.

유니크 제약을 위한 인덱스는 idx_scan 이 0 이어도 지우면 안 돼요. 조회에 안 쓰여도 무결성을 지키는 일을 하고 있어요. 외래 키가 참조하는 인덱스도 마찬가지고요.

처방

최소 한 달, 가능하면 한 분기 이상의 통계를 보고 판단하세요. stats_reset 을 먼저 확인하는 게 첫 단계예요.
지우기 전에 관찰 단계를 두세요. MySQL 8.0 은 INVISIBLE 인덱스가 있어서 옵티마이저에게만 숨길 수 있어요. 유지 비용은 계속 들지만 되돌리기가 즉시라 안전해요. PostgreSQL 에는 대응하는 기능이 없어서, 이름을 바꿔 두고 애플리케이션 오류가 없는지 보는 방법을 씁니다.
중복부터 걷어내세요. (a, b) 가 있는데 (a) 가 따로 있는 경우, 같은 컬럼 집합에 순서만 다른 인덱스가 여럿인 경우. 이건 통계 없이도 판단할 수 있어요.
유니크 제약과 외래 키 인덱스는 목록에서 먼저 제외하세요. 조회에 안 쓰여도 하는 일이 있어요.
복제본에서도 확인하세요. 통계는 인스턴스별로 쌓입니다.
새 인덱스를 추가할 때 기존 것과 겹치는지 먼저 보세요. 추가는 쉽고 제거는 어려우니, 들어올 때 거르는 게 비용이 제일 쌉니다.

지금 확인해 보세요

지워도 되는지 판단하는 재료:

코드

· 스캔 횟수 + 스캔당 읽은 행 (효율까지 본다)
SELECT indexrelname, idx_scan,
       idx_tup_read,
       CASE WHEN idx_scan > 0
            THEN idx_tup_read / idx_scan END AS rows_per_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
 ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;
✕ rows_per_scan 이 크면 자주 쓰여도 효율이 나쁘다

· 인덱스 합계가 테이블보다 큰가
SELECT relname,
       pg_size_pretty(pg_table_size(relid)) AS table,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes
  FROM pg_stat_user_tables
 ORDER BY pg_indexes_size(relid) DESC LIMIT 10;

· 유니크·외래키용은 후보에서 제외한다
✓ SELECT conname, contype FROM pg_constraint
✓ WHERE conindid = 'idx_a'::regclass;

핵심 한 줄인덱스는 읽기를 사고 쓰기를 판다. 안 쓰면 지불만 남는다.

왜 그런가

코드로 보면

실전에서는

처방

지금 확인해 보세요

이어서 읽기