配置提示（Hints）¶
提示（Hints）是以特殊注释形式添加到查询中的指令，用于建议查询优化器如何执行查询。提示使用/*+ ... */语法。
支持以下类型的提示：
| 提示 | 作用范围 | 说明 |
|---|---|---|
INDEX |
点/边模式 | 建议优化器使用指定的索引。 |
IGNORE_INDEX |
点/边模式 | 建议优化器不使用指定的索引。 |
NO_REORDER |
路径模式 | 阻止带注解路径的MATCH模式展开顺序重排，保持查询文本中的从左到右顺序。 |
索引提示¶
索引提示（Index hints）以注释的形式添加到查询中，用于建议查询优化器使用或不使用某个索引。
索引提示适用于 MATCH 语句，且必须加在 MATCH 语句的点或边模式之后。更多信息，请参见 MATCH。
前提条件¶
已创建索引。更多信息，请参见索引概述。
语法¶
- index_hint
index_hint ::=
'/*+' 'INDEX' '(' index_name (',' index_name)* ')' '*/'
| '/*+' 'IGNORE_INDEX' '(' index_name (',' index_name)* ')' '*/'
参数¶
INDEX¶
该关键字用于指定查询优化器在执行查询时应考虑使用的索引。请注意，只有此处指定的索引才可能会被查询优化器使用。
IGNORE_INDEX¶
该关键字用于指定查询优化器在执行查询时不可以使用的索引。请注意，此处指定的索引将不会被查询优化器使用。
index_name¶
提示查询优化器使用或不使用的索引名称。可使用 SHOW (ALL) 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。
作用范围¶
- 该提示仅作用于单条路径模式，不影响整个
MATCH子句。 - 在多路径
MATCH中，可以只对部分路径使用NO_REORDER，其余路径仍可由 CBO 自由优化。 - 在
EXISTS子查询和标量VALUE子查询中同样支持。 - 与路径类型（如
TRAIL）和路径变量兼容。
示例¶
以下示例基于示例数据集，并假设 CREATE NORMAL INDEX 示例一节中介绍的索引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