[PostgreSQL] 안쓰는 인덱스 정리하기

PostgreSQL은 인덱스 사용에 대한 통계 정보를 알아서 관리해준다.
그래서 안쓰는 인덱스를 찾는게 꽤 편리한 편이다.

다음 쿼리를 사용하면, 사용 빈도가 낮은 인덱스를 찾을 수 있다.

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scan_count,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM
    pg_stat_user_indexes
where 1=1
 	and relname = '테이블명'
ORDER BY
    idx_scan ASC, pg_relation_size(indexrelid) DESC;

idx_scan(scan_count)가 바로 인덱스가 몇번 사용되었는지를 나타내는 지표다.
0번이면 가차없이 날려버리면 되는거지만, 사용된 적이 있지만 실질적으로 미사용인지를 찾는건 좀 어렵다.

이 통계 데이터는 그냥 단순히 사용 횟수만 기록하고, 최근 한달동안 사용된 적이 있는지를 판결할 수 있는 데이터는 없다.
그래서 최근 사용량 같은걸 얻고 싶다면 따로 테이블 만들고 diff를 히스토리로 기록하게끔 작성을 해줘야 한다.

그리고 전체 인덱스에서 필요없는걸 찾으려면 이렇게 하면 된다.

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0
    AND indexrelname NOT LIKE 'pg_%'  -- PostgreSQL 시스템 인덱스 제외
ORDER BY
    pg_relation_size(indexrelid) DESC;