KingbaseES Hint 使用

慕震博

2023-12-01

前言：KingbaseES V8R6C4 之前版本hint 使用方法是与Postgresql 相同的，通过 pg_hint_plan扩展，支持在SQL中使用hint。由于该版本的hint只能放置于SQL开始处，无法对子查询单独使用hint，实际使用非常不方便。由于无法对子查询单独使用hint，对于父查询与子查询使用相同表别名的情况就无法使用hint。从V8R6C4版本开始，KingbaseES 在hint 使用方法上与oracle进行了兼容，hint 只允许放在 select 后面，同时对于子查询，支持使用单独的hint。

一、启用hint支持

V8R6C4之前版本：设置 shared_preload_libraries=‘sys_hint_plan’，重启后，出现参数sys_hint_plan.enable_hint，设置该参数为 on。

V8R6C4：直接设置 enable_hint = on。该版本已直接将hint功能集成到内核中，不需要设置 shared_preload_libraries=‘sys_hint_plan’。

二、hint位置不同

1、V8R6C4之前版本

hint 可以放置于select 前后，甚至explain 之前。具体看以下例子。

没使用hint时的执行计划：

test=# explain analyze select * from t1 where id=123456;
                                                   QUERY PLAN                                                  
----------------------------------------------------------------------------------------------------------------
 Index Scan using ind_t1_id on t1  (cost=0.42..8.44 rows=1 width=208) (actual time=0.021..0.022 rows=1 loops=1)
   Index Cond: (id = 123456)
 Planning Time: 0.124 ms
 Execution Time: 0.038 ms

hint 位置很随意，可以支持以下3种方式：

test=# explain analyze /*+seqscan(t1)*/select * from t1 where id=123456;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..8383.00 rows=1 width=208) (actual time=15.768..24.416 rows=1 loops=1)
   Filter: (id = 123456)
   Rows Removed by Filter: 199999
 Planning Time: 0.223 ms
 Execution Time: 24.446 ms
(5 rows)
 
test=# explain analyze select/*+seqscan(t1)*/ * from t1 where id=123456;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..8383.00 rows=1 width=208) (actual time=16.367..24.982 rows=1 loops=1)
   Filter: (id = 123456)
   Rows Removed by Filter: 199999
 Planning Time: 0.123 ms
 Execution Time: 25.000 ms
(5 rows)
 
test=# /*+seqscan(t1)*/explain analyze select * from t1 where id=123456;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..8383.00 rows=1 width=208) (actual time=15.967..26.650 rows=1 loops=1)
   Filter: (id = 123456)
   Rows Removed by Filter: 199999
 Planning Time: 0.154 ms
 Execution Time: 26.671 ms
(5 rows)

2、V8R6C4版本

hint 只能放置于select 后面，具体见以下例子。

test=# explain analyze select/*+seqscan(t1)*/  * from t1 where id=123456;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..8383.00 rows=1 width=208) (actual time=36.196..55.491 rows=1 loops=1)
   Filter: (id = 123456)
   Rows Removed by Filter: 199999
 Planning Time: 0.177 ms
 Execution Time: 55.537 ms
(5 rows)
 
test=# explain analyze/*+seqscan(t1)*/ select * from t1 where id=123456;
                                                   QUERY PLAN                                                  
----------------------------------------------------------------------------------------------------------------
 Index Scan using ind_t1_id on t1  (cost=0.42..8.44 rows=1 width=208) (actual time=0.173..0.174 rows=1 loops=1)
   Index Cond: (id = 123456)
 Planning Time: 0.495 ms
 Execution Time: 0.291 ms
(4 rows)

三、V8R6C4支持子查询单独hint

V8R6C4之前的版本不支持子查询hint，所有hint 必须放在父查询。

--所有hint都必须放在父查询select后面，父查询的hint 对于子查询是有效的。
test=# explain select/*+indexscan(t2 ind_t2_id)*/ count(*) from t1 where exists (select id from t2 where t2.id=t1.id);
                                          QUERY PLAN                                         
----------------------------------------------------------------------------------------------
 Aggregate  (cost=25680.20..25680.21 rows=1 width=8)
   ->  Merge Semi Join  (cost=1.36..25180.20 rows=200000 width=0)
         Merge Cond: (t1.id = t2.id)
         ->  Index Only Scan using ind_t1_id on t1  (cost=0.42..11090.42 rows=200000 width=4)
         ->  Index Scan using ind_t2_id on t2  (cost=0.42..11090.42 rows=200000 width=4)
(5 rows)
 
--子查询hint 不生效。
test=# explain select count(*) from t1 where exists (select/*+indexscan(t2 ind_t2_id)*/ id from t2 where t2.id=t1.id);
                                 QUERY PLAN                                
