openGauss/MogDB特性通过Hint改变执行计划

原作者：何放

Plan Hint概念

Plan Hint为用户提供了直接影响执行计划生成的手段，用户可以通过指定join顺序，join、scan方法，指定结果行数，等多个手段来进行执行计划的调优，以提升查询的性能。

Hint影响Join顺序

join_table_list为表示表join顺序的hint字符串，可以包含当前层的任意个表（别名），或对于子查询提升的场景，也可以包含子查询的hint别名，同时任意表可以使用括号指定优先级，表之间使用空格分隔。

须知：

表只能用单个字符串表示，不能带schema。 表如果存在别名，需要优先使用别名来表示该表。

join table list中指定的表需要满足以下要求，否则会报语义错误。

• list中的表必须在当前层或提升的子查询中存在。

• list中的表在当前层或提升的子查询中必须是唯一的。如果不唯一，需要使用不同的别名进行区分。

• 同一个表只能在list里出现一次。

• 如果表存在别名，则list中的表需要使用别名。

  例如：

leading(t1 t2 t3 t4 t5)表示：t1、t2、t3、t4、t5先join，五表join顺序及内外表不限。leading((t1 t2 t3 t4 t5))表示：t1和t2先join，t2做内表；再和t3 join，t3做内表；再和t4 join，t4做内表；再和t5 join，t5做内表。leading(t1 (t2 t3 t4) t5)表示：t2、t3、t4先join，内外表不限；再和t1、t5 join，内外表不限。leading((t1 (t2 t3 t4) t5))表示：t2、t3、t4先join，内外表不限；在最外层，t1再和t2、t3、t4的join表join，t1为外表，再和t5 join，t5为内表。leading((t1 (t2 t3) t4 t5)) leading((t3 t2))表示：t2、t3先join，t2做内表；然后再和t1 join，t2、t3的join表做内表；然后再依次跟t4、t5做join，t4、t5做内表。

Hint改变join连接的顺序

示例

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
;


执行计划所示中join关系为：store_returns和item表先join，然后依次跟store_sales，store，customer，promotion，ad2做join。

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=23.52..23.53 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
-> Nested Loop (cost=4.27..23.49 rows=1 width=776)
-> Nested Loop (cost=4.27..22.80 rows=1 width=416)
-> Nested Loop (cost=4.27..22.39 rows=1 width=420)
-> Nested Loop (cost=4.27..21.98 rows=1 width=420)
-> Nested Loop (cost=4.27..21.57 rows=1 width=262)
Join Filter: (item.i_item_sk = store_sales.ss_item_sk)
-> Nested Loop (cost=4.27..20.78 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 = 36::numeric) AND (i_current_price Bitmap Heap Scan on store_returns (cost=4.27..9.61 rows=2 width=8)
Recheck Cond: (sr_item_sk = item.i_item_sk)
-> Bitmap Index Scan on store_returns_pkey (cost=0.00..4.26 rows=2 width=0)
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.40 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)
(25 rows)


使用Hint如下，无需改变查询语句。

explain
select /*+ leading((((((store_sales store) promotion) item) customer) ad2) store_returns) leading((store_sales store))*/
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
;


使用Hint指定表之间的join关系是: store_sales和store先join，store做内表，然后依次跟promotion, item, customer, ad2, store_returns做join，执行计划如下。

