centos 6.8 + postgresql 9.6 + pg_hint_plan

下载对应版本的 pg_hint_plan
https://zh.osdn.net/projects/pghintplan/releases/

# 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文件夹下的帮助文档

参考:
https://osdn.net/projects/pghintplan/
https://zh.osdn.net/projects/pghintplan/releases/