标签
PostgreSQL , PPAS , enterprisedb , 索引推荐
背景
PostgreSQL商用版本EnterpriseDB，内置了索引推荐功能，原理与这里描述类似。
《PostgreSQL SQL自动优化案例 - 极简，自动推荐索引》
目前仅支持BTREE索引的（单列或多列）推荐，（有一些限制，暂时不支持继承表的推荐，暂时不支持表达式索引的推荐）。
索引推荐用法
1、创建推荐存储表与推荐查阅函数。
-- 在需要用到索引推荐的数据库中，安装索引推荐用到的表、函数、视图
-- 调用 index_advisor.sql
psql -f $PGHOME/share/contrib/index_advisor.sql
psql.bin:/opt/edb/as10/share/contrib/index_advisor.sql:22: ERROR: relation "index_advisor_log" already exists
psql.bin:/opt/edb/as10/share/contrib/index_advisor.sql:25: ERROR: relation "ia_reloid" already exists
psql.bin:/opt/edb/as10/share/contrib/index_advisor.sql:28: ERROR: relation "ia_backend_pid" already exists
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW
2、加载推荐模块
-- 在需要用到索引推荐的会话中加载推荐模块
load 'index_advisor';
或者可以设置为自动加载。
vi $PGDATA/postgresql.conf
shared_preload_libraries = 'index_advisor'
# - Other Defaults -
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#session_preload_libraries = ''
#oracle_home ='' # path to the Oracle home directory;
# only used by OCI Dblink; defaults
# to ORACLE_HOME environment variable.
3、设置会话参数，选择是否需要推荐。如果需要推荐，则会自动对这个会话接下来的SQL进行索引推荐（如果多次执行，并被推荐的话，SQL都会被记录在表中）。
postgres=# set index_advisor.enabled TO on;
SET
设置后，这个会话的所有QUERY都会被推荐模块进行计算。并将需要推荐的SQL记录下来，包括PID，索引列，索引估算大小，成本估算等。
postgres=# select * from pgbench_history where bid=2;
tid | bid | aid | delta | mtime | filler
------+-----+---------+-------+---------------------------+--------
444 | 2 | 4438685 | -2029 | 12-JAN-18 20:46:43.07816 |
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
32898
(1 row)
postgres=# select * from index_advisor_log ;
reloid | relname | attrs | benefit | index_size | backend_pid | timestamp
--------+------------------+-------+---------+------------+-------------+----------------------------------
16397 | pgbench_history | {2} | 175955 | 1260456 | 32898 | 14-JAN-18 21:32:19.564707 +08:00
4、查阅推荐。
postgres=# select * from show_index_recommendations(32898);
show_index_recommendations
--------------------------------------------------------------------------------------------------------------------------------------
create index idx_pgbench_history_bid on public.pgbench_history(bid);/* size: 1260456 KB, benefit: 175955, gain: 0.139596676718584 */
(1 row)
或
postgres=# select * from show_index_recommendations();
show_index_recommendations
--------------------------------------------------------------------------------------------------------------------------------------
create index idx_pgbench_history_bid on public.pgbench_history(bid);/* size: 1260456 KB, benefit: 175955, gain: 0.139596676718584 */
(1 row)
5、查看虚拟索引执行计划(HYPOTHETICAL PLAN 表示包含虚拟索引时的执行计划)。
postgres=# explain select * from pgbench_history where bid=2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Gather (cost=1000.00..455329.29 rows=470413 width=116)
Workers Planned: 6
-> Parallel Seq Scan on pgbench_history (cost=0.00..407287.99 rows=78402 width=116)
Filter: (bid = 2)
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: '===[ HYPOTHETICAL PLAN ]==='::text
-> Bitmap Heap Scan on pgbench_history (cost=5339.17..279373.83 rows=470413 width=116)
Recheck Cond: (bid = 2)
-> Bitmap Index Scan on "<hypothetical-index>:8" (cost=0.00..5221.56 rows=470413 width=0)
Index Cond: (bid = 2)
(10 rows)
6、关闭推荐。
postgres=# set index_advisor.enabled TO off;
SET
关闭推荐后，不会使用虚拟索引，也不会再计算是否需要推荐索引。
postgres=# explain select * from pgbench_history where bid=2;
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather (cost=1000.00..455329.29 rows=470413 width=116)
Workers Planned: 6
-> Parallel Seq Scan on pgbench_history (cost=0.00..407287.99 rows=78402 width=116)
Filter: (bid = 2)
(4 rows)
7、解读推荐索引信息。
postgres=# select * from show_index_recommendations();
show_index_recommendations
--------------------------------------------------------------------------------------------------------------------------------------
create index idx_pgbench_history_bid on public.pgbench_history(bid);/* size: 1260456 KB, benefit: 175955, gain: 0.139596676718584 */
(1 row)
size，这个索引占用的空间，对应index_advisor_log.index_size。
benefit，这个推荐索引涉及的index_advisor_log.benefit，即加上索引后，相比没有索引时的explain成本估算COST差异。
gain，index_advisor_log -> sum(benefit)/greatest(size(s))，即多次推荐的统计，获得了多少提升空间，越大，表示提升效果越好。
如何让普通用户支持推荐
1、允许普通用户加载推荐模块
需要将$PGHOME/lib/index_advisor.so拷贝到$PGHOME/lib/plugins/index_advisor.so。普通用户才能LOAD。
2、允许普通用户增删改查推荐存储表
-
Grant SELECT and INSERT privileges on the index_advisor_log table to allow a user to invoke Index Advisor.
-
Grant DELETE privileges on the index_advisor_log table to allow the specified user to delete the table contents.
-
Grant SELECT privilege on the index_recommendations view.
推荐的配置
1、在template1模板库、以及已有的数据库（如果这些库需要使用索引推荐功能的话）中安装index_advisor.sql
psql template1 -f $PGHOME/share/contrib/index_advisor.sql
psql template1 <<EOF
grant select,insert,delete on index_advisor_log to public;
grant select on index_recommendations to public;
EOF
2、配置postgresql.conf，默认加载模块
vi $PGDATA/postgresql.conf
shared_preload_libraries = 'index_advisor'
3、配置postgresql.conf，index_advisor.enabled=off，默认关闭计算
vi $PGDATA/postgresql.conf
index_advisor.enabled=off
4、当用户需要用到推荐模块时，只要打开index_advisor.enabled参数即可。
psql
psql.bin (10.1.5)
Type "help" for help.
postgres=# show index_advisor.enabled ;
index_advisor.enabled
-----------------------
off
(1 row)
postgres=# explain select * from pgbench_history where bid=2;
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather (cost=1000.00..455329.29 rows=470413 width=116)
Workers Planned: 6
-> Parallel Seq Scan on pgbench_history (cost=0.00..407287.99 rows=78402 width=116)
Filter: (bid = 2)
(4 rows)
postgres=# set index_advisor.enabled =on;
SET
postgres=# explain select * from pgbench_history where bid=2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Gather (cost=1000.00..455329.29 rows=470413 width=116)
Workers Planned: 6
-> Parallel Seq Scan on pgbench_history (cost=0.00..407287.99 rows=78402 width=116)
Filter: (bid = 2)
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: '===[ HYPOTHETICAL PLAN ]==='::text
-> Bitmap Heap Scan on pgbench_history (cost=5339.17..279373.83 rows=470413 width=116)
Recheck Cond: (bid = 2)
-> Bitmap Index Scan on "<hypothetical-index>:2" (cost=0.00..5221.56 rows=470413 width=0)
Index Cond: (bid = 2)
(10 rows)
postgres=# set index_advisor.enabled =off;
SET