[PostgreSQL] 미사용·중복 인덱스 색출 — pg_stat_user_indexes로 안 쓰는 57MB 걷어내기

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

부제: 인덱스가 쌓이기만 하고 뭘 지워도 되는지 모를 때 — 한 번도 안 탄 인덱스와 겹치는 인덱스를 찾아낸다

-- 테이블별 인덱스 사용 횟수(idx_scan)와 크기를 나란히
SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'm5_orders'
ORDER BY idx_scan;

     indexrelname     | idx_scan | size
----------------------+----------+-------
 idx_m5_amount_unused |        0 | 43 MB   ← 한 번도 안 탐: 삭제 후보
 idx_m5_uid_dup       |        0 | 14 MB   ← (user_id) 단독: 아래 복합과 중복
 idx_m5_us            |     8016 | 14 MB   ← (user_id,status): 얘가 다 처리

pg_stat_user_indexes.idx_scan은 그 인덱스가 누적 몇 번 스캔에 쓰였는지 카운트다. idx_scan = 0이면 통계 수집 이후 한 번도 안 탄 인덱스 — 읽기엔 도움 안 주면서 INSERT/UPDATE마다 갱신 비용만 물리고 디스크(여기선 43MB)를 잡아먹는 순수 부채다. 두 번째 idx_m5_uid_dup은 더 교묘하다: (user_id) 단독 인덱스인데, 복합 인덱스 idx_m5_us(user_id, status)가 user_id를 선두로 가지므로 user_id 조회를 이미 다 처리한다(그래서 idx_scan=0). 이런 선두 컬럼이 겹치는 중복 인덱스는 좁은 쪽을 지워도 된다. 단, 판단 전 두 가지를 확인한다 — (1) 통계가 리셋된 뒤 충분한 기간(월간 배치까지 한 사이클) 관찰했는가, (2) 그 인덱스가 UNIQUE 제약이나 FK를 떠받치고 있진 않은가. 이 둘만 통과하면 DROP INDEX CONCURRENTLY로 안전하게 걷어낸다.

이렇게도 쓴다

DB 전체에서 안 쓰는 인덱스를 낭비 크기 순으로 뽑는다. (조합: 전역 스캔)

SELECT relname, indexrelname, 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;

 

선두 컬럼이 겹치는 중복 인덱스를 정의로 찾아낸다. (조합: pg_indexes)

SELECT tablename, indexname, indexdef FROM pg_indexes
WHERE tablename='m5_orders' ORDER BY indexdef;

 

관찰을 새로 시작하려면 통계 카운터를 리셋한다. (조합: pg_stat_reset)

SELECT pg_stat_reset();   -- 이 시점부터 idx_scan 다시 0에서 누적

 

지울 땐 테이블 잠금 없이 동시 삭제한다. (조합: DROP INDEX CONCURRENTLY)

DROP INDEX CONCURRENTLY idx_m5_amount_unused;

 

UNIQUE·FK를 떠받치는 인덱스인지 먼저 확인한다(그러면 못 지운다). (조합: 제약 확인)

SELECT conname, contype FROM pg_constraint WHERE conindid = 'idx_m5_us'::regclass;

 

인덱스별 읽기 vs 쓰기 부담을 함께 봐 유지 가치를 판단한다. (조합: idx_tup_read)

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes WHERE relname='m5_orders';