운영중인 DB가 느려진 것 같아서 인덱스 리빌드를 결정했다.
대상 선정을 위한 쿼리를 만들어 보았다.
SELECT
PSUI.schemaname
, PSUI.relname AS table_name
, PSUI.indexrelname AS index_name
, PSUI.idx_scan /* 인덱스가 사용된 횟수 */
, PSUI.idx_tup_read /* 인덱스 튜플 수 */
, PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
, pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
, 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
LEFT OUTER JOIN pg_stat_all_indexes PSAI
ON PSAI.relname = PSUI.relname
AND PSAI.indexrelname = PSUI.indexrelname
ORDER BY PSUI.idx_tup_read DESC, PSUI.idx_scan DESC, pg_relation_size(PSAI.indexrelid) DESC
;
------------------------------------------------------------------------------------------------------------------------
-- 거의 사용되니 않는 인덱스 -> 삭제하는 게 나을 수 있겠다
SELECT
PSUI.schemaname
, PSUI.relname AS table_name
, PSUI.indexrelname AS index_name
, PSUI.idx_scan /* 인덱스가 사용된 횟수 */
, PSUI.idx_tup_read /* 인덱스 튜플 수 */
, PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
, pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
, 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
LEFT OUTER JOIN pg_stat_all_indexes PSAI
ON PSAI.relname = PSUI.relname
AND PSAI.indexrelname = PSUI.indexrelname
WHERE PSUI.idx_scan < 100
ORDER BY PSUI.idx_scan DESC
;
------------------------------------------------------------------------------------------------------------------------
-- 크기가 너무 큰 인덱스
SELECT
PSUI.schemaname
, PSUI.relname AS table_name
, PSUI.indexrelname AS index_name
, PSUI.idx_scan /* 인덱스가 사용된 횟수 */
, PSUI.idx_tup_read /* 인덱스 튜플 수 */
, PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
, pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
, 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
LEFT OUTER JOIN pg_stat_all_indexes PSAI
ON PSAI.relname = PSUI.relname
AND PSAI.indexrelname = PSUI.indexrelname
ORDER BY pg_relation_size(PSAI.indexrelid) DESC
LIMIT 50
;
------------------------------------------------------------------------------------------------------------------------
-- 테이블 싸이즈에 비해 크기가 너무 큰 인덱스
SELECT
PSUI.schemaname
, PSUI.relname AS table_name
, PSUI.indexrelname AS index_name
, PSUI.idx_scan /* 인덱스가 사용된 횟수 */
, PSUI.idx_tup_read /* 인덱스 튜플 수 */
, PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
, pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
, pg_size_pretty(pg_relation_size(T.oid)) AS table_size /* 테이블의 크기 */
, ROUND(pg_relation_size(PSAI.indexrelid)::numeric / pg_relation_size(T.oid) * 100, 2) AS INDEX_RATIO
, 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
INNER JOIN pg_class T
ON PSUI.relname = T.relname
LEFT OUTER JOIN pg_stat_all_indexes PSAI
ON PSAI.relname = PSUI.relname
AND PSAI.indexrelname = PSUI.indexrelname
WHERE pg_relation_size(T.oid) > 0
AND pg_relation_size(PSAI.indexrelid) > 0
AND PSUI.idx_scan > 0
AND ROUND(pg_relation_size(PSAI.indexrelid)::numeric / pg_relation_size(T.oid) * 100, 2) > 100
ORDER BY INDEX_RATIO DESC
LIMIT 50
;
------------------------------------------------------------------------------------------------------------------------
-- 오래된 인덱스
SELECT
PSUI.schemaname
, PSUI.relname AS table_name
, PSUI.indexrelname AS index_name
, PSUI.idx_scan /* 인덱스가 사용된 횟수 */
, PSUI.idx_tup_read /* 인덱스 튜플 수 */
, PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
, pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
, pg_stat_get_last_vacuum_time(PSAI.indexrelid) AS last_vacumm
, 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
LEFT OUTER JOIN pg_stat_all_indexes PSAI
ON PSAI.relname = PSUI.relname
AND PSAI.indexrelname = PSUI.indexrelname
WHERE PSUI.idx_scan = 0
OR PSAI.idx_scan = 0
ORDER BY last_vacumm ASC NULLS FIRST
;
'Database' 카테고리의 다른 글
2025년 SQLD 자격증 시험 일정과 준비 방법 (0) | 2025.01.19 |
---|---|
[PostgreSQL] PostgreSQL AccessShareLock 이해 및 해제 가이드 (2) | 2024.11.19 |
[PostgreSQL] 사용중인 function의 정의 내용을 특정 문자열 검색 하는 방법 (0) | 2024.11.15 |
[DB] 특정 기간중 DB CPU 부하가 높은 SQL 찾기 (0) | 2023.04.20 |
[DB] Oracle 쿼리 튜닝을 위한 쿼리 분석 방법 (0) | 2023.04.13 |