PostgreSQL: 사용하지 않는 인덱스(INDEX) 찾기 그리고 성능 향상

인덱스(Index) 혹은 색인은 테이블 설계시 많이 고민하는 부분이고 성능 향상에 많은 도움을 주고 있지만 프로그램의 설계 변경 혹은 기능 개선등 여러 상황에 따라 사용하지 않는 인덱스가 발생하게 됩니다.
여러 상황 때문에 현재는 사용하지 않는 인덱스를 찾아보기 위한 간략한 정리 내용입니다.

인덱스 구조 이해를 위한 예시

인덱스는 지정한 컬럼에 대한 매핑 정보를 가지고 있습니다.

사용하지 않는 인덱스를 찾아봐야되는 이유

보통 쿼리 튜닝을 위해 운영중인 데이터베이스에서 EXPLAIN 명령어를 많이 사용하고 필요시 인덱스 추가는 생기지만 삭제는 거의 없을거라 생각됩니다.
만약 그렇다면 극단적으로 생각했을때 오래된 프로그램 일 수록 추가된 인덱스는 많지만 삭제된 인덱스가 없다는건 불필요한 인덱스도 많을수 있다는 겁니다.
그 불필요한 인덱스는 데이터베이스를 느리게하고 Vacuum시 불필요한 서버 자원을 낭비하게됩니다.

그래서 성능 향상과 쾌적한 데이터베이스 운영을 위해 인덱스 추가만큼 모니터링도 중요합니다.

"pg_stat_user_indexes" 뷰 설명

PostgreSQL은 다양한 통계 정보를 가지고 있는데, 인덱스 통계 정보를 이용하여 사용하지 않는 인덱스를 찾아 보겠습니다.
통계 정보는 사용자 통계(pg_stat_user_indexes)와 시스템 통계(pg_stat_sys_indexes)가 각각 존재하며 두 정보가 합쳐진 pg_stat_all_indexes 뷰가 있으니 필요한 정보를 사용하시면 됩니다.
지금은 프로그램 상에서 혹은 직접 실행한 쿼리에 의한 사용 여부를 알고 싶은거기 때문에 사용자 통계(pg_stat_user_indexes)를 이용하겠습니다.

열	유형	설명
relid	oid	테이블 OID
indexrelid	oid	인덱스 OID
schemaname	name	스키마 이름
relname	name	테이블 이름
indexrelname	name	인덱스 이름
idx_scan	bigint	인덱스 스캔 실행 횟수
idx_tup_read	bigint	인덱스 스캔에 반환 된 인덱스 항목 개수
idx_tup_fetch	bigint	이 인덱스를 사용하여 인덱스 스캔에서 추출된 유효한 테이블 행 수

사용하지 않은 인덱스 확인

SELECT
    schemaname AS schema_name,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

주의 사항

PostgreSQL 8.2 이하 버전에서는 기본 값이 통계 정보 사용 안함입니다. 사용하기 위해서는 stats_row_level를 변경해야 합니다.
PostgreSQL 8.3 이상 버전에서는 기본 값이 통계 정보 사용입니다. 매개 변수 이름이 track_counts로 변경되었습니다.
통계 정보라는 점에서 즉시 반영되지 않는 정보가 있습니다. pg_stat_reset() 또는 ANALYZE 명령어로 갱신할 수 있습니다.
운영중인 서비스라면 통계 정보를 바탕으로 지속적인 모니터링과 정말 미 사용중인지 추가적인 확인 작업을 가져야합니다. 해당 인덱스가 1달에 한번 사용하는 작업에서 사용 될수도 있으며 통계 정보에 누락된 정보가 있을수 있다는 의문을 무시하면 안됩니다.

개발자스럽다