更新时间:2024-05-07 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、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)