索引推荐（index_adviser） - RDS PostgreSQL数据库| 阿里云

索引推荐（index_adviser）组件

执行创建索引推荐插件语句时，同时会创建index_advisory表、show_index_advisory()函数和select_index_advisory视图。


组件	说明
index_advisory	此表在创建索引推荐（index_adviser）插件时自动创建，将索引编制建议记录在index_advisory表中。
show_index_advisory()	show_index_advisory()是一个PL/pgSQL函数，它解释并显示特定的索引推荐会话（由其后端进程ID标识）期间提出的建议。
select_index_advisory	索引推荐（index_adviser）根据查询分析期间存储在index_advisory表中的信息，创建select_index_advisory视图。该视图所生成输出的格式与show_index_advisory()函数的相同，显示所有索引推荐会话期间提出的建议。

使用方法

创建index_adviser插件

postgres=# create extension index_adviser;
CREATE EXTENSION

加载index_adviser插件
```
postgres=# LOAD 'index_adviser';
LOAD
```
说明以上加载语句只对当前会话有效。如果您希望所有会话都默认加载index_adviser，您需要配置shared_preload_libraries参数并重启实例（会对性能有一些影响），配置方法如下：
```
shared_preload_libraries='index_adviser'
```

使用示例

创建表

CREATE TABLE t( a INT, b INT );
INSERT INTO t SELECT s, 99999 - s FROM generate_series(0,99999) AS s;
ANALYZE t;
所生成的表包含以下各行：
   a   |   b
-------+-------
     0 | 99999
     1 | 99998
     2 | 99997
     3 | 99996
       .
       .
       .
 99997 |     2
 99998 |     1
 99999 |     0

查询单条SQL建议

如果您希望索引推荐分析查询并提出索引编制建议但不实际执行查询，请将EXPLAIN关键字作为SQL语句的前缀，示例如下：

postgres=# EXPLAIN SELECT * FROM t WHERE a < 10000;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=9983 width=8)
   Filter: (a < 10000)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..256.52 rows=9983 width=8)
         Index Cond: (a < 10000)
(6 rows)

postgres=# EXPLAIN SELECT * FROM t WHERE a = 100;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
   Filter: (a = 100)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..2.64 rows=1 width=8)
         Index Cond: (a = 100)
(6 rows)

postgres=# EXPLAIN SELECT * FROM t WHERE b = 10000;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
   Filter: (b = 10000)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_b_idx" on t  (cost=0.42..2.64 rows=1 width=8)
         Index Cond: (b = 10000)
(6 rows)

您可通过psql命令行查询index_advisory表内存储的索引编制建议，示例如下：

postgres=# SELECT * FROM index_advisory;
 reloid | relname | attrs | benefit | original_cost | new_cost | index_size | backend_pid |            timestamp
--------+---------+-------+---------+---------------+----------+------------+-------------+----------------------------------
  16438 | t       | {1}   | 1337.43 |          1693 |  355.575 |       2624 |       79370 | 18-JUN-21 08:55:51.492388 +00:00
  16438 | t       | {1}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:00.319336 +00:00
  16438 | t       | {2}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:07.814453 +00:00
(3 rows)


列名	类型	描述
reloid	oid	索引的基表的OID。
relname	name	索引的基表的名称。
attrs	integer[]	生成建议的索引列（由列编号标识）。
benefit	real	此查询的索引的计算收益。
original_cost	real	使用索引之前的平均代价（即执行SQL的预估时间）。
new_cost	real	使用索引之后的平均代价（即执行SQL的预估时间）。
index_size	integer	磁盘页中的估计索引大小。
backend_pid	integer	生成此建议的进程ID。
timestamp	timestamp	生成此建议的日期及时间。

如果语句不带EXPLAIN关键字前缀，索引推荐将在语句执行期间分析语句并记录建议。

说明不得在只读事务中运行索引推荐（index_adviser）。

查询WorkLoad级别建议
- 通过show_index_advisory()函数获取单个会话的WorkLoad建议
  此函数用于获取单个会话的索引推荐（由后端进程ID标识），您可通过指定会话的进程ID来调用该函数：
```
SELECT show_index_advisory( pid );
```
  说明 pid指当前会话的进程ID，您可以在index_advisory表中查看backend_pid参数来获得。您也可以将null作为传递值为当前会话返回结果集。
```
postgres=# SELECT show_index_advisory(null);
                                                             show_index_advisory
----------------------------------------------------------------------------------------------------------------------------------------------------
 create index idx_t_a on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
 create index idx_t_b on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
(2 rows)
```
  说明结果集中每行的表示意义如下：
  
  创建索引推荐建议的索引所需的SQL语句。
  
  索引页的估计大小。
  
  使用索引的总收益（benefit）。
  
  使用索引的增益（gain=benefit/size）。
  
  使用索引之前的平均代价（即执行SQL的预估时间）。
  
  使用索引之后的平均代价（即执行SQL的预估时间）。
- 通过select_index_advisory视图获取所有会话的WorkLoad建议
  此视图包含计算的指标和CREATE INDEX语句，展示当前位于index_advisory表中所有会话的索引编制建议。表t中列a和列b的索引编制建议显示如下：
```
postgres=# SELECT * FROM select_index_advisory;
 backend_pid |                                                             show_index_advisory
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------
       79370 | create index t_a_idx on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
       79370 | create index t_b_idx on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
(2 rows)
```
  在每个会话中，从同一建议的索引中受益的所有查询的结果将被组合起来，以便按每个建议的索引生成一组指标，此指标反映在名为benefit和gain的字段中，字段公式如下所示：
```
size = MAX(index size of all queries)
benefit = SUM(benefit of each query)
gain = SUM(benefit of each query) / MAX(index size of all queries)
```
  说明如果单条SQL建议同时创建多个索引，则index_advisory表中记录的new_cost为创建了多个索引之后的代价，而非创建某一个索引之后的代价。
  
  当对给定会话期间得到的不同建议索引的相对优势进行比较时，gain指标十分有用。gain值越大，从索引中得到的成本效益就越高，这可以抵消索引可能消耗的磁盘空间。

前提条件

索引推荐（index_adviser）组件

使用方法

使用示例