时间戳

首页 > 解决方案 > PostgreSQL 的统计收集器是否跟踪索引的*所有*使用情况？

postgresql - PostgreSQL 的统计收集器是否跟踪索引的*所有*使用情况？

问题描述

在接管了一个相当复杂的数据库的 DBA 职责后，我想消除任何消耗大量磁盘空间但未被使用的索引。我运行了以下命令，以识别未使用的索引，对那些占用磁盘空间最多的索引进行排序：

SELECT 
    schemaname,
    pg_stat_all_indexes.relname AS table,
    pg_class.relname AS index,
    pg_total_relation_size(oid) AS size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM  pg_class
JOIN pg_stat_all_indexes ON pg_stat_all_indexes.indexrelname = pg_class.relname
WHERE  
    relkind =('i')
ORDER BY size DESC

我有点惊讶于有多少大索引似乎根本没有被使用——正如 idx_scan 列的 0 所证明的那样。其中一些明显未使用的索引包括一个执行非常具体的函数调用（如下面的人为示例），并且似乎已设置为辅助 API 功能。

--not real index
CREATE INDEX foo_transform_foo_name_idx
    ON foo USING btree
    (foo_transform_name(foo_name));

我的问题是，统计收集器是否捕获特定索引的所有使用，即使这些索引是从 SQL 语言函数或以其他方式扫描的？

标签： postgresqldatabase-indexes

解决方案

这些索引从未被扫描过。但是，索引还有一些其他用途：

  • 它们强制执行唯一性和其他约束

  • 他们ANALYZE收集有关索引表达式的统计信息

使用我博客中的这个查询来查找可以删除而不会产生任何负面影响的索引：

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)
ORDER BY pg_relation_size(s.indexrelid) DESC;

推荐阅读