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

一月 29, 2024

摘要:在本教程中,您将学习如何在 PostgreSQL 中检查未使用的索引。

为什么索引没有被使用?

大型数据库通常都有自己的生命周期。许多应用程序来来去去,不断发展,PostgreSQL 服务器升级,配置设置调整,硬件更换,表重组,查询、数据和访问模式发生变化。

是否对某个查询使用某个索引的决定,由 PostgreSQL 的查询规划器根据以下因素做出:

  • 它是启发式的算法 – 它们本身可能由于 PostgreSQL 版本升级而发生变化
  • 配置设置 – 可能会针对当前硬件进行调整,也可能未进行调整
  • 表数据的统计信息 – 自创建索引以来,这些数据可能随时间而变化
  • 使用索引的成本 – 使用其他较新的索引或表扫描可能更划算

在使用数据库的多个版本的应用程序中,为其创建索引的原始查询本身可能不再执行,也不再频繁地执行。应用程序本身可能已经使用新的技术栈、新的 ORM 或全新的技术进行了重构或重写。

从理论上讲,可以根据数据库中的实际数据,查看每个应用程序的每个查询,并确定最佳索引集。在实践中,对于具有较大团队的组织中的大型、长期存在的数据库,这几乎是不可能的。从数据库操作的角度来看,将定期审查和删除多余索引作为维护任务更为实际。

为什么要删除未使用的索引?

每个人都知道数据库索引是一件好事,因为它可以加快 SQL 查询的速度。但这是有代价的。

索引的缺点有:

  • 索引会占用空间。 数据库索引使用的存储空间与数据本身一样多的情况,并不少见。而且,您数据库想要的可靠、快速的存储设备,并不一定便宜。索引占用的空间,也会增加物理备份的大小和持续时间。
  • 索引会减慢数据修改速度。 每当您INSERT到表或从表DELETE时,除了表本身(“堆表”)之外,还必须修改所有索引。而且修改索引的复杂数据结构,比修改堆表本身的成本高得多,堆表之所以得名,正是因为它基本上是一堆无序的数据(众所周知,维护秩序比无序管理需要做更多工作)。修改一个带索引的表的成本,很容易比修改一个无索引的表高出一个数量级。
  • 索引会阻碍 HOT 更新。 由于 PostgreSQL 的机制原因,每次UPDATE都会导致写入一个新的行版本(“元组”),这会导致表上的每个索引中都有一个新条目。这种行为被称为“写入放大”,并引起了很多抨击。如果 a)新元组与旧元组位于同一表块中,并且 b)未修改索引列,则可以避免这种不良影响。然后,PostgreSQL 将新元组创建为“仅堆表元组”(即 HOT),这样效率更高,也减少了VACUUM必须做的工作。

索引的多种用途

现在我们知道我们不需要不必要的索引。问题在于,索引的用途如此之多,以至于很难确定是否需要某个索引。

以下列出了 PostgreSQL 中索引的所有好处:

  1. 索引可以加快在WHERE子句中使用索引列(或表达式)的查询。 传统的 B 树索引支持<<==>=>运算符,而 PostgreSQL 中的许多其他索引类型,可以支持一些更奇特的运算符,如“重叠”(用于范围或几何类型)、“距离”(用于单词)或正则表达式匹配。
  2. B 树索引可以加快max()min()聚合的速度。
  3. B 树索引可以加快ORDER BY子句的速度。
  4. 索引可以加快连接速度。这取决于优化器选择的“连接策略”:例如,哈希连接永远不会使用到索引。
  5. FOREIGN KEY约束所在的源表上的 B 树索引,可避免在目标表中删除行(或修改键)时进行顺序扫描。在约束所在的源表上进行扫描是必须的,需要确保修改不会违反约束。
  6. 索引可用于强制实施约束。唯一性 B 树索引用于强制执行PRIMARY KEYUNIQUE约束,而排除性约束会使用 GiST 索引。
  7. 索引可以为优化器提供更好的值分布统计信息。如果在表达式上创建索引,那么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。那些很少使用的索引,也可以考虑删除。