注: 本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

前言

在生产环境上，由于不规范的优化措施，数据库中可能存在大量的索引，并且相当一部分的索引重未被使用过，今天带大家如何找出这些索引。

一、存在大量未使用的索引带来的危害

在PostgreSQL中，存在大量未使用的索引可能会带来以下几个问题：

1. 性能下降：每当对表进行插入、删除或更新操作时，相关的索引都需要被更新。如果有大量未使用的索引，这些索引的维护工作可能会消耗大量的CPU和I/O资源，导致数据库性能下降。

2. 占用存储空间：索引本身会占用存储空间。如果有大量未使用的索引，这些索引会浪费大量的磁盘空间，可能导致存储成本增加。

3. 影响查询优化器的决策：PostgreSQL的查询优化器在决定查询执行计划时，会考虑所有可用的索引。如果有大量未使用的索引，可能会增加查询优化器的计算复杂度，影响其决策的效率和准确性。

4. 备份和恢复时间增加：如果有大量未使用的索引，备份和恢复数据库的时间可能会增加，因为索引数据也需要被备份和恢复。

因此，定期审查和清理未使用的索引是一个很好的数据库维护实践。但在删除索引之前，需要确保这些索引真的不再需要，因为有些索引可能只在特定的查询或特定的时间（如每月的报表生成）中被使用。

二、实战案例

接下来，我们以实际案例来演示索引过多带来的性能影响

2.1 创建带索引的表

drop table t1 ;
create table t1(a int, b int, c int);
create index idx_a on t1(a);
create index idx_b on t1(b);
create index idx_c on t1(c);
复制

2.2 创建不带索引的表

drop table t2;
create table t2(a int, b int, c int);
复制

2.3 插入数据测试

postgres=# \timing on
Timing is on.

postgres=#  insert into t1  select id,id,id from generate_series(1,1000000) as s(id);
INSERT 0 1000000
Time: 5318.992 ms (00:05.319)

postgres=#  insert into t2  select id,id,id from generate_series(1,1000000) as s(id);
INSERT 0 1000000
Time: 1179.886 ms (00:01.180)
复制

上面结果可以看出，同样结构的表，插入数据的操作，有无索引的性能相关近5倍！

2.4 修改数据测试

postgres=# update t1 set b=b+1000;
UPDATE 1000000
Time: 11041.398 ms (00:11.041)
postgres=# update t2 set b=b+1000;
UPDATE 2000000
Time: 4478.148 ms (00:04.478)
复制

上面结果可以看出，同样结构的表，修改数据的操作，有无索引的性能相关近3倍！

2.5 查看此时表的体积

postgres=# select pg_size_pretty(pg_total_relation_size('t1'::regclass::oid)),
postgres-#       pg_size_pretty(pg_total_relation_size('t2'::regclass::oid));
 pg_size_pretty | pg_size_pretty 
----------------+----------------
 213 MB         | 169 MB
(1 row)
复制

因为索引的原因，t1的体积更大。

三、索引使用的规则

PostgreSQL的查询优化器使用一种称为成本优化的方法来决定是否使用索引，以及选择使用哪个索引。以下是一些影响PostgreSQL使用索引的主要因素：

1. 查询的选择性：索引对于高选择性的查询最有用。选择性是指查询结果返回的记录占表中总记录的比例。例如，如果你正在查询一个人口为几十亿的国家中的一小部分人，这个查询就有很高的选择性，使用索引会很有帮助。相反，如果你正在查询的数据占总数据的大部分，那么全表扫描可能会更快。

2. 索引类型：PostgreSQL支持多种类型的索引，包括B-tree、Hash、GiST、SP-GiST、GIN和BRIN。每种索引类型都有其特定的用途和优势。例如，B-tree索引适用于等于、大于、小于等操作，而GIN索引适用于数组和全文搜索。

3. 数据分布：如果表中的数据分布不均匀，索引可能会更有效。例如，如果一个字段的值大部分都是唯一的，那么对这个字段创建索引可能会很有帮助。

4. 索引大小和深度：大的索引需要更多的磁盘I/O操作来读取，而深的索引需要更多的磁盘I/O操作来遍历。因此，如果一个索引很大或很深，查询优化器可能会选择不使用它。

