pg_idx_advisor搭建及使用方法

pg_idx_advisor是一款根据查询计划而提供索引推荐的插件
本插件仅支持Postgresql 9.5版本,9.5以上版本无法使用
准备环境:
已搭建好的Postgresql 9.5
下载源码包,网址为:https://github.com/cohenjo/pg_idx_advisor
解压源码包:
[root@ysw4 桌面]# unzip pg_idx_advisor-master.zip
[root@ysw4 桌面]# cd pg_idx_advisor-master
准备开始编译,编译时要指定pg_config的绝对路径
[root@ysw4 pg_idx_advisor-master]# env PG_CONFIG=/path/to/pg_config make
[root@ysw4 pg_idx_advisor-master]# env PG_CONFIG=/path/to/pg_config make install
编译完成后还要讲动态库拷贝至$pglibdir
[root@ysw4 pg_idx_advisor-master]# cp pg_idx_advisor.so /path/to/share/extension/
然后使用psql登录数据库
postgres=# create extension pg_idx_advisor ;
postgres=#

使用样例

postgres=# explain select c21 from entities_113681518 where c11 = 200;
INFO:
** Plan with original indexes **

QUERY PLAN

Seq Scan on entities_113681518 (cost=0.00..10.00 rows=1 width=8)
Filter: (c11 = 200)

** Plan with hypothetical indexes **
read only, advice, index: create index on entities_113681518(c11)
Index Scan using :49154 on entities_113681518 (cost=0.00..8.02 rows=1 width=8)
Index Cond: (c11 = 200)
(7 rows)

Works with CTE as well:

postgres=# explain with vals as (select c21 from entities_113681518 where c11 = 200 ) select * from vals;
INFO:
** Plan with original indexes **

QUERY PLAN

CTE Scan on vals (cost=10.00..10.02 rows=1 width=8)
CTE vals
-> Seq Scan on entities_113681518 (cost=0.00..10.00 rows=1 width=8)
Filter: (c11 = 200)

** Plan with hypothetical indexes **
read only, advice, index: create index on entities_113681518(c11)
CTE Scan on vals (cost=8.02..8.04 rows=1 width=8)
CTE vals
-> Index Scan using :49156 on entities_113681518 (cost=0.00..8.02 rows=1 width=8)
Index Cond: (c11 = 200)
(11 rows)

文章浏览总量 1,005 次

要发表评论,您必须先登录