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

icodebroker POSTGRESQL 2024-06-18

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

▶ 예제 코드 (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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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;