PostgreSQL 教程: 检查未使用的索引

一月 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;