跳转至

`HINT`查询

hint查询

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

功能描述

Hint查询通过在SELECT关键字后输入如下形式指定：

/*+ <plan hint>*/

可以同时指定多个hint，之间使用空格分隔。hint只能影响当前层的计划，对于子查询计划的hint，需要在子查询的select关键字后指定hint。

例如：

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

其中<plan_hint1>，<plan_hint2>为外层查询的hint，<plan_hint3>为内层子查询的hint。

须知：

如果在视图定义（CREATE VIEW）时指定hint，则在该视图每次被应用时会使用该hint。

支持类型

当前版本Hint支持的类型如下，后续版本会进行增强。

  • 指定Join顺序的Hint - leading hint。
  • 指定Join方式的Hint，仅支持除semi/anti join，unique plan之外的常用hint。
  • 指定结果集行数的Hint。
  • 指定Scan方式的Hint，仅支持常用的tablescan，indexscan和indexonlyscan的hint。
  • 指GUC参数设置的Hint。

注意事项

Set的hint仅对影响计划生成的GUC参数有效。

用例

后续章节用于说明hint功能所用到的表的创建命令如下：

create table src(a int);
insert into src values(1);

create table hint_t1(a int, b int, c int);
insert into hint_t1 select generate_series(1, 2000), generate_series(1, 1000), generate_series(1, 500) from src;

create table hint_t2(a int, b int, c int);
insert into hint_t2 select generate_series(1, 1000), generate_series(1, 500), generate_series(1, 100) from src;

create table hint_t3(a int, b int, c int);
insert into hint_t3 select generate_series(1, 100), generate_series(1, 50), generate_series(1, 25) from src;

create table hint_t4(a int, b int, c int);
insert into hint_t4 select generate_series(1, 10), generate_series(1, 5), generate_series(1, 2) from src;

create table hint_t5(a int, b int, c int);
insert into hint_t5 select generate_series(1, 5), generate_series(1, 5), generate_series(1, 2) from src;

analyze hint_t1;
analyze hint_t2;
analyze hint_t3;
analyze hint_t4;
analyze hint_t5;

scan hint

指明scan使用的方法，可以是tablescan、indexscan和indexonlyscan。

语法格式

[no] tablescan|indexscan|indexonlyscan(table [index])

参数说明

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

  • table表示hint指定的表，只能指定一个表，如果表存在别名应优先使用别名进行hint。

  • index表示使用indexscan或indexonlyscan的hint时，指定的索引名称，当前只能指定一个。

说明：

对于indexscan或indexonlyscan，只有hint的索引属于hint的表时，才能使用该hint。

示例

为了hint使用索引扫描，需要首先在表hint_t1的a列上创建索引，名称为index_t1_a。

create index index_t1_a on hint_t1(a);

不使用hint的查询计划： 

explain
select * from hint_t1 where a > 1;

计划： 

 Gather Motion 3:1  (slice1; executors: 3)  (cost=0.00..35.98 rows=1998 width=12)
   ->  Seq Scan on hint_t1  (cost=0.00..9.34 rows=666 width=12)
         Filter: (a > 1)
 Optimizer: SeaboxSQL query optimizer
(4 rows)

使用hint的查询计划：

explain
select /*+ indexscan(hint_t1 index_t1_a)*/* from hint_t1 where a > 1;

该hint表示：hint_t1表使用索引index_t1_a进行扫描。生成计划如下所示：

 Gather Motion 3:1  (slice1; executors: 3)  (cost=0.15..46.46 rows=1998 width=12)
   ->  Index Scan using index_t1_a on hint_t1  (cost=0.15..19.82 rows=666 width=12)
         Index Cond: (a > 1)
 Optimizer: SeaboxSQL query optimizer