5. 硬件和系统配置：硬件的性能（如CPU速度、内存大小、磁盘速度等）和PostgreSQL的配置（如工作内存、随机页面成本等）也会影响查询优化器的决策。

6. 统计信息：PostgreSQL的查询优化器使用统计信息来估计查询的成本。这些统计信息包括表的大小、索引的大小、数据的分布等。如果这些统计信息不准确，查询优化器可能会做出错误的决策。

最后，要注意的是，查询优化器的目标是尽可能快地返回查询结果，而不一定是尽可能少地读取磁盘页面。因此，有时候即使存在索引，查询优化器也可能选择全表扫描，因为它认为这样会更快。

四、如何找出未使用的索引

在pgv16的版本中，有一个视图pg_stat_user_indexes可以查看索引使用情况

postgres=# \d pg_stat_user_indexes
                  View "pg_catalog.pg_stat_user_indexes"
    Column     |           Type           | Collation | Nullable | Default 
---------------+--------------------------+-----------+----------+---------
 relid         | oid                      |           |          | 
 indexrelid    | oid                      |           |          | 
 schemaname    | name                     |           |          | 
 relname       | name                     |           |          | 
 indexrelname  | name                     |           |          | 
 idx_scan      | bigint                   |           |          | 
 last_idx_scan | timestamp with time zone |           |          | 
 idx_tup_read  | bigint                   |           |          | 
 idx_tup_fetch | bigint                   |           |          | 
复制

查看当前索引使用的频率

postgres=# select schemaname||'.'||relname||'.'||indexrelname,idx_scan,last_idx_scan from pg_stat_user_indexes;
    ?column?     | idx_scan | last_idx_scan 
-----------------+----------+---------------
 public.t1.idx_a |        0 | 
 public.t1.idx_b |        0 | 
 public.t1.idx_c |        0 | 
(3 rows)
复制

调用索引再次查看

postgres=# explain select a from t1 where a=111;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Only Scan using idx_a on t1  (cost=0.42..4.44 rows=1 width=4)
   Index Cond: (a = 111)
(2 rows)

Time: 1.009 ms
postgres=# select schemaname||'.'||relname||'.'||indexrelname,idx_scan,last_idx_scan from pg_stat_user_indexes;
    ?column?     | idx_scan | last_idx_scan 
-----------------+----------+---------------
 public.t1.idx_a |        0 | 
 public.t1.idx_b |        0 | 
 public.t1.idx_c |        0 | 
(3 rows)

Time: 1.208 ms
复制

SQL的执行计划中显示调用了索引idx_a ，查询pg_stat_user_indexes结果显示索引的扫描次数没有增加，为什么？原来explain并未真正执行SQL。

那加上analyze参数

postgres=# explain analyze select a from t1 where a=111;
                                                  QUERY PLAN                                 
                  
---------------------------------------------------------------------------------------------
 Index Only Scan using idx_a on t1  (cost=0.42..4.44 rows=1 width=4) (actual time=0.064..0.066 rows=1 loops=1)
   Index Cond: (a = 111)
   Heap Fetches: 0
 Planning Time: 0.060 ms
 Execution Time: 0.082 ms
(5 rows)

Time: 0.690 ms
postgres=# select schemaname||'.'||relname||'.'||indexrelname,idx_scan,last_idx_scan from pg_stat_user_indexes;
    ?column?     | idx_scan |        last_idx_scan         
-----------------+----------+------------------------------
 public.t1.idx_a |        1 | 2024-02-21 19:57:55.97131-08
 public.t1.idx_b |        0 | 
 public.t1.idx_c |        0 | 
(3 rows)

Time: 1.252 ms
复制

在v16之前的版本中,pg_stat_user_indexes.last_idx_scan字段是没有的，只能通过手工编写脚本来记录索引扫描时间，也可以通过SELECT pg_stat_reset()函数重置索引的使用记录，人工观察一周来排查。这里不再赘述。

五、总结

通过以上实验，我们知道了表上索引过多的危害及如何定位未使用索引的方法，对pg数据库的索引有进一步的了解。