Postgresql学习04-pg_hint_plan安装及使用、Sql优化小知识_pg hint

一、环境

二、下载链接

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

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'
1

五、支持的Hint介绍

1、扫描方法

2、连接方法

3、连接顺序

4、行号修正

5、并行查询的配置

6、GUC

六、Sql执行计划操作符

七、执行计划代价估算

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4、Sql执行计划

czg=# EXPLAIN SELECT * FROM tenk1;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..322.02 rows=20902 width=16)
(1 row)
1
2
3
4
5

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
2
3
4

估算结果和和执行计划中的一样。

八、如何看执行计划

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

操作符执行顺序为：
（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
1
2
3
4
5

（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
1
2
3

（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
1
2
3
4
5

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

（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)
1
2
3
4
5
6
7
8
9
10
11
12
13

操作符执行顺序为：
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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

九、通过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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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)
1
2
3
4
5
6
7
8
9
10
11

（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)
1
2
3
4
5
6
7
8
9
10
11
12

（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)
1
2
3
4
5

当前会话生效：

czg=# set pg_hint_plan.enable_hint_table=on;
SET
1
2

永久生效，修改完此参数记得重启数据库：

czg=# alter system  set pg_hint_plan.enable_hint_table=on;
ALTER SYSTEM
1
2

（4）pg_hint_plan参数介绍

默认为：

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

（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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

（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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

还有一点hint_plan.hints表如果只存select后面的语句，不存explain，那我们在Psql中执行explain select语句，也是不能固定计划的，实际生产中使用这个固定计划的功能，就不能通过explain来确定计划是否固定，需要实际执行一下，通过快慢来判断是否生效。