Configure hints¶
Hints are special comments that you can add to a query to advise the query optimizer on how to execute the query. Hints use the /*+ ... */ syntax.
The following types of hints are supported:
| Hint | Scope | Description |
|---|---|---|
INDEX |
Node/Edge pattern | Advises the optimizer to use the specified index(es). |
IGNORE_INDEX |
Node/Edge pattern | Advises the optimizer to not use the specified index(es). |
NO_REORDER |
Path pattern | Prevents the optimizer from changing the expansion order of the annotated MATCH path, keeping textual (left-to-right) order. |
Index hints¶
An index hint is a comment that you can add to a query to advise the query optimizer on which indexes to use or not use.
Index hints apply to MATCH statements and must be appended to node or edge patterns in the MATCH statements. For more information, see MATCH.
Prerequisites¶
An index is created. For more information, see Indexes overview.
Syntax¶
- index_hint
index_hint ::=
'/*+' 'INDEX' '(' index_name (',' index_name)* ')' '*/'
| '/*+' 'IGNORE_INDEX' '(' index_name (',' index_name)* ')' '*/'
Parameters¶
INDEX¶
The keyword that indicates the index that the query optimizer should consider to use when executing the query. Note that only the index that you hint may be used by the query optimizer.
IGNORE_INDEX¶
The keyword that indicates the index that the query optimizer should NOT use when executing the query. Note that the index you hint will not be used by the query optimizer.
index_name¶
The name of the index that you want to hint to the query optimizer. To query the indexes that are available, use SHOW (ALL) INDEXES.
If the specified index is invalid, that is, the index does not exist or is not applicable to the specified node or edge type in the query, the query optimizer does not consider the invalid index.
When multiple indexes are specified in an INDEX hint, the query optimizer considers only valid indexes and uses the one or ones that are most suitable for the query.
When multiple indexes are specified in an IGNORE_INDEX hint, the query optimizer does not consider or use any of these indexes.
NO_REORDER hint ¶
The NO_REORDER hint is a path-level hint that prevents the optimizer from changing the expansion order of an annotated graph pattern. When this hint is applied, the optimizer plans that path pattern in the textual (left-to-right) order as written in the query instead of choosing a potentially different order based on cost estimation.
Starting in 5.3.0, query-level control over whether the optimizer can change the expansion order of MATCH patterns is provided by enable_reorder, whose default value is false. NO_REORDER remains useful when enable_reorder is enabled for the whole query, session, or deployment and you want to keep one specific path in textual order.
Syntax¶
- no_reorder_hint
no_reorder_hint ::=
'/*+' 'NO_REORDER' '*/'
The NO_REORDER hint is placed immediately before a path pattern in a MATCH statement:
MATCH /*+ NO_REORDER */ (a)-[e0]->(b)-[e1]->(c)
...
Use cases¶
- Plan stability: When
enable_reorderis enabled, the optimizer may pick different starting nodes or expansion orders depending on data statistics.NO_REORDERlocks the annotated path to textual order. - Performance tuning: When you know the optimal expansion direction for your data, you can override the reorder decision for one path without disabling reorder everywhere.
- Debugging: Compare execution plans with and without
NO_REORDERto determine whether reordering is causing performance issues. You can useEXPLAIN CBOto inspect the effect. For more information, see EXPLAIN.
Scope¶
- The hint applies to a single path pattern only, not the entire
MATCHclause. - In a multi-path
MATCH, you can applyNO_REORDERto some paths while leaving others for CBO to optimize freely. - The hint is also supported inside
EXISTSsubqueries and scalarVALUEsubqueries. - The hint is compatible with path modes (e.g.,
TRAIL) and path variables.
Examples¶
The following examples are based on example dataset and assume that indexes idx_actor_age and idx_watch_rating introduced in the Examples section of CREATE NORMAL INDEX are created.
Hint to use an index on a node type¶
'''
PROFILE
USE movie
MATCH (v@Actor{birthDate: date('1951-03-28')}/*+ INDEX(idx_actor_age) */)
RETURN v.id AS id, v.name AS name
'''
Return:
+------+-----------------+
| 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
The execution plan shows that the index idx_actor_age is used to execute the query.
Hint to not use an index on a node type¶
'''
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
'''
Return:
+------+-----------------+
| 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
The execution plan shows that the index idx_actor_age is not used to execute the query.
Hint to use an index on an edge type¶
'''
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
'''
Return:
+------+------------------------------+
| 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
The execution plan shows that the index idx_watch_rating is used to execute the query.
Hint to not use an index on an edge type¶
'''
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
'''
Return:
+------+-------------------+
| 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
The execution plan shows that the index idx_watch_rating is not used to execute the query.
Hint to use multiple indexes¶
'''
// Create a new index on the `Actor` node type
USE movie CREATE NORMAL INDEX IF NOT EXISTS idx_actor_name ON NODE Actor ( name ASC )
'''
'''
// Hint to use multiple indexes
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
'''
Return:
+------+-----------------+
| 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
The preceding example hints to use the indexes idx_actor_age, idx_actor_name, and a non-existing index non_exist_index. The query optimizer considers only the valid indexes idx_actor_age and idx_actor_name, and uses the most suitable one (idx_actor_age) to execute the query.
Hint to not use multiple indexes¶
'''
// Create a new index on the `Actor` node type
USE movie CREATE NORMAL INDEX IF NOT EXISTS idx_actor_name ON NODE Actor ( name ASC )
'''
'''
// Hint to not use multiple indexes
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
'''
Return:
+------+-----------------+
| 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
Single path with 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
'''
Expand to view results
+------+---------+
| 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
Without the hint, CBO might choose to start from b (a selective node with a specific id) and expand backward to a. With NO_REORDER, the optimizer starts from a and expands to b in textual order.
Mixed paths with 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
'''
Expand to view results
+------+-------------------------+------+-----------------------+
| 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
The first path uses textual order; the second path is still optimized by CBO.
Path variable and path pattern with 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
'''
Expand to view results
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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