PostgreSQL 인덱스 사용 분석

PostgreSQL 인덱스 사용 분석

Postgres를 분석하고 어떤 누락된 인덱스를 생성해야 하는지, 어떤 사용하지 않는 인덱스를 제거해야 하는지 결정하는 도구나 방법이 있습니까?SQL Server용 "profiler" 도구로 이 작업을 수행한 적은 있지만 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 than 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

이렇게 하면 인덱스 검색보다 시퀀스 검색이 더 많은지 확인합니다.테이블이 작으면 Postgres가 시퀀스 스캔을 선호하는 것처럼 보이므로 무시됩니다.

위의 쿼리는 누락된 인덱스를 나타냅니다.

다음 단계는 누락된 결합 인덱스를 탐지하는 것입니다.쉽지는 않지만 할 수 있는 일인 것 같아요.느린 쿼리를 분석하는 중...pg_stat_stat_stat_stat_stats가 도움이 될 수 있다고 들었습니다...

통계를 확인합니다. pg_stat_user_tables그리고.pg_stat_user_indexes처음부터 시작해야 할 것들입니다.

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

누락된 인덱스 확인 접근 방식...아니요. 하지만 의사 인덱스나 기계로 읽을 수 있는 EXPLE과 같은 향후 릴리스에서 이것을 더 쉽게 만들 계획이 있습니다.

현재는 다음과 같은 작업이 필요합니다.EXPLAIN ANALYZE쿼리를 제대로 수행하지 못한 다음 수동으로 최적의 경로를 결정합니다.pgFouine과 같은 일부 로그 분석기는 쿼리를 확인하는 데 도움이 될 수 있습니다.

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

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

이렇게 하면 판독, 스캔, 페치된 튜플을 식별하는 데 도움이 됩니다.

Postgre 분석을 위한 또 다른 새롭고 흥미로운 도구SQL은 PgHero입니다.데이터베이스 조정에 더 중점을 두고 다양한 분석과 제안을 합니다.

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

참조는 이 블로그에서 가져온 것입니다.

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;

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 에서 확인하시기 바랍니다.

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

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

다른 접근 방식은 다음과 같습니다.pg_stat_user_tables그리고 그들에 대한 많은 수의 순차적 스캔이 있는 테이블을 찾습니다.seq_tup_fetch를 할 때는 인덱스를 사용합니다.idx_fetch_tup대신 카운트가 증가합니다.그러면 테이블에 대한 쿼리에 응답할 수 있을 정도로 인덱스가 제대로 작성되지 않은 경우에 대한 정보를 얻을 수 있습니다.

실제로 어떤 열에서 인덱스를 작성해야 하는지 알고 계십니까?일반적으로 다시 쿼리 로그 분석 작업으로 이어집니다.

PoWA는 Postgre를 위한 흥미로운 도구인 것 같습니다.SQL 9.4+.통계를 수집하고 시각화하며 인덱스를 제안합니다.확장자를 사용합니다.

PoWA는 성능 통계를 수집하고 실시간 차트와 그래프를 제공하여 Postgre를 모니터링하고 조정하는 PostgreSQL Workload Analyzer입니다.SQL 서버.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 

언급URL : https://stackoverflow.com/questions/3318727/postgresql-index-usage-analysis