更新时间：2024-11-12 GMT+08:00
Plan Hint调优概述
Plan Hint为用户提供了直接影响执行计划生成的方法，用户可以通过指定join顺序，join、stream、scan方法，指定结果行数，指定重分布过程中的倾斜信息等多种方法来进行执行计划的调优，以提升查询的性能。
GaussDB还提供了SQL PATCH功能，在不修改业务语句的前提下通过创建SQL PATCH的方式使得hint生效。
功能描述
Plan Hint支持在SELECT、INSERT、UPDATE、DELETE、MERGE等关键字后通过如下形式指定：
1
|
/*+ <plan hint>*/
|
可以同时指定多个hint，之间使用空格分隔。hint只能hint当前层的计划，对于子查询计划的hint，需要在子查询的select关键字后指定hint。
例如：
1
|
select /*+ <plan_hint1> <plan_hint2> */ * from t1, (select /*+ <plan_hint3> */ * from t2) where 1=1; |
其中<plan_hint1>，<plan_hint2>为外层查询的hint，<plan_hint3>为内层子查询的hint。
检查Plan Hint调优的效果可以借助explain语法进行分析。通过explain可以查看使用Plan Hint后目标SQL的计划，对比计划是否符合要求以验证Plan Hint的效果。explain有多种计划展示的模式，通过explain_perf_mode进行控制。本节的示例一般通过设置explain_perf_mode为pretty模式来展示计划，展示较全的计划相关信息。部分示例设置explain_perf_mode为normal模式以精简输出信息。
如果在视图定义（CREATE VIEW）时指定hint，则在该视图每次被应用时会使用该hint。
当使用random plan功能（参数plan_mode_seed不为0）时，查询指定的Plan Hint不会被使用。
支持范围
当前版本Plan Hint支持的范围如下，后续版本会进行增强。
- 指定Join顺序的hint - leading hint。
- 指定Join方式的hint，仅支持除semi/anti join、unique plan之外的常用hint。
- 指定结果集行数的hint。
- 指定Stream方式的hint。
- 指定Scan方式的hint，仅支持常用的tablescan、indexscan、index、indexonlyscan和gsi的hint。
- 指定子链接块名的hint。
- 指定倾斜信息的hint，仅支持Join与HashAgg的重分布过程倾斜。
- 指定本query内生效的guc参数的hint（在视图内使用不生效）。
- 指定使用custom plan或generic plan的hint（只对PBE执行的查询语句生效）。
- 指定子查询不展开的hint。
- 指定当前查询语句不进入全局计划缓存（enable_global_plancache打开且当前语句为PBE执行时生效）。
- 指定内表物化的hint。
- 指定Bitmapscan的hint。
- 指定Agg方法的hint。
注意事项
- 不支持Sort、Setop和Subplan的hint。
- 不支持SMP和Node Group场景下的hint。
示例
创建示例表和索引：
create table t1(c1 int, c2 int, c3 int);
create table t2(c1 int, c2 int, c3 int);
create table t3(c1 int, c2 int, c3 int);
create index it1 on t1(c1,c2);
create index it2 on t2(c1,c2);
create index it3 on t1(c3,c2);
-- 下面TPCH数据表需要插入10X数据量已匹配给出的计划示例
create table store
(
s_store_sk integer not null,
s_store_id char(16) not null,
s_rec_start_date date ,
s_rec_end_date date ,
s_closed_date_sk integer ,
s_store_name varchar(50) ,
s_number_employees integer ,
s_floor_space integer ,
s_hours char(20) ,
s_manager varchar(40) ,
s_market_id integer ,
s_geography_class varchar(100) ,
s_market_desc varchar(100) ,
s_market_manager varchar(40) ,
s_division_id integer ,
s_division_name varchar(50) ,
s_company_id integer ,
s_company_name varchar(50) ,
s_street_number varchar(10) ,
s_street_name varchar(60) ,
s_street_type char(15) ,
s_suite_number char(10) ,
s_city varchar(60) ,
s_county varchar(30) ,
s_state char(2) ,
s_zip char(10) ,
s_country varchar(20) ,
s_gmt_offset decimal(5,2) ,
s_tax_precentage decimal(5,2) ,
primary key (s_store_sk)
);
create table store_sales
(
ss_sold_date_sk integer ,
ss_sold_time_sk integer ,
ss_item_sk integer not null,
ss_customer_sk integer ,
ss_cdemo_sk integer ,
ss_hdemo_sk integer ,
ss_addr_sk integer ,
ss_store_sk integer ,
ss_promo_sk integer ,
ss_ticket_number integer not null,
ss_quantity integer ,
ss_wholesale_cost decimal(7,2) ,
ss_list_price decimal(7,2) ,
ss_sales_price decimal(7,2) ,
ss_ext_discount_amt decimal(7,2) ,
ss_ext_sales_price decimal(7,2) ,
ss_ext_wholesale_cost decimal(7,2) ,
ss_ext_list_price decimal(7,2) ,
ss_ext_tax decimal(7,2) ,
ss_coupon_amt decimal(7,2) ,
ss_net_paid decimal(7,2) ,
ss_net_paid_inc_tax decimal(7,2) ,
ss_net_profit decimal(7,2) ,
primary key (ss_item_sk, ss_ticket_number)
);
create table store_returns
(
sr_returned_date_sk integer ,
sr_return_time_sk integer ,
sr_item_sk integer not null,
sr_customer_sk integer ,
sr_cdemo_sk integer ,
sr_hdemo_sk integer ,
sr_addr_sk integer ,
sr_store_sk integer ,
sr_reason_sk integer ,
sr_ticket_number integer not null,
sr_return_quantity integer ,
sr_return_amt decimal(7,2) ,
sr_return_tax decimal(7,2) ,
sr_return_amt_inc_tax decimal(7,2) ,
sr_fee decimal(7,2) ,
sr_return_ship_cost decimal(7,2) ,
sr_refunded_cash decimal(7,2) ,
sr_reversed_charge decimal(7,2) ,
sr_store_credit decimal(7,2) ,
sr_net_loss decimal(7,2) ,
primary key (sr_item_sk, sr_ticket_number)
);
create table customer
(
c_customer_sk integer not null,
c_customer_id char(16) not null,
c_current_cdemo_sk integer ,
c_current_hdemo_sk integer ,
c_current_addr_sk integer ,
c_first_shipto_date_sk integer ,
c_first_sales_date_sk integer ,
c_salutation char(10) ,
c_first_name char(20) ,
c_last_name char(30) ,
c_preferred_cust_flag char(1) ,
c_birth_day integer ,
c_birth_month integer ,
c_birth_year integer ,
c_birth_country varchar(20) ,
c_login char(13) ,
c_email_address char(50) ,
c_last_review_date char(10) ,
primary key (c_customer_sk)
);
create table promotion
(
p_promo_sk integer not null,
p_promo_id char(16) not null,
p_start_date_sk integer ,
p_end_date_sk integer ,
p_item_sk integer ,
p_cost decimal(15,2) ,
p_response_target integer ,
p_promo_name char(50) ,
p_channel_dmail char(1) ,
p_channel_email char(1) ,
p_channel_catalog char(1) ,
p_channel_tv char(1) ,
p_channel_radio char(1) ,
p_channel_press char(1) ,
p_channel_event char(1) ,
p_channel_demo char(1) ,
p_channel_details varchar(100) ,
p_purpose char(15) ,
p_discount_active char(1) ,
primary key (p_promo_sk)
);
create table customer_address
(
ca_address_sk integer not null,
ca_address_id char(16) not null,
ca_street_number char(10) ,
ca_street_name varchar(60) ,
ca_street_type char(15) ,
ca_suite_number char(10) ,
ca_city varchar(60) ,
ca_county varchar(30) ,
ca_state char(2) ,
ca_zip char(10) ,
ca_country varchar(20) ,
ca_gmt_offset decimal(5,2) ,
ca_location_type char(20) ,
primary key (ca_address_sk)
);
create table item
(
i_item_sk integer not null,
i_item_id char(16) not null,
i_rec_start_date date ,
i_rec_end_date date ,
i_item_desc varchar(200) ,
i_current_price decimal(7,2) ,
i_wholesale_cost decimal(7,2) ,
i_brand_id integer ,
i_brand char(50) ,
i_class_id integer ,
i_class char(50) ,
i_category_id integer ,
i_category char(50) ,
i_manufact_id integer ,
i_manufact char(50) ,
i_size char(20) ,
i_formulation char(20) ,
i_color char(20) ,
i_units char(10) ,
i_container char(10) ,
i_manager_id integer ,
i_product_name char(50) ,
primary key (i_item_sk)
);
本章节大部分示例使用下述语句，便于Plan Hint支持的各方法作对比，示例语句及不带hint的原计划如下所示：
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 |
explain select i_product_name product_name ,i_item_sk item_sk ,s_store_name store_name ,s_zip store_zip ,ad2.ca_street_number c_street_number ,ad2.ca_street_name c_street_name ,ad2.ca_city c_city ,ad2.ca_zip c_zip ,count(*) cnt ,sum(ss_wholesale_cost) s1 ,sum(ss_list_price) s2 ,sum(ss_coupon_amt) s3 FROM store_sales ,store_returns ,store ,customer ,promotion ,customer_address ad2 ,item WHERE ss_store_sk = s_store_sk AND ss_customer_sk = c_customer_sk AND ss_item_sk = i_item_sk and ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number and c_current_addr_sk = ad2.ca_address_sk and ss_promo_sk = p_promo_sk and i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and i_current_price between 35 and 35 + 10 and i_current_price between 35 + 1 and 35 + 15 group by i_product_name ,i_item_sk ,s_store_name ,s_zip ,ad2.ca_street_number ,ad2.ca_street_name ,ad2.ca_city ,ad2.ca_zip ; |
HashAggregate (cost=53.53..53.76 rows=1 width=880)
Group By Key: item.i_product_name, item.i_item_sk, store.s_store_name, store.s_zip, ad2.ca_street_number, ad2.ca_street_name, ad2.ca_city, ad2.ca_zip
-> Streaming (type: GATHER) (cost=53.53..53.76 rows=2 width=880)
Node/s: All datanodes
-> HashAggregate (cost=53.10..53.11 rows=2 width=880)
Group By Key: item.i_product_name, item.i_item_sk, store.s_store_name, store.s_zip, ad2.ca_street_number, ad2.ca_street_name, ad2.ca_city, ad2.ca_zip
-> Nested Loop (cost=0.00..53.07 rows=2 width=776)
-> Streaming(type: REDISTRIBUTE) (cost=0.00..46.36 rows=2 width=416)
Spawn on: All datanodes
-> Nested Loop (cost=0.00..45.99 rows=2 width=416)
-> Streaming(type: REDISTRIBUTE) (cost=0.00..39.27 rows=2 width=258)
Spawn on: All datanodes
-> Nested Loop (cost=0.00..38.99 rows=2 width=258)
-> Streaming(type: REDISTRIBUTE) (cost=0.00..32.28 rows=2 width=262)
Spawn on: All datanodes
-> Nested Loop (cost=0.00..32.00 rows=2 width=262)
-> Streaming(type: REDISTRIBUTE) (cost=0.00..25.28 rows=2 width=262)
Spawn on: All datanodes
-> Nested Loop (cost=0.00..25.00 rows=2 width=262)
-> Nested Loop (cost=0.00..21.64 rows=2 width=270)
-> Seq Scan on item (cost=0.00..13.36 rows=1 width=208)
Filter: ((i_current_price >= 35::numeric) AND (i_current_price <= 45::numeric) AND (i_current_price >= 36::numeric) AND
(i_current_price <= 50::numeric) AND (i_color = ANY ('{maroon,burnished,dim,steel,navajo,chocolate}'::bpchar[])))
-> Index Scan using store_sales_pkey on store_sales (cost=0.00..8.27 rows=1 width=62)
Index Cond: (ss_item_sk = item.i_item_sk)
-> Index Only Scan using store_returns_pkey on store_returns (cost=0.00..3.35 rows=1 width=8)
Index Cond: ((sr_item_sk = store_sales.ss_item_sk) AND (sr_ticket_number = store_sales.ss_ticket_number))
-> Index Scan using customer_pkey on customer (cost=0.00..3.35 rows=1 width=8)
Index Cond: (c_customer_sk = store_sales.ss_customer_sk)
-> Index Only Scan using promotion_pkey on promotion (cost=0.00..3.35 rows=1 width=4)
Index Cond: (p_promo_sk = store_sales.ss_promo_sk)
-> Index Scan using store_pkey on store (cost=0.00..3.35 rows=1 width=166)
Index Cond: (s_store_sk = store_sales.ss_store_sk)
-> Index Scan using customer_address_pkey on customer_address ad2 (cost=0.00..3.35 rows=1 width=368)
Index Cond: (ca_address_sk = customer.c_current_addr_sk)
(34 rows)
父主题： 使用Plan Hint进行调优
