一、环境
名称版本操作系统Centos7.9Postgresql14.4pg_hint_plan1.4
二、下载链接
pg_hint_plan下载地址
三、pg_hint_plan用途
当判断数据库优化器生成的执行计划不是最优的时候，可以在不修改Sql的情况下（例如：等价改写、非等价改写）通过添加Hint，来影响执行计划的生成。
四、安装pg_hint_plan
Postgresql源码安装可以参考之前的博客分享： 《Postgresql学习01-PG14.2-Linux平台源码编译安装和配置开机自启》
[root@node0 pkg]# unzip pg_hint_plan-master.zip
[root@node0 pkg]# source /home/postgres/.bashrc
[root@node0 pkg]# cd pg_hint_plan-master/
[root@node0 pg_hint_plan-master]# ll
总用量 280
-rw-r--r-- 1 root root 1593 1月 19 14:12 COPYRIGHT
-rw-r--r-- 1 root root 1295 1月 19 14:12 COPYRIGHT.postgresql
-rw-r--r-- 1 root root 55461 1月 19 14:12 core.c
drwxr-xr-x 2 root root 22 1月 19 14:12 data
drwxr-xr-x 2 root root 123 1月 19 14:12 doc
drwxr-xr-x 2 root root 297 1月 19 14:12 expected
drwxr-xr-x 2 root root 27 1月 19 14:12 input
-rw-r--r-- 1 root root 2033 1月 19 14:12 Makefile
-rw-r--r-- 1 root root 12313 1月 19 14:12 make_join_rel.c
-rw-r--r-- 1 root root 521 1月 19 14:12 normalize_query.h
drwxr-xr-x 2 root root 27 1月 19 14:12 output
-rw-r--r-- 1 root root 683 1月 19 14:12 pg_hint_plan--1.4.sql
-rw-r--r-- 1 root root 124735 1月 19 14:12 pg_hint_plan.c
-rw-r--r-- 1 root root 102 1月 19 14:12 pg_hint_plan.control
-rw-r--r-- 1 root root 8342 1月 19 14:12 pg_stat_statements.c
-rw-r--r-- 1 root root 35842 1月 19 14:12 README.md
drwxr-xr-x 2 root root 33 1月 19 14:12 SPECS
drwxr-xr-x 2 root root 314 1月 19 14:12 sql
-rwxr-xr-x 1 root root 9109 1月 19 14:12 update_copied_funcs.pl
[root@node0 pg_hint_plan-master]# make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wf
ormat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fPIC -I. -I./ -I/opt/pg14-4/include/postgresql/server -I/opt/pg14-4/include/postgresql/internal -D_GNU_SOURCE -c -o pg_hint_plan.o pg_hint_plan.cgcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wf
ormat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fPIC pg_hint_plan.o -L/opt/pg14-4/lib -Wl,--as-needed -Wl,-rpath,'/opt/pg14-4/lib',--enable-new-dtags -shared -o pg_hint_plan.so
[root@node0 pg_hint_plan-master]# make install
/usr/bin/mkdir -p '/opt/pg14-4/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/pg14-4/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/pg14-4/lib/postgresql'
/usr/bin/install -c -m 644 .//pg_hint_plan.control '/opt/pg14-4/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//pg_hint_plan--*.sql '/opt/pg14-4/share/postgresql/extension/'
/usr/bin/install -c -m 755 pg_hint_plan.so '/opt/pg14-4/lib/postgresql/'
[root@node0 pg_hint_plan-master]# systemctl restart pg14-4.service
[root@node0 pg14-4]# su - postgres
上一次登录：一 7月 18 15:19:03 CST 2022
[postgres@node0 ~]$ psql
psql (14.4)
Type "help" for help.
postgres=# create extension pg_hint_plan;
CREATE EXTENSION
postgres=# load 'pg_hint_plan';
LOAD
postgres=# select * from pg_available_extensions where name = 'pg_hint_plan';
name | default_version | installed_version | comment
--------------+-----------------+-------------------+---------
pg_hint_plan | 1.4 | 1.4 |
(1 row)
postgres=# \d hint_plan.hints
Table "hint_plan.hints"
Column | Type | Collation | Nullable | Default
-------------------+---------+-----------+----------+---------------------------------------------
id | integer | | not null | nextval('hint_plan.hints_id_seq'::regclass)
norm_query_string | text | | not null |
application_name | text | | not null |
hints | text | | not null |
Indexes:
"hints_pkey" PRIMARY KEY, btree (id)
"hints_norm_and_app" UNIQUE, btree (norm_query_string, application_name)
1、注意点
（1）必须执行create extension pg_hint_plan;，不然找不到hint_plan.hints表。
（2）上面的load ‘pg_hint_plan’;是临时生效，如果重启数据库，就不生效了，建议用下面这个方法可以永久生效，修改postgresql.conf中的shared_preload_libraries参数，修改完记得重启数据库。
shared_preload_libraries = 'pg_hint_plan'
五、支持的Hint介绍
1、扫描方法
Hint描述SeqScan(table)强制对表进行顺序扫描TidScan(table)强制对表进行TID扫描。IndexScan(table[ index…])强制对表进行索引扫描。限制指定索引(如果有)。IndexOnlyScan(table[ index…])强制仅对表进行索引扫描。如果有，则限制为指定索引。如果索引扫描不可用，可以使用索引扫描。可用于PostgreSQL 9.2及更高版本。BitmapScan(table[ index…])强制对表进行位图扫描。如果有，恢复到指定的索引。IndexScanRegexp(table[ POSIX Regexp…]) IndexOnlyScanRegexp(table[ POSIX Regexp…]) BitmapScanRegexp(table[ POSIX Regexp…])强制索引扫描或仅索引扫描(适用于PostgreSQL 9.2及以后版本)或位图扫描表。限制为匹配指定POSIX正则表达式模式的索引NoSeqScan(table)强制不对表进行顺序扫描。NoTidScan(table)强制不对表进行TID扫描。NoIndexScan(table)强制对表不做索引扫描，只做索引扫描(适用于PostgreSQL 9.2及以后版本)。NoIndexOnlyScan(table)强制不在表上只做索引扫描。可用于PostgreSQL 9.2及更高版本。NoBitmapScan(table)强制不对表进行位图扫描。
2、连接方法
Hint描述NestLoop(table table[ table…])强制联接的嵌套循环由指定的表组成。HashJoin(table table[ table…])强制由指定表组成的连接的散列连接。MergeJoin(table table[ table…]))强制合并联接，因为联接由指定的表组成。NoNestLoop(table table[ table…]))对于由指定表组成的连接，强制不执行嵌套循环。NoHashJoin(table table[ table…]))强制不执行散列连接，因为连接由指定的表组成。NoMergeJoin(table table[ table…]))强制不进行合并连接，因为连接由指定的表组成。
3、连接顺序
Hint描述Leading(table table[ table…]) )强制指定的连接顺序。Leading(
4、行号修正
Hint描述Rows(table table[ table…] correction)) )纠正由指定表组成的连接的结果的行号。可用的校正方法有绝对(#)、加法(+)、减法(-)和乘法(*)。应该是strtod()可以读取的字符串。
5、并行查询的配置
Hint描述Parallel(table <# of workers> [soft|hard])强制或禁止指定表的并行执行。<# of worker >是期望的并行工作者数量，其中0表示抑制并行执行。如果第三个参数是软参数(默认值)，它只更改max_parallel_workers_per_gather，并将其他所有工作留给规划器。Hard意味着强制执行指定的工人数量。
6、GUC
Hint描述Set(GUC-param value)将规划器运行时的GUC参数设置为该值。
六、Sql执行计划操作符
操作符描述Seq Scan顺序扫描也就是全表扫描Index Scan索引扫描Bitmap Index Scan位图索引扫描Bitmap Heap Scan位图堆扫描Subquery Scan子查询Tid Scan行号扫描Function Scan函数扫描Nested Loop嵌套循环连接Merge Join归并连接Hash Join哈希连接Sort排序运算Unique唯一运算Limit限制返回的行数Aggregate聚合运算Group分组运算Append追加运算，union出现Materialize子查询SetOp交集INTERCECT、不包含EXCEPT出现
七、执行计划代价估算
1、准备测试环境
czg=# create table tenk1(id int,num int,name varchar(100),sex int);
CREATE TABLE
czg=# insert into tenk1 select generate_series(1,20902),generate_series(1,20902),chr(generate_series(19968,40869)),random()::int;
INSERT 0 20902
czg=# create table tenk2(id int,num int,name varchar(100),sex int);
CREATE TABLE
czg=# insert into tenk2 select generate_series(1,20902),generate_series(1,20902),chr(generate_series(19968,40869)),random()::int;
INSERT 0 20902
czg=# select * from tenk1 limit 10;
id | num | name | sex
----+-----+------+-----
1 | 1 | 一 | 1
2 | 2 | 丁 | 0
3 | 3 | 丂 | 0
4 | 4 | 七 | 1
5 | 5 | 丄 | 0
6 | 6 | 丅 | 1
7 | 7 | 丆 | 1
8 | 8 | 万 | 1
9 | 9 | 丈 | 0
10 | 10 | 三 | 0
(10 rows)
2、收集统计信息并查看
czg=# \x
Expanded display is on.
czg=# ANALYZE VERBOSE TENK1;
INFO: analyzing "public.tenk1"
INFO: "tenk1": scanned 113 of 113 pages, containing 20902 live rows and 0 dead rows; 20902 rows in sample, 20902 estimated total rows
ANALYZE
czg=# ANALYZE VERBOSE TENK2;
INFO: analyzing "public.tenk2"
INFO: "tenk2": scanned 113 of 113 pages, containing 20902 live rows and 0 dead rows; 20902 rows in sample, 20902 estimated total rows
ANALYZE
czg=# select * from pg_stats where tablename = 'tenk1';
-[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | tenk1
attname | name
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {丆,炿,飊,蔜,鎊,夶,墂,桲,愺,蕆,嚫,叱,絀,鷀,髊,蜑,掋,苵,妬,莪,匥,蚡,縛,竿,舸,淈,敮,寒,佫,謼,镮,晦,积,瀱,笺,薑,嵥,聙,踙,屫
,瞰,郀,焜,壏,踜,蒞,聊,陵,髏,倫,麦,痗,緬,縸,詉,鎳,輫,匹,胓,脐,虔,锲,紌,悫,氄,繅,赏,冼,授,丝,膆,孡,洮,齠,駼,紈,踓,诬,稀,夏,鄕,卨,綇,袨,珚,眏,偞,义,
絪,巊,淤,籞,跃,醩,颭,蒖,址,譸,僎,耔,阼}
correlation | 0.016811712
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-[ RECORD 2 ]----------+-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | tenk1
attname | sex
inherited | f
null_frac | 0
avg_width | 4
n_distinct | 2
most_common_vals | {0,1}
most_common_freqs | {0.5002392,0.49976078}
histogram_bounds |
correlation | 0.493415
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-[ RECORD 3 ]----------+-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | tenk1
attname | id
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,210,419,628,837,1046,1255,1464,1673,1882,2091,2300,2509,2718,2927,3136,3345,3554,3763,3972,4181,4390,4599,4808,5017,5
226,5435,5644,5853,6062,6271,6480,6689,6898,7107,7316,7525,7734,7943,8152,8361,8570,8779,8988,9197,9406,9615,9824,10033,10242,10451,10660,10869,1
1078,11287,11496,11705,11914,12123,12332,12541,12750,12959,13168,13377,13586,13795,14004,14213,14422,14631,14840,15049,15258,15467,15676,15885,16
094,16303,16512,16721,16930,17139,17348,17557,17766,17975,18184,18393,18602,18811,19020,19229,19438,19647,19856,20065,20274,20483,20692,20902}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-[ RECORD 4 ]----------+-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | tenk1
attname | num
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,210,419,628,837,1046,1255,1464,1673,1882,2091,2300,2509,2718,2927,3136,3345,3554,3763,3972,4181,4390,4599,4808,5017,5
226,5435,5644,5853,6062,6271,6480,6689,6898,7107,7316,7525,7734,7943,8152,8361,8570,8779,8988,9197,9406,9615,9824,10033,10242,10451,10660,10869,1
1078,11287,11496,11705,11914,12123,12332,12541,12750,12959,13168,13377,13586,13795,14004,14213,14422,14631,14840,15049,15258,15467,15676,15885,16
094,16303,16512,16721,16930,17139,17348,17557,17766,17975,18184,18393,18602,18811,19020,19229,19438,19647,19856,20065,20274,20483,20692,20902}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
czg=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
-[ RECORD 1 ]----
relpages | 113
reltuples | 20902
3、查看系统参数
czg=# \x
Expanded display is off.
czg=# select name,setting from pg_settings where name like '%_cost';
name | setting
-------------------------+---------
cpu_index_tuple_cost | 0.005
cpu_operator_cost | 0.0025
cpu_tuple_cost | 0.01
jit_above_cost | 100000
jit_inline_above_cost | 500000
jit_optimize_above_cost | 500000
parallel_setup_cost | 1000
parallel_tuple_cost | 0.1
random_page_cost | 4
seq_page_cost | 1
(10 rows)
4、Sql执行计划
czg=# EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..322.02 rows=20902 width=16)
(1 row)
5、代价估算公式
cost = (disk pages read * seq_page_cost) + (rows scanned * cpu_tuple_cost)
= 113 * 1.0 + 20902 * 0.01
= 113 + 209.02
= 322.02
估算结果和和执行计划中的一样。
八、如何看执行计划
1、两表连接查询
czg=# set enable_hashjoin = off;
SET
czg=# insert into tenk1 values(1,1,'一',1);
INSERT 0 1
czg=# explain (analyze,verbose,buffers) select * from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = '一';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..957.57 rows=1 width=32) (actual time=0.027..17.943 rows=2 loops=1)
Output: t1.id, t1.num, t1.name, t1.sex, t2.id, t2.num, t2.name, t2.sex
Join Filter: (t1.id = t2.id)
Rows Removed by Join Filter: 41802
Buffers: shared hit=339
-> Seq Scan on public.tenk1 t1 (cost=0.00..374.28 rows=1 width=16) (actual time=0.016..4.690 rows=2 loops=1)
Output: t1.id, t1.num, t1.name, t1.sex
Filter: ((t1.name)::text = '一'::text)
Rows Removed by Filter: 20901
Buffers: shared hit=113
-> Seq Scan on public.tenk2 t2 (cost=0.00..322.02 rows=20902 width=16) (actual time=0.005..2.774 rows=20902 loops=2)
Output: t2.id, t2.num, t2.name, t2.sex
Buffers: shared hit=226
Planning Time: 0.157 ms
Execution Time: 17.974 ms
(15 rows)
操作符执行顺序为： （1）先全表扫描tenk1表，根据name = '一’进行过滤，tenk1作为驱动表，驱动表滤出2条数据，过滤掉了20901条数据。
-> Seq Scan on public.tenk1 t1 (cost=0.00..374.28 rows=1 width=16) (actual time=0.016..4.690 rows=2 loops=1)
Output: t1.id, t1.num, t1.name, t1.sex
Filter: ((t1.name)::text = '一'::text)
Rows Removed by Filter: 20901
Buffers: shared hit=113
（2）再全表扫描tenk2表，tenk2作为被驱动表，loops=2，表示执行两次，总结为：驱动表滤出多少条数据，被驱动表执行多少次。
-> Seq Scan on public.tenk2 t2 (cost=0.00..322.02 rows=20902 width=16) (actual time=0.005..2.774 rows=20902 loops=2)
Output: t2.id, t2.num, t2.name, t2.sex
Buffers: shared hit=226
（3）最后将两个表根据等值条件t1.id = t2.id进行嵌套循环连接。
Nested Loop (cost=0.00..957.57 rows=1 width=32) (actual time=0.027..17.943 rows=2 loops=1)
Output: t1.id, t1.num, t1.name, t1.sex, t2.id, t2.num, t2.name, t2.sex
Join Filter: (t1.id = t2.id)
Rows Removed by Join Filter: 41802
Buffers: shared hit=339
2、多表连接查询
（1）准备测试环境
czg=# CREATE TABLE FLATING_T1(A1 INT,A2 INT);
CREATE TABLE
czg=# CREATE TABLE FLATING_T2(B1 INT,B2 INT);
CREATE TABLE
czg=# CREATE TABLE FLATING_T3(C1 INT,C2 INT);
CREATE TABLE
czg=# INSERT INTO FLATING_T1 SELECT generate_series(1,10),generate_series(1,10);
INSERT 0 10
czg=# INSERT INTO FLATING_T2 SELECT generate_series(1,100000),generate_series(1,100000);
INSERT 0 100000
czg=# INSERT INTO FLATING_T3 SELECT generate_series(1,100000),generate_series(1,100000);
INSERT 0 100000
czg=# ANALYZE VERBOSE FLATING_T1;
INFO: analyzing "public.flating_t1"
INFO: "flating_t1": scanned 1 of 1 pages, containing 10 live rows and 0 dead rows; 10 rows in sample, 10 estimated total rows
ANALYZE
czg=# ANALYZE VERBOSE FLATING_T2;
INFO: analyzing "public.flating_t2"
INFO: "flating_t2": scanned 443 of 443 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows
ANALYZE
czg=# ANALYZE VERBOSE FLATING_T3;
INFO: analyzing "public.flating_t3"
INFO: "flating_t3": scanned 443 of 443 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows
ANALYZE
（2）小实验
czg=# EXPLAIN SELECT COUNT(*) FROM FLATING_T1 LEFT JOIN FLATING_T2 CROSS JOIN FLATING_T3 ON A1=B1;
QUERY PLAN
------------------------------------------------------------------------------------------
Aggregate (cost=162515637.22..162515637.23 rows=1 width=8)
-> Hash Right Join (cost=1.23..162513137.22 rows=1000000 width=0)
Hash Cond: (flating_t2.b1 = flating_t1.a1)
-> Nested Loop (cost=0.00..125003136.00 rows=10000000000 width=4)
-> Seq Scan on flating_t2 (cost=0.00..1443.00 rows=100000 width=4)
-> Materialize (cost=0.00..1943.00 rows=100000 width=0)
-> Seq Scan on flating_t3 (cost=0.00..1443.00 rows=100000 width=0)
-> Hash (cost=1.10..1.10 rows=10 width=4)
-> Seq Scan on flating_t1 (cost=0.00..1.10 rows=10 width=4)
(9 rows)
操作符执行顺序为： flating_t3 》Materialize 》flating_t2 》Nested Loop 》 flating_t1 》Hash 》Hash Right Join 》 Aggregate
flating_t3有10万条数，flating_t2有10万条数，进行交叉连接（也就是笛卡尔积连接）返回的结果是100亿，耗时在这里了，之后再和 flating_t1的10条数据进行左外连接。
（3）如何优化呢
等价改写的方式就可以出来啦。 我们先让 flating_t1和flating_t2进行左外连接得出10条数据，再和flating_t3进行交叉连接，得出最终的100万条数据。
czg=# explain
czg-# with a as (select * from FLATING_T1 LEFT JOIN FLATING_T2 ON A1=B1)
czg-# SELECT COUNT(*) FROM a,FLATING_T3;
QUERY PLAN
------------------------------------------------------------------------------------------
Aggregate (cost=18262.35..18262.36 rows=1 width=8)
-> Nested Loop (cost=1.23..15762.35 rows=1000000 width=0)
-> Seq Scan on flating_t3 (cost=0.00..1443.00 rows=100000 width=0)
-> Materialize (cost=1.23..1819.37 rows=10 width=0)
-> Hash Right Join (cost=1.23..1819.32 rows=10 width=0)
Hash Cond: (flating_t2.b1 = flating_t1.a1)
-> Seq Scan on flating_t2 (cost=0.00..1443.00 rows=100000 width=4)
-> Hash (cost=1.10..1.10 rows=10 width=4)
-> Seq Scan on flating_t1 (cost=0.00..1.10 rows=10 width=4)
(9 rows)
czg=# with a as (select * from FLATING_T1 LEFT JOIN FLATING_T2 ON A1=B1)
SELECT COUNT(*) FROM a,FLATING_T3;
count
---------
1000000
(1 row)
九、通过Hint改变执行计划
1、原执行计划
czg=# set enable_hashjoin = on;
SET
czg=# explain (analyze,verbose,buffers) select * from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = '一';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=374.29..774.70 rows=1 width=32) (actual time=4.817..11.239 rows=2 loops=1)
Output: t1.id, t1.num, t1.name, t1.sex, t2.id, t2.num, t2.name, t2.sex
Hash Cond: (t2.id = t1.id)
Buffers: shared hit=226
-> Seq Scan on public.tenk2 t2 (cost=0.00..322.02 rows=20902 width=16) (actual time=0.012..2.577 rows=20902 loops=1)
Output: t2.id, t2.num, t2.name, t2.sex
Buffers: shared hit=113
-> Hash (cost=374.28..374.28 rows=1 width=16) (actual time=4.794..4.795 rows=2 loops=1)
Output: t1.id, t1.num, t1.name, t1.sex
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=113
-> Seq Scan on public.tenk1 t1 (cost=0.00..374.28 rows=1 width=16) (actual time=0.010..4.787 rows=2 loops=1)
Output: t1.id, t1.num, t1.name, t1.sex
Filter: ((t1.name)::text = '一'::text)
Rows Removed by Filter: 20901
Buffers: shared hit=113
Planning Time: 0.199 ms
Execution Time: 11.274 ms
(18 rows)
2、Hint提示后的执行计划
czg=# explain (analyze,verbose,buffers) select /*+NestLoop(t2 t1)*/* from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = '一';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..957.57 rows=1 width=32) (actual time=0.025..15.314 rows=2 loops=1)
Output: t1.id, t1.num, t1.name, t1.sex, t2.id, t2.num, t2.name, t2.sex
Join Filter: (t1.id = t2.id)
Rows Removed by Join Filter: 41802
Buffers: shared hit=339
-> Seq Scan on public.tenk1 t1 (cost=0.00..374.28 rows=1 width=16) (actual time=0.015..3.748 rows=2 loops=1)
Output: t1.id, t1.num, t1.name, t1.sex
Filter: ((t1.name)::text = '一'::text)
Rows Removed by Filter: 20901
Buffers: shared hit=113
-> Seq Scan on public.tenk2 t2 (cost=0.00..322.02 rows=20902 width=16) (actual time=0.005..2.469 rows=20902 loops=2)
Output: t2.id, t2.num, t2.name, t2.sex
Buffers: shared hit=226
Planning Time: 0.153 ms
Execution Time: 15.344 ms
(15 rows)
3、固定执行计划
由于生产环境中有些应用是不可以修改代码的，及时是加Hint也不可以，pg_hint_plan支持固定执行计划，下面我们来看一下：
（1）hint_plan.hints表介绍
czg=# \d hint_plan.hints
Table "hint_plan.hints"
Column | Type | Collation | Nullable | Default
-------------------+---------+-----------+----------+---------------------------------------------
id | integer | | not null | nextval('hint_plan.hints_id_seq'::regclass)
norm_query_string | text | | not null |
application_name | text | | not null |
hints | text | | not null |
Indexes:
"hints_pkey" PRIMARY KEY, btree (id)
"hints_norm_and_app" UNIQUE, btree (norm_query_string, application_name)
列名含义id唯一标识，自动增长的，我们不用维护。norm_query_string需要加Hint的Sql，查询中的常量必须替换为？。application_name应用的名字，为空字符串的话，表示任何的会话都可以。hints需要加的Hint。
（2）插入数据
czg=# insert into hint_plan.hints(norm_query_string,application_name,hints)
czg-# values ('explain (analyze,verbose,buffers) select from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = ?;','','NestLoop(t2 t1)');
INSERT 0 1
czg=# select * from hint_plan.hints;
id | norm_query_string | application_name | hints
----+-------------------------------------------------------------------------------------------------------+------------------+------------
-----
14 | explain (analyze,verbose,buffers) select from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = ?; | | NestLoop(t2
t1)
(1 row)
（3）开启参数pg_hint_plan.enable_hint_table
默认参数pg_hint_plan.enable_hint_table是关闭的
czg=# show pg_hint_plan.enable_hint_table;
pg_hint_plan.enable_hint_table
--------------------------------
off
(1 row)
当前会话生效：
czg=# set pg_hint_plan.enable_hint_table=on;
SET
永久生效，修改完此参数记得重启数据库：
czg=# alter system set pg_hint_plan.enable_hint_table=on;
ALTER SYSTEM
（4）pg_hint_plan参数介绍
参数名描述pg_hint_plan.enable_hintTrue 为启动 .pg_hint_planpg_hint_plan.enable_hint_tableTrue 为可以在表上加Hintpg_hint_plan.parse_messages指定提示解析错误的日志级别。有效值为.error ，warning ，notice ，info ，log ，debugpg_hint_plan.debug_print控制调试打印和详细信息。有效的值是off ，on ，detailed ，verbosepg_hint_plan.message_level指定调试打印的消息级别。有效值为error ，warning ，notice ，info ，log ，debug
默认为：
czg=# show pg_hint_plan.enable_hint;
pg_hint_plan.enable_hint
--------------------------
on
(1 row)
czg=# show pg_hint_plan.enable_hint_table;
pg_hint_plan.enable_hint_table
--------------------------------
off
(1 row)
czg=# show pg_hint_plan.parse_messages;
pg_hint_plan.parse_messages
-----------------------------
info
(1 row)
czg=# show pg_hint_plan.debug_print;
pg_hint_plan.debug_print
--------------------------
off
(1 row)
czg=# show pg_hint_plan.message_level ;
pg_hint_plan.message_level
----------------------------
log
(1 row)
（5）查看计划是否固定
执行计划固定成功
czg=# explain (analyze,verbose,buffers) select from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = '一';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4.33..1910.93 rows=5 width=0) (actual time=0.051..33.639 rows=5 loops=1)
Join Filter: (t1.id = t2.id)
Rows Removed by Join Filter: 104505
Buffers: shared hit=118
-> Seq Scan on public.tenk2 t2 (cost=0.00..322.02 rows=20902 width=4) (actual time=0.008..2.858 rows=20902 loops=1)
Output: t2.id, t2.num, t2.name, t2.sex
Buffers: shared hit=113
-> Materialize (cost=4.33..21.27 rows=5 width=4) (actual time=0.000..0.000 rows=5 loops=20902)
Output: t1.id
Buffers: shared hit=5
-> Bitmap Heap Scan on public.tenk1 t1 (cost=4.33..21.25 rows=5 width=4) (actual time=0.038..0.045 rows=5 loops=1)
Output: t1.id
Recheck Cond: ((t1.name)::text = '一'::text)
Heap Blocks: exact=3
Buffers: shared hit=5
-> Bitmap Index Scan on tenk1_index (cost=0.00..4.32 rows=5 width=0) (actual time=0.032..0.032 rows=5 loops=1)
Index Cond: ((t1.name)::text = '一'::text)
Buffers: shared hit=2
Query Identifier: 6941249274061524011
Planning Time: 0.159 ms
Execution Time: 33.671 ms
(21 rows)
（6）注意点
hint_plan.hints表中的Sql必须和实际执行的Sql一模一样，如果多一个空格，计划就变了，如下：
czg=# explain (analyze,verbose,buffers) select from tenk1 t1, tenk2 t2 where t1.id = t2.id and t1.name = '一';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=21.31..526.27 rows=5 width=0) (actual time=0.062..7.982 rows=5 loops=1)
Hash Cond: (t2.id = t1.id)
Buffers: shared hit=118
-> Seq Scan on public.tenk2 t2 (cost=0.00..322.02 rows=20902 width=4) (actual time=0.008..3.052 rows=20902 loops=1)
Output: t2.id, t2.num, t2.name, t2.sex
Buffers: shared hit=113
-> Hash (cost=21.25..21.25 rows=5 width=4) (actual time=0.046..0.048 rows=5 loops=1)
Output: t1.id
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=5
-> Bitmap Heap Scan on public.tenk1 t1 (cost=4.33..21.25 rows=5 width=4) (actual time=0.029..0.037 rows=5 loops=1)
Output: t1.id
Recheck Cond: ((t1.name)::text = '一'::text)
Heap Blocks: exact=3
Buffers: shared hit=5
-> Bitmap Index Scan on tenk1_index (cost=0.00..4.32 rows=5 width=0) (actual time=0.022..0.022 rows=5 loops=1)
Index Cond: ((t1.name)::text = '一'::text)
Buffers: shared hit=2
Query Identifier: 6941249274061524011
Planning Time: 0.144 ms
Execution Time: 8.017 ms
(21 rows)
还有一点hint_plan.hints表如果只存select后面的语句，不存explain，那我们在Psql中执行explain select语句，也是不能固定计划的，实际生产中使用这个固定计划的功能，就不能通过explain来确定计划是否固定，需要实际执行一下，通过快慢来判断是否生效。
推荐链接
您阅读本篇文章共花了：
发表评论