下载对应版本的 pg_hint_plan
# tar xzvf pg_hint_plan96-1.2.2.tar.gz
# cd pg_hint_plan96-1.2.2.tar.gz
# source /var/lib/pgsql/.bash_profile
# make
# make install
检查这几个文件
/usr/pgsql-9.6/lib/dblink.so
/usr/pgsql-9.6/share/extension/dblink–1.0–1.1.sql
/usr/pgsql-9.6/share/extension/dblink–1.1–1.2.sql
/usr/pgsql-9.6/share/extension/dblink–1.2.sql
/usr/pgsql-9.6/share/extension/dblink.control
/usr/pgsql-9.6/share/extension/dblink–unpackaged–1.0.sql
添加启动参数
$vi ./postgresql.conf
shared_preload_libraries = ‘pg_hint_plan’
pg_hint_plan.enable_hint = on
pg_hint_plan.enable_hint_table = on
pg_hint_plan.debug_print = on
pg_hint_plan.message_level = log
此处请注意，postgresql.auto.conf 会覆盖 postgresql.conf 相同的变量设置。
排查这个问题花费了我半天时间,可以通过查询 pg_settings 来获取参数的设置来源。
select ps.sourcefile,
ps.*
from pg_settings ps
where 1=1
and ps.name like ‘%shared_preload_libraries%’
;
重启PG后就可以使用pg_hint_plan了
$pg_ctl -D $PGDATA restart
============================
Basically pg_hint_plan does not requires CREATE EXTENSION.
Simplly loading it by LOAD command will activate it and of course you can load it globally by setting shared_preload_libraries
in postgresql.conf. Or you might be interested in ALTER USER SET/ALTER DATABASE SET for automatic loading for specific sessions.
postgres=# LOAD ‘pg_hint_plan’;
LOAD
postgres=#
Do CREATE EXTENSION and SET pg_hint_plan.enable_hint_tables TO on if you are planning to hint tables.
============================
select *
from pg_available_extension_versions
where 1=1
and name like ‘%hint_plan%’
;
select *
from pg_extension pc
where 1=1
;
create extension pg_hint_plan
;
会在当前数据库下创建一个 hint_plan 的 schema
SeqScan(t)
IndexScan(t)
/*+
SeqScan(t)
*/
explain
select *
from t_gather_pgsql_space_database t
where 1=1
and t.db_name =’xxxxx’
;
pg_hint_plan 的详细使用请参考tar.gz解压后的doc文件夹下的帮助文档