[DATABASE/POSTGRESQL] 테이블 인덱스 사용률 조회하기

icodebroker POSTGRESQL 2024-06-18

■ 테이블 인덱스 사용률을 조회하는 방법을 보여준다.

▶ 예제 코드 (SQL)


SELECT
    RELNAME
   ,CASE
        WHEN (SEQ_SCAN + IDX_SCAN) != 0 THEN 100.0 * IDX_SCAN / (SEQ_SCAN + IDX_SCAN)
        ELSE                                 0
    END        AS PERCENT_OF_TIMES_INDEX_USED
   ,N_LIVE_TUP AS ROWS_IN_TABLE
FROM PG_STAT_USER_TABLES
ORDER BY N_LIVE_TUP DESC;

1

2

3

4

5

6

7

8

9

10

11

SELECT

RELNAME

,CASE

WHEN (SEQ_SCAN + IDX_SCAN) != 0 THEN 100.0 * IDX_SCAN / (SEQ_SCAN + IDX_SCAN)

ELSE 0

END AS PERCENT_OF_TIMES_INDEX_USED

,N_LIVE_TUP AS ROWS_IN_TABLE

FROM PG_STAT_USER_TABLES

ORDER BY N_LIVE_TUP DESC;