Source: https://dev-docs.yueshu.com.cn/v5.3.2/database-gql-reference/executions/hints/ Documentation version: v5.3.2 # 配置提示（Hints） 提示（Hints）是以特殊注释形式添加到查询中的指令，用于建议查询优化器如何执行查询。提示使用`/*+ ... */`语法。 支持以下类型的提示： | 提示 | 作用范围 | 说明 | `INDEX` | 点/边模式 | 建议优化器使用指定的索引。 | `IGNORE_INDEX` | 点/边模式 | 建议优化器不使用指定的索引。 | `NO_REORDER` | 路径模式 | 阻止带注解路径的`MATCH`模式展开顺序重排，保持查询文本中的从左到右顺序。 ## 索引提示 索引提示（Index hints）以注释的形式添加到查询中，用于建议查询优化器使用或不使用某个索引。 索引提示适用于 MATCH 语句，且必须加在 MATCH 语句的点或边模式之后。更多信息，请参见 [MATCH](https://dev-docs.yueshu.com.cn/v5.3.2/database-gql-reference/dql/match/)。 ### 前提条件 已创建索引。更多信息，请参见[索引概述](https://dev-docs.yueshu.com.cn/v5.3.2/database-gql-reference/ddl/index/)。 ### 语法 语法图源码 index_hint ```` index_hint ::= '/*+' 'INDEX' '(' index_name (',' index_name)* ')' '*/' | '/*+' 'IGNORE_INDEX' '(' index_name (',' index_name)* ')' '*/' ```` ### 参数 #### `INDEX` 该关键字用于指定查询优化器在执行查询时应考虑使用的索引。请注意，只有此处指定的索引才可能会被查询优化器使用。 #### `IGNORE_INDEX` 该关键字用于指定查询优化器在执行查询时不可以使用的索引。请注意，此处指定的索引将不会被查询优化器使用。 #### `index_name` 提示查询优化器使用或不使用的索引名称。可使用 [SHOW (ALL) INDEXES](https://dev-docs.yueshu.com.cn/v5.3.2/database-gql-reference/data-admin/show/show-indexes/) 查询可用索引。 若指定的索引无效，也即该索引不存在或不适用于查询中的指定点或边类型，查询优化器将不会考虑该无效索引。 若在`INDEX`提示中指定了多个索引，查询优化器仅会考虑有效的索引，并使用最适合查询的一个或多个索引。 若在`IGNORE_INDEX`提示中指定了多个索引，查询优化器将不会考虑或使用这些索引。 ## NO_REORDER 提示 []() `NO_REORDER`提示是一个路径级别的提示，用于阻止优化器重排带注解图模式的展开顺序。使用该提示后，优化器将按照查询文本中的书写顺序（从左到右）展开该路径模式，而不会基于代价估算选择其他展开顺序。 从 5.3.0 起，查询级`MATCH`模式展开顺序重排由`enable_reorder`控制，默认值为`false`。当为整条查询、当前会话或整个部署开启`enable_reorder`时，如果只想让某一条路径保持文本顺序，仍可使用`NO_REORDER`。 ### 语法 语法图源码 no_reorder_hint ```` no_reorder_hint ::= '/*+' 'NO_REORDER' '*/' ```` `NO_REORDER`提示位于`MATCH`语句的路径模式之前： ```` MATCH /*+ NO_REORDER */ (a)-[e0]->(b)-[e1]->(c) ... ```` ### 适用场景 - 计划稳定性：当启用`enable_reorder`时，优化器可能根据数据统计信息选择不同的起始点或展开顺序。`NO_REORDER`可将带注解路径锁定为文本顺序。 - 性能调优：当您了解数据的最佳展开方向时，可以使用此提示仅覆盖某一条路径的重排决策，而不必全局关闭重排。 - 调试排查：对比使用和不使用`NO_REORDER`的执行计划，判断重排是否导致了性能问题。您可以结合`EXPLAIN CBO`来查看效果。更多信息，请参见 [EXPLAIN](https://dev-docs.yueshu.com.cn/v5.3.2/database-gql-reference/data-admin/tuning/explain/)。 ### 作用范围 - 该提示仅作用于单条路径模式，不影响整个`MATCH`子句。 - 在多路径`MATCH`中，可以只对部分路径使用`NO_REORDER`，其余路径仍可由 CBO 自由优化。 - 在`EXISTS`子查询和标量`VALUE`子查询中同样支持。 - 与路径类型（如`TRAIL`）和路径变量兼容。 ## 示例 以下示例基于[示例数据集](https://dev-docs.yueshu.com.cn/v5.3.2/database-gql-reference/overview/example-dataset/)，并假设 [CREATE NORMAL INDEX](https://dev-docs.yueshu.com.cn/v5.3.2/database-gql-reference/ddl/index/create/#_5) 示例一节中介绍的索引`idx_actor_age`和`idx_watch_rating`均已创建。 ### 提示使用点类型的索引 ```` ''' PROFILE USE movie MATCH (v@Actor{birthDate: date('1951-03-28')}/*+ INDEX(idx_actor_age) */) RETURN v.id AS id, v.name AS name ''' ```` 返回： ```` +------+-----------------+ | id | name | +------+-----------------+ | 4826 | Matti Pellonpää | +------+-----------------+ Got 1 rows (time spent 4.543ms/5.132794ms) • [P0]CallbackSink │ details: v.id AS id, v.name AS name │ time(ms): 0.00 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Project │ details: v.id AS id, v.name AS name │ time(ms): 0.04 │ rows: 1 │ memory(KiB): 0.56 │ blocked(ms): 0.00 │ └── • [P0]StorageRemoteAccess │ details: varName: v, IndexScan: {Actor: {id, name │ , birthDate}}, filter: None, index: idx_ │ actor_age, constraint: [/birthDate]:[/DA │ TE "1951-03-28"] │ time(ms): 0.00 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 10.49 │ └── • [SP0]CallbackSink │ details: varName: v, IndexScan: UNKNOWN, filter: │ None, index: idx_actor_age, constraint: │ [/birthDate]:[/DATE "1951-03-28"] │ time(ms): 0.02 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [SP0]NodesScan details: varName: v, IndexScan: UNKNOWN, filter: None, index: idx_actor_age, constraint: [/birthDate]:[/DATE "1951-03-28"] time(ms): 3.00 rows: 1 memory(KiB): 93.31 blocked(ms): 0.00 Execution Plan: [Px] means pipeline-x and [S] means storage side. Elapsed Time: parse time : 330 us build time : 267 us optimize time : 361 us serialize time: 45 us total time : 12609 us ```` 执行计划显示索引`idx_actor_age`被用于执行查询。 ### 提示不使用点类型的索引 ```` ''' PROFILE USE movie MATCH (v@Actor{birthDate: date('1951-03-28')}/*+ IGNORE_INDEX(idx_actor_age) */) RETURN v.id AS id, v.name AS name ''' ```` 返回： ```` +------+-----------------+ | id | name | +------+-----------------+ | 4826 | Matti Pellonpää | +------+-----------------+ Got 1 rows (time spent 3.193ms/3.819243ms) +------+-----------------+ | id | name | +------+-----------------+ | 4826 | Matti Pellonpää | +------+-----------------+ Got 1 rows (time spent 6.999ms/8.012739ms) • [P0]CallbackSink │ details: v.id AS id, v.name AS name │ time(ms): 0.00 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Project │ details: v.id AS id, v.name AS name │ time(ms): 0.03 │ rows: 1 │ memory(KiB): 0.56 │ blocked(ms): 0.00 │ └── • [P0]StorageRemoteAccess │ details: varName: v, TableScan: {Actor: {id, name │ , birthDate}}, filter: v.birthDate = DAT │ E "1951-03-28" │ time(ms): 0.00 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 5.37 │ └── • [SP0]CallbackSink │ details: varName: v, TableScan: UNKNOWN, filter: │ v.birthDate = DATE "1951-03-28" │ time(ms): 0.04 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [SP0]NodesScan details: varName: v, TableScan: UNKNOWN, filter: v.birthDate = DATE "1951-03-28" time(ms): 4.18 rows: 1 memory(KiB): 54.01 blocked(ms): 0.00 Execution Plan: [Px] means pipeline-x and [S] means storage side. Elapsed Time: parse time : 295 us build time : 163 us optimize time : 188 us serialize time: 30 us total time : 6999 us ```` 执行计划显示索引`idx_actor_age`未被用于执行查询。 ### 提示使用边类型的索引 ```` ''' PROFILE USE movie MATCH ()-[e:Watch/*+ INDEX(idx_watch_rating) */]->(b) WHERE e.rate > 4.0 RETURN e.rate AS rate, b.name AS name ORDER BY rate DESC LIMIT 10 ''' ```` 返回： ```` +------+------------------------------+ | rate | name | +------+------------------------------+ | 5.0 | A.I. Artificial Intelligence | | 5.0 | Bend It Like Beckham | | 5.0 | Ocean's Thirteen | | 5.0 | Ocean's Thirteen | | 5.0 | Ocean's Thirteen | | 5.0 | A.I. Artificial Intelligence | | 5.0 | Princess Mononoke | | 5.0 | Princess Mononoke | | 5.0 | Princess Mononoke | | 5.0 | Princess Mononoke | +------+------------------------------+ Got 10 rows (time spent 8.525ms/9.887066ms) • [P0]CallbackSink │ details: rate DESC LIMIT 10 │ time(ms): 0.00 │ rows: 10 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]TopN │ details: rate DESC LIMIT 10 │ time(ms): 0.60 │ rows: 10 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Project │ details: e.rate AS rate, b.name AS name │ time(ms): 0.08 │ rows: 1106 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]HashJoinProbe │ details: element_id(b)=_right_node_id(e) │ time(ms): 0.28 │ rows: 1106 │ memory(KiB): 0.00 │ blocked(ms): 3.94 │ ├── • [P0]StorageRemoteAccess │ │ details: varName: b, TableScan: {Movie: {name}}, │ │ filter: None │ │ time(ms): 0.01 │ │ rows: 72 │ │ memory(KiB): 0.00 │ │ blocked(ms): 0.96 │ │ │ └── • [SP0]CallbackSink │ │ details: varName: b, TableScan: UNKNOWN, filter: │ │ None │ │ time(ms): 0.03 │ │ rows: 72 │ │ memory(KiB): 0.00 │ │ blocked(ms): 0.00 │ │ │ └── • [SP0]NodesScan │ details: varName: b, TableScan: UNKNOWN, filter: │ None │ time(ms): 1.27 │ rows: 72 │ memory(KiB): 68.70 │ blocked(ms): 0.00 │ └── • [P1]HashJoinBuild │ details: element_id(b)=_right_node_id(e) │ time(ms): 0.23 │ rows: 72 │ memory(KiB): 62.31 │ blocked(ms): 0.00 │ └── • [P1]StorageRemoteAccess │ details: direction: outgoing: true, incoming: fal │ se, undirected0: false, undirected1: fal │ se, varName: e, IndexScan: {Watch: {rate │ }}, filter: node_type_id(_left_node_id(e │ )) = 1026, index: idx_watch_rating, cons │ traint: [/rate]:(/NULL - /4.000000476837 │ 158] │ time(ms): 0.02 │ rows: 1106 │ memory(KiB): 0.00 │ blocked(ms): 2.12 │ └── • [SP0]CallbackSink │ details: direction: outgoing: true, incoming: fal │ se, undirected0: false, undirected1: fal │ se, varName: e, IndexScan: UNKNOWN, filt │ er: node_type_id(_left_node_id(e)) = 102 │ 6, index: idx_watch_rating, constraint: │ [/rate]:(/NULL - /4.000000476837158] │ time(ms): 0.08 │ rows: 1106 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [SP0]EdgesScan details: direction: outgoing: true, incoming: fal se, undirected0: false, undirected1: fal se, varName: e, IndexScan: UNKNOWN, filt er: node_type_id(_left_node_id(e)) = 102 6, index: idx_watch_rating, constraint: [/rate]:(/NULL - /4.000000476837158] time(ms): 3.15 rows: 1106 memory(KiB): 72.62 blocked(ms): 0.00 Execution Plan: [Px] means pipeline-x and [S] means storage side. Elapsed Time: parse time : 277 us build time : 350 us optimize time : 539 us serialize time: 28 us total time : 8525 us ```` 执行计划显示索引`idx_watch_rating`被用于执行查询。 ### 提示不使用边类型的索引 ```` ''' PROFILE USE movie MATCH ()-[e:Watch/*+ IGNORE_INDEX(idx_watch_rating) */]->(b) WHERE e.rate > 4.0 RETURN e.rate AS rate, b.name AS name ORDER BY rate DESC LIMIT 10 ''' ```` 返回： ```` +------+-------------------+ | rate | name | +------+-------------------+ | 5.0 | Life Is Beautiful | | 5.0 | Life Is Beautiful | | 5.0 | Life Is Beautiful | | 5.0 | Life Is Beautiful | | 5.0 | Life Is Beautiful | | 5.0 | Life Is Beautiful | | 5.0 | City of God | | 5.0 | City of God | | 5.0 | City of God | | 5.0 | City of God | +------+-------------------+ Got 10 rows (time spent 20.26ms/22.060133ms) • [P0]CallbackSink │ details: rate DESC LIMIT 10 │ time(ms): 0.00 │ rows: 10 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]TopN │ details: rate DESC LIMIT 10 │ time(ms): 0.73 │ rows: 10 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Project │ details: e.rate AS rate, b.name AS name │ time(ms): 0.12 │ rows: 1106 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]HashJoinProbe │ details: element_id(b)=_right_node_id(e) │ time(ms): 0.45 │ rows: 1106 │ memory(KiB): 0.00 │ blocked(ms): 12.08 │ ├── • [P0]StorageRemoteAccess │ │ details: varName: b, TableScan: {Movie: {name}}, │ │ filter: None │ │ time(ms): 0.01 │ │ rows: 72 │ │ memory(KiB): 0.00 │ │ blocked(ms): 3.32 │ │ │ └── • [SP0]CallbackSink │ │ details: varName: b, TableScan: UNKNOWN, filter: │ │ None │ │ time(ms): 0.03 │ │ rows: 72 │ │ memory(KiB): 0.00 │ │ blocked(ms): 0.00 │ │ │ └── • [SP0]NodesScan │ details: varName: b, TableScan: UNKNOWN, filter: │ None │ time(ms): 1.33 │ rows: 72 │ memory(KiB): 68.70 │ blocked(ms): 0.00 │ └── • [P1]HashJoinBuild │ details: element_id(b)=_right_node_id(e) │ time(ms): 0.30 │ rows: 72 │ memory(KiB): 62.31 │ blocked(ms): 0.00 │ └── • [P1]StorageRemoteAccess │ details: direction: outgoing: true, incoming: fal │ se, undirected0: false, undirected1: fal │ se, varName: e, TableScan: {Watch: {rate │ }}, filter: (e.rate > 4) AND (node_type_ │ id(_left_node_id(e)) = 1026) │ time(ms): 0.01 │ rows: 1106 │ memory(KiB): 0.00 │ blocked(ms): 11.69 │ └── • [SP0]CallbackSink │ details: direction: outgoing: true, incoming: fal │ se, undirected0: false, undirected1: fal │ se, varName: e, TableScan: UNKNOWN, filt │ er: (e.rate > 4) AND (node_type_id(_left │ _node_id(e)) = 1026) │ time(ms): 0.05 │ rows: 1106 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [SP0]EdgesScan details: direction: outgoing: true, incoming: fal se, undirected0: false, undirected1: fal se, varName: e, TableScan: UNKNOWN, filt er: (e.rate > 4) AND (node_type_id(_left _node_id(e)) = 1026) time(ms): 11.85 rows: 1106 memory(KiB): 40.80 blocked(ms): 0.00 Execution Plan: [Px] means pipeline-x and [S] means storage side. Elapsed Time: parse time : 254 us build time : 294 us optimize time : 343 us serialize time: 36 us total time : 20260 us ```` 执行计划显示索引`idx_watch_rating`未被用于执行查询。 ### 提示使用多个索引 ```` ''' // 为 Actor 点类型创建一个新的索引 USE movie CREATE NORMAL INDEX IF NOT EXISTS idx_actor_name ON NODE Actor ( name ASC ) ''' ''' // 提示查询优化器使用多个索引 PROFILE USE movie MATCH (v@Actor{birthDate: date('1951-03-28')}/*+ INDEX(idx_actor_age, idx_actor_name, non_exist_index) */) RETURN v.id AS id, v.name AS name ''' ```` 返回： ```` +------+-----------------+ | id | name | +------+-----------------+ | 4826 | Matti Pellonpää | +------+-----------------+ Got 1 rows (time spent 12.321ms/13.505626ms) • [P0]CallbackSink │ details: v.id AS id, v.name AS name │ time(ms): 0.00 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Project │ details: v.id AS id, v.name AS name │ time(ms): 0.03 │ rows: 1 │ memory(KiB): 0.56 │ blocked(ms): 0.00 │ └── • [P0]StorageRemoteAccess │ details: varName: v, IndexScan: {Actor: {id, name │ , birthDate}}, filter: None, index: idx_ │ actor_age, constraint: [/birthDate]:[/DA │ TE "1951-03-28"] │ time(ms): 0.00 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 10.41 │ └── • [SP0]CallbackSink │ details: varName: v, IndexScan: UNKNOWN, filter: │ None, index: idx_actor_age, constraint: │ [/birthDate]:[/DATE "1951-03-28"] │ time(ms): 0.02 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [SP0]NodesScan details: varName: v, IndexScan: UNKNOWN, filter: None, index: idx_actor_age, constraint: [/birthDate]:[/DATE "1951-03-28"] time(ms): 3.70 rows: 1 memory(KiB): 93.31 blocked(ms): 0.00 Execution Plan: [Px] means pipeline-x and [S] means storage side. Elapsed Time: parse time : 230 us build time : 202 us optimize time : 223 us serialize time: 38 us total time : 12321 us ```` 上述示例提示查询优化器使用索引`idx_actor_age`、`idx_actor_name`和一个不存在的索引`non_exist_index`。查询优化器仅考虑有效索引`idx_actor_age`和`idx_actor_name`，并使用最合适的索引（即`idx_actor_age`）来执行查询。 ### 提示不使用多个索引 ```` ''' // 为 Actor 点类型创建一个新的索引 USE movie CREATE NORMAL INDEX IF NOT EXISTS idx_actor_name ON NODE Actor ( name ASC ) ''' ''' // 提示查询优化器不使用索引 idx_actor_age 和 idx_actor_name PROFILE USE movie MATCH (v@Actor{birthDate: date('1951-03-28')}/*+ IGNORE_INDEX(idx_actor_age, idx_actor_name ) */) RETURN v.id AS id, v.name AS name ''' ```` 返回： ```` +------+-----------------+ | id | name | +------+-----------------+ | 4826 | Matti Pellonpää | +------+-----------------+ Got 1 rows (time spent 6.018ms/7.147414ms) • [P0]CallbackSink │ details: v.id AS id, v.name AS name │ time(ms): 0.00 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Project │ details: v.id AS id, v.name AS name │ time(ms): 0.02 │ rows: 1 │ memory(KiB): 0.56 │ blocked(ms): 0.00 │ └── • [P0]StorageRemoteAccess │ details: varName: v, TableScan: {Actor: {id, name │ , birthDate}}, filter: v.birthDate = DAT │ E "1951-03-28" │ time(ms): 0.00 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 3.59 │ └── • [SP0]CallbackSink │ details: varName: v, TableScan: UNKNOWN, filter: │ v.birthDate = DATE "1951-03-28" │ time(ms): 0.06 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [SP0]NodesScan details: varName: v, TableScan: UNKNOWN, filter: v.birthDate = DATE "1951-03-28" time(ms): 4.32 rows: 1 memory(KiB): 54.01 blocked(ms): 0.00 Execution Plan: [Px] means pipeline-x and [S] means storage side. Elapsed Time: parse time : 382 us build time : 310 us optimize time : 343 us serialize time: 64 us total time : 6018 us ```` ### 单路径使用 NO_REORDER 提示 ```` ''' SESSION SET enable_reorder = true ''' ''' PROFILE USE movie MATCH /*+ NO_REORDER */ (a:Person)-[e0:Watch]->(b:Movie{id:101}) RETURN a.id, e0.rate ''' ```` 展开查看返回 ```` +------+---------+ | a.id | e0.rate | +------+---------+ | 318 | 3.0 | +------+---------+ Got 1 rows (time spent 18.26ms/19.619136ms) • [P0]CallbackSink │ details: a.id, e0.rate │ time(ms): 0.00 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Project │ details: a.id, e0.rate │ time(ms): 0.01 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]NEHashJoinProbe │ details: Project: e0, a, element_id(b)=_right_no │ de_id(e0) │ time(ms): 0.11 │ rows: 1 │ memory(KiB): 0.00 │ blocked(ms): 12.67 │ ├── • [P0]StorageRemoteAccess │ │ details: scanStrategy: SortedSeek, varName: b, In │ │ dexScan: {Movie: {id}}, filter: None, in │ │ dex: pkIndex_Movie, constraint: [/id]:[/ │ │ 101], graph: movie, schema: /default_sch │ │ ema, b{}, GraphId: 1024 │ │ time(ms): 0.09 │ │ rows: 1 │ │ memory(KiB): 0.16 │ │ blocked(ms): 0.51 │ │ │ └── • [SP0]CallbackSink │ │ details: scanStrategy: SortedSeek, varName: b, In │ │ dexScan: {Movie: {id}}, filter: None, in │ │ dex: pkIndex_Movie, constraint: [/id]:[/ │ │ 101], graph: movie, schema: /default_sch │ │ ema, b{}, GraphId: 1024 │ │ time(ms): 0.00 │ │ rows: 1 │ │ memory(KiB): 0.00 │ │ blocked(ms): 0.00 │ │ │ └── • [SP0]NodesScan │ details: scanStrategy: SortedSeek, varName: b, In │ dexScan: {Movie: {id}}, filter: None, in │ dex: pkIndex_Movie, constraint: [/id]:[/ │ 101], graph: movie, schema: /default_sch │ ema, b{}, GraphId: 1024 │ time(ms): 0.11 │ rows: 1 │ memory(KiB): 48.66 │ blocked(ms): 0.00 │ └── • [P1]NEHashJoinBuild │ details: Project: e0, a, element_id(b)=_right_no │ de_id(e0) │ time(ms): 0.44 │ rows: 92 │ memory(KiB): 223.41 │ blocked(ms): 0.00 │ └── • [P1]StorageRemoteAccess │ details: _left_node_id(e0)=element_id(a) │ time(ms): 1.20 │ rows: 3910 │ memory(KiB): 0.38 │ blocked(ms): 10.69 │ └── • [SP0]CallbackSink │ details: _left_node_id(e0)=element_id(a) │ time(ms): 0.03 │ rows: 3910 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [SP0]NEHashJoinProbe │ details: _left_node_id(e0)=element_id(a) │ time(ms): 2.33 │ rows: 3910 │ memory(KiB): 73.03 │ blocked(ms): 7.50 │ ├── • [SP0]EdgesScan │ details: direction: outgoing: true, incoming: fal │ se, undirected0: false, undirected1: fal │ se, scanStrategy: SortedSeek, varName: e │ 0, TableScan: {Watch: {rate}}, filter: N │ one, graph: movie, schema: /default_sche │ ma, e0{}, GraphId: 1024 │ time(ms): 4.92 │ rows: 3910 │ memory(KiB): 72.64 │ blocked(ms): 0.00 │ └── • [SP1]NEHashJoinBuild │ details: _left_node_id(e0)=element_id(a) │ time(ms): 17.04 │ rows: 569 │ memory(KiB): 3.84 │ blocked(ms): 0.00 │ └── • [SP1]NodesScan details: scanStrategy: SortedSeek, varName: a, Ta bleScan: {User: {id}}, filter: None, gra ph: movie, schema: /default_schema, a{}, GraphId: 1024 time(ms): 1.88 rows: 569 memory(KiB): 48.62 blocked(ms): 0.00 Execution Plan: [Px] means pipeline-x and [S] means storage side. Elapsed Time: parse time : 1041 us build time : 804 us optimize time : 1368 us serialize time: 80 us total time : 18260 us ```` 若不使用该提示，CBO 可能选择从`b`（具有特定`id`的高选择率节点）开始，反向展开到`a`。使用`NO_REORDER`后，优化器将按文本顺序从`a`开始展开到`b`。 ### 混合路径使用 NO_REORDER 提示 ```` ''' SESSION SET enable_reorder = true ''' ''' PROFILE USE movie MATCH /*+ NO_REORDER */ (a:Person)-[e0:Watch]->(b:Movie), (c:Person{id:1})-[e1:Watch]->(d:Movie) WHERE e0.rate > 3 AND e1.rate > 3 RETURN a.id, b.name, c.id, d.name LIMIT 10 ''' ```` 展开查看返回 ```` +------+-------------------------+------+-----------------------+ | a.id | b.name | c.id | d.name | +------+-------------------------+------+-----------------------+ | 600 | Nightmare on Elm Street | 1 | Star Wars: A New Hope | | 274 | Nightmare on Elm Street | 1 | Star Wars: A New Hope | | 608 | Nightmare on Elm Street | 1 | Star Wars: A New Hope | | 561 | Nightmare on Elm Street | 1 | Star Wars: A New Hope | | 207 | Nightmare on Elm Street | 1 | Star Wars: A New Hope | | 91 | Nightmare on Elm Street | 1 | Star Wars: A New Hope | | 225 | Nightmare on Elm Street | 1 | Star Wars: A New Hope | | 387 | Nightmare on Elm Street | 1 | Star Wars: A New Hope | | 546 | Nightmare on Elm Street | 1 | Star Wars: A New Hope | | 172 | Nightmare on Elm Street | 1 | Star Wars: A New Hope | +------+-------------------------+------+-----------------------+ Got 10 rows (time spent 41.812ms/42.946334ms) • [P0]CallbackSink │ details: a.id, b.name, c.id, d.name │ time(ms): 0.00 │ rows: 10 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Project │ details: a.id, b.name, c.id, d.name │ time(ms): 0.02 │ rows: 10 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Limit │ details: Offset:0, Limit:10, Partial:false, Appro │ x:false │ time(ms): 0.11 │ rows: 10 │ memory(KiB): 9.86 │ blocked(ms): 0.00 │ └── • [P0]CartesianProductProbe │ time(ms): 9.75 │ rows: 1024 │ memory(KiB): 4293.22 │ blocked(ms): 22.11 │ ├── • [P0]NEHashJoinProbe │ │ details: Project: c, d, element_id(d)=_right_nod │ │ e_id(e1) │ │ time(ms): 0.06 │ │ rows: 1 │ │ memory(KiB): 60.80 │ │ blocked(ms): 0.00 │ │ │ ├── • [P0]StorageRemoteAccess │ │ │ details: scanStrategy: SortedSeek, varName: d, Ta │ │ │ bleScan: {Movie: {name}}, filter: None, │ │ │ graph: movie, schema: /default_schema, d │ │ │ {}, GraphId: 1024 │ │ │ time(ms): 0.17 │ │ │ rows: 1 │ │ │ memory(KiB): 48.63 │ │ │ blocked(ms): 3.62 │ │ │ │ │ └── • [SP0]CallbackSink │ │ │ details: scanStrategy: SortedSeek, varName: d, Ta │ │ │ bleScan: {Movie: {name}}, filter: None, │ │ │ graph: movie, schema: /default_schema, i │ │ │ nput: |_id|: |289315302070550529|,|28931 │ │ │ 5070142316545|, d{}, GraphId: 1024 │ │ │ time(ms): 0.01 │ │ │ rows: 9 │ │ │ memory(KiB): 0.00 │ │ │ blocked(ms): 0.00 │ │ │ │ │ └── • [SP0]NodesScan │ │ details: scanStrategy: SortedSeek, varName: d, Ta │ │ bleScan: {Movie: {name}}, filter: None, │ │ graph: movie, schema: /default_schema, i │ │ nput: |_id|: |289315302070550529|,|28931 │ │ 5070142316545|, d{}, GraphId: 1024 │ │ time(ms): 0.30 │ │ rows: 9 │ │ memory(KiB): 68.70 │ │ blocked(ms): 0.00 │ │ │ └── • [P1]NEHashJoinBuild │ │ details: Project: c, d, element_id(d)=_right_nod │ │ e_id(e1) │ │ time(ms): 0.01 │ │ rows: 9 │ │ memory(KiB): 0.54 │ │ blocked(ms): 0.00 │ │ │ └── • [P1]StorageRemoteAccess │ │ details: _left_node_id(e1)=element_id(c) │ │ time(ms): 0.44 │ │ rows: 9 │ │ memory(KiB): 0.16 │ │ blocked(ms): 14.35 │ │ │ └── • [SP0]CallbackSink │ │ details: _left_node_id(e1)=element_id(c) │ │ time(ms): 0.00 │ │ rows: 9 │ │ memory(KiB): 0.00 │ │ blocked(ms): 0.00 │ │ │ └── • [SP0]NEHashJoinProbe │ │ details: _left_node_id(e1)=element_id(c) │ │ time(ms): 0.03 │ │ rows: 9 │ │ memory(KiB): 0.00 │ │ blocked(ms): 0.34 │ │ │ ├── • [SP0]EdgesScan │ │ details: direction: outgoing: true, incoming: fal │ │ se, undirected0: false, undirected1: fal │ │ se, scanStrategy: SortedSeek, varName: e │ │ 1, TableScan: {Watch: {rate}}, filter: e │ │ 1.rate > 3, graph: movie, schema: /defau │ │ lt_schema, e1{}, GraphId: 1024 │ │ time(ms): 0.11 │ │ rows: 9 │ │ memory(KiB): 0.00 │ │ blocked(ms): 0.00 │ │ │ └── • [SP1]NEHashJoinBuild │ │ details: _left_node_id(e1)=element_id(c) │ │ time(ms): 0.03 │ │ rows: 1 │ │ memory(KiB): 0.13 │ │ blocked(ms): 0.00 │ │ │ └── • [SP1]NodesScan │ details: scanStrategy: SortedSeek, varName: c, In │ dexScan: {User: {id}}, filter: None, ind │ ex: pkIndex_User, constraint: [/id]:[/1] │ , graph: movie, schema: /default_schema, │ c{}, GraphId: 1024 │ time(ms): 0.23 │ rows: 1 │ memory(KiB): 48.66 │ blocked(ms): 0.00 │ └── • [P2]Materialize │ time(ms): 0.38 │ rows: 0 │ memory(KiB): 8.12 │ blocked(ms): 0.00 │ └── • [P2]NEHashJoinProbe │ details: Project: a, b, element_id(b)=_right_nod │ e_id(e0) │ time(ms): 0.98 │ rows: 2717 │ memory(KiB): 624.34 │ blocked(ms): 14.65 │ ├── • [P2]StorageRemoteAccess │ │ details: scanStrategy: SortedSeek, varName: b, Ta │ │ bleScan: {Movie: {name}}, filter: None, │ │ graph: movie, schema: /default_schema, b │ │ {}, GraphId: 1024 │ │ time(ms): 0.40 │ │ rows: 88 │ │ memory(KiB): 89.13 │ │ blocked(ms): 5.23 │ │ │ └── • [SP0]CallbackSink │ │ details: scanStrategy: SortedSeek, varName: b, Ta │ │ bleScan: {Movie: {name}}, filter: None, │ │ graph: movie, schema: /default_schema, i │ │ nput: |_id|: |289165210143424513|,|28917 │ │ 9134427398145|,|289169140038500353|..., │ │ b{}, GraphId: 1024 │ │ time(ms): 0.02 │ │ rows: 88 │ │ memory(KiB): 0.00 │ │ blocked(ms): 0.00 │ │ │ └── • [SP0]NodesScan │ details: scanStrategy: SortedSeek, varName: b, Ta │ bleScan: {Movie: {name}}, filter: None, │ graph: movie, schema: /default_schema, i │ nput: |_id|: |289165210143424513|,|28917 │ 9134427398145|,|289169140038500353|..., │ b{}, GraphId: 1024 │ time(ms): 0.96 │ rows: 88 │ memory(KiB): 68.70 │ blocked(ms): 0.00 │ └── • [P3]NEHashJoinBuild │ details: Project: a, b, element_id(b)=_right_nod │ e_id(e0) │ time(ms): 0.40 │ rows: 88 │ memory(KiB): 164.68 │ blocked(ms): 0.00 │ └── • [P3]StorageRemoteAccess │ details: _left_node_id(e0)=element_id(a) │ time(ms): 2.13 │ rows: 2717 │ memory(KiB): 0.38 │ blocked(ms): 11.12 │ └── • [SP0]CallbackSink │ details: _left_node_id(e0)=element_id(a) │ time(ms): 0.04 │ rows: 2717 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [SP0]NEHashJoinProbe │ details: _left_node_id(e0)=element_id(a) │ time(ms): 1.04 │ rows: 2717 │ memory(KiB): 69.19 │ blocked(ms): 5.34 │ ├── • [SP0]EdgesScan │ details: direction: outgoing: true, incoming: fal │ se, undirected0: false, undirected1: fal │ se, scanStrategy: SortedSeek, varName: e │ 0, TableScan: {Watch: {rate}}, filter: e │ 0.rate > 3, graph: movie, schema: /defau │ lt_schema, e0{}, GraphId: 1024 │ time(ms): 6.25 │ rows: 2717 │ memory(KiB): 49.79 │ blocked(ms): 0.00 │ └── • [SP1]NEHashJoinBuild │ details: _left_node_id(e0)=element_id(a) │ time(ms): 31.94 │ rows: 569 │ memory(KiB): 3.84 │ blocked(ms): 0.00 │ └── • [SP1]NodesScan details: scanStrategy: SortedSeek, varName: a, Ta bleScan: {User: {id}}, filter: None, gra ph: movie, schema: /default_schema, a{}, GraphId: 1024 time(ms): 2.04 rows: 569 memory(KiB): 48.62 blocked(ms): 0.00 Execution Plan: [Px] means pipeline-x and [S] means storage side. Elapsed Time: parse time : 454 us build time : 1108 us optimize time : 2159 us serialize time: 115 us total time : 41812 us ```` 第一条路径使用文本顺序；第二条路径仍由 CBO 优化。 ### 路径变量和路径模式使用 NO_REORDER 提示 ```` ''' SESSION SET enable_reorder = true ''' ''' PROFILE USE movie MATCH /*+ NO_REORDER */ p = TRAIL (a:Person)-[e0:Watch]->(b:Movie)<-[e1:Act]-(c:Person) RETURN p LIMIT 5 ''' ```` 展开查看返回 ```` +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | p | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | (289057316269981697@User:Person{id:495})-[0@Watch:Watch{rate:5.0}]->(289158123447386113@Movie:Movie{id:454,name:William Shakespeare's Romeo & Juliet})<-[0@Act:Act{}]-(288432235319656449@Actor:Person{birthDate:1969-04-06,id:6215,name:Paul Rudd}) | | (289065369333661697@User:Person{id:212})-[0@Watch:Watch{rate:3.0}]->(289158123447386113@Movie:Movie{id:454,name:William Shakespeare's Romeo & Juliet})<-[0@Act:Act{}]-(288432235319656449@Actor:Person{birthDate:1969-04-06,id:6215,name:Paul Rudd}) | | (289068526134624258@User:Person{id:33})-[0@Watch:Watch{rate:4.0}]->(289158123447386113@Movie:Movie{id:454,name:William Shakespeare's Romeo & Juliet})<-[0@Act:Act{}]-(288432235319656449@Actor:Person{birthDate:1969-04-06,id:6215,name:Paul Rudd}) | | (289068899796779009@User:Person{id:414})-[0@Watch:Watch{rate:4.0}]->(289158123447386113@Movie:Movie{id:454,name:William Shakespeare's Romeo & Juliet})<-[0@Act:Act{}]-(288432235319656449@Actor:Person{birthDate:1969-04-06,id:6215,name:Paul Rudd}) | | (289070922726375426@User:Person{id:287})-[0@Watch:Watch{rate:3.0}]->(289158123447386113@Movie:Movie{id:454,name:William Shakespeare's Romeo & Juliet})<-[0@Act:Act{}]-(288432235319656449@Actor:Person{birthDate:1969-04-06,id:6215,name:Paul Rudd}) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Got 5 rows (time spent 65.592ms/66.867639ms) • [P0]CallbackSink │ details: PATH[a, e0, b, e1, c] AS p │ time(ms): 0.00 │ rows: 5 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Project │ details: PATH[a, e0, b, e1, c] AS p │ time(ms): 0.42 │ rows: 5 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Limit │ details: Offset:0, Limit:5, Partial:false, Approx │ :false │ time(ms): 0.05 │ rows: 5 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]Project │ details: a, e0, b, e1, c │ time(ms): 0.00 │ rows: 1024 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [P0]NEHashJoinProbe │ details: element_id(c)=_right_node_id(e1) │ time(ms): 1.12 │ rows: 1024 │ memory(KiB): 0.00 │ blocked(ms): 56.89 │ ├── • [P0]StorageRemoteAccess │ │ details: scanStrategy: SortedSeek, varName: c, Ta │ │ bleScan: {Actor: {birthDate, name, id}}, │ │ filter: None, graph: movie, schema: /de │ │ fault_schema, c{}, GraphId: 1024 │ │ time(ms): 0.46 │ │ rows: 132 │ │ memory(KiB): 89.13 │ │ blocked(ms): 1.17 │ │ │ └── • [SP0]CallbackSink │ │ details: scanStrategy: SortedSeek, varName: c, Ta │ │ bleScan: {Actor: {birthDate, name, id}}, │ │ filter: None, graph: movie, schema: /de │ │ fault_schema, input: |_id|: |28850681313 │ │ 1784193|,|288498339161309185|,|288505713 │ │ 620156417|..., c{}, GraphId: 1024 │ │ time(ms): 0.01 │ │ rows: 804 │ │ memory(KiB): 0.00 │ │ blocked(ms): 0.00 │ │ │ └── • [SP0]NodesScan │ details: scanStrategy: SortedSeek, varName: c, Ta │ bleScan: {Actor: {birthDate, name, id}}, │ filter: None, graph: movie, schema: /de │ fault_schema, input: |_id|: |28850681313 │ 1784193|,|288498339161309185|,|288505713 │ 620156417|..., c{}, GraphId: 1024 │ time(ms): 1.29 │ rows: 804 │ memory(KiB): 93.52 │ blocked(ms): 0.00 │ └── • [P1]NEHashJoinBuild │ details: element_id(c)=_right_node_id(e1) │ time(ms): 7.00 │ rows: 1356 │ memory(KiB): 2586.79 │ blocked(ms): 0.00 │ └── • [P1]NEHashJoinProbe │ details: element_id(b)=_right_node_id(e0) │ time(ms): 29.43 │ rows: 66624 │ memory(KiB): 18223.28 │ blocked(ms): 17.85 │ ├── • [P1]StorageRemoteAccess │ │ details: _left_node_id(e1)=element_id(b) │ │ time(ms): 0.61 │ │ rows: 1496 │ │ memory(KiB): 89.13 │ │ blocked(ms): 1.66 │ │ │ └── • [SP0]CallbackSink │ │ details: _left_node_id(e1)=element_id(b) │ │ time(ms): 0.02 │ │ rows: 1496 │ │ memory(KiB): 0.00 │ │ blocked(ms): 0.00 │ │ │ └── • [SP0]NEHashJoinProbe │ │ details: _left_node_id(e1)=element_id(b) │ │ time(ms): 0.87 │ │ rows: 1496 │ │ memory(KiB): 86.92 │ │ blocked(ms): 1.89 │ │ │ ├── • [SP0]EdgesScan │ │ details: direction: outgoing: false, incoming: tr │ │ ue, undirected0: false, undirected1: fal │ │ se, scanStrategy: SortedSeek, varName: e │ │ 1, TableScan: {Act: {}}, filter: None, g │ │ raph: movie, schema: /default_schema, e1 │ │ {}, GraphId: 1024 │ │ time(ms): 2.37 │ │ rows: 1496 │ │ memory(KiB): 64.33 │ │ blocked(ms): 0.00 │ │ │ └── • [SP1]NEHashJoinBuild │ │ details: _left_node_id(e1)=element_id(b) │ │ time(ms): 0.50 │ │ rows: 92 │ │ memory(KiB): 0.84 │ │ blocked(ms): 0.00 │ │ │ └── • [SP1]NodesScan │ details: scanStrategy: SortedSeek, varName: b, Ta │ bleScan: {Movie: {name, id}}, filter: No │ ne, graph: movie, schema: /default_schem │ a, input: |_id|: |289165210143424513|,|2 │ 89179134427398145|,|289163943128072193|. │ .., b{}, GraphId: 1024 │ time(ms): 1.14 │ rows: 92 │ memory(KiB): 85.02 │ blocked(ms): 0.00 │ └── • [P2]NEHashJoinBuild │ details: element_id(b)=_right_node_id(e0) │ time(ms): 0.43 │ rows: 92 │ memory(KiB): 223.50 │ blocked(ms): 0.00 │ └── • [P2]StorageRemoteAccess │ details: _left_node_id(e0)=element_id(a) │ time(ms): 0.66 │ rows: 3910 │ memory(KiB): 0.38 │ blocked(ms): 16.42 │ └── • [SP0]CallbackSink │ details: _left_node_id(e0)=element_id(a) │ time(ms): 0.04 │ rows: 3910 │ memory(KiB): 0.00 │ blocked(ms): 0.00 │ └── • [SP0]NEHashJoinProbe │ details: _left_node_id(e0)=element_id(a) │ time(ms): 3.14 │ rows: 3910 │ memory(KiB): 73.03 │ blocked(ms): 6.48 │ ├── • [SP0]EdgesScan │ details: direction: outgoing: true, incoming: fal │ se, undirected0: false, undirected1: fal │ se, scanStrategy: SortedSeek, varName: e │ 0, TableScan: {Watch: {rate}}, filter: N │ one, graph: movie, schema: /default_sche │ ma, e0{}, GraphId: 1024 │ time(ms): 3.03 │ rows: 3910 │ memory(KiB): 72.64 │ blocked(ms): 0.00 │ └── • [SP1]NEHashJoinBuild │ details: _left_node_id(e0)=element_id(a) │ time(ms): 38.92 │ rows: 569 │ memory(KiB): 3.84 │ blocked(ms): 0.00 │ └── • [SP1]NodesScan details: scanStrategy: SortedSeek, varName: a, Ta bleScan: {User: {id}}, filter: None, gra ph: movie, schema: /default_schema, a{}, GraphId: 1024 time(ms): 1.85 rows: 569 memory(KiB): 48.62 blocked(ms): 0.00 Execution Plan: [Px] means pipeline-x and [S] means storage side. Elapsed Time: parse time : 356 us build time : 862 us optimize time : 968 us serialize time: 518 us total time : 65592 us ````