自动索引推荐_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文介绍了自动索引推荐功能的背景信息以及使用方法等内容。

前提条件

支持的PolarDB PostgreSQL版的版本如下：

PostgreSQL 14（内核小版本14.10.19.0及以上）

说明

您可通过如下语句查看PolarDB PostgreSQL版的内核小版本的版本号：

select version();

背景信息

数据库性能优化往往是数据库管理的关键任务之一，而对于SQL查询的性能提升，索引起着至关重要的作用。合理的索引可以使得数据库快速定位到表中特定的行，直接获取到已经排序好的数据，极大缩短查询时间。

不过，并非所有索引都能有效提升性能。一个合适的索引能够大幅提升查询速度，而不恰当的索引不仅无法提升性能，反而可能由于维护索引本身的开销而降低数据库的整体性能。此外，索引还占用存储空间，过多的无效索引会浪费存储资源。因此，确定何时以及在哪些列上创建索引是一个技术性和经验性都很强的领域，这通常需要深入分析查询的执行路径和数据的分布特征。

为了协助用户解决这一技术难题并优化数据库的性能，PolarDB PostgreSQL版推出自动索引推荐功能。该功能可以简化索引的创建流程，降低开发和维护数据库索引的难度。用户仅需通过EXPLAIN命令对慢SQL语句进行性能分析，系统便能基于该分析提供合适的索引创建建议，并估计出预期的查询加速效果。

自动索引推荐功能的主要优势如下：

精准推荐：利用数据库自动搜集的统计信息和优化器代价模型，能够为具体的SQL查询推荐合适的索引配置，避免了盲目地添加索引可能带来的性能负担和试错成本。
效率提升：减少手动分析慢查询的时间，帮助快速识别并针对性地解决性能瓶颈，缩短问题定位和解决的周期。
易于操作：通过一个简单的EXPLAIN命令就能获得索引推荐信息，同时提供创建索引的SQL命令以及相应的性能提升预测，优化了用户后续操作流程。

使用指南

说明

如果发现推荐的索引效果不符合预期或没有推荐出合适的索引，请联系我们进行排查优化。
以下操作建议使用psql客户端，因为DMS客户端目前不会展示notice提示。

配置自动索引推荐

设置所有用户加载polar_advisor，即在session_preload_libraries中增加polar_advisor。

-- session_preload_libraries默认为空，此处添加polar_advisor
alter role all set session_preload_libraries to 'polar_advisor';

设置所有用户在特定节点上开启索引推荐，推荐仅在RO节点开启，避免消耗RW节点的计算资源。

-- 设置在RO节点上开启索引推荐【推荐】
alter role all set polar_advisor_type to 'index(ro)';

-- 设置在所有节点上开启索引推荐
alter role all set polar_advisor_type to 'index';

使用自动索引推荐

使用不带ANALYZE语句的EXPLAIN命令为慢SQL生成执行计划，自动索引推荐将会自动运行，检测到推荐的索引后会自动输出，包括如下信息：

advise：创建推荐索引的命令。
old cost：原计划即现在计划的cost。
new cost：预估的创建推荐索引后的计划的cost。
saved cost：新计划较老计划节省的cost。
estimate：预估的加速效果。

自动索引推荐现在能识别以下类型的算子，目前仅支持推荐BTREE索引，示例如下。

构造表及数据。

create table t( a int,b int);
insert into t select i,i from generate_series(1,10000)i;
analyze t;

根据等值条件推荐单列索引。

/*FORCE_SLAVE*/ explain select * from t where a = 1;
INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
NOTICE:  IND ADV:   old cost 0.00..170.00
NOTICE:  IND ADV:   new cost 0.04..2.25
NOTICE:  IND ADV: saved cost -0.04..167.75
NOTICE:  IND ADV: estimate 75.5 times faster
                    QUERY PLAN
---------------------------------------------------
 Seq Scan on t  (cost=0.00..170.00 rows=1 width=8)
   Filter: (a = 1)
