一月 29, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中检查未使用的索引。
目录
为什么索引没有被使用？
大型数据库通常都有自己的生命周期。许多应用程序来来去去，不断发展，PostgreSQL 服务器升级，配置设置调整，硬件更换，表重组，查询、数据和访问模式发生变化。
是否对某个查询使用某个索引的决定，由 PostgreSQL 的查询规划器根据以下因素做出：
- 它是启发式的算法 – 它们本身可能由于 PostgreSQL 版本升级而发生变化
- 配置设置 – 可能会针对当前硬件进行调整，也可能未进行调整
- 表数据的统计信息 – 自创建索引以来，这些数据可能随时间而变化
- 使用索引的成本 – 使用其他较新的索引或表扫描可能更划算
在使用数据库的多个版本的应用程序中，为其创建索引的原始查询本身可能不再执行，也不再频繁地执行。应用程序本身可能已经使用新的技术栈、新的 ORM 或全新的技术进行了重构或重写。
从理论上讲，可以根据数据库中的实际数据，查看每个应用程序的每个查询，并确定最佳索引集。在实践中，对于具有较大团队的组织中的大型、长期存在的数据库，这几乎是不可能的。从数据库操作的角度来看，将定期审查和删除多余索引作为维护任务更为实际。
为什么要删除未使用的索引？
每个人都知道数据库索引是一件好事，因为它可以加快 SQL 查询的速度。但这是有代价的。
索引的缺点有：
- 索引会占用空间。 数据库索引使用的存储空间与数据本身一样多的情况，并不少见。而且，您数据库想要的可靠、快速的存储设备，并不一定便宜。索引占用的空间，也会增加物理备份的大小和持续时间。
- 索引会减慢数据修改速度。 每当您
INSERT到表或从表DELETE时，除了表本身（“堆表”）之外，还必须修改所有索引。而且修改索引的复杂数据结构，比修改堆表本身的成本高得多，堆表之所以得名，正是因为它基本上是一堆无序的数据（众所周知，维护秩序比无序管理需要做更多工作）。修改一个带索引的表的成本，很容易比修改一个无索引的表高出一个数量级。 - 索引会阻碍 HOT 更新。 由于 PostgreSQL 的机制原因，每次
UPDATE都会导致写入一个新的行版本（“元组”），这会导致表上的每个索引中都有一个新条目。这种行为被称为“写入放大”，并引起了很多抨击。如果 a）新元组与旧元组位于同一表块中，并且 b）未修改索引列，则可以避免这种不良影响。然后，PostgreSQL 将新元组创建为“仅堆表元组”（即 HOT），这样效率更高，也减少了VACUUM必须做的工作。
索引的多种用途
现在我们知道我们不需要不必要的索引。问题在于，索引的用途如此之多，以至于很难确定是否需要某个索引。
以下列出了 PostgreSQL 中索引的所有好处：
- 索引可以加快在
WHERE子句中使用索引列（或表达式）的查询。 传统的 B 树索引支持<、<=、=、>=和>运算符，而 PostgreSQL 中的许多其他索引类型，可以支持一些更奇特的运算符，如“重叠”（用于范围或几何类型）、“距离”（用于单词）或正则表达式匹配。 - B 树索引可以加快
max()和min()聚合的速度。 - B 树索引可以加快
ORDER BY子句的速度。 - 索引可以加快连接速度。这取决于优化器选择的“连接策略”：例如，哈希连接永远不会使用到索引。
FOREIGN KEY约束所在的源表上的 B 树索引，可避免在目标表中删除行（或修改键）时进行顺序扫描。在约束所在的源表上进行扫描是必须的，需要确保修改不会违反约束。- 索引可用于强制实施约束。唯一性 B 树索引用于强制执行
PRIMARY KEY和UNIQUE约束，而排除性约束会使用 GiST 索引。 - 索引可以为优化器提供更好的值分布统计信息。如果在表达式上创建索引，那么
ANALYZE和自动分析后台进程不仅会收集表列中数据分布的统计信息，还会收集索引中出现的每个表达式相关的统计信息。对于包含索引表达式的复杂条件的“选择性”，这有助于优化器进行良好的估计，从而选择更好的计划。这是索引的一个被广泛忽视的好处！
查找未使用的索引
以下查询可显示出所有和上述用途无关的索引。
它利用了这样一个事实，即上述列表中所有的索引使用场景（最后两个除外）都会触发索引扫描。
为了完整起见，需要补充一点，track_counts参数必须保持 “打开” 状态才能使查询正常工作，否则不会在pg_stat_user_indexes中跟踪索引的使用。但无论如何都不要更改该参数，否则 autovacuum 会停止工作。
要查找自上次使用pg_stat_reset()进行统计信息重置以来，从未使用过的索引，请使用
SELECT s.schemaname,
s.relname AS tablename,
s.indexrelname AS indexname,
pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0 -- has never been scanned
AND 0 <> ALL (i.indkey) -- no index column is an expression
AND NOT i.indisunique -- is not a UNIQUE index
AND NOT EXISTS -- does not enforce a constraint
(SELECT 1 FROM pg_catalog.pg_constraint c
WHERE c.conindid = s.indexrelid)
AND NOT EXISTS -- is not an index partition
(SELECT 1 FROM pg_catalog.pg_inherits AS inh
WHERE inh.inhrelid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;
一些备注：
- 不要在测试数据库上这样做，而是在生产数据库上这样做！
- 如果您的软件在多个客户站点上运行，请在所有站点上运行查询。不同的用户会有不同的使用软件的方式，这可能会导致使用不同的索引。
- 您可以在查询中将
s.idx_scan = 0替换为其他条件，例如s.idx_scan < 10。那些很少使用的索引，也可以考虑删除。