PostgreSQL索引使用分析

Question

PostgreSQL索引使用分析

sqldatabase-designpostgresql

114

有没有一种工具或方法可以分析Postgres，并确定应创建哪些缺失索引，以及应删除哪些未使用的索引？我对使用SQLServer的“profiler”工具进行此操作有一点经验，但我不知道Postgres是否包含类似的工具。

- Cerin

9个回答

26

检查统计数据。 pg_stat_user_tables 和 pg_stat_user_indexes 是开始的指标。

请参阅 "统计收集器"。

- Frank Heikens

19

在确定缺失索引的方法上...没有。但是有一些计划在未来的版本中使这更容易，比如伪索引和可机读的EXPLAIN。

目前，您需要使用EXPLAIN ANALYZE分析性能不佳的查询，然后手动确定最佳路线。像pgFouine这样的日志分析工具可以帮助确定查询。

至于未使用的索引，您可以使用以下内容来帮助识别它们:

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

这将有助于识别已读取、扫描和获取的元组。

- rfusca

17

另一个分析PostgreSQL的有趣工具是PgHero。它更专注于调整数据库，并提供大量分析和建议。

- n1000

16

您可以使用以下查询来查找索引使用情况和索引大小：

参考此博客。

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;

- Anvesh

10

可以在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

- Shree Prakash

4

在PostgreSQL wiki上有多个脚本链接可帮助您查找未使用的索引。基本技术是查看pg_stat_user_indexes，并查找其中idx_scan为零或非常低的索引，即这些索引很少被用来回答查询。如果应用程序已更改，并且曾经使用的索引现在可能没有使用，有时必须运行pg_stat_reset()以将所有统计数据重置为0，然后收集新数据；您可以保存一切当前值并计算差异来解决这个问题。

目前还没有好的工具可以建议缺失的索引。一种方法是记录您运行的查询并使用查询日志分析工具（如pgFouine或pqa）分析哪些查询需要较长时间才能运行。请参见“记录困难查询”获取更多信息。

另一种方法是查看pg_stat_user_tables，并查找大量连续扫描的表，其中seq_tup_fetch很大。当使用索引时，idx_fetch_tup计数会增加。这可以提示您何时对表的索引不足以回答针对它的查询。

实际上，确定您应该在哪些列上创建索引？通常会再次导致查询日志分析。

- Greg Smith

1

PoWA 看起来是一个有趣的工具，适用于 PostgreSQL 9.4+。它收集统计数据，可视化并建议索引。它使用 pg_stat_statements 扩展。

PoWA 是 PostgreSQL 工作量分析器，收集性能统计信息，并提供实时图表和图形，以帮助监视和调整您的 PostgreSQL 服务器。它类似于 Oracle AWR 或 SQL Server MDW。

- n1000

0

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

- madjardi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- guettli · Accepted Answer

我想用这个方法来查找缺失的索引：

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 可能会有帮助...