----------------------------------------------------------------------------
 Aggregate  (cost=24644.00..24644.01 rows=1 width=8)
   ->  Hash Semi Join  (cost=11165.00..24144.00 rows=200000 width=0)
         Hash Cond: (t1.id = t2.id)
         ->  Seq Scan on t1  (cost=0.00..7883.00 rows=200000 width=4)
         ->  Hash  (cost=7883.00..7883.00 rows=200000 width=4)
               ->  Seq Scan on t2  (cost=0.00..7883.00 rows=200000 width=4)
(6 rows)

V8R6C4 支持子查询使用单独的hint，具体见以下例子：

--不使用hint 情况，走全表访问。
test=# explain analyze select count(*) from t1 where exists (select id from t2 where t2.id=t1.id);
                                                         QUERY PLAN                                                        
----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=24644.00..24644.01 rows=1 width=8) (actual time=188.164..188.166 rows=1 loops=1)
   ->  Hash Semi Join  (cost=11165.00..24144.00 rows=200000 width=0) (actual time=65.473..178.178 rows=200000 loops=1)
         Hash Cond: (t1.id = t2.id)
         ->  Seq Scan on t1  (cost=0.00..7883.00 rows=200000 width=4) (actual time=0.007..30.990 rows=200000 loops=1)
         ->  Hash  (cost=7883.00..7883.00 rows=200000 width=4) (actual time=65.243..65.243 rows=200000 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 2786kB
               ->  Seq Scan on t2  (cost=0.00..7883.00 rows=200000 width=4) (actual time=0.006..32.561 rows=200000 loops=1)
 Planning Time: 0.183 ms
 Execution Time: 188.219 ms
(9 rows)
 
--子查询使用hint
test=# explain select count(*) from t1 where exists (select/*+indexscan(t2 ind_t2_id)*/ id from t2 where t2.id=t1.id);
                                          QUERY PLAN                                         
----------------------------------------------------------------------------------------------
 Aggregate  (cost=25680.61..25680.62 rows=1 width=8)
   ->  Merge Semi Join  (cost=1.36..25180.61 rows=200000 width=0)
         Merge Cond: (t1.id = t2.id)
         ->  Index Only Scan using ind_t1_id on t1  (cost=0.42..11090.42 rows=200000 width=4)
         ->  Index Scan using ind_t2_id on t2  (cost=0.42..11090.42 rows=200000 width=4)
(5 rows)

四、父查询hint对于子查询同样有效

--在父查询可以对子查询的表指定hint
test=# explain select/*+indexscan(t2 ind_t2_id)*/ count(*) from t1 where exists (select id from t2 where t2.id=t1.id);
                                          QUERY PLAN                                         
----------------------------------------------------------------------------------------------
 Aggregate  (cost=25680.61..25680.62 rows=1 width=8)
   ->  Merge Semi Join  (cost=1.36..25180.61 rows=200000 width=0)
         Merge Cond: (t1.id = t2.id)
         ->  Index Only Scan using ind_t1_id on t1  (cost=0.42..11090.42 rows=200000 width=4)
         ->  Index Scan using ind_t2_id on t2  (cost=0.42..11090.42 rows=200000 width=4)
(5 rows)
 
--在父查询指定涉及子查询表的连接方式
test=# explain select/*+nestloop(t1 t2)*/ count(*) from t1 where exists (select id from t2 where t2.id=t1.id) ;
                                     QUERY PLAN                                     
-------------------------------------------------------------------------------------
 Aggregate  (cost=123619.00..123619.01 rows=1 width=8)
   ->  Nested Loop Semi Join  (cost=0.42..123119.00 rows=200000 width=0)
         ->  Seq Scan on t1  (cost=0.00..7883.00 rows=200000 width=4)
         ->  Index Only Scan using ind_t2_id on t2  (cost=0.42..0.57 rows=1 width=4)
               Index Cond: (id = t1.id)
(5 rows)
 
--甚至在子查询也指定子表与父表的连接方式
test=# explain select count(*) from t1 where exists (select/*+nestloop(t1 t2)*/ id from t2 where t2.id=t1.id) ;
QUERY PLAN
-------------------------------------------------------------------------------------
Aggregate (cost=123619.00..123619.01 rows=1 width=8)
  -> Nested Loop Semi Join (cost=0.42..123119.00 rows=200000 width=0)
       -> Seq Scan on t1 (cost=0.00..7883.00 rows=200000 width=4)
       -> Index Only Scan using ind_t2_id on t2 (cost=0.42..0.57 rows=1 width=4)
Index Cond: (id = t1.id)
(5 rows)

五、注意点

hint 指定的多项的分隔符只能是空格。
可以不用创建 pg_hint_plan，该扩展插件实际提供 hint_plan.hints 表。

KingbaseES Hint 使用

一、启用hint支持

二、hint位置不同

1、V8R6C4之前版本

2、V8R6C4版本

三、V8R6C4支持子查询单独hint

四、父查询hint对于子查询同样有效

五、注意点

相关阅读

相关文章

相关问答

相关文档