Enable_hint_table 使用

KingbaseES enable_hint_table 可以看成类似 oracle outline 工具，可以在不修改SQL 的情况下，通过hint 改变SQL 的执行计划。

一、启用enable_hint_table

1、设置shared_preload_libraries 参数，增加 sys_hint_plan ， 重启数据库

2、修改参数 sys_hint_plan.enable_hint=on , select sys_reload_conf()

3、create extension sys_hint_plan ；创建后，会新建 hint_plan.hints 表。

4、设置 set sys_hint_plan.enable_hint_table = on

二、举例说明

1、没使用 hint_plan.hints 时的执行计划

?

1 2 3 4 5 6 7 8 9 10 11 12

test=# delete from  hint_plan.hints;  DELETE 1 test=# explain analyze select * from t1 where t1.id1 = 1;                                                   QUERY PLAN                                                   ---------------------------------------------------------------------------------------------------------------  Bitmap Heap Scan on t1  (cost=4.20..13.67 rows=6 width=44) (actual time=0.002..0.002 rows=0 loops=1)    Recheck Cond: (id1 = 1)    ->  Bitmap Index Scan on ind_t1  (cost=0.00..4.20 rows=6 width=0) (actual time=0.001..0.001 rows=0 loops=1)          Index Cond: (id1 = 1)  Planning Time: 0.067 ms  Execution Time: 0.011 ms (6 rows)

默认情况下，SQL 走索引扫描。

2、使用 hint_plan.hints 

通过在 hint_plan.hints 插入一条记录，提示使用 SeqScan。

?

1 2 3 4 5 6 7 8 9 10

test=# insert into hint_plan.hints(norm_query_string, application_name, hints) VALUES ( 'Explain analyze select * from t1 where t1.id1 = ?;','','SeqScan(t1)'); INSERT 0 1 test=# Explain analyze select * from t1 where t1.id1 = 1;                                                                                                                                                QUERY PLAN                                          ----------------------------------------------------------------------------------------------  Seq Scan on t1  (cost=0.00..24.12 rows=6 width=44) (actual time=0.004..0.004 rows=0 loops=1)    Filter: (id1 = 1)  Planning Time: 0.169 ms  Execution Time: 0.017 ms (4 rows)

这里有两个地方需要注意：

1、SQL 后面必须有个 “;” ，否则会因为SQL 不匹配而不生效

2、application_name='' ，表示任意应用，注意中间没有空格，否则会因为应用不匹配而不生效

三、SQL 匹配

1、空格不匹配，无法使用hint_table

?

1 2 3 4 5 6 7 8 9 10

test=# explain analyze select * from t1 where t1.id1 =1;                                                    QUERY PLAN                                                   ---------------------------------------------------------------------------------------------------------------  Bitmap Heap Scan on t1  (cost=4.20..13.67 rows=6 width=44) (actual time=0.002..0.002 rows=0 loops=1)    Recheck Cond: (id1 = 1)    ->  Bitmap Index Scan on ind_t1  (cost=0.00..4.20 rows=6 width=0) (actual time=0.001..0.001 rows=0 loops=1)          Index Cond: (id1 = 1)  Planning Time: 0.069 ms  Execution Time: 0.011 ms (6 rows)

2、别名不匹配，无法使用hint_table

?

1 2 3 4 5 6 7 8 9 10

test=# explain analyze select * from t1 a where a.id1 = 1;                                                   QUERY PLAN                                                   ---------------------------------------------------------------------------------------------------------------  Bitmap Heap Scan on t1 a  (cost=4.20..13.67 rows=6 width=44) (actual time=0.003..0.003 rows=0 loops=1)    Recheck Cond: (id1 = 1)    ->  Bitmap Index Scan on ind_t1  (cost=0.00..4.20 rows=6 width=0) (actual time=0.002..0.002 rows=0 loops=1)          Index Cond: (id1 = 1)  Planning Time: 0.053 ms  Execution Time: 0.012 ms (6 rows)

3、大小写不匹配，可以使用hint_table

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

test=# explain analyze select * from t1 WHERE t1.id1 = 1;                                           QUERY PLAN                                          ----------------------------------------------------------------------------------------------  Seq Scan on t1  (cost=0.00..24.12 rows=6 width=44) (actual time=0.002..0.002 rows=0 loops=1)    Filter: (id1 = 1)  Planning Time: 0.080 ms  Execution Time: 0.008 ms (4 rows)   test=# explain analyze select * from T1 where t1.id1 = 1;                                           QUERY PLAN                                          ----------------------------------------------------------------------------------------------  Seq Scan on t1  (cost=0.00..24.12 rows=6 width=44) (actual time=0.002..0.002 rows=0 loops=1)    Filter: (id1 = 1)  Planning Time: 0.076 ms  Execution Time: 0.008 ms (4 rows)