EnterpriseDB's index advisor

索引建议器随PPAS安装，是在给定负载下帮助确定在哪个表上的哪一列上建立索引以提高性能。

索引建议器和PPAS的查询计划器一起通过创建 【虚拟】 的索引计算执行开销。

有两种方式使用索引建议器分析SQL查询
 a 提供包含sql语句的索引文件，激活索引建议器。其会生成文本的create index语句。
 b 在edb-psql中提供sql语句，以让索引建议器分析。
其会尝试在增、删、查、改语句上提供建议

用explain时或比较有无相关索引时的执行计划开销，如果相关索引能提高性能，就输出有、无该索引的时的查询计划。

一    索引建议器组件
1
--/opt/PostgresPlus/9.1AS/lib/plugins/ plugin_index_advisor.so
/opt/PostgresPlus/9.2AS/lib/index_advisor.so
-- win:
-- plugin_index_advisor.dll
2
/opt/PostgresPlus/9.1AS/下有一个程序
pg_advise_index
win：
pg_advise_index.exe
3
:/opt/PostgresPlus/9.1AS/share/contrib
index_advisor.sql
其中创建下面的对象以查询索引建议：
create table index_advisor_log(
create or replace function show_index_recommendations
create or replace view index_recommendations as
上面的对象需要创建者激活 索引建议器 的用户能够访问、查询到的地方
如果找不到表index_advisor_log，就把建议的索引创建在一个临时的同名表中。

二 索引建议器配置
为当前session使用不需要配置，为多个session使用需要创建表index_advisor_log。
 a 配置search_path=index_advisor_in_schema, and_old_values
 b psql# \i /xxx/index_advisor.sql
非超级用户和这些东西的属主的用户 需要 赋予在表index_advisor_log表上的增、删、查功能。
和试图index_recommendations上的查权限

$ edb-psql -d edb -U enterprisedb
edb-psql (9.0.0.6)
Type "help" for help.
edb=# CREATE SCHEMA ia;
CREATE SCHEMA
edb=# SET search_path TO ia;
SET
edb=# \i /opt/PostgresPlus/9.0AS/share/contrib/index_advisor.sql
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW
edb=# GRANT USAGE ON SCHEMA ia TO ia_user;
GRANT
edb=# GRANT SELECT, INSERT, DELETE ON index_advisor_log TO ia_user;
GRANT
edb=# GRANT SELECT ON index_recommendations TO ia_user;
GRANT

三 使用索引建议器
CREATE TABLE t( a INT, b INT );
INSERT INTO t SELECT s, 99999 - s FROM generate_series(0,99999) AS s;
ANALYZE t;

1
使用pg_advise_index工具

1）
建立workload.sql，内容如下：
SELECT * FROM t WHERE a = 500;
SELECT * FROM t WHERE b < 1000;

2）
$ ./pg_advise_index -d edb -U enterprisedb -s 100M -o advisory.sql workload.sql
poolsize = 102400 KB
load workload from file 'workload.sql'
Analyzing queries .. done.
size = 2624 KB, benefit = 1684.720000
size = 2624 KB, benefit = 1650.610000
/* 1. t("a"): size=2624 KB, benefit=1684.72 */
/* 2. t("b"): size=2624 KB, benefit=1650.61 */
/* Total size = 5248KB */

3）
创建生成的索引
$ edb-psql -d edb -h localhost -U enterprisedb -e -f advisory.sql

2
在psql中索引建议器

1）
加载索引建议器插件
edb=# LOAD '$libdir/plugins/plugin_index_advisor';
建议器发现所有这个会话中执行的sql并给出建议，如果只想分析而不想执行这些sql语句，可以加EXPLAIN。

2）
在psql里执行
edb=# 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..8.28 rows=1 width=8)
   One-Time Filter: '===[ HYPOTHETICAL PLAN ]==='::text
   ->  Index Scan using "<hypothetical-index>:3" on t  (cost=0.00..8.28 rows=1 w
idth=8)
         Index Cond: (a = 100)
(6 rows)

对比没有使用索引建议器的：
edb=# 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)
(2 rows)

3）
可以用下面的命令停止、启动索引建议器插件功能
edb=# show index_advisor.enabled;
edb=# set index_advisor.enabled= off;
edb=# set index_advisor.enabled= on;

4）
查看索引建议器的建议。
有三种方式：
 A 运行函数 show_index_recommendations
 B 查看表 index_advisor_log
 C 查询视图 index_recommendations
A.
本会话中执行下面函数 看建议的索引
edb=# select show_index_recommendations(null);
B
查看表 index_advisor_log 看相关情况
其中列benefit表示：Calculated benefit of the index for this query
benefit = (normal execution cost) - (execution cost with hypothetical index)
C
查看backend_pid，和建议的索引创建语句。
edb=# select * from index_recommendations;
 backend_pid |                                        show_index_recommendations
                                        
-------------+------------------------------------------------------------------
----------------------------------------
        4198 | create index idx_t_a on enterprisedb.t(a);/* size: 2624 KB, benef
it: 4707.28, gain: 1.79393321711843 */

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)
gain 在比较不同的索引建议哪个更好时有用，用于表示单位磁盘消耗取得的收益。

四 限制
1 不会建议多列索引
2 索引建议器忽略任何where子句里的计算式，因此建议索引里的列也不会是如何表达式。
3 不考虑继承inheritance，如果在父表上做建议，不会给出任何子表上的建议索引。
4 恢复pg_dump备份的带有index_advisor_log的备份文件时，或者在index_advisor_log表里有建议索引的表时，
可能会有"broken links"，因为表index_advisor_log里的行引用的表的OID改变。
如果需要正常显示已恢复的数据库里表index_advisor_log里的以前的建议索引，需要
UPDATE index_advisor_log SET reloid = new_oid WHERE reloid = old_oid;