1、安装pg_hint_plan扩展
psql
CREATE EXTENSION pg_hint_plan;2、加载扩展
vi /date/halo/postgresql.conf
将pg_hint_plan加到参数shared_preload_libraries中，修改配置好参数需重启。
可以查询到插件配置表示安装成功：
select name, setting from pg_settings where name like '%pg_hint_plan%';
name | setting
--------------------------------+---------
pg_hint_plan.debug_print | off
pg_hint_plan.enable_hint | on
pg_hint_plan.enable_hint_table | off
pg_hint_plan.hints_anywhere | off
pg_hint_plan.message_level | log
pg_hint_plan.parse_messages | info3、pg_hint_plan参数介绍
4、使用hint制定执行计划
创建测试表
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(50),
email VARCHAR(100)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
total_amount NUMERIC(10, 2),
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
INSERT INTO customers (customer_name, email) VALUES ('John Doe', 'john.doe@example.com');
INSERT INTO customers (customer_name, email) VALUES ('Jane Smith', 'jane.smith@example.com');
INSERT INTO customers (customer_name, email) VALUES ('Alice Johnson', 'alice.johnson@example.com');
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, '2023-01-01', 100.50);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, '2023-01-02', 200.75);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, '2023-01-03', 150.20);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, '2023-01-04', 300.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, '2023-01-05', 175.50);不使用hint的执行计划，执行计划中使用的是hash的方式连接
halo0root=# explain analyze select * from customers a,orders b where a.customer_id=b.customer_id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=14.95..42.19 rows=1360 width=372) (actual time=0.043..0.050 rows=5 loops=1)
Hash Cond: (b.customer_id = a.customer_id)
-> Seq Scan on orders b (cost=0.00..23.60 rows=1360 width=32) (actual time=0.003..0.004 rows=5 loops=1)
-> Hash (cost=12.20..12.20 rows=220 width=340) (actual time=0.015..0.016 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on customers a (cost=0.00..12.20 rows=220 width=340) (actual time=0.001..0.003 rows=3 loops=1)
Planning Time: 0.518 ms
Execution Time: 0.130 ms
(8 rows)使用hint让SQL使用merge的方式连接
halo0root=# explain analyze select /*+ mergejoin(a b)*/* from customers a,orders b where a.customer_id=b.customer_id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Merge Join (cost=115.14..136.64 rows=1360 width=372) (actual time=0.022..0.025 rows=5 loops=1)
Merge Cond: (a.customer_id = b.customer_id)
-> Sort (cost=20.76..21.31 rows=220 width=340) (actual time=0.013..0.014 rows=3 loops=1)
Sort Key: a.customer_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on customers a (cost=0.00..12.20 rows=220 width=340) (actual time=0.007..0.008 rows=3 loops=1)
-> Sort (cost=94.38..97.78 rows=1360 width=32) (actual time=0.004..0.004 rows=5 loops=1)
Sort Key: b.customer_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on orders b (cost=0.00..23.60 rows=1360 width=32) (actual time=0.001..0.002 rows=5 loops=1)
Planning Time: 0.164 ms
Execution Time: 0.076 ms
(12 rows)5、将制定的执行计划固定到hint_plan.hints
当前会话级会开启hint_table，也可设置用户级
ALTER ROLE hint IN DATABASE halo0root SET pg_hint_plan.parse_messages=log;
ALTER ROLE hint IN DATABASE halo0root SET pg_hint_plan.enable_hint_table=on;[halo@WY ~]$ psql -U hint
halo0root=# set pg_hint_plan.enable_hint = on; --启用SQL查询使用Hint Plan注释
SET
halo0root=# set pg_hint_plan.enable_hint_table = on; --启用表级别使用Hint Plan注释
SET
halo0root=# set pg_hint_plan.debug_print = verbose; --打印日志信息
SET
halo0root=# set pg_hint_plan.message_level = notice; --设置信息的级别
SET
halo0root=# explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <='200' and b.order_date <='2023-01-05';
NOTICE: pg_hint_plan[qno=0x1]: planner: enable_hint=1, hint_inhibit_level=1
NOTICE: pg_hint_plan[qno=0x1]: planner: no valid hint
NOTICE: pg_hint_plan[qno=0x3]: no match found in table: application name = "psql", normalized_query="explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <=? and b.order_date <=?;"
NOTICE: hints in comment="(none)", query="explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <='200' and b.order_date <='2023-01-05';", debug_query_string="explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <='200' and b.order_date <='2023-01-05';"
NOTICE: pg_hint_plan[qno=0x3]: planner: enable_hint=1, hint_inhibit_level=1
NOTICE: pg_hint_plan[qno=0x3]: planner: no valid hint
NOTICE: pg_hint_plan[qno=0x5]: no match found in table: application name = "psql", normalized_query="explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <=? and b.order_date <=?;"
NOTICE: hints in comment="(none)", query="explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <='200' and b.order_date <='2023-01-05';", debug_query_string="explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <='200' and b.order_date <='2023-01-05';"
NOTICE: pg_hint_plan[qno=0x3]: planner: no valid hint
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=14.95..45.76 rows=151 width=372) (actual time=0.046..0.057 rows=3 loops=1)
Hash Cond: (b.customer_id = a.customer_id)
-> Seq Scan on orders b (cost=0.00..30.40 rows=151 width=32) (actual time=0.018..0.019 rows=3 loops=1)
Filter: ((total_amount <= '200'::numeric) AND ((order_date)::timestamp without time zone <= '2023-01-05 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 2
-> Hash (cost=12.20..12.20 rows=220 width=340) (actual time=0.013..0.013 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on customers a (cost=0.00..12.20 rows=220 width=340) (actual time=0.001..0.003 rows=3 loops=1)
Planning Time: 0.419 ms
Execution Time: 0.097 ms
(10 rows)将打印日志输出SQL语句插入hint_plan.hints
halo0root=# insert into hint_plan.hints ( norm_query_string , application_name , hints ) values ($$explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <=? and b.order_date <=?;$$,'*','mergejoin(a b)');
NOTICE: pg_hint_plan[qno=0x12]: no match found in table: application name = "psql", normalized_query="insert into hint_plan.hints ( norm_query_string , application_name , hints ) values (?,?,?);"
NOTICE: hints in comment="(none)", query="insert into hint_plan.hints ( norm_query_string , application_name , hints ) values ($$explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <=? and b.order_date <=?;$$,'*','mergejoin(a b)');", debug_query_string="insert into hint_plan.hints ( norm_query_string , application_name , hints ) values ($$explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <=? and b.order_date <=?;$$,'*','mergejoin(a b)');"
NOTICE: pg_hint_plan[qno=0x14]: no match found in table: application name = "psql", normalized_query="insert into hint_plan.hints ( norm_query_string , application_name , hints ) values (?,?,?);"
NOTICE: hints in comment="(none)", query="insert into hint_plan.hints ( norm_query_string , application_name , hints ) values ($$explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <=? and b.order_date <=?;$$,'*','mergejoin(a b)');", debug_query_string="insert into hint_plan.hints ( norm_query_string , application_name , hints ) values ($$explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <=? and b.order_date <=?;$$,'*','mergejoin(a b)');"
NOTICE: pg_hint_plan[qno=0x12]: planner: no valid hint
INSERT 0 1再次执行SQL，执行计划会先匹配hint表固定的计划执行
halo0root=# explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <='200' and b.order_date <='2023-01-05';
NOTICE: pg_hint_plan[qno=0x16]: hints from table: "mergejoin(a b)": normalized_query="explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <=? and b.order_date <=?;", application name ="psql"
NOTICE: pg_hint_plan[qno=0x18]: hints from table: "mergejoin(a b)": normalized_query="explain analyze select * from customers a,orders b where a.customer_id=b.customer_id and b.total_amount <=? and b.order_date <=?;", application name ="psql"
NOTICE: pg_hint_plan[qno=0x16]: planner
NOTICE: pg_hint_plan[qno=0x16]: setup_hint_enforcement no hint applied: relation=19140(customers), inhparent=0, current_hint=0x1eaadf0, hint_inhibit_level=0, scanmask=0x1f
NOTICE: pg_hint_plan[qno=0x16]: setup_hint_enforcement no hint applied: relation=19147(orders), inhparent=0, current_hint=0x1eaadf0, hint_inhibit_level=0, scanmask=0x1f
NOTICE: pg_hint_plan[qno=0x16]: HintStateDump: {used hints:MergeJoin(a b)}, {not used hints:(none)}, {duplicate hints:(none)}, {error hints:(none)}
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=56.62..59.99 rows=151 width=372) (actual time=0.055..0.057 rows=3 loops=1)
Merge Cond: (a.customer_id = b.customer_id)
-> Sort (cost=20.76..21.31 rows=220 width=340) (actual time=0.013..0.013 rows=3 loops=1)
Sort Key: a.customer_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on customers a (cost=0.00..12.20 rows=220 width=340) (actual time=0.006..0.007 rows=3 loops=1)
-> Sort (cost=35.86..36.24 rows=151 width=32) (actual time=0.038..0.039 rows=3 loops=1)
Sort Key: b.customer_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on orders b (cost=0.00..30.40 rows=151 width=32) (actual time=0.011..0.012 rows=3 loops=1)
Filter: ((total_amount <= '200'::numeric) AND ((order_date)::timestamp without time zone <= '2023-01-05 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 2
Planning Time: 0.408 ms
Execution Time: 0.094 ms
注：插入到hint表里面的SQL语句常量需要替换成'?'，Oracle模式下application_name不能为空，可以设置为'*'，同时SQL格式也要一致；否则匹配不上，既不会生效。
最后修改时间：2024-03-06 14:14:55
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
