Halo数据库—pg_hint_plan安装及使用

前言

    我们在日常开发中会遇到一些执行优化器没有生成最优的执行计划的情况，此时我们的DBA或者开发者希望在SQL中有一种可以手工干预执行计划生成的方式。如果是做过Oracle的小伙伴肯定会想起hint的功能，当然了我们的Halo数据库也是支持该功能的，只需要我们安装一下pg_hint_plan插件扩展即可。今天来跟大家简单分享一下pg_hint_plan插件的使用。

psqlCREATE EXTENSION pg_hint_plan;

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    | info

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);

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)

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)

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 hinthalo0root=# set pg_hint_plan.enable_hint = on;  --启用SQL查询使用Hint Plan注释SEThalo0root=# set pg_hint_plan.enable_hint_table = on;  --启用表级别使用Hint Plan注释SEThalo0root=# set pg_hint_plan.debug_print = verbose;  --打印日志信息SEThalo0root=# set pg_hint_plan.message_level = notice;  --设置信息的级别SEThalo0root=# 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=1NOTICE:  pg_hint_plan[qno=0x1]: planner: no valid hintNOTICE:  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=1NOTICE:  pg_hint_plan[qno=0x3]: planner: no valid hintNOTICE:  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)

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 hintINSERT 0 1

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]: plannerNOTICE:  pg_hint_plan[qno=0x16]: setup_hint_enforcement no hint applied: relation=19140(customers), inhparent=0, current_hint=0x1eaadf0, hint_inhibit_level=0, scanmask=0x1fNOTICE:  pg_hint_plan[qno=0x16]: setup_hint_enforcement no hint applied: relation=19147(orders), inhparent=0, current_hint=0x1eaadf0, hint_inhibit_level=0, scanmask=0x1fNOTICE:  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(14 rows)