(2 rows)

根据比较条件推荐单列索引。

 /*FORCE_SLAVE*/ explain select * from t where a > 10; -- 索引筛选不了多少数据因此没有推荐索引
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on t  (cost=0.00..170.00 rows=9990 width=8)
   Filter: (a > 10)
(2 rows)

 /*FORCE_SLAVE*/ explain select * from t where a < 10;
INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
NOTICE:  IND ADV:   old cost 0.00..170.00
NOTICE:  IND ADV:   new cost 0.04..2.39
NOTICE:  IND ADV: saved cost -0.04..167.61
NOTICE:  IND ADV: estimate 71.1 times faster
                    QUERY PLAN
---------------------------------------------------
 Seq Scan on t  (cost=0.00..170.00 rows=9 width=8)
   Filter: (a < 10)
(2 rows)

根据多个等值或比较条件推荐组合索引。

/*FORCE_SLAVE*/ explain select * from t where a = 1 and b = 1;
INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (b, a)"
NOTICE:  IND ADV:   old cost 0.00..195.00
NOTICE:  IND ADV:   new cost 0.04..1.16
NOTICE:  IND ADV: saved cost -0.04..193.84
NOTICE:  IND ADV: estimate 168.8 times faster
                    QUERY PLAN
---------------------------------------------------
 Seq Scan on t  (cost=0.00..195.00 rows=1 width=8)
   Filter: ((a = 1) AND (b = 1))
(2 rows)

根据Order BY算子推荐单列索引。

 /*FORCE_SLAVE*/ explain select * from t order by a limit 10;
INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
NOTICE:  IND ADV:   old cost 361.10..361.12
NOTICE:  IND ADV:   new cost 0.04..0.26
NOTICE:  IND ADV: saved cost 361.06..360.86
NOTICE:  IND ADV: estimate 1366.8 times faster
                            QUERY PLAN
-------------------------------------------------------------------
 Limit  (cost=361.10..361.12 rows=10 width=8)
   ->  Sort  (cost=361.10..386.10 rows=10000 width=8)
         Sort Key: a
         ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=8)
(4 rows)

根据Group BY算子推荐单列索引。

/*FORCE_SLAVE*/ explain select a,sum(b) from t group by a having a < 10 ;
INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
NOTICE:  IND ADV:   old cost 170.14..170.30
NOTICE:  IND ADV:   new cost 0.04..2.53
NOTICE:  IND ADV: saved cost 170.11..167.77
NOTICE:  IND ADV: estimate 67.4 times faster
                          QUERY PLAN
---------------------------------------------------------------
 GroupAggregate  (cost=170.14..170.30 rows=9 width=12)
   Group Key: a
   ->  Sort  (cost=170.14..170.17 rows=9 width=8)
         Sort Key: a
         ->  Seq Scan on t  (cost=0.00..170.00 rows=9 width=8)
               Filter: (a < 10)
(6 rows)

根据等值连接操作推荐单列索引。

/*FORCE_SLAVE*/ explain select * from t t1,t t2 where t1.a = t2.a limit 10;
INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
NOTICE:  IND ADV:   old cost 270.00..270.28
NOTICE:  IND ADV:   new cost 0.07..0.70
NOTICE:  IND ADV: saved cost 269.93..269.58
NOTICE:  IND ADV: estimate 384.3 times faster
                                 QUERY PLAN
----------------------------------------------------------------------------
 Limit  (cost=270.00..270.28 rows=10 width=16)
   ->  Hash Join  (cost=270.00..552.50 rows=10000 width=16)
         Hash Cond: (t1.a = t2.a)
         ->  Seq Scan on t t1  (cost=0.00..145.00 rows=10000 width=8)
         ->  Hash  (cost=145.00..145.00 rows=10000 width=8)
               ->  Seq Scan on t t2  (cost=0.00..145.00 rows=10000 width=8)
(6 rows)