[DATABASE/POSTGRESQL] 미사용 인덱스 조회하기

■ 미사용 인덱스를 조회하는 방법을 보여준다.

 

▶ 예제 코드 (SQL)

SELECT
    RELNAME      AS TABLE_NAME
   ,INDEXRELNAME AS INDEX_NAME
   ,IDX_SCAN
   ,IDX_TUP_READ
   ,IDX_TUP_FETCH
   ,PG_SIZE_PRETTY(PG_RELATION_SIZE(INDEXRELNAME::REGCLASS))
FROM  PG_STAT_ALL_INDEXES
WHERE SCHEMANAME    = 'public'
AND   IDX_SCAN      = 0
AND   IDX_TUP_READ  = 0
AND   IDX_TUP_FETCH = 0
ORDER BY PG_RELATION_SIZE(INDEXRELNAME::REGCLASS) DESC;