# Plan Hint调优概述 Plan Hint为用户提供了直接影响执行计划生成的方法，用户可以通过指定join顺序，join、scan方法，指定结果行数，等多种方法来进行执行计划的调优，以提升查询的性能。 GaussDB还提供了SQL PATCH功能，在不修改业务语句的前提下通过创建SQL PATCH的方式使得hint生效。 #### 功能描述 Plan Hint在SELECT、INSERT、UPDATE、DELETE、MERGE等关键字后通过如下形式指定： ``` /*+ */ ``` 可以同时指定多个hint，之间使用空格分隔。关键字和hint之间不能有注释，hint必须紧挨着关键字，否则失效。hint只能hint当前层的计划，对于子查询计划的hint，需要在子查询的select关键字后指定hint。 例如： ``` select /*+ */ * from t1, (select /*+ */ * from t2) where 1=1; ``` 其中\，\为外层查询的hint，\为内层子查询的hint。 检查Plan Hint调优的效果可以借助explain语法进行分析。通过explain可以查看使用Plan Hint后目标SQL的计划，对比计划是否符合要求以验证Plan Hint的效果。explain有多种计划展示的模式，通过explain_perf_mode进行控制。本节的示例一般通过设置explain_perf_mode为pretty模式来展示计划，展示较全的计划相关信息。部分示例设置explain_perf_mode为normal模式以精简输出信息。 ![](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/public_sys-resources/notice_3.0-zh-cn.png) 如果在视图定义（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的hint。 - 指定子链接块名的hint。 - 指定子查询不展开的hint。 - 指定内表物化的hint。 - 指定Bitmapscan的hint。 - 指定Agg方法的hint。 #### 注意事项 不支持Sort、Setop和Subplan的hint。 #### 示例 本章节大部分示例使用下述语句，便于Plan Hint支持的各方法作对比，示例语句及不带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); 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) ); 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=18.09..18.10 rows=1 width=776) 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..18.06 rows=1 width=776) -> Nested Loop (cost=0.00..17.37 rows=1 width=416) -> Nested Loop (cost=0.00..17.08 rows=1 width=420) -> Nested Loop (cost=0.00..16.67 rows=1 width=420) -> Nested Loop (cost=0.00..16.26 rows=1 width=262) Join Filter: (item.i_item_sk = store_sales.ss_item_sk) -> Nested Loop (cost=0.00..15.46 rows=2 width=216) -> Seq Scan on item (cost=0.00..11.16 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) AN D (i_color = ANY ('{maroon,burnished,dim,steel,navajo,chocolate}'::bpchar[]))) -> Index Only Scan using store_returns_pkey on store_returns (cost=0.00..4.29 rows=2 width=8) Index Cond: (sr_item_sk = item.i_item_sk) -> Index Scan using store_sales_pkey on store_sales (cost=0.00..0.38 rows=1 width=62) Index Cond: ((ss_item_sk = store_returns.sr_item_sk) AND (ss_ticket_number = store_returns.sr_ticket_number)) -> Index Scan using store_pkey on store (cost=0.00..0.40 rows=1 width=166) Index Cond: (s_store_sk = store_sales.ss_store_sk) -> Index Scan using customer_pkey on customer (cost=0.00..0.40 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..0.28 rows=1 width=4) Index Cond: (p_promo_sk = store_sales.ss_promo_sk) -> Index Scan using customer_address_pkey on customer_address ad2 (cost=0.00..0.68 rows=1 width=368) Index Cond: (ca_address_sk = customer.c_current_addr_sk) (23 rows) ```