pg_hint_plan学习笔记

下载 pg_hint_plan

https://github.com/ossc-db/pg_hint_plan/releases

上传，解压 pg_hint_plan

pg_hint_plan-REL12_1_3_7.zip

编译

/root/software/pg_hint_plan-REL13_1_3_7
#export PATH=/opt/pgsql/bin:$PATH
# gmake
# gmake install

# gmake
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/pg12/include/postgresql/server -I/opt/pg12/include/postgresql/internal  -D_GNU_SOURCE   -c -o pg_hint_plan.o pg_hint_plan.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC pg_hint_plan.o -L/opt/pg12/lib   -Wl,--as-needed -Wl,-rpath,'/opt/pg12/lib',--enable-new-dtags -Wl,--build-id  -shared -o pg_hint_plan.so

# gmake install
/usr/bin/mkdir -p '/opt/pg12/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/pg12/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/pg12/lib/postgresql'
/usr/bin/install -c -m 644 .//pg_hint_plan.control '/opt/pg12/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//pg_hint_plan--*.sql  '/opt/pg12/share/postgresql/extension/'
/usr/bin/install -c -m 755  pg_hint_plan.so '/opt/pg12/lib/postgresql/'

配置

$ vi $PGDATA/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

$ pg_ctl stop
$ pg_ctl start
$ psql test2
test2=# create extension pg_hint_plan;
CREATE EXTENSION

test2=# \dx
                                                       List of installed extensions
        Name        | Version |   Schema   |                                          Description                                
          
--------------------+---------+------------+-------------------------------------------------------------------------------------
 pg_hint_plan       | 1.3.7   | hint_plan  | 

测试使用hint

test2=# create table test_hint(id int,c varchar(100));
CREATE TABLE
test2=# insert into test_hint select i,'test'||i from generate_series(1,10000) i;
INSERT 0 10000
test2=# 

test2=# create index idx_test_hint_id on test_hint(id);
CREATE INDEX

test2=# ANALYZE VERBOSE test_hint;
INFO:  analyzing "public.test_hint"
INFO:  "test_hint": scanned 55 of 55 pages, containing 10000 live rows and 0 dead rows; 10000 rows in sample, 10000 estimated total rows


---默认使用索引扫描
test2=# explain analyze select * from test_hint where id=10;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_test_hint_id on test_hint  (cost=0.29..8.30 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=1)
   Index Cond: (id = 10)
 Planning Time: 0.111 ms
 Execution Time: 0.024 ms
(4 rows)

--使用hint，强制走seqscan
test2=# explain analyze select /*+seqscan(t) */ * from test_hint t where id=10;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on test_hint t  (cost=0.00..180.00 rows=1 width=12) (actual time=0.022..2.691 rows=1 loops=1)
   Filter: (id = 10)
   Rows Removed by Filter: 9999
 Planning Time: 0.311 ms
 Execution Time: 2.712 ms
(5 rows)

--使用hint table
INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
    VALUES (
        'explain analyze select * from test_hint t where id=?;',
        '',
        'SeqScan(t)'
    );

test2=# select * from hint_plan.hints;
 id |                   norm_query_string                   | application_name |   hints    
----+-------------------------------------------------------+------------------+------------
  1 | explain analyze select * from test_hint t where id=?; |                  | SeqScan(t)
(1 row)

test2=# explain analyze select * from test_hint t where id=10;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on test_hint t  (cost=0.00..180.00 rows=1 width=12) (actual time=0.029..2.816 rows=1 loops=1)
   Filter: (id = 10)
   Rows Removed by Filter: 9999
 Planning Time: 0.184 ms
 Execution Time: 2.837 ms
(5 rows)

pg_hint_plan提供的hint

pg_hint_plan文档
pg_hint_plan src/doc/hint_list.html
pg_hint_plan src/doc/pg_hint_plan.html

The hint table

The following example shows how to operate with the hint table.

postgres=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
postgres-# VALUES (
postgres(# ‘EXPLAIN SELECT * FROM t1 WHERE t1.id = ?;’,
postgres(# ‘’,
postgres(# ‘SeqScan(t1)’
postgres(# );
INSERT 0 1

Hints are described in a comment in a special form in the above section. This is inconvenient in the case where queries cannot be edited. In the case hints can be placed in a special table named "hint_plan.hints". The table consists of the following columns.

The following example shows how to operate with the hint table.

postgres=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
postgres-#     VALUES (
postgres(#         'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
postgres(#         '',
postgres(#         'SeqScan(t1)'
postgres(#     );
INSERT 0 1
postgres=# UPDATE hint_plan.hints
postgres-#    SET hints = 'IndexScan(t1)'
postgres-#  WHERE id = 1;
UPDATE 1
postgres=# DELETE FROM hint_plan.hints
postgres-#  WHERE id = 1;
DELETE 1
postgres=#

The hint table is owned by the creator user and having the default previledges at the time of creation. during CREATE EXTENSION. Table hints are prioritized than comment hits.