centos6.8版本服务器pgsql9.6+pg_hint_plan的使用

下载对应版本的 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文件夹下的帮助文档