WARNING: Duplicated or conflict hint: Leading(store_sales store), will be discarded.
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=52.63..52.64 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
-> Nested Loop (cost=23.65..52.60 rows=1 width=776)
-> Nested Loop (cost=23.65..52.19 rows=1 width=784)
-> Nested Loop (cost=23.65..51.50 rows=1 width=424)
-> Nested Loop (cost=23.65..51.09 rows=1 width=424)
Join Filter: (store_sales.ss_item_sk = item.i_item_sk)
-> Seq Scan on item (cost=0.00..11.16 rows=1 width=208)
Filter: ((i_current_price >= 35::numeric) AND (i_current_price = 36::numeric) AND (i_current_price Hash Join (cost=23.65..39.38 rows=44 width=216)
Hash Cond: (store_sales.ss_promo_sk = promotion.p_promo_sk)
-> Hash Join (cost=10.99..26.05 rows=74 width=220)
Hash Cond: (store_sales.ss_store_sk = store.s_store_sk)
-> Seq Scan on store_sales (cost=0.00..13.38 rows=338 width=62)
-> Hash (cost=10.44..10.44 rows=44 width=166)
-> Seq Scan on store (cost=0.00..10.44 rows=44 width=166)
-> Hash (cost=11.18..11.18 rows=118 width=4)
-> Seq Scan on promotion (cost=0.00..11.18 rows=118 width=4)
-> 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 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)
-> Index Only Scan using store_returns_pkey on store_returns (cost=0.00..0.41 rows=1 width=8)
Index Cond: ((sr_item_sk = store_sales.ss_item_sk) AND (sr_ticket_number = store_sales.ss_ticket_number))
(24 rows)


Hint改变join连接的内表

我们发现两表连接，不管是左右连接还是内连接，优化器都会选择a表做内表，可以使用join顺序的hint改变连接的内表为b表。

create table a (id int,name varchar(10));
create table b (id int,name varchar(10));
--查看执行计划
explain select * from a , b where a.name = b.name;
explain select * from a left join b on a.name = b.name;
explain select * from b right join a on a.name = b.name;
explain select * from b , a where a.name = b.name;
explain select * from b left join a on a.name = b.name;
explain select * from a right join b on a.name = b.name;
--不受Hint影响的执行计划，a表为连接内表
QUERY PLAN
-------------------------------------------------------------
Hash Join (cost=1.18..30.69 rows=53 width=30)
Hash Cond: ((b.name)::text = (a.name)::text)
-> Seq Scan on b (cost=0.00..25.13 rows=1513 width=24)
-> Hash (cost=1.08..1.08 rows=8 width=6)
-> Seq Scan on a (cost=0.00..1.08 rows=8 width=6)
(5 rows)

--Hint影响之后的执行计划，b表变为了内表
openGauss=# explain select /*+ leading((a b))*/ * from a , b where a.name = b.name;
QUERY PLAN
------------------------------------------------------------------
Hash Join (cost=44.04..45.75 rows=53 width=30)
Hash Cond: ((a.name)::text = (b.name)::text)
-> Seq Scan on a (cost=0.00..1.08 rows=8 width=6)
-> Hash (cost=25.13..25.13 rows=1513 width=24)
-> Seq Scan on b (cost=0.00..25.13 rows=1513 width=24)
(5 rows)


Join方式的Hint

指明Join使用的方法，可以为Nested Loop，Hash Join和Merge Join。

--语法格式
[no] nestloop|hashjoin|mergejoin(table_list)


参数说明

• no表示hint的join方式不使用。

• table_list为表示hint表集合的字符串，该字符串中的表与join_table_list相同，只是中间不允许出现括号指定join的优先级。

  例如:

no nestloop(t1 t2 t3)表示: 生成t1，t2，t3三表连接计划时，不使用nestloop。三表连接计划可能是t2 t3先join，再跟t1 join，或t1 t2先join，再跟t3 join。此hint只hint最后一次join的join方式，对于两表连接的方法不hint。如果需要，可以单独指定，例如: 任意表均不允许nestloop连接，且希望t2 t3先join，则增加hint: no nestloop(t2 t3)。

Join方式指定hash join

示例，原语句使用如下hint。

explain
select /*+ hashjoin(store_sales store_returns item) */
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
;


该hint表示：生成store_sales，store_returns和item三表的结果集时，最后的两表关联使用hashjoin。生成计划如下所示。

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=38.69..38.70 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
-> Nested Loop (cost=20.81..38.66 rows=1 width=776)
-> Nested Loop (cost=20.81..37.97 rows=1 width=416)
-> Nested Loop (cost=20.81..37.56 rows=1 width=420)
-> Nested Loop (cost=20.81..37.15 rows=1 width=420)
-> Hash Join (cost=20.81..36.74 rows=1 width=262)
Hash Cond: ((store_sales.ss_item_sk = store_returns.sr_item_sk) AND (store_sales.ss_ticket_number = store_returns.sr_ticket_number))
-> Seq Scan on store_sales (cost=0.00..13.38 rows=338 width=62)
-> Hash (cost=20.78..20.78 rows=2 width=216)
-> Nested Loop (cost=4.27..20.78 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 = 36::numeric) AND (i_current_price Bitmap Heap Scan on store_returns (cost=4.27..9.61 rows=2 width=8)
Recheck Cond: (sr_item_sk = item.i_item_sk)
-> Bitmap Index Scan on store_returns_pkey (cost=0.00..4.26 rows=2 width=0)
Index Cond: (sr_item_sk = item.i_item_sk)
-> 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.40 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)
(25 rows)


使用SQL_PATCH定义Hint

以上案例中的Hint都可以用5.0.0新特性SQL_PATCH来使用，如下示例为hashjoin指定后两表hashjoin的SQL_PATCH。

--打开FullSQL统计信息
set track_stmt_stat_level = 'L1,L1';

------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=38.69..38.70 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
-> Nested Loop (cost=20.81..38.66 rows=1 width=776)
-> Nested Loop (cost=20.81..37.97 rows=1 width=416)
-> Nested Loop (cost=20.81..37.56 rows=1 width=420)
-> Nested Loop (cost=20.81..37.15 rows=1 width=420)
-> Hash Join (cost=20.81..36.74 rows=1 width=262)
Hash Cond: ((store_sales.ss_item_sk = store_returns.sr_item_sk) AND (store_sales.ss_ticket_number = store_returns.sr_ticket_numbe
r))
-> Seq Scan on store_sales (cost=0.00..13.38 rows=338 width=62)
-> Hash (cost=20.78..20.78 rows=2 width=216)
-> Nested Loop (cost=4.27..20.78 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 = 36::nume
ric) AND (i_current_price Bitmap Heap Scan on store_returns (cost=4.27..9.61 rows=2 width=8)
Recheck Cond: (sr_item_sk = item.i_item_sk)
-> Bitmap Index Scan on store_returns_pkey (cost=0.00..4.26 rows=2 width=0)
Index Cond: (sr_item_sk = item.i_item_sk)
-> 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.40 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)
(25 rows)


参考文档

https://docs.mogdb.io/zh/mogdb/v3.0/9-hint-based-tuning

https://docs.opengauss.org/zh/docs/5.0.0/docs/PerformanceTuningGuide/使用Plan-Hint进行调优.html