centos 6.8 + pgsql 9.6 + pg_hint_plan
来源:互联网 发布:ant maven知乎 编辑:程序博客网 时间:2024/04/30 18:49
下载对应版本的 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/
- centos 6.8 + pgsql 9.6 + pg_hint_plan
- centos 6.8 + pgsql 9.6 + dblink
- centos 6.8 + pgsql 9.6 + file_fdw
- centos 6.8 + pgsql 9.6 + pg_stat_statements
- centos 6.8 + pgsql 9.6 + pgagent
- centos 6.8 + pgsql 9.6 + mysql_fdw
- centos 6.8 + pgsql 9.6 + pg_buffercache
- centos 6.8 + pgsql 9.6 + adminpack
- centos 6.8 + pgsql 9.6 + pldebugger/pldbgapi
- centos 6.8 + yum + pgsql 9.6 的一些路径
- CentOS php pgsql 扩展
- centos php添加pgsql扩展
- centos php添加pgsql扩展
- centos 7.4 + yum + pgsql 10.1
- centos 7.4 + pgsql 10.1 + pg_amqp
- pgsql
- centos环境+pgsql+postgis+apache+php等插件的安装
- 【php+centos7+pgsql】centos下面配置php访问postgresql的方式
- 简介
- LINQ体验(5)——LINQ to SQL语句之Select/Distinct和Count/Sum/Min/Max/Avg
- Bug的严重程度(Blocker, Critical, Major, Minor/Trivial)和Bug修复优先级如何定义
- 【Windows】上帝模式(更详尽的更强大的控制面板)
- 用python做词云
- centos 6.8 + pgsql 9.6 + pg_hint_plan
- LINQ体验(6)——LINQ to SQL语句之Join和Order By
- linux文件及路径权限
- “FCoE全解系列”之关键特性和技术分析
- 从JDK源码看System.exit
- Maven error: Failed to execute goal on project : Could not resolve dependencies for project 解决办法
- 在window下安装深度学习环境遇到的坑
- 【第1080期】安息吧 REST API,GraphQL 长存
- LINQ体验(7)——LINQ to SQL语句之Group By/Having和Exists/In/Any/All/Contains