Plan Hint Optimization

In plan hints, you can specify a join order; join, stream, and scan operations, the number of rows in a result, and redistribution skew information to tune an execution plan, improving query performance.

GaussDB also provides the SQL patch function. You can create an SQL patch to make hints take effect without modifying service statements.

1

/*+ <plan hint>*/

Example:

1

select /*+ <plan_hint1> <plan_hint2> */ * from t1, (select /*+ <plan_hint3> */ * from t2) where 1=1;

In the preceding command, <plan_hint1> and <plan_hint2> are the hints of a query, and <plan_hint3> is the hint of its subquery.

You can use the EXPLAIN syntax to analyze the plan hint optimization effect. You can use EXPLAIN to view the plan of the target SQL statement after the plan hint is used and check whether the plan meets the requirements to verify the plan hint effect. EXPLAIN has multiple plan display modes, which are controlled by explain_perf_mode. In some examples in this section, explain_perf_mode is set to pretty to display complete plan information. In some examples, explain_perf_mode is set to normal to simplify the output information.

If a hint is specified in the CREATE VIEW statement, the hint will be applied each time this view is used.

If the random plan function is enabled (plan_mode_seed is set to a value other than 0), the specified hint will not be used.

• Join order hints (leading).
• Join operation hints, excluding the semi join, anti join, and unique plan hints.
• Rows hints.
• Stream operation hints.
• Scan operation hints, supporting only the tablescan, indexscan, and indexonlyscan hints.
• Sublink name hints.
• Skew hints, supporting only the skew in the redistribution involving Join or HashAgg.
• Hints of GUC parameters, which take effect in a query and do not take effect in views.
• Hints that use the custom plan or generic plan and are valid only for query statements executed by PBE.
• Hints specifying not to expand subqueries.
• Hints specifying that the current query statement does not enter the global plan cache. The hints are valid only when enable_global_plancache is enabled and the current statement is executed by PBE.

 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)