[sql] PostgreSQL 인덱스 사용 분석

Question 1

Postgres를 분석하고 생성해야하는 누락 된 인덱스와 제거해야하는 사용하지 않는 인덱스를 결정하는 도구 또는 방법이 있습니까? SQLServer 용 “프로파일 러”도구를 사용하여이 작업을 수행 한 경험이 있지만 Postgres에 포함 된 유사한 도구를 알지 못합니다.

Question 2

누락 된 인덱스를 찾으려면 다음과 같이하십시오.

SELECT
  relname                                               AS TableName,
  to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999')                AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'public'
      AND 50 * seq_scan > idx_scan -- more than 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

인덱스 스캔보다 시퀀스 스캔이 더 많은지 확인합니다. 테이블이 작 으면 Postgres가 시퀀스 스캔을 선호하기 때문에 무시됩니다.

위의 쿼리는 누락 된 인덱스를 보여줍니다.

다음 단계는 누락 된 결합 인덱스를 감지하는 것입니다. 쉽지는 않지만 가능하다고 생각합니다. 느린 쿼리를 분석 할 수도 있습니다 … pg_stat_statements 가 도움이 될 수 있다고 들었습니다 …

Question 3

통계를 확인하십시오. pg_stat_user_tables그리고 pg_stat_user_indexes시작할 것입니다.

” 통계 수집기 “를 참조하십시오 .

Question 4

결측 인덱스 접근 방식에 대해 …. 그러나 의사 인덱스 및 기계 판독 가능 EXPLAIN과 같이 향후 릴리스에서이를 더 쉽게 만들 계획이 있습니다.

현재는 EXPLAIN ANALYZE쿼리 성능 이 좋지 않은 다음 수동으로 최적의 경로를 결정해야합니다. pgFouine 과 같은 일부 로그 분석기 는 쿼리를 결정하는 데 도움이 될 수 있습니다.

사용하지 않는 인덱스에 대해서는 다음과 같은 것을 사용하여 식별 할 수 있습니다.

select * from pg_stat_all_indexes where schemaname <> 'pg_catalog';

이것은 읽고, 스캔하고, 가져온 튜플을 식별하는 데 도움이됩니다.

Question 5

PostgreSQL을 분석하기위한 또 다른 새롭고 흥미로운 도구는 PgHero 입니다. 데이터베이스 튜닝에 더 중점을두고 수많은 분석과 제안을합니다.

Question 6

아래 쿼리를 사용하여 인덱스 사용량 및 인덱스 크기를 찾을 수 있습니다.

이 블로그에서 참조를 가져 왔습니다.

SELECT
    pt.tablename AS TableName
    ,t.indexname AS IndexName
    ,to_char(pc.reltuples, '999,999,999,999') AS TotalRows
    ,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
    ,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
    ,to_char(t.idx_scan, '999,999,999,999') AS TotalNumberOfScan
    ,to_char(t.idx_tup_read, '999,999,999,999') AS TotalTupleRead
    ,to_char(t.idx_tup_fetch, '999,999,999,999') AS TotalTupleFetched
FROM pg_tables AS pt
LEFT OUTER JOIN pg_class AS pc
    ON pt.tablename=pc.relname
LEFT OUTER JOIN
(
    SELECT
        pc.relname AS TableName
        ,pc2.relname AS IndexName
        ,psai.idx_scan
        ,psai.idx_tup_read
        ,psai.idx_tup_fetch
        ,psai.indexrelname
    FROM pg_index AS pi
    JOIN pg_class AS pc
        ON pc.oid = pi.indrelid
    JOIN pg_class AS pc2
        ON pc2.oid = pi.indexrelid
    JOIN pg_stat_all_indexes AS psai
        ON pi.indexrelid = psai.indexrelid
)AS T
    ON pt.tablename = T.TableName
WHERE pt.schemaname='public'
ORDER BY 1;

Question 7

PostgreSQL 위키 에서 사용하지 않는 색인을 찾는 데 도움이되는 스크립트에 대한 여러 링크가 있습니다 . 기본 기술은 인덱스가 쿼리에 응답하는 데 사용 된 횟수가 0이거나 최소한 매우 낮은 pg_stat_user_indexes항목을보고 찾는 것 idx_scan입니다. 응용 프로그램이 변경되었고 이전에 사용 된 인덱스가 현재가 아닐 경우 가끔 pg_stat_reset()모든 통계를 0으로 되 돌린 다음 새 데이터를 수집하기 위해 실행 해야합니다. 모든 것에 대한 현재 값을 저장하고 대신 델타를 계산하여 알아낼 수 있습니다.

누락 된 인덱스를 제안 할 수있는 좋은 도구가 아직 없습니다. 한 가지 접근 방식은 실행중인 쿼리를 기록하고 pgFouine 또는 pqa와 같은 쿼리 로그 분석 도구를 사용하여 실행하는 데 오랜 시간이 걸리는 쿼리를 분석하는 것입니다. 자세한 내용은 ” 어려운 쿼리 로깅 “을 참조하십시오.

다른 접근 방식은 pg_stat_user_tables대량의 순차 스캔이있는 테이블을보고 찾는 것입니다 seq_tup_fetch. 인덱스를 사용하면 idx_fetch_tup대신 카운트가 증가합니다. 이는 테이블에 대한 쿼리에 응답 할 수있을만큼 충분히 인덱싱되지 않은 테이블을 알 수 있습니다.

실제로 인덱싱해야 할 열을 파악하고 있습니까? 일반적으로 다시 쿼리 로그 분석 항목으로 이어집니다.

Question 8

postgres 콘솔에서 다음 쿼리를 사용하여 찾을 수 있습니다.

use db_name
select * from pg_stat_user_indexes;
select * from pg_statio_user_indexes;

자세한 내용은 https://www.postgresql.org/docs/current/monitoring-stats.html

[sql] PostgreSQL 인덱스 사용 분석

답변

답변

답변

답변

답변

답변

답변