PostgreSQL 인덱스 사용 분석

PostgreSQL 인덱스 사용 분석

---

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

---

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

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 then 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

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

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

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

---

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

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

---

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

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

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

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

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

---

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

---

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

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

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

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

---

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

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

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;

---

PoWA 는 PostgreSQL 9.4+를위한 흥미로운 도구처럼 보입니다. 통계를 수집하고 시각화하고 인덱스를 제안합니다. pg_stat_statements확장을 사용합니다 .

PoWA는 성능 통계를 수집하고 실시간 차트 및 그래프를 제공하여 PostgreSQL 서버를 모니터링하고 조정하는 데 도움이되는 PostgreSQL 워크로드 분석기입니다. Oracle AWR 또는 SQL Server MDW와 유사합니다.

---

CREATE EXTENSION pgstattuple; 
CREATE TABLE test(t INT); 
INSERT INTO test VALUES(generate_series(1, 100000)); 
SELECT * FROM pgstatindex('test_idx'); 

version            | 2 
tree_level         | 2 
index_size         | 105332736 
root_block_no      | 412 
internal_pages     | 40 
leaf_pages         | 12804 
empty_pages        | 0 
deleted_pages      | 13 
avg_leaf_density   | 9.84 
leaf_fragmentation | 21.42 

---

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

---

이것은 도움이 될 것입니다 : 실용적인 쿼리 분석

참고 URL : https://stackoverflow.com/questions/3318727/postgresql-index-usage-analysis