SQL PostgreSQL 索引使用分析
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3318727/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL Index Usage Analysis
提问by Cerin
Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the "profiler" tool for SQLServer, but I'm not aware of a similar tool included with Postgres.
是否有工具或方法可以分析 Postgres，并确定应该创建哪些缺失的索引，以及应该删除哪些未使用的索引？我有一些使用 SQLServer 的“分析器”工具执行此操作的经验，但我不知道 Postgres 中包含类似的工具。
回答by guettli
I like this to find missing indexes:
我喜欢这样找到丢失的索引：
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;
This checks if there are more sequence scans then index scans. If the table is small, it gets ignored, since Postgres seems to prefer sequence scans for them.
这将检查是否有更多的序列扫描然后索引扫描。如果表很小，它会被忽略，因为 Postgres 似乎更喜欢对它们进行序列扫描。
Above query does reveal missing indexes.
上面的查询确实揭示了缺失的索引。
The next step would be to detect missing combined indexes. I guess this is not easy, but doable. Maybe analyzing the slow queries ... I heard pg_stat_statementscould help...
下一步是检测缺失的组合索引。我想这并不容易，但可行。也许分析缓慢的查询......我听说pg_stat_statements可以帮助......
回答by Frank Heikens
Check the statistics. pg_stat_user_tablesand pg_stat_user_indexesare the ones to start with.
检查统计数据。pg_stat_user_tables并且pg_stat_user_indexes是开始的那些。
See "The Statistics Collector".
参见“统计收集器”。
回答by rfusca
On the determine missing indexes approach....Nope. But there's some plans to make this easier in future release, like pseudo-indexes and machine readable EXPLAIN.
在确定缺少索引的方法....不。但是有一些计划在未来的版本中使这更容易，比如伪索引和机器可读的解释。
Currently, you'll need to EXPLAIN ANALYZEpoor performing queries and then manually determine the best route. Some log analyzers like pgFouinecan help determine the queries.
目前，您需要EXPLAIN ANALYZE执行较差的查询，然后手动确定最佳路线。一些日志分析器如pgFouine可以帮助确定查询。
As far as an unused index, you can use something like the following to help identify them:
至于未使用的索引，您可以使用以下内容来帮助识别它们：
select * from pg_stat_all_indexes where schemaname <> 'pg_catalog';
This will help identify tuples read, scanned, fetched.
这将有助于识别读取、扫描、获取的元组。
回答by n1000
回答by Anvesh
You can use below query to find Index usage and Index size:
您可以使用以下查询来查找索引使用情况和索引大小：
Reference is taken from this blog.
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;
回答by Greg Smith
There are multiple links to scripts that will help you find unused indexes at the PostgreSQL wiki. The basic technique is to look at pg_stat_user_indexesand look for ones where idx_scan, the count of how many times that index has been used to answer queries, is zero, or at least very low. If the application has changed and a formerly used index probably isn't now, you sometimes have to run pg_stat_reset()to get all the statistics back to 0 and then collect new data; you might save the current values for everything and compute a delta instead to figure that out.
有多个脚本链接可以帮助您在PostgreSQL wiki 中找到未使用的索引。基本技术是查看pg_stat_user_indexes并查找idx_scan索引已用于回答查询的次数为零或至少非常低的索引。如果应用程序已更改并且以前使用的索引现在可能已不存在，则有时必须运行pg_stat_reset()以将所有统计信息恢复为 0，然后收集新数据；您可能会保存所有内容的当前值并计算增量来解决这个问题。
There isn't any good tools available yet to suggest missing indexes. One approach is to log the queries you're running and analyze which ones are taking a long time to run using a query log analysis tool like pgFouine or pqa. See "Logging Difficult Queries" for more info.
目前还没有任何好的工具可以建议缺失的索引。一种方法是记录您正在运行的查询，并使用查询日志分析工具（如 pgFouine 或 pqa）分析哪些查询需要很长时间才能运行。有关详细信息，请参阅“记录困难查询”。
The other approach is to look at pg_stat_user_tablesand look for tables that have large numbers of sequential scans against them, where seq_tup_fetchis large. When an index is used the idx_fetch_tupcount is increased instead. That can clue you into when a table is not indexed well enough to answer queries against it.
另一种方法是查看pg_stat_user_tables并查找对它们进行大量顺序扫描的表，其中seq_tup_fetch很大。当使用索引时，idx_fetch_tup计数会增加。这可以提示您何时表的索引不够好，无法回答针对它的查询。
Actually figuring out which columns you should then index on? That usually leads back to the query log analysis stuff again.
实际上弄清楚你应该索引哪些列？这通常会再次回到查询日志分析的内容。
回答by Shree Prakash
It can be found by using following query in postgres console
可以通过在 postgres 控制台中使用以下查询找到它
use db_name
select * from pg_stat_user_indexes;
select * from pg_statio_user_indexes;
For More Details https://www.postgresql.org/docs/current/monitoring-stats.html
更多详情https://www.postgresql.org/docs/current/monitoring-stats.html
回答by n1000
PoWAseems like an interesting tool for PostgreSQL 9.4+. It collects statistics, visualizes them, and suggests indexes. It uses the pg_stat_statementsextension.
PoWA似乎是 PostgreSQL 9.4+ 的一个有趣工具。它收集统计数据，将它们可视化，并建议索引。它使用pg_stat_statements扩展名。
PoWA is PostgreSQL Workload Analyzer that gathers performance stats and provides real-time charts and graphs to help monitor and tune your PostgreSQL servers. It is similar to Oracle AWR or SQL Server MDW.
PoWA 是 PostgreSQL 工作负载分析器，可收集性能统计数据并提供实时图表和图形，以帮助监控和调整您的 PostgreSQL 服务器。它类似于 Oracle AWR 或 SQL Server MDW。
回答by madjardi
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
回答by Jo?o Pereira
This should help: Pratical Query Analysis
这应该会有所帮助：Practical Query Analysis