(4 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只影响最后一次join的join方式，对于两表连接的方法不限制。如果需要，可以单独指定，例如：任意表均不允许nestloop连接，且希望t2 t3先join，则增加hint：no nestloop(t2 t3)。

示例

不使用hint的join查询：

explain (costs off)
select
  *
from hint_t1 as t1
join hint_t2 as t2
on(t1.a = t2.b)
join hint_t3 as t3
on (t2.a = t3.b)
join hint_t4 as t4
on (t4.c = t3.c);

结果： 

 Gather Motion 3:1  (slice1; executors: 3)
   ->  Nested Loop
         ->  Redistribute Motion 3:3  (slice2; executors: 3)
               Hash Key: t2.b
               ->  Hash Join
                     Hash Cond: (t2.a = t3.b)
                     ->  Seq Scan on hint_t2 t2
                     ->  Hash
                           ->  Redistribute Motion 3:3  (slice3; executors: 3)
                                 Hash Key: t3.b
                                 ->  Hash Join
                                       Hash Cond: (t3.c = t4.c)
                                       ->  Seq Scan on hint_t3 t3
                                       ->  Hash
                                             ->  Broadcast Motion 3:3  (slice4; executors: 3)
                                                   ->  Seq Scan on hint_t4 t4
         ->  Index Scan using index_t1_a on hint_t1 t1
               Index Cond: (a = t2.b)
 Optimizer: SeaboxSQL query optimizer
(19 rows)

使用hint的join查询：

explain (costs off)
select /*+ hashjoin(t1 t2) mergejoin (t1 t2 t3) nestloop(t1 t2 t3 t4)*/
  *
from hint_t1 as t1
join hint_t2 as t2
on(t1.a = t2.b)
join hint_t3 as t3
on (t2.a = t3.b)
join hint_t4 as t4
on (t4.c = t3.c);

该hint表示：t1,t2使用hash join，结果和t3作merge join(顺序不限定)；上面结果和t4作nestloop(顺序不限定)。生成计划如下所示：

 Gather Motion 3:1  (slice1; executors: 3)
   ->  Nested Loop
         Join Filter: (t3.c = t4.c)
         ->  Merge Join
               Merge Cond: (t2.a = t3.b)
               ->  Sort
                     Sort Key: t2.a
                     ->  Hash Join
                           Hash Cond: (t1.a = t2.b)
                           ->  Seq Scan on hint_t1 t1
                           ->  Hash
                                 ->  Redistribute Motion 3:3  (slice2; executors: 3)
                                       Hash Key: t2.b
                                       ->  Seq Scan on hint_t2 t2
               ->  Sort
                     Sort Key: t3.b
                     ->  Broadcast Motion 3:3  (slice3; executors: 3)
                           ->  Seq Scan on hint_t3 t3
         ->  Materialize
               ->  Broadcast Motion 3:3  (slice4; executors: 3)
                     ->  Seq Scan on hint_t4 t4
 Optimizer: SeaboxSQL query optimizer
(22 rows)

leading hint

功能描述

指明join的顺序，包括内外表顺序。

语法格式

  • 仅指定join顺序，不指定内外表顺序。
leading(join_table_list)
  • 同时指定join顺序和内外表顺序，内外表顺序仅在最外层生效。
leading((join_table_list))

参数说明

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的茶查询：

explain (costs off)
select
  *
from hint_t1 as t1
join hint_t2 as t2
on(t1.a = t2.b)
join hint_t3 as t3
on (t2.a = t3.b)
join hint_t4 as t4
on (t4.c = t3.c);

结果：

 Gather Motion 3:1  (slice1; executors: 3)
   ->  Nested Loop
         ->  Redistribute Motion 3:3  (slice2; executors: 3)
               Hash Key: t2.b
               ->  Hash Join
                     Hash Cond: (t2.a = t3.b)
                     ->  Seq Scan on hint_t2 t2
                     ->  Hash
                           ->  Redistribute Motion 3:3  (slice3; executors: 3)
                                 Hash Key: t3.b
                                 ->  Hash Join
                                       Hash Cond: (t3.c = t4.c)
                                       ->  Seq Scan on hint_t3 t3
                                       ->  Hash
                                             ->  Broadcast Motion 3:3  (slice4; executors: 3)
                                                   ->  Seq Scan on hint_t4 t4
         ->  Index Scan using index_t1_a on hint_t1 t1
               Index Cond: (a = t2.b)
 Optimizer: SeaboxSQL query optimizer
(19 rows)

使用leading hint的查询：

explain (costs off)
select /*+ leading(((t1 t2) (t3 t4))) */
  *
from hint_t1 as t1
join hint_t2 as t2
on(t1.a = t2.b)
join hint_t3 as t3
on (t2.a = t3.b)
join hint_t4 as t4
on (t4.c = t3.c);

结果：

 Gather Motion 3:1  (slice1; executors: 3)
   ->  Hash Join
         Hash Cond: (t2.a = t3.b)
         ->  Hash Join
               Hash Cond: (t1.a = t2.b)
               ->  Seq Scan on hint_t1 t1
                     BloomFilter consumer: bf_index: 2, ((tle_resno: 1, var: t1.a))
               ->  Hash
                     ->  Redistribute Motion 3:3  (slice2; executors: 3)
                           Hash Key: t2.b
                           BloomFilter producer: bf_index: 2, need_redist: 0, log2(ndistinct): 8, ((tle_resno: 2, var: t2.b))
                           ->  Seq Scan on hint_t2 t2
                                 BloomFilter consumer: bf_index: 1, ((tle_resno: 1, var: t2.a))
         ->  Hash
               ->  Broadcast Motion 3:3  (slice3; executors: 3)
                     BloomFilter producer: bf_index: 1, need_redist: 1, log2(ndistinct): 5, ((tle_resno: 2, var: t3.b))
                     ->  Hash Join
                           Hash Cond: (t3.c = t4.c)
                           ->  Seq Scan on hint_t3 t3
                                 BloomFilter consumer: bf_index: 3, ((tle_resno: 3, var: t3.c))
                           ->  Hash
                                 ->  Broadcast Motion 3:3  (slice4; executors: 3)
                                       BloomFilter producer: bf_index: 3, need_redist: 0, log2(ndistinct): 1, ((tle_resno: 3, var: t4.c))
                                       ->  Seq Scan on hint_t4 t4
 Optimizer: SeaboxSQL query optimizer
(25 rows)

该hint表示：表之间的join关系是：t1和t2先join， 结果作为外表；t3和t4作join， 结果作为内表。

row hint

功能描述

指明中间结果集的大小，支持绝对值和相对值的hint。

语法格式

rows(table_list #|+|-|* const)

参数说明

  • #,+,-,*，进行行数估算hint的四种操作符号。#表示直接使用后面的行数进行hint。+,-,*表示对原来估算的行数进行加、减、乘操作，运算后的行数最小值为1行。table_list为hint对应的单表或多表join结果集，与Join方式的Hint中table_list相同。

  • const可以是任意非负数，支持科学计数法。

例如：

rows(t1 #5)表示：指定t1表的结果集为5行。

rows(t1 t2 t3 *1000)表示：指定t1, t2, t3 join完的结果集的行数乘以1000。

建议

  • 推荐使用两个表*的hint。对于两个表的采用*操作符的hint，只要两个表出现在join的两端，都会触发hint。例如：设置hint为rows(t1 t2 * 3)，对于(t1 t3 t4)和(t2 t5 t6)join时，由于t1和t2出现在join的两端，所以其join的结果集也会应用该hint规则乘以3。
  • rows hint支持在单表、多表、function table及subquery scan table的结果集上指定hint。

示例

不使用rows hint的查询：

explain
select
  *
from hint_t1 as t1
join hint_t2 as t2
on(t1.a = t2.b)
join hint_t3 as t3
on (t2.a = t3.b);

结果为：

 Gather Motion 3:1  (slice1; executors: 3)  (cost=2.56..11.90 rows=25 width=36)
   ->  Nested Loop  (cost=2.56..11.56 rows=8 width=36)
         ->  Redistribute Motion 3:3  (slice2; executors: 3)  (cost=2.40..8.37 rows=17 width=24)
               Hash Key: t2.b
               ->  Hash Join  (cost=2.40..8.03 rows=17 width=24)
                     Hash Cond: (t2.a = t3.b)
                     ->  Seq Scan on hint_t2 t2  (cost=0.00..4.33 rows=333 width=12)
                     ->  Hash  (cost=1.99..1.99 rows=33 width=12)
                           ->  Redistribute Motion 3:3  (slice3; executors: 3)  (cost=0.00..1.99 rows=33 width=12)
                                 Hash Key: t3.b
                                 ->  Seq Scan on hint_t3 t3  (cost=0.00..1.33 rows=33 width=12)
         ->  Index Scan using index_t1_a on hint_t1 t1  (cost=0.15..0.18 rows=1 width=12)
               Index Cond: (a = t2.b)
 Optimizer: SeaboxSQL query optimizer
(14 rows)

explain
select  /*+ rows(t2 t3 #230)*/
  *
from hint_t1 as t1
join hint_t2 as t2
on(t1.a = t2.b)
join hint_t3 as t3
on (t2.a = t3.b);

该hint表示：t2，t3关联的结果集估算行数制定为230。生成计划如下所示：

 Gather Motion 3:1  (slice1; executors: 3)  (cost=12.13..17.91 rows=25 width=36)
   ->  Merge Join  (cost=12.13..17.57 rows=8 width=36)
         Merge Cond: (t1.a = t2.b)
         ->  Index Scan using index_t1_a on hint_t1 t1  (cost=0.15..18.16 rows=667 width=12)
         ->  Sort  (cost=11.98..12.17 rows=77 width=24)
               Sort Key: t2.b
               ->  Redistribute Motion 3:3  (slice2; executors: 3)  (cost=2.40..9.57 rows=77 width=24)
                     Hash Key: t2.b
                     ->  Hash Join  (cost=2.40..8.03 rows=77 width=24)
                           Hash Cond: (t2.a = t3.b)
                           ->  Seq Scan on hint_t2 t2  (cost=0.00..4.33 rows=333 width=12)
                           ->  Hash  (cost=1.99..1.99 rows=33 width=12)
                                 ->  Redistribute Motion 3:3  (slice3; executors: 3)  (cost=0.00..1.99 rows=33 width=12)
                                       Hash Key: t3.b
                                       ->  Seq Scan on hint_t3 t3  (cost=0.00..1.33 rows=33 width=12)
 Optimizer: SeaboxSQL query optimizer
(16 rows)

其中t2,t3的结果集（Hash Join (cost=2.40..8.03 rows=77 width=24)）的真实数值77 = 230 / executor_nums。

set hint

功能描述

设置本次查询执行内生效的查询优化相关的GUC参数。支持子查询（不可提升）单独指定set hint。

语法格式

set(param value)

参数说明

  • param表示参数名
  • value表示参数的取值

说明： - 设置不是在计划生成阶段生效的参数或者参数设置非法，set hint不生效。

示例

不使用set hint的查询：

explain (costs off)
select 1 from hint_t1 as t1 join
(select sum(t2.a) as a from hint_t2 as t2 join hint_t3 as t3
on (t2.a = t3.a)) as AA on(t1.a = AA.a);

结果：

 Gather Motion 3:1  (slice1; executors: 3)
   ->  Hash Join
         Hash Cond: (t1.a = (sum(t2.a)))
         ->  Seq Scan on hint_t1 t1
               BloomFilter consumer: bf_index: 1, ((tle_resno: 1, var: t1.a))
         ->  Hash
               ->  Redistribute Motion 1:3  (slice2; executors: 1)
                     Hash Key: (sum(t2.a))
                     BloomFilter producer: bf_index: 1, need_redist: 0, log2(ndistinct): 0, ((tle_resno: 1, var: (sum(t2.a))))
                     ->  Finalize Aggregate
                           ->  Gather Motion 3:1  (slice3; executors: 3)
                                 ->  Partial Aggregate
                                       ->  Hash Join
                                             Hash Cond: (t2.a = t3.a)
                                             ->  Seq Scan on hint_t2 t2
                                                   BloomFilter consumer: bf_index: 2, ((tle_resno: 1, var: t2.a))
                                             ->  Hash
                                                   ->  Seq Scan on hint_t3 t3
                                                         BloomFilter producer: bf_index: 2, need_redist: 0, log2(ndistinct): 6, ((tle_resno: 1, var: t3.a))
 Optimizer: SeaboxSQL query optimizer
(20 rows)

上面计划显示： 子查询不能提升，t2,t3作hash join； 最后跟t1作hash join。

使用set hint的查询：

explain (costs off)
select /*+ set(enable_hashjoin off)*/  1 from hint_t1 as t1 join
(select /*+  set(enable_hashjoin off)*/  sum(t2.a) as a from hint_t2 as t2 join hint_t3 as t3
on (t2.a = t3.a)) as AA on(t1.a = AA.a);

结果：

 Gather Motion 3:1  (slice1; executors: 3)
   ->  Merge Join
         Merge Cond: (t1.a = (sum(t2.a)))
         ->  Index Only Scan using index_t1_a on hint_t1 t1
         ->  Sort
               Sort Key: (sum(t2.a))
               ->  Redistribute Motion 1:3  (slice2; executors: 1)
                     Hash Key: (sum(t2.a))
                     ->  Finalize Aggregate
                           ->  Gather Motion 3:1  (slice3; executors: 3)
                                 ->  Partial Aggregate
                                       ->  Merge Join
                                             Merge Cond: (t2.a = t3.a)
                                             ->  Sort
                                                   Sort Key: t2.a
                                                   ->  Seq Scan on hint_t2 t2
                                             ->  Sort
                                                   Sort Key: t3.a
                                                   ->  Seq Scan on hint_t3 t3
 Optimizer: SeaboxSQL query optimizer
(20 rows)

上面查询计划显示：外层查询和子查询都没有使用hash join。