lt_hint_plan 是使用特殊格式的注释提示短语来控制执行计划的功能。
LightDB使用基于成本的优化器，它利用数据统计而不是静态规则。优化器（优化器）估计每个可能的SQL语句执行计划的成本，然后最终执行成本最低的执行计划。优化器尽力选择最佳的执行计划，但并不完美，因为它没有考虑数据之间的相关性等一些数据属性。
lt_hint_plan可以使用所谓的“提示”来微调执行计划，这些提示是特殊格式的SQL注释中的简单描述。
lt_hint_plan会读取给定目标SQL语句中的特殊格式注释中的提示短语。这种特殊格式以字符序列 "/*+" 开始，以 "*/" 结尾。提示短语由提示名称和后面用括号括起来并由空格或逗号分隔的参数组成。每个提示短语都可以通过换行符进行分隔以提高可读性。在SQL语句开头的提示称为全局级别提示，而在语句中的提示称为语句级别提示（仅影响当前查询块而不使用qb_name提示）。
在下面的示例中，选择哈希连接作为连接方法，并使用顺序扫描方法扫描ltbench_accounts表。
lightdb@postgres=# /*+
lightdb@postgres*# HashJoin(a b)
lightdb@postgres*# SeqScan(a)
lightdb@postgres*# */
lightdb@postgres-# EXPLAIN SELECT *
lightdb@postgres-# FROM ltbench_branches b
lightdb@postgres-# JOIN ltbench_accounts a ON b.bid = a.bid
lightdb@postgres-# ORDER BY a.aid;
QUERY PLAN
---------------------------------------------------------------------------------------
Sort (cost=31465.84..31715.84 rows=100000 width=197)
Sort Key: a.aid
-> Hash Join (cost=1.02..4016.02 rows=100000 width=197)
Hash Cond: (a.bid = b.bid)
-> Seq Scan on ltbench_accounts a (cost=0.00..2640.00 rows=100000 width=97)
-> Hash (cost=1.01..1.01 rows=1 width=100)
-> Seq Scan on ltbench_branches b (cost=0.00..1.01 rows=1 width=100)
(7 rows)
lightdb@postgres=#
在上面的部分中，提示在特殊格式的注释中进行描述。在无法编辑查询的情况下，这种方式并不方便。在这种情况下，提示可以放置在名为"hint_plan.hints"的特殊表中。该表由以下列组成。
Table 15.1. hint table
| 列名 | 描述 |
|---|---|
id | 用于标识提示行的唯一编号，该列由序列自动填充。 |
norm_query_string | 与待提示的查询匹配的模式。查询中的常量必须替换为 "?"，如以下示例所示。模式中的空格是有意义的。 |
application_name | 应用提示的会话的application_name值。以下示例中的提示适用于从psql连接的会话。空字符串表示任何application_name的会话。 |
hints | 提示短语。这必须是一系列提示，不包括周围的注释标记。 |
以下示例展示了如何操作提示表。
lightdb@postgres=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints) lightdb@postgres-# VALUES ( lightdb@postgres(# 'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;', lightdb@postgres(# '', lightdb@postgres(# 'SeqScan(t1)' lightdb@postgres(# ); INSERT 0 1 lightdb@postgres=# UPDATE hint_plan.hints lightdb@postgres-# SET hints = 'IndexScan(t1)' lightdb@postgres-# WHERE id = 1; UPDATE 1 lightdb@postgres=# DELETE FROM hint_plan.hints lightdb@postgres-# WHERE id = 1; DELETE 1 lightdb@postgres=#
提示表由创建者用户拥有，并在创建时拥有默认权限。在创建扩展期间，表提示优先于注释提示。
根据提示短语可以对其进行分类，按照其可以影响计划的对象类型和方式进行分类，分为八种类型。扫描方法、连接方法、连接顺序、行数修正、并行查询、GUC设置、查询块名称和其他提示。您可以在提示列表中查看每种类型的提示短语列表。
扫描方法提示会强制在目标表上执行特定的扫描方法。如果有任何别名名称，lt_hint_plan将通过别名来识别目标表。在此类提示中，它们是“SeqScan”、“IndexScan”等。
扫描提示对普通表、继承表、UNLOGGED表、临时表和系统目录有效。外部（外键）表、表函数、VALUES子句、CTE、视图和子查询不受影响。
lightdb@postgres=# /*+ lightdb@postgres*# SeqScan(t1) lightdb@postgres*# IndexScan(t2 t2_pkey) lightdb@postgres*# */ lightdb@postgres-# SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
为了与Oracle兼容，还支持“full”和“index”关键字。它们只是“seqscan”、“indexscan”的别名。
lightdb@postgres=# EXPLAIN SELECT* FROM t2, t1 WHERE t1.id = t2.id;
QUERY PLAN
------------------------------------------------------------------
Hash Join (cost=60.85..99.39 rows=2260 width=16)
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2 (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on t1 (cost=0.00..32.60 rows=2260 width=8)
(5 rows)
lightdb@postgres=# EXPLAIN SELECT /*+index(t1)*/* FROM t2, t1 WHERE t1.id = t2.id;
LOG: lt_hint_plan:
used hint:
INDEX(t1@lt#0)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------------------------------------------------
Hash Join (cost=61.01..111.85 rows=2260 width=16)
Hash Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 @"lt#0" (cost=0.15..45.06 rows=2260 width=8)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on t2 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
(5 rows)
lightdb@postgres=# EXPLAIN SELECT /*+full(t1)*/* FROM t2, t1 WHERE t1.id = t2.id;
LOG: lt_hint_plan:
used hint:
FULL(t1@lt#0)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=60.85..99.39 rows=2260 width=16)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on t2 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
(5 rows)
lightdb@postgres=#
这只会影响普通表、继承表、UNLOGGED表、临时表、外部（外键）表、系统目录、表函数、VALUES命令结果和CTE的连接。但是对视图和子查询的连接不受影响。
为了与Oracle兼容，还支持“use_hash”、“use_nl”、“use_merge”、“no_use_hash”、“no_use_nl”和“no_use_merge”关键字。
当lightdb_dblevel_syntax_compatible_type为“off”时，连接方法提示可以指定两个或多个表，并将强制执行涉及指定表的连接方法。
lightdb@postgres=# EXPLAIN (COSTS false) SELECT /*+leading(t1 t2 t3)*/* FROM t1, t2 ,t3 WHERE t1.id = t2.id and t2.id=t3.id;
LOG: lt_hint_plan:
used hint:
Leading(((t1@lt#0 t2@lt#0) t3@lt#0))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Hash Join
Hash Cond: (t1.id = t3.id)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 @"lt#0"
-> Hash
-> Seq Scan on t2 @"lt#0"
-> Hash
-> Seq Scan on t3 @"lt#0"
(9 rows)
lightdb@postgres=# EXPLAIN (COSTS false) SELECT /*+leading(t1 t2 t3) use_nl(t1 t2) use_merge(t1 t2 t3)*/* FROM t1, t2 ,t3 WHERE t1.id = t2.id and t2.id=t3.id;
LOG: lt_hint_plan:
used hint:
USE_NL(t1@lt#0 t2@lt#0)
USE_MERGE(t1@lt#0 t2@lt#0 t3@lt#0)
Leading(((t1@lt#0 t2@lt#0) t3@lt#0))
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------
Merge Join
Merge Cond: (t1.id = t3.id)
-> Nested Loop
-> Index Scan using t1_pkey on t1 @"lt#0"
-> Index Scan using t2_pkey on t2 @"lt#0"
Index Cond: (id = t1.id)
-> Index Scan using t3_pkey on t3 @"lt#0"
(7 rows)
lightdb@postgres=#
当lightdb_dblevel_syntax_compatible_type不为“off”时，连接方法提示可以指定一个或多个表，并指导优化器使用指定的连接方法将每个指定表与另一个数据源连接。当参考表被强制成连接的内部表时，优化器使用这些提示。如果参考表是外部表，则忽略这些提示。建议与连接顺序提示（'leading'、'ordered'）一起使用。
lightdb@postgres=# EXPLAIN (COSTS false) SELECT /*+leading(t1 t2 t3)*/* FROM t1, t2 ,t3 WHERE t1.id = t2.id and t2.id=t3.id;
LOG: lt_hint_plan:
used hint:
Leading(((t1@lt#0 t2@lt#0) t3@lt#0))
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Hash Join
Hash Cond: (t1.id = t3.id)
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 @"lt#0"
-> Hash
-> Seq Scan on t2 @"lt#0"
-> Hash
-> Seq Scan on t3 @"lt#0"
(9 rows)
lightdb@postgres=# EXPLAIN (COSTS false) SELECT /*+leading(t1 t2 t3) use_nl(t2) use_merge(t3)*/* FROM t1, t2 ,t3 WHERE t1.id = t2.id and t2.id=t3.id;
LOG: lt_hint_plan:
used hint:
USE_NL(t2@lt#0)
USE_MERGE(t3@lt#0)
Leading(((t1@lt#0 t2@lt#0) t3@lt#0))
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------
Merge Join
Merge Cond: (t1.id = t3.id)
-> Nested Loop
-> Index Scan using t1_pkey on t1 @"lt#0"
-> Index Scan using t2_pkey on t2 @"lt#0"
Index Cond: (id = t1.id)
-> Index Scan using t3_pkey on t3 @"lt#0"
(7 rows)
lightdb@postgres=# EXPLAIN (COSTS false) SELECT /*+leading(t1 t2 t3) use_nl(t1) use_merge(t2)*/* FROM t1, t2 ,t3 WHERE t1.id = t2.id and t2.id=t3.id;
LOG: lt_hint_plan:
used hint:
USE_MERGE(t2@lt#0)
Leading(((t1@lt#0 t2@lt#0) t3@lt#0))
not used hint:
USE_NL(t1@lt#0)
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------
Hash Join
Hash Cond: (t1.id = t3.id)
-> Merge Join
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1 @"lt#0"
-> Index Scan using t2_pkey on t2 @"lt#0"
-> Hash
-> Seq Scan on t3 @"lt#0"
(8 rows)
lightdb@postgres=#
当lightdb_dblevel_syntax_compatible_type不为“off”时，指定多个表的连接方法提示可以被视为指定一个表的连接方法提示组， 因此当存在冲突时，它只会影响涉及的表。例如：“use_hash（a b）”和“use_nl（a）”发生冲突，但对于表b，也使用了“use_hash”。
lightdb@postgres=# EXPLAIN (COSTS false) SELECT /*+leading(t1 t2 t3) use_nl(t2 t3) use_merge(t2)*/* FROM t1, t2 ,t3 WHERE t1.id = t2.id and t2.id=t3.id;
INFO: lt_hint_plan: hint syntax error at or near "use_nl(t2 t3) use_merge(t2)"
DETAIL: Conflict join method hint.
LOG: lt_hint_plan:
used hint:
USE_NL(t3@lt#0) --> USE_NL(t2@lt#0 t3@lt#0)
Leading(((t1@lt#0 t2@lt#0) t3@lt#0))
not used hint:
duplication hint:
USE_NL(t2@lt#0) --> USE_NL(t2@lt#0 t3@lt#0)
USE_MERGE(t2@lt#0)
error hint:
QUERY PLAN
----------------------------------------------
Nested Loop
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 @"lt#0"
-> Hash
-> Seq Scan on t2 @"lt#0"
-> Index Scan using t3_pkey on t3 @"lt#0"
Index Cond: (id = t1.id)
(8 rows)
lightdb@postgres=#
当lightdb_dblevel_syntax_compatible_type不为“off”时，连接方法提示可能与“swap_join_inputs”提示发生冲突，而不使用“leading”提示。
lightdb@postgres=# EXPLAIN (COSTS false) SELECT/*+leading(t1 t2) use_hash(t2) swap_join_inputs(t2)*/ * FROM t1, t2 WHERE t1.id = t2.id;
LOG: lt_hint_plan:
used hint:
USE_HASH(t2@lt#0)
Leading((t1@lt#0 t2@lt#0))
swap_join_inputs(t2@lt#0)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------
Hash Join
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2 @"lt#0"
-> Hash
-> Seq Scan on t1 @"lt#0"
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) SELECT/*+use_hash(t2) swap_join_inputs(t2)*/ * FROM t1, t2 WHERE t1.id = t2.id;
INFO: lt_hint_plan: join hint conflict with swap_join_inputs hint, ignore swap_join_inputs
DETAIL: use USE_HASH
LOG: lt_hint_plan:
used hint:
USE_HASH(t2@lt#0)
not used hint:
duplication hint:
swap_join_inputs(t2@lt#0)
error hint:
QUERY PLAN
------------------------------------
Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 @"lt#0"
-> Hash
-> Seq Scan on t2 @"lt#0"
(5 rows)
lightdb@postgres=#
通常，“Leading”提示强制执行两个或多个表的连接顺序。有两种强制执行的方式。一种是强制执行连接的特定顺序，但不限制每个连接级别的方向。另一种方式是在强制执行连接顺序的同时，还强制执行连接方向。详细信息请参见提示列表表格。
lightdb@postgres=# /*+ lightdb@postgres*# NestLoop(t1 t2) lightdb@postgres*# MergeJoin(t1 t2 t3) lightdb@postgres*# Leading(t1 t2 t3) lightdb@postgres*# */ lightdb@postgres-# SELECT * FROM table1 t1 lightdb@postgres-# JOIN table table2 t2 ON (t1.key = t2.key) lightdb@postgres-# JOIN table table3 t3 ON (t2.key = t3.key);
为了与Oracle兼容，“ordered”提示也被支持。ORDERED提示指示LightDB按照FROM子句中出现的顺序连接表。LightDB的“ordered”提示只是“leading”提示的别名，因此它不会像Oracle一样覆盖“leading”提示。
lightdb@postgres=# EXPLAIN SELECT * FROM t3, t2, t1 WHERE t1.id = t2.id;
QUERY PLAN
------------------------------------------------------------------------
Nested Loop (cost=60.85..63982.64 rows=5107600 width=24)
-> Hash Join (cost=60.85..99.39 rows=2260 width=16)
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2 (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on t1 (cost=0.00..32.60 rows=2260 width=8)
-> Materialize (cost=0.00..43.90 rows=2260 width=8)
-> Seq Scan on t3 (cost=0.00..32.60 rows=2260 width=8)
(8 rows)
lightdb@postgres=# EXPLAIN SELECT/*+ordered*/ * FROM t3, t2, t1 WHERE t1.id = t2.id;
LOG: lt_hint_plan:
used hint:
Leading(@lt#0 ((t3@lt#0 t2@lt#0) t1@lt#0))
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------
Hash Join (cost=60.85..77409.29 rows=5107600 width=24)
Hash Cond: (t2.id = t1.id)
-> Nested Loop (cost=0.00..63915.85 rows=5107600 width=16)
-> Seq Scan on t3 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
-> Materialize (cost=0.00..43.90 rows=2260 width=8)
-> Seq Scan on t2 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on t1 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
(8 rows)
lightdb@postgres=#
为了与Oracle兼容，“leading”提示也被支持，用于指定一个表作为连接的起点。
lightdb@postgres=# EXPLAIN SELECT * FROM t3, t2, t1 WHERE t1.id = t2.id;
QUERY PLAN
------------------------------------------------------------------------
Nested Loop (cost=60.85..63982.64 rows=5107600 width=24)
-> Hash Join (cost=60.85..99.39 rows=2260 width=16)
Hash Cond: (t2.id = t1.id)
-> Seq Scan on t2 (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on t1 (cost=0.00..32.60 rows=2260 width=8)
-> Materialize (cost=0.00..43.90 rows=2260 width=8)
-> Seq Scan on t3 (cost=0.00..32.60 rows=2260 width=8)
(8 rows)
lightdb@postgres=# EXPLAIN SELECT/*+leading(t1)*/ * FROM t3, t2, t1 WHERE t1.id = t2.id;
LOG: lt_hint_plan:
used hint:
Leading(t1@lt#0)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------------
Nested Loop (cost=10000000060.85..10000063982.64 rows=5107600 width=24)
-> Hash Join (cost=60.85..99.39 rows=2260 width=16)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on t1 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on t2 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
-> Materialize (cost=0.00..43.90 rows=2260 width=8)
-> Seq Scan on t3 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
(8 rows)
lightdb@postgres=# EXPLAIN SELECT/*+leading(t3)*/ * FROM t3, t2, t1 WHERE t1.id = t2.id;
LOG: lt_hint_plan:
used hint:
Leading(t3@lt#0)
not used hint:
duplication hint:
error hint:
QUERY PLAN
-------------------------------------------------------------------------------------------
-
Nested Loop (cost=10000000000.16..10000064316.68 rows=5107600 width=24)
-> Seq Scan on t3 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
-> Materialize (cost=10000000000.16..10000000444.73 rows=2260 width=16)
-> Nested Loop (cost=10000000000.16..10000000433.43 rows=2260 width=16)
-> Seq Scan on t2 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
-> Index Scan using t1_pkey on t1 @"lt#0" (cost=0.15..0.18 rows=1 width=8)
Index Cond: (id = t2.id)
(7 rows)
lightdb@postgres=#
“Rows”提示可以纠正优化器限制导致的连接行数估计错误。
“Rows”提示还可以纠正表或子查询的行数估计错误。
lightdb@postgres=# /*+ Rows(a #10) */ SELECT... ; Sets rows of table result to 10 lightdb@postgres=# /*+ Rows(a 10) */ SELECT... ; Sets rows of table result to 10 lightdb@postgres=# /*+ Rows(a +10) */ SELECT... ; Increments row number of tableby 10 lightdb@postgres=# /*+ Rows(a -10) */ SELECT... ; Subtracts 10 from the row number of table. lightdb@postgres=# /*+ Rows(a *10) */ SELECT... ; Makes the row number of table 10 times larger. lightdb@postgres=# /*+ Rows(a b #10) */ SELECT... ; Sets rows of join result to 10 lightdb@postgres=# /*+ Rows(a b 10) */ SELECT... ; Sets rows of join result to 10 lightdb@postgres=# /*+ Rows(a b +10) */ SELECT... ; Increments row number of join by 10 lightdb@postgres=# /*+ Rows(a b -10) */ SELECT... ; Subtracts 10 from the row number of join. lightdb@postgres=# /*+ Rows(a b *10) */ SELECT... ; Makes the row number of join 10 times larger. lightdb@postgres=# /*+ cardinality(a #10) */ SELECT... ; Sets rows of table result to 10 lightdb@postgres=# /*+ cardinality(a 10) */ SELECT... ; Sets rows of table result to 10 lightdb@postgres=# /*+ cardinality(a +10) */ SELECT... ; Increments row number of tableby 10 lightdb@postgres=# /*+ cardinality(a -10) */ SELECT... ; Subtracts 10 from the row number of table. lightdb@postgres=# /*+ cardinality(a *10) */ SELECT... ; Makes the row number of table 10 times larger. lightdb@postgres=# /*+ cardinality(a b #10) */ SELECT... ; Sets rows of join result to 10 lightdb@postgres=# /*+ cardinality(a b 10) */ SELECT... ; Sets rows of join result to 10 lightdb@postgres=# /*+ cardinality(a b +10) */ SELECT... ; Increments row number of join by 10 lightdb@postgres=# /*+ cardinality(a b -10) */ SELECT... ; Subtracts 10 from the row number of join. lightdb@postgres=# /*+ cardinality(a b *10) */ SELECT... ; Makes the row number of join 10 times larger.
对于参数化路径，该提示无效，但它会影响基于参数化路径的连接成本。这与Oracle不同，Oracle不会影响连接成本。
lightdb@postgres=# explain select /*+ use_nl(a b) leading(a b)*/* from t1 a , t2 b where a.key1=b.key1;
QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop (cost=0.15..433.43 rows=2260 width=16)
-> Seq Scan on t1 a @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
-> Index Scan using t2_pkey on t2 b @"lt#0" (cost=0.15..0.18 rows=1 width=8)
Index Cond: (key1 = a.key1)
(4 rows)
lightdb@postgres=# explain select /*+ cardinality(b 1000)use_nl(a b) leading(a b)*/* from t1 a , t2 b where a.key1=b.key1;
QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop (cost=0.15..433.43 rows=1000 width=16)
-> Seq Scan on t1 a @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
-> Index Scan using t2_pkey on t2 b @"lt#0" (cost=0.15..0.18 rows=1 width=8)
Index Cond: (key1 = a.key1)
(4 rows)
chuhx@postgres=#
对于无法拉取的子查询，它将作用于子查询； 对于已拉取的子查询，如果子查询中只有一个表，则将应用于子查询中的表。
chuhx@postgres=# explain with cte as (select distinct * from t1) select /*+hashjoin(t2, cte) cardinality(cte 1000) */* from t2,cte where cte.key1=t2.key1;
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=104.75..152.57 rows=1000 width=16)
Hash Cond: (t1.key1 = t2.key1)
-> HashAggregate (cost=43.90..66.50 rows=2260 width=8)
Group Key: t1.key1, t1.key2
-> Seq Scan on t1 @"lt#1" (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on t2 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
(7 rows)
chuhx@postgres=# explain with cte as (select * from t1) select /*+hashjoin(t2, cte) cardinality(cte 1000) */* from t2,cte where cte.key1=t2.key1;
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=45.10..83.64 rows=1000 width=16)
Hash Cond: (t2.key1 = t1.key1)
-> Seq Scan on t2 @"lt#0" (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=32.60..32.60 rows=1000 width=8)
-> Seq Scan on t1 @"lt#1" (cost=0.00..32.60 rows=1000 width=8)
(5 rows)
chuhx@postgres=#
“Parallel”提示强制执行扫描的并行执行配置。第三个参数指定了强制执行的强度。"soft"意味着lt_hint_plan仅更改max_parallel_worker_per_gather，而将其他参数保留给优化器。"hard"更改其他优化器参数，以强制应用数字。这可能会影响普通表、继承父项、非日志表和系统目录。外部表、表函数、值子句、CTE、视图和子查询不受影响。视图的内部表可以通过其实际名称/别名作为目标对象来指定。以下示例显示了如何在每个表上不同地强制执行查询。
lightdb@postgres=# explain /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);
QUERY PLAN
-------------------------------------------------------------------------------
Hash Join (cost=2.86..11406.38 rows=101 width=4)
Hash Cond: (c1.a = c2.a)
-> Gather (cost=0.00..7652.13 rows=1000101 width=4)
Workers Planned: 3
-> Parallel Seq Scan on c1 (cost=0.00..7652.13 rows=322613 width=4)
-> Hash (cost=1.59..1.59 rows=101 width=4)
-> Gather (cost=0.00..1.59 rows=101 width=4)
Workers Planned: 5
-> Parallel Seq Scan on c2 (cost=0.00..1.59 rows=59 width=4)
lightdb@postgres=# EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;
QUERY PLAN
-----------------------------------------------------------------------------------
Finalize Aggregate (cost=693.02..693.03 rows=1 width=8)
-> Gather (cost=693.00..693.01 rows=5 width=8)
Workers Planned: 5
-> Partial Aggregate (cost=693.00..693.01 rows=1 width=8)
-> Parallel Seq Scan on tl (cost=0.00..643.00 rows=20000 width=4)
这个"Parallel"提示也可以只指定一个整数来使用，表示它适用于当前查询块中的所有表。
lightdb@postgres=# explain select /*+parallel(3)*/* from t1, t2 where t1.id=t2.id;
QUERY PLAN
-----------------------------------------------------------------------------------
----
Hash Join (cost=28.25..34.19 rows=2260 width=16)
Hash Cond: (t1.id = t2.id)
-> Gather (cost=0.00..0.00 rows=2260 width=8)
Workers Planned: 3
-> Parallel Seq Scan on t1 @"lt#0" (cost=0.00..0.00 rows=729 width=8)
-> Hash (cost=0.00..0.00 rows=2260 width=8)
-> Gather (cost=0.00..0.00 rows=2260 width=8)
Workers Planned: 3
-> Parallel Seq Scan on t2 @"lt#0" (cost=0.00..0.00 rows=729 width
=8)
(9 rows)
lightdb@postgres=#
“Set”提示在规划过程中只更改GUC参数。在查询规划中显示的GUC参数可以对规划产生预期的影响，除非任何其他提示与优化器方法配置参数发生冲突。lt_hint_plan的GUC参数也可以通过此提示进行设置，但它可能不会像您期望的那样工作。
lightdb@postgres=# /*+ Set(random_page_cost 2.0) */ lightdb@postgres-# SELECT * FROM table1 t1 WHERE key = 'value'; ...
‘Opt_param’提示和 ‘Set’提示一样，用于在生成执行计划的过程中修改GUC参数. 但是也有些与‘Set’提示不同的地方 (不过与oracle的‘opt_param’相同):
参数名必须用单引号括起来。
字符型的参数值要用单引号括起来，数值型的不用。
EXPLAIN (COSTS false) select * from test_opt_param1 a join test_opt_param2 b on a.key1=b.key1;
QUERY PLAN
-------------------------------------------
Hash Join
Hash Cond: (a.key1 = b.key1)
-> Seq Scan on test_opt_param1 a
-> Hash
-> Seq Scan on test_opt_param2 b
(5 rows)
EXPLAIN (COSTS false) select/*+opt_param('enable_hashjoin', 'off')*/ * from test_opt_param1 a join test_opt_param2 b on a.key1=b.key1;
LOG: lt_hint_plan:
used hint:
opt_param('enable_hashjoin' 'off')
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------
Merge Join
Merge Cond: (a.key1 = b.key1)
-> Index Scan using test_opt_param1_pkey on test_opt_param1 a @"lt#0"
-> Index Scan using test_opt_param2_pkey on test_opt_param2 b @"lt#0"
(4 rows)
EXPLAIN (COSTS false) select/*+opt_param(enable_hashjoin, 'off')*/ * from test_opt_param1 a join test_opt_param2 b on a.key1=b.key1;
INFO: lt_hint_plan: hint syntax error at or near "opt_param(enable_hashjoin, 'off')"
DETAIL: opt_param hint requires parameter name to be enclosed in single quotation marks.
LOG: lt_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
opt_param(enable_hashjoin 'off')
QUERY PLAN
---------------------------------------------------
Hash Join
Hash Cond: (a.key1 = b.key1)
-> Seq Scan on test_opt_param1 a @"lt#0"
-> Hash
-> Seq Scan on test_opt_param2 b @"lt#0"
(5 rows)
lightdb@postgres=# explain select * from test_opt_param1 where key1=1;
QUERY PLAN
-----------------------------------------------------------------------------
---------------
Index Scan using test_opt_param1_pkey on test_opt_param1 (cost=0.15..2.17 r
ows=1 width=8)
Index Cond: (key1 = 1)
(2 rows)
lightdb@postgres=# explain select/*+ Set(random_page_cost 1000) */ * from test_opt_param1 where key1=1;
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on test_opt_param1 @"lt#0" (cost=0.00..38.25 rows=1 width=8)
Filter: (key1 = 1)
(2 rows)
使用qb_name提示为查询块定义名称。然后可以在另一个查询块中使用该名称来提示出现在命名查询块中的表。如果两个或多个查询块具有相同的名称，或者同一个查询块用不同的名称提示两次，则所有名称和引用它们的提示都将被忽略。未使用此提示命名的查询块具有唯一的系统生成名称。
lightdb@postgres=# explain select/*+indexscan(t2@qb)*/ * from t1 ,(select /*+qb_name(qb)*/* from t2 where id >10) as tt;
LOG: lt_hint_plan:
used hint:
IndexScan(t2@qb)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------------------------------------------
Nested Loop (cost=0.13..3.21 rows=3 width=532)
-> Index Scan using idx_t2_id on t2 (cost=0.13..2.15 rows=1 width=8)
Index Cond: (id > 10)
-> Seq Scan on t1 (cost=0.00..1.03 rows=3 width=524)
(4 rows)
lightdb@postgres=#
lightdb@postgres=# explain select/*+indexscan(t2@qb) indexscan(t3@qb1)*/ * from t1 ,(select /*+qb_name(qb)*/* from t2 where id >10) as tt where exists (select/*+qb_name(qb1)*/ * from t3 where id>1);
QUERY PLAN
-----------------------------------------------------------------------------------
-
Result (cost=2.28..5.35 rows=3 width=532)
One-Time Filter: $0
InitPlan 1 (returns $0)
-> Index Scan using idx_t3_id on t3 @qb1 (cost=0.13..2.15 rows=1 width=0)
Index Cond: (id > 1)
-> Nested Loop (cost=2.28..5.35 rows=3 width=532)
-> Index Scan using idx_t2_id on t2 @qb (cost=0.13..2.15 rows=1 width=8)
Index Cond: (id > 10)
-> Seq Scan on t1 @"lt#0" (cost=0.00..1.03 rows=3 width=524)
(9 rows)
lightdb@postgres=#
不能归类为上述提示的提示。
“NO_MERGE”提示指示优化器不要将外部查询和任何内联视图查询合并为单个查询。
lightdb@postgres=# EXPLAIN (COSTS false) select * from t1, (select /*+no_merge*/t2.id from t2,t3) tt where t1.id=tt.id;
LOG: lt_hint_plan:
used hint:
no_merge
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Hash Join
Hash Cond: (t2.id = t1.id)
-> Nested Loop
-> Seq Scan on t2 @"lt#0"
-> Materialize
-> Seq Scan on t3 @"lt#0"
-> Hash
-> Seq Scan on t1 @"lt#1"
(8 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select/*+ no_merge(tt)*/ * from t1, (select t2.id from t2,t3) tt where t1.id=tt.id;
LOG: lt_hint_plan:
used hint:
no_merge(tt@lt#0)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------
Hash Join
Hash Cond: (t2.id = t1.id)
-> Nested Loop
-> Seq Scan on t2
-> Materialize
-> Seq Scan on t3
-> Hash
-> Seq Scan on t1 @"lt#0"
(8 rows)
lightdb@postgres=#
lightdb@postgres=# EXPLAIN (COSTS false) select/*+ no_merge(@qb)*/ * from t1, (select/*+qb_name(qb)*/ t2.id from t2,t3) tt where t1.id=tt.id;
LOG: lt_hint_plan:
used hint:
no_merge(@qb)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------------
Hash Join
Hash Cond: (t2.id = t1.id)
-> Nested Loop
-> Seq Scan on t2 @qb
-> Materialize
-> Seq Scan on t3 @qb
-> Hash
-> Seq Scan on t1 @"lt#0"
(8 rows)
lightdb@postgres=#
“use_hash_aggregation”提示指示优化器在分组时使用哈希聚合。 “no_use_hash_aggregation”提示指示优化器在分组时不使用哈希聚合，而使用排序。
使用这些提示有两种方法。第一种没有参数，指定当前语句是否使用哈希算法，第二种与查询块名称组合使用。
以下示例显示了use_hash_aggregation提示的效果。
lightdb@postgres=# EXPLAIN (COSTS false) select max(id) from t1 where id>1 group by id order by id;
QUERY PLAN
-------------------------------------------
GroupAggregate
Group Key: id
-> Index Only Scan using t1_pkey on t1
Index Cond: (id > 1)
(4 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select/*+ use_hash_aggregation*/ max(id) from t1 where id>1 group by id order by id;
QUERY PLAN
------------------------------------
Sort
Sort Key: id
-> HashAggregate
Group Key: id
-> Seq Scan on t1 @"lt#0"
Filter: (id > 1)
(6 rows)
lightdb@postgres=#
以下示例显示了no_use_hash_aggregation提示的效果。
lightdb@postgres=# EXPLAIN (COSTS false) select max(id) from t1 where id>1 group by id;
QUERY PLAN
--------------------------
HashAggregate
Group Key: id
-> Seq Scan on t1
Filter: (id > 1)
(4 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select/*+ no_use_hash_aggregation*/ max(id) from t1 where id>1 group by id;
QUERY PLAN
---------------------------------------------------
GroupAggregate
Group Key: id
-> Index Only Scan using t1_pkey on t1 @"lt#0"
Index Cond: (id > 1)
(4 rows)
lightdb@postgres=#
半连接的提示有“semijoin”，“hash_sj”，“nl_sj”，“merge_sj”和“no_semijoin”，用于控制是否使用半连接或指定半连接算法。 反连接的提示有“hash_aj”，“nl_aj”，“merge_aj”，用于指定反连接算法。
使用这些提示有两种方法。第一种没有参数，必须放置在子链接中，第二种与查询块名称组合使用。
以下示例显示了使用半连接提示的效果。
lightdb@postgres=# create table test1 (key1 int primary key, key2 int);
CREATE TABLE
lightdb@postgres=# create table test2 (key1 int primary key, key2 int);
CREATE TABLE
lightdb@postgres=#
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists (select * from test2 where test1.key1=test2.key1);
QUERY PLAN
----------------------------------------
Hash Join
Hash Cond: (test1.key1 = test2.key1)
-> Seq Scan on test1
-> Hash
-> Seq Scan on test2
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists (select /*+semijoin*/* from test2 where test1.key1=test2.key1);
QUERY PLAN
---------------------------------------------------------
Merge Semi Join
Merge Cond: (test1.key1 = test2.key1)
-> Index Scan using test1_pkey on test1 @"lt#1"
-> Index Only Scan using test2_pkey on test2 @"lt#0"
(4 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists (select /*+hash_sj*/* from test2 where test1.key1=test2.key1);
QUERY PLAN
----------------------------------------
Hash Semi Join
Hash Cond: (test1.key1 = test2.key1)
-> Seq Scan on test1 @"lt#1"
-> Hash
-> Seq Scan on test2 @"lt#0"
(5 rows)
lightdb@postgres=#
以下示例显示了不使用半连接提示的效果。
lightdb@postgres=# create table tt1 (id int, t int, name varchar(255));
CREATE TABLE
lightdb@postgres=# create table tt2 (id int , salary int);
CREATE TABLE
lightdb@postgres=# create index idx_t1_id on tt1(id);
CREATE INDEX
lightdb@postgres=# create index idx_t2_id on tt2(id);
CREATE INDEX
lightdb@postgres=#
lightdb@postgres=# EXPLAIN (COSTS false) select * from tt1 where exists (select * from tt2 where tt1.id=tt2.id);
QUERY PLAN
----------------------------------------------
Nested Loop Semi Join
-> Seq Scan on tt1
-> Index Only Scan using idx_t2_id on tt2
Index Cond: (id = tt1.id)
(4 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from tt1 where exists (select /*+no_semijoin*/* from tt2 where tt1.id=tt2.id);
QUERY PLAN
-------------------------------------------
Hash Join
Hash Cond: (tt1.id = tt2.id)
-> Seq Scan on tt1 @"lt#1"
-> Hash
-> HashAggregate
Group Key: tt2.id
-> Seq Scan on tt2 @"lt#0"
(7 rows)
lightdb@postgres=#
以下示例显示了使用反连接提示的效果。
lightdb@postgres=# create table tt1 (id int, t int, name varchar(255));
CREATE TABLE
lightdb@postgres=# create table tt2 (id int , salary int);
CREATE TABLE
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where not exists (select 1 from test2 where test1.key1=test2.key1);
QUERY PLAN
----------------------------------------
Hash Anti Join
Hash Cond: (test1.key1 = test2.key1)
-> Seq Scan on test1
-> Hash
-> Seq Scan on test2
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where not exists (select/*+hash_aj*/ 1 from test2 where test1.key1=test2.key1);
QUERY PLAN
----------------------------------------
Hash Anti Join
Hash Cond: (test1.key1 = test2.key1)
-> Seq Scan on test1 @"lt#1"
-> Hash
-> Seq Scan on test2 @"lt#0"
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where not exists (select/*+nl_aj*/ 1 from test2 where test1.key1=test2.key1);
QUERY PLAN
---------------------------------------------------------
Nested Loop Anti Join
-> Seq Scan on test1 @"lt#1"
-> Index Only Scan using test2_pkey on test2 @"lt#0"
Index Cond: (key1 = test1.key1)
(4 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where not exists (select/*+merge_aj*/ 1 from test2 where test1.key1=test2.key1);
QUERY PLAN
---------------------------------------------------------
Merge Anti Join
Merge Cond: (test1.key1 = test2.key1)
-> Index Scan using test1_pkey on test1 @"lt#1"
-> Index Only Scan using test2_pkey on test2 @"lt#0"
(4 rows)
lightdb@postgres=#
"no_semijoin"并不会阻止拉升子链接，而是在可以选择其他路径时不选择半连接路径。
no_semijoin invalid：
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists (select /*+no_semijoin*/* from test2 where test1.key1>test2.key2);
LOG: lt_hint_plan:
used hint:
no_semijoin
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------------
Nested Loop Semi Join
Join Filter: (test1.key1 > test2.key2)
-> Seq Scan on test1 @"lt#1"
-> Materialize
-> Seq Scan on test2 @"lt#0"
(5 rows)
lightdb@postgres=#
当与连接方法提示一起使用时，连接方法提示将被忽略。以下示例展示了它。
lightdb@postgres=# explain select/*+nestloop(test1 test2@qb)*/ * from test1 where exists (select /*+semijoin qb_name(qb)*/* from test2 where test1.key1=test2.key1);
INFO: lt_hint_plan: join type hint is conflicted with special join hint,ignore join type hint
DETAIL: join type hint: NestLoop, special join hint: semijoin(@qb)
LOG: lt_hint_plan:
used hint:
semijoin
not used hint:
duplication hint:
NestLoop(test1@lt#0 test2@qb)
error hint:
QUERY PLAN
-----------------------------------------------------------------------------------
--------
Merge Semi Join (cost=0.31..124.01 rows=2260 width=8)
Merge Cond: (test1.key1 = test2.key1)
-> Index Scan using test1_pkey on test1 @"lt#0" (cost=0.15..45.06 rows=2260 wi
dth=8)
-> Index Only Scan using test2_pkey on test2 @qb (cost=0.15..45.06 rows=2260 w
idth=4)
(4 rows)
lightdb@postgres=#
当与前导提示一起使用时，与这些提示冲突的前导提示部分将被忽略， 但这仍可能使这些提示无效。当存在冲突时，结果可能不如预期， 所以最好的方法是解决冲突。以下示例展示了冲突。
lightdb@postgres=# explain select/*+leading(test2@qb test1 )*/ * from test1 where exists (select /*+semijoin qb_name(qb)*/* from test2 where test1.key1=test2.key1);
INFO: lt_hint_plan: special join hint conflict with sub of leading hint, ignore sub of leading hint
DETAIL: use special join hint: semijoin(@qb), the sub of leading has 2 relation
LOG: lt_hint_plan:
used hint:
Leading((test2@qb test1@lt#0))
semijoin
not used hint:
duplication hint:
error hint:
QUERY PLAN
-----------------------------------------------------------------------------------
--------
Merge Semi Join (cost=0.31..124.01 rows=2260 width=8)
Merge Cond: (test1.key1 = test2.key1)
-> Index Scan using test1_pkey on test1 @"lt#0" (cost=0.15..45.06 rows=2260 wi
dth=8)
-> Index Only Scan using test2_pkey on test2 @qb (cost=0.15..45.06 rows=2260 w
idth=4)
(4 rows)
lightdb@postgres=#
这个提示"swap_join_inputs"指定哈希连接的外部表。
以下示例展示了"swap_join_inputs"的效果。
lightdb@postgres=# create table test1 (key1 int primary key, key2 int);
CREATE TABLE
lightdb@postgres=# create table test2 (key1 int primary key, key2 int);
CREATE TABLE
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1, test2 where test1.key1 = test2.key1;
QUERY PLAN
----------------------------------------
Hash Join
Hash Cond: (test1.key1 = test2.key1)
-> Seq Scan on test1
-> Hash
-> Seq Scan on test2
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select /*+swap_join_inputs(test2)*/* from test1, test2 where test1.key1 = test2.key1;
LOG: lt_hint_plan:
used hint:
swap_join_inputs(test2@lt#0)
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------
Hash Join
Hash Cond: (test2.key1 = test1.key1)
-> Seq Scan on test2 @"lt#0"
-> Hash
-> Seq Scan on test1 @"lt#0"
(5 rows)
lightdb@postgres=#
使用这个提示将强制SQL尽可能使用哈希连接。以下示例展示了它。
lightdb@postgres=# create table t_1(key1 int not null);
CREATE TABLE
lightdb@postgres=# create table t_2(key1 int not null);
CREATE TABLE
lightdb@postgres=# explain select /*swap_join_inputs(t_2)*/* from t_1,t_2 where t_1.key1=t_2.key1;
QUERY PLAN
-------------------------------------------------------------------
Merge Join (cost=359.57..860.00 rows=32512 width=8)
Merge Cond: (t_1.key1 = t_2.key1)
-> Sort (cost=179.78..186.16 rows=2550 width=4)
Sort Key: t_1.key1
-> Seq Scan on t_1 (cost=0.00..35.50 rows=2550 width=4)
-> Sort (cost=179.78..186.16 rows=2550 width=4)
Sort Key: t_2.key1
-> Seq Scan on t_2 (cost=0.00..35.50 rows=2550 width=4)
(8 rows)
lightdb@postgres=# explain select /*+swap_join_inputs(t_2)*/* from t_1,t_2 where t_1.key1=t_2.key1;
LOG: lt_hint_plan:
used hint:
swap_join_inputs(t_2@lt#0)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------------
Hash Join (cost=67.38..1247.18 rows=32512 width=8)
Hash Cond: (t_2.key1 = t_1.key1)
-> Seq Scan on t_2 @"lt#0" (cost=0.00..35.50 rows=2550 width=4)
-> Hash (cost=35.50..35.50 rows=2550 width=4)
-> Seq Scan on t_1 @"lt#0" (cost=0.00..35.50 rows=2550 width=4)
(5 rows)
lightdb@postgres=#
当与前导提示一起使用时，与这些提示冲突的前导提示部分将被忽略。 以下示例展示了它。
lightdb@postgres=# EXPLAIN (COSTS false) select /*+ leading(test1 test2) swap_join_inputs(test2)*/* from test1, test2 where test1.key1 = test2.key1;
INFO: lt_hint_plan: leading hint conflict with swap_join_inputs hint, ignore leading hint
DETAIL: use swap_join_hint(test2@lt#0)
LOG: lt_hint_plan:
used hint:
Leading((test1@lt#0 test2@lt#0))
swap_join_inputs(test2@lt#0)
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------
Hash Join
Hash Cond: (test2.key1 = test1.key1)
-> Seq Scan on test2 @"lt#0"
-> Hash
-> Seq Scan on test1 @"lt#0"
(5 rows)
lightdb@postgres=#
当与连接方法提示一起使用时，如果连接方法提示指定了非哈希连接方法，那么这个提示就不能使用。 以下示例展示了它。
lightdb@postgres=# EXPLAIN (COSTS false) select /*+ nestloop(test1 test2) swap_join_inputs(test2)*/* from test1, test2 where test1.key1 = test2.key1;
LOG: lt_hint_plan:
used hint:
NestLoop(test1@lt#0 test2@lt#0)
not used hint:
swap_join_inputs(test2@lt#0)
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------
Nested Loop
-> Seq Scan on test1 @"lt#0"
-> Index Scan using test2_pkey on test2 @"lt#0"
Index Cond: (key1 = test1.key1)
(4 rows)
lightdb@postgres=#
这个提示不应该用于半连接和反连接，因为半连接和反连接不能改变外部表。 当与半连接和反连接提示一起使用时，这个提示将被忽略。以下示例展示了它。
lightdb@postgres=# EXPLAIN (COSTS false) select/*+swap_join_inputs(test2@qb)*/ * from test1 where exists ( select/*+semijoin qb_name(qb)*/ * from test2 where test1.key1 = test2.key1);
INFO: lt_hint_plan: special join hint conflict with swap_join_inputs hint, ignore swap_join_inputs hint
DETAIL: ignore swap_join_hint(test2@qb), use semijoin(@qb)
LOG: lt_hint_plan:
used hint:
semijoin
not used hint:
duplication hint:
swap_join_inputs(test2@qb)
error hint:
QUERY PLAN
-----------------------------------------------------
Merge Semi Join
Merge Cond: (test1.key1 = test2.key1)
-> Index Scan using test1_pkey on test1 @"lt#0"
-> Index Only Scan using test2_pkey on test2 @qb
(4 rows)
lightdb@postgres=#
这个提示"ordered_predicates"强制优化器保留谓词评估的顺序，除了用作索引键的谓词。 对于SELECT语句的WHERE子句使用这个提示。
您可以使用ordered_predicates提示覆盖这些默认的评估规则，然后您的WHERE子句项将按照它们在查询中出现的顺序进行评估。 在查询的WHERE子句中使用函数时，通常会使用ordered_predicates提示。 在您知道最具限制性的谓词并希望Lightdb首先评估它们的情况下，它也非常有用。
以下示例展示了"ordered_predicates"的效果。
lightdb@postgres=# create table test1 (key1 int, key2 int);
CREATE TABLE
lightdb@postgres=# create table test2 (key1 int, key2 int);
CREATE TABLE
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where mod(key1,10)=2 and key2=2;
QUERY PLAN
------------------------------------------------
Seq Scan on test1
Filter: ((key2 = 2) AND (mod(key1, 10) = 2))
(2 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select /*+ordered_predicates*/* from test1 where mod(key1,10)=2 and key2=2;
QUERY PLAN
------------------------------------------------
Seq Scan on test1 @"lt#0"
Filter: ((mod(key1, 10) = 2) AND (key2 = 2))
(2 rows)
lightdb@postgres=#
这个提示不会影响优化器生成的传递生成的谓词。
在以下示例中，"b.key2 = 1"被优化器重新生成，因此它的顺序不能被保留。
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 a left join test2 b on a.key1=b.key1 and a.key2=b.key2 where mod(b.key2, 10) = 2 and b.key2 = 1;
QUERY PLAN
------------------------------------------------------
Nested Loop
Join Filter: (a.key1 = b.key1)
-> Seq Scan on test2 b
Filter: ((key2 = 1) AND (mod(key2, 10) = 2))
-> Seq Scan on test1 a
Filter: (key2 = 1)
(6 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select/*+ordered_predicates*/ * from test1 a left join test2 b on a.key1=b.key1 and a.key2=b.key2 where mod(b.key2, 10) = 2 and b.key2 = 1;
QUERY PLAN
------------------------------------------------------
Nested Loop
Join Filter: (a.key1 = b.key1)
-> Seq Scan on test2 b @"lt#0"
Filter: ((key2 = 1) AND (mod(key2, 10) = 2))
-> Seq Scan on test1 a @"lt#0"
Filter: (key2 = 1)
(6 rows)
lightdb@postgres=#
提取子查询的优化是RBO，它会尽可能地提取子查询。 所以没有必要实现"unnest"提示，我们在这里只支持语法。
"no_unnest"提示可用于强制不提取子查询。
以下示例展示了"no_unnest"的效果。
lightdb@postgres=# create table test1 (key1 int, key2 int);
CREATE TABLE
lightdb@postgres=# create table test2 (key1 int, key2 int);
CREATE TABLE
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists(select * from test2 where test1.key1=test2.key1);
QUERY PLAN
----------------------------------------
Hash Join
Hash Cond: (test1.key1 = test2.key1)
-> Seq Scan on test1
-> Hash
-> HashAggregate
Group Key: test2.key1
-> Seq Scan on test2
(7 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists(select/*+no_unnest*/ * from test2 where test1.key1=test2.key1);
QUERY PLAN
---------------------------------------------------------
Seq Scan on test1 @"lt#1"
Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
SubPlan 1
-> Seq Scan on test2 @"lt#0"
Filter: (test1.key1 = key1)
SubPlan 2
-> Seq Scan on test2 test2_1 @"lt#0"
(7 rows)
lightdb@postgres=#
现在'pq_distribute'提示仅用于并行哈希连接， 并且只有这种用法('pq_distribute(inner_table none broadcast)')有效。
支持使用('pq_distribute(inner_table hash hash)')，但不起作用。
当与'leading'提示一起使用时，只有在使用'leading'提示后指定的表是内部表时才会使用它。
以下示例展示了"pq_distribute"的效果。
lightdb@postgres=# create table t1 (id int, val text);
CREATE TABLE
lightdb@postgres=# create table t2 (id int, val text);
CREATE TABLE
lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
lightdb@lt_test-# EXPLAIN (COSTS false) SELECT /*+ leading(t1 t2) hashjoin(t1 t2)*/ * FROM t1 join t2 on t1.id=t2.id;
QUERY PLAN
---------------------------------------------------
Gather
Workers Planned: 3
-> Parallel Hash Join
Hash Cond: (t1.id = t2.id)
-> Parallel Seq Scan on t1 @"lt#0"
-> Parallel Hash
-> Parallel Seq Scan on t2 @"lt#0"
(7 rows)
lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
lightdb@lt_test-# EXPLAIN (COSTS false) select/*+leading(t1 t2) hashjoin(t1 t2) pq_distribute(t2 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
QUERY PLAN
---------------------------------------------
Gather
Workers Planned: 3
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Parallel Seq Scan on t1 @"lt#0"
-> Hash
-> Seq Scan on t2 @"lt#0"
(7 rows)
lightdb@lt_test=#
当与'swap_join_inputs'提示一起使用时，如果使用了'swap_join_inputs'提示，它将始终被使用。 它与Oracle不同，在Oracle中，如果指定的表在使用'swap_join_inputs'后成为内部表，则'pq_distribute'将不起作用。
lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+leading(t1 t2) hashjoin(t1 t2) pq_distribute(t1 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
QUERY PLAN
---------------------------------------------------
Gather
Workers Planned: 3
-> Parallel Hash Join
Hash Cond: (t1.id = t2.id)
-> Parallel Seq Scan on t1 @"lt#0"
-> Parallel Hash
-> Parallel Seq Scan on t2 @"lt#0"
(7 rows)
lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+leading(t1 t2) hashjoin(t1 t2) pq_distribute(t1 none broadcast) swap_join_inputs(t2)*/ * from t1 join t2 on t1.id=t2.id;
QUERY PLAN
---------------------------------------------
Gather
Workers Planned: 3
-> Hash Join
Hash Cond: (t2.id = t1.id)
-> Parallel Seq Scan on t2 @"lt#0"
-> Hash
-> Seq Scan on t1 @"lt#0"
(7 rows)
lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+leading(t1 t2) hashjoin(t1 t2) pq_distribute(t2 none broadcast) swap_join_inputs(t2)*/ * from t1 join t2 on t1.id=t2.id;
QUERY PLAN
---------------------------------------------
Gather
Workers Planned: 3
-> Hash Join
Hash Cond: (t2.id = t1.id)
-> Parallel Seq Scan on t2 @"lt#0"
-> Hash
-> Seq Scan on t1 @"lt#0"
(7 rows)
lightdb@lt_test=#
在没有指定连接顺序的情况下，'pq_distribute'提示可能会被使用，但不起作用， 因为它仅在指定的表是内部表且连接是并行哈希连接时才能工作。 如果具有指定表作为外部表的另一路径的成本更低，优化器将选择另一路径。
lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+hashjoin(t1 t2)pq_distribute(t2 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
QUERY PLAN
---------------------------------------------------
Gather
Workers Planned: 3
-> Parallel Hash Join
Hash Cond: (t2.id = t1.id)
-> Parallel Seq Scan on t2 @"lt#0"
-> Parallel Hash
-> Parallel Seq Scan on t1 @"lt#0"
(7 rows)
lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+hashjoin(t1 t2)pq_distribute(t1 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
QUERY PLAN
---------------------------------------------------
Gather
Workers Planned: 3
-> Parallel Hash Join
Hash Cond: (t1.id = t2.id)
-> Parallel Seq Scan on t1 @"lt#0"
-> Parallel Hash
-> Parallel Seq Scan on t2 @"lt#0"
(7 rows)
lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+hashjoin(t1 t2)pq_distribute(t1 none broadcast) pq_distribute(t2 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
QUERY PLAN
---------------------------------------------
Gather
Workers Planned: 3
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Parallel Seq Scan on t1 @"lt#0"
-> Hash
-> Seq Scan on t2 @"lt#0"
(7 rows)
lightdb@lt_test=#
在 LightDB 中，下推带子链接的过滤条件是一个 RBO 优化，过滤条件能下推就会下推。
"no_push_subq" 提示能够用来强制优化器不下推带子链接的过滤条件
"push_subq" 提示能够用来强制优化器下推带子链接的过滤条件。 但是因为 LightDB 默认就会尽可能下推带子链接的过滤条件，因此使用这个hint与不使用效果一致。 如果你使用这个hint，在这个hint可以被使用时，它会被标记为已使用。
下面的例子展示了 "no_push_subq" 的作用。 "push_subq" 的用法与"no_push_subq" 类似。
create table test_no_push_subq1 as select * from pg_class order by oid limit 100;
create table test_no_push_subq2 as select * from pg_class order by oid limit 100;
create table test_no_push_subq3 as select * from pg_class order by oid limit 100;
create table test_no_push_subq4 as select * from pg_class order by oid limit 100;
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select max(oid) from test_no_push_subq2);
QUERY PLAN
----------------------------------------------
Nested Loop
Join Filter: (a.reltype = b.reltype)
InitPlan 1 (returns $0)
-> Aggregate
-> Seq Scan on test_no_push_subq2
-> Seq Scan on test_no_push_subq1 a
Filter: (oid = $0)
-> Materialize
-> Seq Scan on test_no_push_subq3 b
Filter: (oid = $0)
(10 rows)
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select/*+no_push_subq*/ max(oid) from test_no_push_subq2);
QUERY PLAN
------------------------------------------------------------
Hash Join
Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
Join Filter: (a.oid = $0)
InitPlan 1 (returns $0)
-> Aggregate
-> Seq Scan on test_no_push_subq2 @"lt#0"
-> Seq Scan on test_no_push_subq1 a @"lt#1"
-> Hash
-> Seq Scan on test_no_push_subq3 b @"lt#1"
(9 rows)
lightdb@postgres=#
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select max(oid) from test_no_push_subq2 c where a.oid = c.oid);
QUERY PLAN
-----------------------------------------------------------------------------------
Nested Loop
Join Filter: ((a.reltype = b.reltype) AND (a.oid = b.oid))
-> Seq Scan on test_no_push_subq3 b
-> Materialize
-> Seq Scan on test_no_push_subq1 a
Filter: (oid = (SubPlan 2))
SubPlan 2
-> Result
InitPlan 1 (returns $1)
-> Limit
-> Seq Scan on test_no_push_subq2 c
Filter: ((oid IS NOT NULL) AND (a.oid = oid))
(12 rows)
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select/*+no_push_subq*/ max(oid) from test_no_push_subq2 c where a.oid = c.oid);
QUERY PLAN
-----------------------------------------------------------------------
Hash Join
Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
Join Filter: (a.oid = (SubPlan 2))
-> Seq Scan on test_no_push_subq1 a @"lt#1"
-> Hash
-> Seq Scan on test_no_push_subq3 b @"lt#1"
SubPlan 2
-> Result
InitPlan 1 (returns $1)
-> Limit
-> Seq Scan on test_no_push_subq2 c @"lt#0"
Filter: ((oid IS NOT NULL) AND (a.oid = oid))
(12 rows)
lightdb@postgres=#
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select * from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype
lightdb@postgres-# where (select oid from test_no_push_subq2 c where c.oid=a.oid) = (select oid from test_no_push_subq3 d where d.oid=a.oid);
QUERY PLAN
------------------------------------------------------
Nested Loop
Join Filter: (a.reltype = b.reltype)
-> Seq Scan on test_no_push_subq3 b
-> Materialize
-> Seq Scan on test_no_push_subq1 a
Filter: ((SubPlan 1) = (SubPlan 2))
SubPlan 1
-> Seq Scan on test_no_push_subq2 c
Filter: (oid = a.oid)
SubPlan 2
-> Seq Scan on test_no_push_subq3 d
Filter: (oid = a.oid)
(12 rows)
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select * from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype
lightdb@postgres-# where (select/*+no_push_subq*/ oid from test_no_push_subq2 c where c.oid=a.oid) = (select oid from test_no_push_subq3 d where d.oid=a.oid);
QUERY PLAN
------------------------------------------------------
Hash Join
Hash Cond: (a.reltype = b.reltype)
Join Filter: ((SubPlan 1) = (SubPlan 2))
-> Seq Scan on test_no_push_subq1 a @"lt#2"
-> Hash
-> Seq Scan on test_no_push_subq3 b @"lt#2"
SubPlan 1
-> Seq Scan on test_no_push_subq2 c @"lt#0"
Filter: (oid = a.oid)
SubPlan 2
-> Seq Scan on test_no_push_subq3 d @"lt#1"
Filter: (oid = a.oid)
(12 rows)
lightdb@postgres=#
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid > all (select oid from test_no_push_subq2 c where c.oid =a.oid);
QUERY PLAN
------------------------------------------------------------
Hash Join
Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
-> Seq Scan on test_no_push_subq1 a
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on test_no_push_subq2 c
Filter: (oid = a.oid)
-> Hash
-> Seq Scan on test_no_push_subq3 b
(9 rows)
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid > all (select/*+ no_push_subq*/ oid from test_no_push_subq2 c where c.oid =a.oid);
QUERY PLAN
------------------------------------------------------------
Hash Join
Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
Join Filter: (SubPlan 1)
-> Seq Scan on test_no_push_subq1 a @"lt#1"
-> Hash
-> Seq Scan on test_no_push_subq3 b @"lt#1"
SubPlan 1
-> Seq Scan on test_no_push_subq2 c @"lt#0"
Filter: (oid = a.oid)
(9 rows)
lightdb@postgres=#
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where exists(select/*+no_unnest*/ * from test_no_push_subq2 c where a.oid = c.oid and c.oid=2691);
QUERY PLAN
---------------------------------------------------------------
Hash Join
Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
-> Seq Scan on test_no_push_subq1 a @"lt#1"
Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
SubPlan 1
-> Result
One-Time Filter: (a.oid = '2691'::oid)
-> Seq Scan on test_no_push_subq2 c @"lt#0"
Filter: (oid = '2691'::oid)
SubPlan 2
-> Seq Scan on test_no_push_subq2 c_1 @"lt#0"
Filter: (oid = '2691'::oid)
-> Hash
-> Seq Scan on test_no_push_subq3 b @"lt#1"
(14 rows)
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where exists(select/*+no_unnest no_push_subq*/ * from test_no_push_subq2 c where a.oid = c.oid and c.oid=2691);
QUERY PLAN
--------------------------------------------------------------
Hash Join
Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
Join Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
-> Seq Scan on test_no_push_subq1 a @"lt#1"
-> Hash
-> Seq Scan on test_no_push_subq3 b @"lt#1"
SubPlan 1
-> Result
One-Time Filter: (a.oid = '2691'::oid)
-> Seq Scan on test_no_push_subq2 c @"lt#0"
Filter: (oid = '2691'::oid)
SubPlan 2
-> Seq Scan on test_no_push_subq2 c_1 @"lt#0"
Filter: (oid = '2691'::oid)
(14 rows)
lightdb@postgres=#
需要注意的是，在 "push_subq" 与 "no_push_subq" 不作用于同一个子查询，但作用于同一个过滤条件时 "push_subq" 起效，"no_push_subq" 不被使用。与oracle一致， 例子如下(SubPlan2 中的"no_push_subq" 不被使用);
EXPLAIN (COSTS false)
select * from test_push_subq1 a join test_push_subq3 b on a.reltype =b.reltype
where ((select/*+no_push_subq*/ relname from test_push_subq2 c where c.oid=a.oid), (select/*+push_subq*/ reltype from test_push_subq2 c where c.oid=a.oid)) = (select/*+no_push_subq*/ relname, reltype from test_push_subq2);
LOG: lt_hint_plan:
used hint:
no_push_subq
push_subq
not used hint:
no_push_subq
duplication hint:
error hint:
QUERY PLAN
-------------------------------------------------------
Hash Join
Hash Cond: (a.reltype = b.reltype)
Join Filter: ((SubPlan 1) = $2)
InitPlan 3 (returns $2,$3)
-> Seq Scan on test_push_subq2 @"lt#0"
-> Seq Scan on test_push_subq1 a @"lt#3"
Filter: ((SubPlan 2) = $3)
SubPlan 2
-> Seq Scan on test_push_subq2 c_1 @"lt#2"
Filter: (oid = a.oid)
-> Hash
-> Seq Scan on test_push_subq3 b @"lt#3"
SubPlan 1
-> Seq Scan on test_push_subq2 c @"lt#1"
Filter: (oid = a.oid)
(15 rows)
“ignore_row_on_dupkey_index” 提示可用于忽略特定列集或指定索引的唯一键违规。 目前使用这个提示等同于使用` on conflict do nothing `子句。 如果使用了这个hint，且在插入数据时启用DML错误日志记录，则不会记录违反唯一键的日志，也不会导致语句终止。
如果违反了特定的规则，这个提示的语义效果将导致语句报错:
您必须指定一个正确的索引。如果没有指定索引，那么该语句将导致LT008错误(oracle是ORA-38912)。 如果指定了多个索引，那么该语句会导致LT010错误(oracle是ORA-38915)。
如果指定了index，那么索引必须存在且唯一。否则，该语句导致LT009错误(oracle是ORA-38913)。
IGNORE_ROW_ON_DUPKEY_INDEX提示仅适用于单表插入操作。 它不支持更新、删除、合并(合并还不支持hint，将忽略)或多表插入操作，并将导致LT011错误(oracle是ORA-38917)。
如果指定的列对应有多个索引，那么这条语句会导致LT010错误(oracle没有这个特性，因为同一个字段不能有多个索引)。
不能与on conflict子句一起使用，一起用会报LT011错误(oracle没有on conflict子句)。
下面的例子展示了“ignore_row_on_dupkey_index”提示的效果:
create table test_ignore_dupkey(key1 int, key2 int, key3 int, key4 int); create unique index test_ignore_dupkey_ui1 on test_ignore_dupkey(key1, key2); create unique index test_ignore_dupkey_ui2 on test_ignore_dupkey(key3, key4); create index test_ignore_dupkey_i on test_ignore_dupkey(key3); lightdb@postgres=# insert into test_ignore_dupkey values(1, 1, 1, 1); INSERT 0 1 lightdb@postgres=# insert into test_ignore_dupkey values(1, 1, 1, 1); ERROR: duplicate key value violates unique constraint "test_ignore_dupkey_ui1" DETAIL: Key (key1, key2)=(1, 1) already exists. lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey test_ignore_dupkey_ui1) */ into test_ignore_dupkey values(1, 1, 1, 2); INSERT 0 0 lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey(key1, key2)) */ into test_ignore_dupkey values(1, 1, 1, 2); INSERT 0 0 lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey) */ into test_ignore_dupkey values(1, 1, 1, 2); ERROR: An index must be specified in the index hint lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey test_ignore_dupkey_ui3) */ into test_ignore_dupkey values(1, 1, 1, 2); ERROR: An index must be specified in the index hint lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey test_ignore_dupkey_i) */ into test_ignore_dupkey values(1, 1, 1, 2); ERROR: Index specified in the index hint is invalid lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey test_ignore_dupkey_ui1 test_ignore_dupkey_ui2) */ into test_ignore_dupkey values(1, 1, 1, 2); ERROR: Multiple indexes in ignore duplicate key hint lightdb@postgres=# update/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey test_ignore_dupkey_ui1) */ test_ignore_dupkey set key2 = 2 where key1 = 1; ERROR: IGNORE_ROW_ON_DUPKEY_INDEX hint disallowed for this operation lightdb@postgres=# create unique index test_ignore_dupkey_ui3 on test_ignore_dupkey(key3, key4); CREATE INDEX lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey(key3, key4)) */ into test_ignore_dupkey values(1, 2, 1, 1); ERROR: Multiple indexes in ignore duplicate key hint DETAIL: find multiple indexes by fields lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey test_ignore_dupkey_ui1) */ into test_ignore_dupkey values(1, 1, 1, 2) lightdb@postgres-# on conflict (key1, key2) do nothing; ERROR: IGNORE_ROW_ON_DUPKEY_INDEX hint disallowed for this operation DETAIL: insert operation with on confict clause is not supported lightdb@postgres=#
ignore_row_on_dupkey_index”提示可以在canopy中工作，但只适用于insert select语句，不支持insert values语句。
create table test_ignore_dupkey(key1 int, key2 int, key3 int, key4 int);
SELECT create_distributed_table('test_ignore_dupkey', 'key1');
create unique index test_ignore_dupkey_ui1 on test_ignore_dupkey(key1, key2);
create unique index test_ignore_dupkey_ui2 on test_ignore_dupkey(key1, key3);
create index test_ignore_dupkey_i on test_ignore_dupkey(key3);
lightdb@postgres=# insert into test_ignore_dupkey values(1, 1, 1, 1);
INSERT 0 1
lightdb@postgres=# insert into test_ignore_dupkey values(1, 1, 1, 1);
ERROR: duplicate key value violates unique constraint "test_ignore_dupkey_ui1_102045"
DETAIL: Key (key1, key2)=(1, 1) already exists.
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey test_ignore_dupkey_ui1) */ into test_ignore_dupkey values(1, 1, 2, 1);
ERROR: duplicate key value violates unique constraint "test_ignore_dupkey_ui1_102045"
DETAIL: Key (key1, key2)=(1, 1) already exists.
create table test_ignore_dupkey1(key1 int, key2 int, key3 int, key4 int);
insert into test_ignore_dupkey1 values(1, 1, 1, 1);
insert into test_ignore_dupkey1 values(1, 1, 1, 1);
insert into test_ignore_dupkey1 values(1, 2, 1, 1);
insert into test_ignore_dupkey1 values(1, 2, 1, 1);
insert into test_ignore_dupkey1 values(1, 1, 1, 2);
insert into test_ignore_dupkey1 values(1, 1, 1, 2);
insert into test_ignore_dupkey1 values(1, 3, 1, 3);
lightdb@postgres=# insert into test_ignore_dupkey select * from test_ignore_dupkey1;
ERROR: duplicate key value violates unique constraint "test_ignore_dupkey_ui1_102045"
DETAIL: Key (key1, key2)=(1, 1) already exists.
CONTEXT: COPY test_ignore_dupkey_102045, line 2
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey(key1, key3)) */ into test_ignore_dupkey select * from test_ignore_dupkey1;
ERROR: duplicate key value violates unique constraint "test_ignore_dupkey_ui1_102045"
DETAIL: Key (key1, key2)=(1, 1) already exists.
CONTEXT: COPY test_ignore_dupkey_102045, line 2
lightdb@postgres=# SELECT create_distributed_table('test_ignore_dupkey1', 'key1');
NOTICE: Copying data from local table...
NOTICE: copying the data has completed
DETAIL: The local data in the table is no longer visible, but is still on disk.
HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$public.test_ignore_dupkey1$$)
create_distributed_table
--------------------------
(1 row)
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey(key1, key3)) */ into test_ignore_dupkey select * from test_ignore_dupkey1;
INSERT 0 1
lightdb@postgres=# select * from test_ignore_dupkey;
key1 | key2 | key3 | key4
------+------+------+------
1 | 1 | 1 | 1
(1 row)
如果你的查询中有一个不可合并的视图或子查询(可能因为no_merge提示)，该如何操作其他表的联结呢? 是应该创建一个大的视图结果并连接一次(no_push_pred)； 还是将连接谓词推到视图中，然后从另一个表中为每个驱动行重新创建视图结果集(push_pred 并且必须是nestloop)。
“no_push_pred” 提示指示优化器不要将连接谓词推送到子查询中。
LightDB 目前还不支持将连接谓词推入视图。目前只是为了兼容oracle。 因此，如果你使用“no_push_pred”提示，它将始终有效。
下面的例子展示了 “no_push_pred” 提示的用法:
create table test_no_push_pred1 (key1 int primary key, key2 int);
create table test_no_push_pred2 (key1 int primary key, key2 int);
create table test_no_push_pred3 (key1 int primary key, key2 int);
set enable_hashjoin to off;
set enable_mergejoin to off;
lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a)*/* from test_no_push_pred1 a join (select * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
QUERY PLAN
----------------------------------------------------------------------------
------
Nested Loop
Join Filter: (a.key1 = test_no_push_pred2.key2)
-> Seq Scan on test_no_push_pred1 a @"lt#1"
-> Materialize
-> Limit
-> Index Scan using test_no_push_pred2_pkey on test_no_push_pred2
(6 rows)
lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a) no_push_pred(b)*/* from test_no_push_pred1 a join (select * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
QUERY PLAN
----------------------------------------------------------------------------
------
Nested Loop
Join Filter: (a.key1 = test_no_push_pred2.key2)
-> Seq Scan on test_no_push_pred1 a @"lt#1"
-> Materialize
-> Limit
-> Index Scan using test_no_push_pred2_pkey on test_no_push_pred2
(6 rows)
lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a) no_push_pred(@qb)*/* from test_no_push_pred1 a join (select/*+ qb_name(qb)*/ * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
QUERY PLAN
----------------------------------------------------------------------------
----------
Nested Loop
Join Filter: (a.key1 = test_no_push_pred2.key2)
-> Seq Scan on test_no_push_pred1 a @"lt#0"
-> Materialize
-> Limit
-> Index Scan using test_no_push_pred2_pkey on test_no_push_pred2 @qb
(6 rows)
lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a) */* from test_no_push_pred1 a join (select/*+ no_push_pred*/ * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
QUERY PLAN
----------------------------------------------------------------------------
--------------
Nested Loop
Join Filter: (a.key1 = test_no_push_pred2.key2)
-> Seq Scan on test_no_push_pred1 a @"lt#1"
-> Materialize
-> Limit
-> Index Scan using test_no_push_pred2_pkey on test_no_push_pred2 @"lt#0"
(6 rows)
星型转换是一种优化器转换，它避免了对星型模式中事实表的全表扫描(星型模式将数据分为事实表和维度表)。 在事实表和维度表的连接中，星型转换可以避免对事实表的全表扫描。星型转换通过位图索引来获取事实表上需要的行（由维度表过滤），从而提高性能。 在某些情况下，查询在维度表的非关联列上有过滤条件。通过组合这些过滤条件可以显著减少数据库需要处理的来自事实表的数据集。
“no_star_transformation” 提示指示优化器不执行星型查询转换。
LightDB 目前还不支持星形变换。目前只是为了兼容oracle。 因此如果你正确使用了 “no_star_transformation” 提示，它总是会起效。
下面的例子展示了 “no_star_transformation” 提示的用法:
CREATE TABLE times (
time_id NUMBER PRIMARY KEY,
calendar_quarter_desc VARCHAR2(20)
);
CREATE TABLE customers (
cust_id NUMBER PRIMARY KEY,
cust_city VARCHAR2(20),
cust_state_province VARCHAR2(20)
);
CREATE TABLE channels (
channel_id NUMBER PRIMARY KEY,
channel_class VARCHAR2(20),
channel_desc VARCHAR2(20)
);
CREATE TABLE sales (
time_id NUMBER REFERENCES times(time_id),
cust_id NUMBER REFERENCES customers(cust_id),
channel_id NUMBER REFERENCES channels(channel_id),
amount_sold NUMBER
);
lightdb@postgres=# EXPLAIN (COSTS false) SELECT/*+no_star_transformation*/ ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc in ('Internet','Catalog')
AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
LOG: lt_hint_plan:
used hint:
no_star_transformation
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------------------------------
---------------------------
GroupAggregate
Group Key: ch.channel_class, c.cust_city, t.calendar_quarter_desc
-> Sort
Sort Key: ch.channel_class, c.cust_city, t.calendar_quarter_desc
-> Nested Loop
-> Nested Loop
-> Hash Join
Hash Cond: (s.cust_id = c.cust_id)
-> Seq Scan on sales s @"lt#0"
-> Hash
-> Seq Scan on customers c @"lt#0"
Filter: ((cust_state_province)::text
= 'CA'::text)
-> Index Scan using times_pkey on times t @"lt#0"
Index Cond: (time_id = s.time_id)
Filter: ((calendar_quarter_desc)::text = ANY ('{1
999-Q1,1999-Q2}'::text[]))
-> Index Scan using channels_pkey on channels ch @"lt#0"
Index Cond: (channel_id = s.channel_id)
Filter: ((channel_desc)::text = ANY ('{Internet,Catalog
}'::text[]))
(18 rows)
lightdb@postgres=# EXPLAIN (COSTS false)
select/*+no_star_transformation(@qb)*/ * from (SELECT/*+qb_name(qb)*/ ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc in ('Internet','Catalog')
AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc);
LOG: lt_hint_plan:
used hint:
no_star_transformation(@qb)
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------------------------------
---------------------------
GroupAggregate
Group Key: ch.channel_class, c.cust_city, t.calendar_quarter_desc
-> Sort
Sort Key: ch.channel_class, c.cust_city, t.calendar_quarter_desc
-> Nested Loop
-> Nested Loop
-> Hash Join
Hash Cond: (s.cust_id = c.cust_id)
-> Seq Scan on sales s @qb
-> Hash
-> Seq Scan on customers c @qb
Filter: ((cust_state_province)::text
= 'CA'::text)
-> Index Scan using times_pkey on times t @qb
Index Cond: (time_id = s.time_id)
Filter: ((calendar_quarter_desc)::text = ANY ('{1
999-Q1,1999-Q2}'::text[]))
-> Index Scan using channels_pkey on channels ch @qb
Index Cond: (channel_id = s.channel_id)
Filter: ((channel_desc)::text = ANY ('{Internet,Catalog
}'::text[]))
(18 rows)
在使用 direct-path 插入时, 表中的空闲空间不会被重用，数据库将插入的数据添加到表中已有的数据之后, 数据被直接写入数据文件，不经过缓冲区缓存，同时完整性约束也会被忽略。 直接路径插入的性能明显优于传统插入。
"append" 优化器提示指示优化器对insert select模式的语句使用 direct-path 插入。
LightDB 目前还不支持 direct-path 插入。支持这个hint目前只是为了兼容oracle。 所以如果你使用"append"提示，它将不会生效。
下面的例子展示了"append"提示的用法:
create table test_append(key1 int, key2 int, key3 int, key4 int);
create table test_append1(key1 int, key2 int, key3 int, key4 int);
insert into test_append1 values(1,2,3,4);
insert into test_append1 values(11,21,31,41);
insert into test_append1 values(111,211,311,411);
lightdb@test_oracle_lt_hint_plan=# EXPLAIN (COSTS FALSE) insert/*+append */ into test_append select 1, 1, 1, 2 from dual;
LOG: lt_hint_plan:
used hint:
not used hint:
append
duplication hint:
error hint:
QUERY PLAN
-------------------------------
Insert on test_append @"lt#1"
-> Result
(2 rows)
lightdb@test_oracle_lt_hint_plan=# EXPLAIN (COSTS FALSE) insert/*+append */ into test_append select * from test_append1;
LOG: lt_hint_plan:
used hint:
not used hint:
append
duplication hint:
error hint:
QUERY PLAN
--------------------------------
Insert on test_append @"lt#1"
-> Seq Scan on test_append1
(2 rows)
在 Oracle 中， OR-expansion 的关键特性是优化器能够把 WHERE 子句中带有 OR 条件或 IN-LIST 的查询块转换成多个查询块的 UNION ALL 组合， 在转换成多个查询块后，每个查询块可以被单独优化和执行。
"no_expand" 优化器提示指示优化器不要考虑使用 OR-expansion 优化 WHERE 子句中带有OR条件或IN-LIST 的查询。
在 LightDB 中，暂不支持 OR-expansion。因此 "no_expand" 优化器提示的效果就是默认行为。 如果使用 "no_expand" 优化器提示，它总是会起效。
下面的案例展示了"no_expand" 优化器提示的用法：
create table t_no_expand1(key1 int, key2 int);
create table t_no_expand2(key1 int, key2 int);
lightdb@postgres=# EXPLAIN (COSTS false) select * from t_no_expand1 where exists (select * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
QUERY PLAN
----------------------------------------------
Seq Scan on t_no_expand1
Filter: ($0 OR (key1 = 10))
InitPlan 1 (returns $0)
-> Seq Scan on t_no_expand2
Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select /*+no_expand*/ * from t_no_expand1 where exists (select * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG: lt_hint_plan:
used hint:
no_expand
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------
Seq Scan on t_no_expand1 @"lt#0"
Filter: ($0 OR (key1 = 10))
InitPlan 1 (returns $0)
-> Seq Scan on t_no_expand2
Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from t_no_expand1 where exists (select /*+no_expand*/ * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG: lt_hint_plan:
used hint:
no_expand
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------
Seq Scan on t_no_expand1 @"lt#1"
Filter: ($0 OR (key1 = 10))
InitPlan 1 (returns $0)
-> Seq Scan on t_no_expand2 @"lt#0"
Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select /*+no_expand(@qb)*/ * from t_no_expand1 where exists (select/*+qb_name(qb)*/ * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG: lt_hint_plan:
used hint:
no_expand(@qb)
not used hint:
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------
Seq Scan on t_no_expand1 @"lt#0"
Filter: ($0 OR (key1 = 10))
InitPlan 1 (returns $0)
-> Seq Scan on t_no_expand2 @qb
Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)
下面的GUC参数会影响lt_hint_plan的行为。
Table 15.2. lt_hint_plan的GUC参数
| 参数名称 | 描述 | 默认值 |
|---|---|---|
| lt_hint_plan.enable_hint | 启用lt_hint_plan。 | on |
| lt_hint_plan.enable_hint_table | 启用按表提示。true或false。 | off |
| lt_hint_plan.parse_messages | 指定提示解析错误的日志级别。有效值为error、warning、notice、info、log、debug。 | INFO |
| lt_hint_plan.debug_print | 控制调试打印和详细程度。有效值为off、on、detailed和verbose。 | off |
| lt_hint_plan.message_level | 指定调试打印的消息级别。有效值为error、warning、notice、info、log、debug。 | INFO |
| lt_hint_plan.show_qb_name_info | 在调试打印中显示qb名称信息。 | on |
对于全局级别的提示，lt_hint_plan仅读取sql头部的第一个块注释（即第一个字符之前）。
lightdb@postgres=# /*+ indexscan(b)*/ explain select * from ltbench_accounts b where b.aid>10;
QUERY PLAN
-----------------------------------------------------------------------------------
---------------------
Index Scan using ltbench_accounts_pkey on ltbench_accounts b (cost=0.29..3666.12
rows=99990 width=97)
Index Cond: (aid > 10)
(2 rows)
lightdb@postgres=#
对于语句级别的提示，lt_hint_plan仅从select、update、delete、insert关键字后的第一个块注释中读取提示(如果关键字后跟着多个注释，hint需要是第一个)。 提示仅在当前查询块中起作用，而不使用qb_name。 在以下示例中，HashJoin(a b)和SeqScan(a)被解析为提示，但IndexScan(a)和IndexScan(b)不被解析为提示。
lightdb@postgres=# /*+
lightdb@postgres*# HashJoin(a b)
lightdb@postgres*# */
lightdb@postgres-# /*+ IndexScan(a) */
lightdb@postgres-# EXPLAIN SELECT /*+ SeqScan(a) */ *
lightdb@postgres-# FROM /*+ IndexScan(b) */ltbench_branches b
lightdb@postgres-# JOIN ltbench_accounts a ON b.bid = a.bid
lightdb@postgres-# ORDER BY a.aid;
QUERY PLAN
-----------------------------------------------------------------------------------
----
Sort (cost=24236.84..24486.84 rows=100000 width=461)
Sort Key: a.aid
-> Hash Join (cost=1.02..4016.02 rows=100000 width=461)
Hash Cond: (a.bid = b.bid)
-> Seq Scan on ltbench_accounts a (cost=0.00..2640.00 rows=100000 width=
97)
-> Hash (cost=1.01..1.01 rows=1 width=364)
-> Seq Scan on ltbench_branches b (cost=0.00..1.01 rows=1 width=36
4)
(7 rows)
lightdb@postgres=#
lt_hint_plan在PL/pgSQL脚本中对查询执行有一些限制。
提示仅对以下类型的查询起作用。
返回一行结果的查询。（SELECT、INSERT、UPDATE和DELETE）
返回多行结果的查询。 (RETURN QUERY)
动态 SQL 语句。 (EXECUTE)
打开光标。 (OPEN)
遍历查询结果 (FOR)
提示注释必须放在查询的第一个单词之后，如下所示，因为前面的注释不会作为查询的一部分发送。
lightdb@postgres=# CREATE FUNCTION hints_func(integer) RETURNS integer AS $$ lightdb@postgres$# DECLARE lightdb@postgres$# id integer; lightdb@postgres$# cnt integer; lightdb@postgres$# BEGIN lightdb@postgres$# SELECT /*+ NoIndexScan(a) */ aid lightdb@postgres$# INTO id FROM ltbench_accounts a WHERE aid = $1; lightdb@postgres$# SELECT /*+ SeqScan(a) */ count(*) lightdb@postgres$# INTO cnt FROM ltbench_accounts a; lightdb@postgres$# RETURN id + cnt; lightdb@postgres$# END; lightdb@postgres$# $$ LANGUAGE plpgsql;
与 LightDB 处理对象名称的方式相似，lt_hint_plan 将把对象名称转换为小写。因此，在提示中，对象名称 TBL 对应的表可以是数据库中的 TBL、tbl 或 Tbl。 使用引号时，lt_hint_plan 将不会将对象名称转换为小写。因此，在提示中，使用对象名称 "TBL" 只能匹配数据库中的 "TBL"，而不能匹配任何未加引号的表名，如 TBL、tbl 或 Tbl。
如果对象名称包含括号、双引号和空格，则应该用双引号括起来作为提示参数。转义规则与 LightDB 相同。
如果存在别名，lt_hint_plan 将通过别名识别目标对象。这个行为可以用来指定在同一个表的多个实例中指向一个特定实例。
lightdb@postgres=# /*+ HashJoin(t1 t1) */
lightdb@postgres-# EXPLAIN SELECT * FROM s1.t1
lightdb@postgres-# JOIN public.t1 ON (s1.t1.id=public.t1.id);
INFO: hint syntax error at or near "HashJoin(t1 t1)"
DETAIL: Relation name "t1" is ambiguous.
...
lightdb@postgres=# /*+ HashJoin(pt st) */
lightdb@postgres-# EXPLAIN SELECT * FROM s1.t1 st
lightdb@postgres-# JOIN public.t1 pt ON (st.id=pt.id);
QUERY PLAN
---------------------------------------------------------------------
Hash Join (cost=64.00..1112.00 rows=28800 width=8)
Hash Cond: (st.id = pt.id)
-> Seq Scan on t1 st (cost=0.00..34.00 rows=2400 width=4)
-> Hash (cost=34.00..34.00 rows=2400 width=4)
-> Seq Scan on t1 pt (cost=0.00..34.00 rows=2400 width=4)
提示适用于视图本身，并且如果对象名称与在视图上扩展查询中的对象名称匹配，则可以影响其中的查询，当作为全局级别提示时。 但是对于 stmt 级别的提示，需要使用 qb_name 提示来操作视图中的表。
create view test_v as select/*+qb_name(qb)*/ * from t1 where id >10;
EXPLAIN (COSTS false) select/*+indexscan(t1)*/ * from test_v;
LOG: lt_hint_plan:
used hint:
not used hint:
IndexScan(t1@lt#0)
duplication hint:
error hint:
QUERY PLAN
---------------------
Seq Scan on t1 @qb
Filter: (id > 10)
(2 rows)
EXPLAIN (COSTS false) select/*+indexscan(t1@qb)*/ * from test_v;
LOG: lt_hint_plan:
used hint:
IndexScan(t1@qb)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------
Index Scan using t1_pkey on t1 @qb
Index Cond: (id > 10)
(2 rows)
/*+indexscan(t1)*/
EXPLAIN (COSTS false) select * from test_v;
LOG: lt_hint_plan:
used hint:
IndexScan(t1@lt#-1)
not used hint:
duplication hint:
error hint:
QUERY PLAN
------------------------------------
Index Scan using t1_pkey on t1 @qb
Index Cond: (id > 10)
(2 rows)
提示只能指向继承表的父表，提示会影响整个继承关系。同时指向子表的提示并没有起作用。
对于全局级别的提示，一个多语句只能有一个提示注释，提示会影响多语句中的所有单个语句。请注意，psql 交互界面上看似的多语句实际上是一系列单个语句，因此提示仅影响紧随其后的语句。 对于 stmt 级别的提示，与单个语句相同。
FROM 子句中的 VALUES 表达式在内部命名为 *VALUES*，因此如果它是查询中唯一的 VALUES，则可以进行提示。在查询中有两个或更多 VALUES 表达式似乎可以通过其 explain 结果进行区分。但实际上这只是一种表面上的区别，它们并不能区分。
lightdb@postgres=# /*+ MergeJoin(*VALUES*_1 *VALUES*) */
EXPLAIN SELECT * FROM (VALUES (1, 1), (2, 2)) v (a, b)
JOIN (VALUES (1, 5), (2, 8), (3, 4)) w (a, c) ON v.a = w.a;
INFO: lt_hint_plan: hint syntax error at or near "MergeJoin(*VALUES*_1 *VALUES*) "
DETAIL: Relation name "*VALUES*" is ambiguous.
QUERY PLAN
-------------------------------------------------------------------------
Hash Join (cost=0.05..0.12 rows=2 width=16)
Hash Cond: ("*VALUES*_1".column1 = "*VALUES*".column1)
-> Values Scan on "*VALUES*_1" (cost=0.00..0.04 rows=3 width=8)
-> Hash (cost=0.03..0.03 rows=2 width=8)
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=8)
lt_hint_plan 通过增加不需要的路径的成本 (cost += disable_cost, disable_cost=1.0e10) 来使执行计划选择所需的路径。
有时即使使用了提示，SQL 也不会按照提示执行，因为所需的路径不可用，比如在没有索引的表上使用 indexscan 提示。在这种情况下，explain 中的成本值不准确。
实际成本可以通过减去 disable_cost 获得。
lightdb@postgres=# create table test(id int);
CREATE TABLE
lightdb@postgres=# explain select * from test where id=1;
QUERY PLAN
------------------------------------------------------
Seq Scan on test (cost=0.00..41.88 rows=13 width=4)
Filter: (id = 1)
(2 rows)
lightdb@postgres=# explain select /*+indexscan(test)*/* from test where id=1;
QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on test @"lt#0" (cost=10000000000.00..10000000041.88 rows=13 width=4)
Filter: (id = 1)
(2 rows)
lightdb@postgres=#
在以下情况下，子查询有时可以使用名称 "ANY_subquery" 进行提示。
IN (SELECT ... {LIMIT | OFFSET ...} ...)
= ANY (SELECT ... {LIMIT | OFFSET ...} ...)
= SOME (SELECT ... {LIMIT | OFFSET ...} ...)
对于这些语法，当优化器在计划包含子查询的表的联接时，会为子查询分配名称，因此可以使用隐式名称在此类联接上应用联接提示，如下所示。
lightdb@postgres=# /*+HashJoin(a1 ANY_subquery)*/
lightdb@postgres=# EXPLAIN SELECT *
lightdb@postgres=# FROM ltbench_accounts a1
lightdb@postgres=# WHERE aid IN (SELECT bid FROM ltbench_accounts a2 LIMIT 10);
QUERY PLAN
---------------------------------------------------------------------------------------------
Hash Semi Join (cost=0.49..2903.00 rows=1 width=97)
Hash Cond: (a1.aid = a2.bid)
-> Seq Scan on ltbench_accounts a1 (cost=0.00..2640.00 rows=100000 width=97)
-> Hash (cost=0.36..0.36 rows=10 width=4)
-> Limit (cost=0.00..0.26 rows=10 width=4)
-> Seq Scan on ltbench_accounts a2 (cost=0.00..2640.00 rows=100000 width=4)
当 IndexOnlyScan 提示指定的索引无法执行索引唯一扫描时，可能会在另一个索引上意外执行索引扫描。
NoIndexScan 提示涉及 NoIndexOnlyScan。
仅当所有基础子查询都是并行安全的时，UNION 才能并行运行。相反，对任何一个子查询强制并行执行，都可以使可并行执行的 UNION 并行运行。同时，使用零个工作进程的并行提示会阻止扫描并行执行。
lt_hint_plan 参数会改变其自身的行为，因此某些参数可能不会按预期工作。
即使在调试日志中报告为“使用的提示”，也会忽略更改 enable_hint、enable_hint_tables 的提示。
设置 debug_print 和 message_level 可以从目标查询处理的中间开始工作。
除非子查询只包含一个表，否则对引用的子查询（可合并）使用的扫描提示、联接提示（包括联接方法和联接顺序提示）和行提示将被忽略。 如果子查询只包含一个表，则对其应用的扫描路径提示或联接提示适用于子查询中的表。
lightdb@postgres=# create table test1(id int primary key, value int);
CREATE TABLE
lightdb@postgres=# create table test2(id int primary key, value int);
CREATE TABLE
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1,(select * from (select * from test2) x) y where test1.id=y.id;
QUERY PLAN
------------------------------------
Hash Join
Hash Cond: (test1.id = test2.id)
-> Seq Scan on test1
-> Hash
-> Seq Scan on test2
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select /*+indexscan(y)*/* from test1,(select * from (select * from test2) x) y where test1.id=y.id;
QUERY PLAN
----------------------------------------------------
Hash Join
Hash Cond: (test2.id = test1.id)
-> Index Scan using test2_pkey on test2 @"lt#2"
-> Hash
-> Seq Scan on test1 @"lt#0"
(5 rows)
lightdb@postgres=#
现在 lt_hint_plan 与 distributed 兼容，可以用于控制涉及分布式表和引用表的执行计划。
要查看数据节点上提示的使用状态，首先需要在数据节点上打开 debug_print，然后设置 canopy.log_remote_commands 为 on， 最后将 canopy.worker_min_messages 设置为 'log'，client_min_messages 设置为 'log' 或将 lt_hint_plan.message_level 设置为 'notice'。以下是示例：
lightdb@postgres=# CREATE TABLE t1 (id int PRIMARY KEY, val int);
CREATE TABLE
lightdb@postgres=# SELECT create_distributed_table('t1', 'id');
create_distributed_table
--------------------------
(1 row)
lightdb@postgres=# set lt_hint_plan.message_level = 'notice';
SET
lightdb@postgres=# set canopy.log_remote_commands=on;
SET
lightdb@postgres=# explain select /*+indexscan(t1)*/* from t1 where id=10;
NOTICE: lt_hint_plan:
used hint:
not used hint:
IndexScan(t1@lt#0)
duplication hint:
error hint:
NOTICE: issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 24, '2022-03-14 15:49:45.634614+08');
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 2
NOTICE: issuing SAVEPOINT canopy_explain_savepoint
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 2
NOTICE: issuing /*+ IndexScan(t1) */EXPLAIN (ANALYZE FALSE, VERBOSE FALSE, COSTS TRUE, BUFFERS FALSE, WAL FALSE, TIMING FALSE, SUMMARY FALSE, FORMAT TEXT) SELECT id, val FROM public.t1_102044 t1 WHERE (id OPERATOR(pg_catalog.=) 10)
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 2
NOTICE: lt_hint_plan:
used hint:
IndexScan(t1@lt#-1)
not used hint:
duplication hint:
error hint:
DETAIL: from 192.168.247.128:6432
NOTICE: issuing ROLLBACK TO SAVEPOINT canopy_explain_savepoint
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 2
NOTICE: issuing COMMIT
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 2
QUERY PLAN
-----------------------------------------------------------------------------------
------------
Custom Scan (Canopy Adaptive) (cost=0.00..0.00 rows=0 width=0)
Task Count: 1
Tasks Shown: All
-> Task
Node: host=192.168.247.128 port=6432 dbname=postgres
-> Index Scan using t1_pkey_102044 on t1_102044 t1 (cost=0.15..2.17 rows
=1 width=8)
Index Cond: (id = 10)
(7 rows)
lightdb@postgres=#
不能下推且不包含对外部查询的引用的公共表达式和子查询是通过将子查询作为解析树递归调用优化器函数来进行规划的。 给出的计划是独立执行的，SQL 语句已经被拆分。因此，在外部查询和子查询上同时起作用的提示，以及作用于子查询的 no_merge、semijoin/antijoin 提示将没有效果。以下是示例：
lightdb@postgres=# CREATE TABLE t1 (id int PRIMARY KEY, val int);
CREATE TABLE
lightdb@postgres=# CREATE TABLE t2 (id int PRIMARY KEY, val int);
CREATE TABLE
lightdb@postgres=# SELECT create_distributed_table('t1', 'id');
create_distributed_table
--------------------------
(1 row)
lightdb@postgres=# SELECT create_distributed_table('t2', 'id');
create_distributed_table
--------------------------
(1 row)
lightdb@postgres=#
lightdb@postgres=# explain select /*+indexscan(t2@qb) hashjoin(t1 t2@qb)*/* from t1, (select/*+qb_name(qb)*/ * from t2 where t2.id >11) x where t1.id=10;
INFO: lt_hint_plan: disable hint after spliting sql , hint: "HashJoin"
DETAIL: it is invalid because of split SQL, defined at "hashjoin(t1 t2@qb)"
NOTICE: lt_hint_plan:
used hint:
IndexScan(t2@qb)
not used hint:
duplication hint:
error hint:
NOTICE: lt_hint_plan:
used hint:
IndexScan(t2@qb)
HashJoin(t1@lt#0 t2@qb)
not used hint:
duplication hint:
error hint:
NOTICE: issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 36, '2022-03-14 16:06:20.313852+08');
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE: issuing SAVEPOINT canopy_explain_savepoint
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE: issuing /*+ IndexScan(t2) */EXPLAIN (ANALYZE FALSE, VERBOSE FALSE, COSTS TRUE, BUFFERS FALSE, WAL FALSE, TIMING FALSE, SUMMARY FALSE, FORMAT TEXT) SELECT id, val FROM public.t2_102136 t2 WHERE (id OPERATOR(pg_catalog.>) 11)
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE: lt_hint_plan:
used hint:
IndexScan(t2@lt#-1)
not used hint:
duplication hint:
error hint:
DETAIL: from 192.168.247.128:6432
NOTICE: issuing ROLLBACK TO SAVEPOINT canopy_explain_savepoint
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE: issuing SAVEPOINT canopy_explain_savepoint
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE: issuing EXPLAIN (ANALYZE FALSE, VERBOSE FALSE, COSTS TRUE, BUFFERS FALSE, WAL FALSE, TIMING FALSE, SUMMARY FALSE, FORMAT TEXT) SELECT t1.id, t1.val, x.id, x.val FROM public.t1_102108 t1, (SELECT intermediate_result.id, intermediate_result.val FROM read_intermediate_result('11_1'::text, 'binary'::canopy_copy_format) intermediate_result(id integer, val integer)) x WHERE (t1.id OPERATOR(pg_catalog.=) 10)
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE: issuing ROLLBACK TO SAVEPOINT canopy_explain_savepoint
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 3
NOTICE: issuing COMMIT
DETAIL: on server lightdb@192.168.247.128:6432 connectionId: 3
QUERY PLAN
-----------------------------------------------------------------------------------
------------------------------------
Custom Scan (Canopy Adaptive) (cost=0.00..0.00 rows=0 width=0)
-> Distributed Subplan 11_1
-> Custom Scan (Canopy Adaptive) (cost=0.00..0.00 rows=100000 width=8)
Task Count: 32
Tasks Shown: One of 32
-> Task
Node: host=192.168.247.128 port=6432 dbname=postgres
-> Index Scan using t2_pkey_102136 on t2_102136 t2 (cost=0.1
5..24.33 rows=753 width=8)
Index Cond: (id > 11)
Task Count: 1
Tasks Shown: All
-> Task
Node: host=192.168.247.128 port=6432 dbname=postgres
-> Nested Loop (cost=0.16..22.17 rows=1000 width=16)
-> Index Scan using t1_pkey_102108 on t1_102108 t1 (cost=0.15..2.1
7 rows=1 width=8)
Index Cond: (id = 10)
-> Function Scan on read_intermediate_result intermediate_result (
cost=0.00..10.00 rows=1000 width=8)
(17 rows)
lightdb@postgres=#
在任何错误上，lt_hint_plan 都会停止解析，并在大多数情况下使用已经解析的提示。 只有在解释 SQL 时才会报告错误。 以下是典型的错误。
任何语法错误或错误的提示名称都会被报告为语法错误。这些错误将在服务器日志中报告，消息级别由lt_hint_plan.message_level指定，如果lt_hint_plan.debug_print处于开启状态及以上。 以数字开头的关键字不会成为提示名称，它不会被报告为语法错误，但会被忽略。
对象错误规范会导致提示被默默地忽略。这种类型的错误将会在服务器日志中与语法错误相同的条件下报告为"未使用的提示"。
对于同一查询块，当出现冗余提示时，其中一个提示将被激活，当出现互相冲突的提示时，将没有提示被激活。 对于不同的查询块，当出现冗余的提示或者互相冲突的提示时，只有子查询的提示将被激活。 无论何时出现冗余的提示或者互相冲突的提示，这种类型的错误将以与语法错误相同的条件在服务器日志中报告为"重复的提示"。 如果qb_name提示冲突，则两个提示都将被忽略。
对于全局级别的提示，提示注释中不能包含另一个块注释。如果lt_hint_plan发现了这种情况，与其他错误不同，它将停止解析并放弃已经解析的所有提示。这种类型的错误将以与其他错误相同的方式报告。 对于语句级别的提示，提示注释中不能包含另一个块注释。它将忽略提示注释中的'/*'。如果报告错误，它只会影响当前查询块的提示。
lightdb@postgres=# explain select * from t1 ,(select * from t2 where id >10) as tt where exists (select /*+ /*+seqscan(t1)*/*/* from t3 where id>1);;
INFO: lt_hint_plan: hint syntax error at or near "+seqscan(t1)"
DETAIL: Unrecognized hint keyword "+seqscan".
QUERY PLAN
------------------------------------------------------------------------
Result (cost=1.04..3.13 rows=3 width=532)
One-Time Filter: $0
InitPlan 1 (returns $0)
-> Seq Scan on t3 @"lt#0" (cost=0.00..1.04 rows=1 width=0)
Filter: (id > 1)
-> Nested Loop (cost=1.04..3.13 rows=3 width=532)
-> Seq Scan on t2 (cost=0.00..1.04 rows=1 width=8)
Filter: (id > 10)
-> Seq Scan on t1 @"lt#1" (cost=0.00..1.03 rows=3 width=524)
(9 rows)
lightdb@postgres=# explain select * from t1 ,(select * from t2 where id >10) as tt where exists (select /*+ /*seqscan(t1)*/*/* from t3 where id>1);;
LOG: lt_hint_plan:
used hint:
not used hint:
SeqScan(t1@lt#0)
duplication hint:
error hint:
QUERY PLAN
------------------------------------------------------------------------
Result (cost=1.04..3.13 rows=3 width=532)
One-Time Filter: $0
InitPlan 1 (returns $0)
-> Seq Scan on t3 @"lt#0" (cost=0.00..1.04 rows=1 width=0)
Filter: (id > 1)
-> Nested Loop (cost=1.04..3.13 rows=3 width=532)
-> Seq Scan on t2 (cost=0.00..1.04 rows=1 width=8)
Filter: (id > 10)
-> Seq Scan on t1 @"lt#1" (cost=0.00..1.03 rows=3 width=524)
(9 rows)
lightdb@postgres=#
优化器不会尝试考虑超过 from_collapse_limit 的 FROM 子句条目的连接顺序。因此，lt_hint_plan 不能像预期的那样影响连接顺序。
当无法执行强制执行计划时，优化器会选择任何可执行的计划。
使用嵌套循环进行 FULL OUTER JOIN。
使用没有在 quals 中使用的列的索引。
对于没有 ctid 条件的查询，使用 TID 扫描。
ECPG(Oracle Pro*c兼容)会删除作为嵌入式 SQLs 编写的查询中的注释，因此无法通过这些查询传递提示信息。唯一的例外是 EXECUTE 命令传递给定字符串未经修改。请在这种情况下考虑提示表。
lt_stat_statements 生成查询 ID 时会忽略注释。因此，具有不同提示的相同查询将被归纳为同一查询。
从21.3版本开始不支持在非dml的update，select, insert，delete 关键字后面使用注释，如： 'select for update /**/;' 不支持. 从24.1.6版本开始，只对 on update current_timestamp 和 with update current_timestamp 中update 后面不支持使用注释。
下面列出了可用的提示。
Table 15.3. 提示列表
| 组 | 格式 | 描述 |
|---|---|---|
| 扫描方法 | SeqScan(table) full(table) | 强制对表进行顺序扫描 |
| TidScan(table) | 强制对表进行 TID 扫描。 | |
| IndexScan(table[ index...]) index(table[ index...]) | 强制对表进行索引扫描。如果指定了，则限制为指定的索引。 | |
| IndexOnlyScan(table[ index...]) | 强制对表进行仅索引扫描。如果指定了，则限制为指定的索引。如果不支持仅索引扫描，则可能使用索引扫描。 | |
| BitmapScan(table[ index...]) | 强制对表进行位图扫描。如果指定了，则限制为指定的索引。 | |
| IndexScanRegexp(table[ POSIX Regexp...]) IndexOnlyScanRegexp(table[ POSIX Regexp...]) BitmapScanRegexp(table[ POSIX Regexp...]) | 强制对表进行索引扫描或仅索引扫描或位图扫描。 限制为与指定的 POSIX 正则表达式模式匹配的索引。 | |
| NoSeqScan(table) | 强制不对表进行顺序扫描。 | |
| NoTidScan(table) | 强制不对表进行 TID 扫描。 | |
| NoIndexScan(table) | 强制不对表进行索引扫描和仅索引扫描。 | |
| NoIndexOnlyScan(table) | 强制不对表进行仅索引扫描。 | |
| NoBitmapScan(table) | 强制不对表进行位图扫描。 | |
| 连接方法 | NestLoop(table table[ table...]) use_nl(table table[ table...]) | 强制为由指定表组成的连接使用嵌套循环。 |
| HashJoin(table table[ table...]) use_hash(table table[ table...]) | 强制为由指定表组成的连接使用哈希连接。 | |
| MergeJoin(table table[ table...]) use_merge(table table[ table...]) | 强制为由指定表组成的连接使用归并连接。 | |
| NoNestLoop(table table[ table...]) no_use_nl(table table[ table...]) | 强制不对由指定表组成的连接使用嵌套循环。 | |
| NoHashJoin(table table[ table...]) no_use_hash(table table[ table...]) | 强制不对由指定表组成的连接使用哈希连接。 | |
| NoMergeJoin(table table[ table...]) no_use_merge(table table[ table...]) | 强制不对由指定表组成的连接使用归并连接。 | |
| 连接方法（Oracle 模式） | NestLoop(table [ table...]) use_nl(table[ table...]) | 强制为内部表指定的连接使用嵌套循环。 |
| HashJoin(table[ table...]) use_hash(table[ table...]) | 强制为内部表指定的连接使用哈希连接。 | |
| MergeJoin(table[ table...]) use_merge(table[ table...]) | 强制为内部表指定的连接使用归并连接。 | |
| NoNestLoop(table[ table...]) no_use_nl(table[ table...]) | 强制不对内部表指定的连接使用嵌套循环。 | |
| NoHashJoin(table[ table...]) no_use_hash(table[ table...]) | 强制不对内部表指定的连接使用哈希连接。 | |
| NoMergeJoin(table[ table...]) no_use_merge(table[ table...]) | 强制不对内部表指定的连接使用归并连接。 | |
| 连接顺序 | Leading(table table[ table...]) Ordered(table table[ table...]) | 强制按指定顺序和方向连接。 |
| Leading([join pair]) Ordered([join pair]) | 强制按指定顺序和方向连接。连接对是由括号括起来的表和/或其他连接对组成的一对， 可以形成嵌套结构。 | |
| Ordered Leading | 强制按 FROM 子句中出现的顺序连接表。 | |
| Leading(table) Ordered(table) | 强制使用指定的表作为连接执行计划的第一个表进行连接。 | |
| 行号修正 | Rows(table [ table...] correction) cardinality(table [ table...] correction) | 修正由指定表组成的连接或子查询结果的行号。可用的修正方法有绝对值（#[n] 或 [n]）、 加法（+[n]）、减法（-[n]）和乘法（*[n]）。[n] 应该是 strtod() 可以读取的字符串。 |
| 并行查询配置 | Parallel(table [# of workers] [soft|hard]) | 强制或禁止指定表的并行执行。[# of workers] 是所需的并行工作进程数，其中零表示禁止并行执行。 如果第三个参数是 soft，它只会更改 max_parallel_workers_per_gather，其他的交给优化器。 hard（默认）表示强制指定数量的工作进程。 |
| Parallel([# of workers] [soft|hard]) | 强制或禁止当前查询块中的表的并行执行。[# of workers] 是所需的并行工作进程数，其中零表示禁止并行执行。 如果第二个参数是 soft，它只会更改 max_parallel_workers_per_gather，其他的交给优化器。 hard（默认）表示强制指定数量的工作进程。 | |
| GUC | Set(GUC-param value) | 在优化器运行时将 GUC 参数设置为指定的值。 |
| Opt_param('GUC-param' value) | 在优化器运行时将 GUC 参数设置为指定的值。 | |
| 查询块名称 | qb_name(name) | 设置查询块名称，然后可以在外部查询块中使用此查询块中的表提示。 不能用于 GUC。 |
| 不合并 | no_merge[(子查询名称)] no_merge(@查询块名称) | 强制不提升子查询。优先级高于连接方法和连接顺序。 |
| 分组方法 | use_hash_aggregation | 强制使用哈希聚合方法进行分组。 |
| no_use_hash_aggregation | 强制不使用哈希聚合方法进行分组，而是使用排序方法。 | |
| 半连接（特殊连接） | semijoin | 强制将相关的EXISTS子查询转换为半连接以访问指定的表。 |
| hash_sj | 强制将相关的EXISTS子查询转换为哈希半连接以访问指定的表。 | |
| nl_sj | 强制将相关的EXISTS子查询转换为嵌套循环半连接以访问指定的表。 | |
| merge_sj | 强制将相关的EXISTS子查询转换为合并半连接以访问指定的表。 | |
| no_semijoin | 如果可能的话，强制不使用半连接，而不是不拉升子链接。 | |
| 反连接（特殊连接） | hash_aj | 强制将相关的NOT EXISTS子查询转换为哈希反连接以访问指定的表。 |
| nl_aj | 强制将相关的NOT EXISTS子查询转换为嵌套循环反连接以访问指定的表。 | |
| merge_aj | 强制将相关的NOT EXISTS子查询转换为合并反连接以访问指定的表。 | |
| Swap_join_inputs | swap_join_input(table) | 在哈希连接时强制使用表作为外部表 |
| 有序谓词 | ordered_predicates | 强制优化器保留谓词评估的顺序 |
| 展开 | unnest | 拉升子链接是一种RBO，因此只支持语法 |
| 不展开 | no_unnest | 强制优化器不拉升子链接 |
| Pq_distribute | pq_distribute(inner_table outer_distribution inner_distribution) | pq_distribute提示指示优化器如何执行并行连接。inner_table是连接的内部表， outer_distribution是外部表的分布，inner_distribution是内部表的分布。 目前仅支持“none broadcast”和“hash hash”，而且只有“none broadcast”可以使用。 |
| No_push_subq | no_push_subq | 强制优化器不下推带子链接的过滤条件，而在最后才使用此条件过滤数据 |
| Push_subq | push_subq | 强制优化器下推带子链接的过滤条件，尽可能早的使用此条件过滤数据(LightDB 的默认行为). |
| Ignore_row_on_dupkey_index | ignore_row_on_dupkey_index(table_name indexname) ignore_row_on_dupkey_index(table_name (colname[, colname...])) | 强制忽略违反指定列集或指定索引的唯一键冲突错误。 |
| No_push_pred | no_push_pred[(sub query name)] no_push_pred(@queryblock_name) | 指示优化器不要将连接谓词推入不能合并的子查询中 |
| No_star_transformation | no_star_transformation | 指示优化器不执行星型查询转换. |
| Append | append | 指示优化器对insert select模式的语句使用 direct-path 插入 |
| No_expand | no_expand | 指示优化器对于在 where 子句中带有 or 条件或 IN-LISTS 的查询不进行 OR-expansion 优化。 |