Operators in detail

Understanding the roles of different operators in an execution plan can be an important step in making queries more efficient. This page contains details and examples for each of the operators used by the Cypher^® planner. For an overview of how the Cypher planner uses operators, see Understanding execution plans → Reading execution plans.

The operators are grouped into categories based on the role they fulfill in executing a Cypher query:

Leaf operators (scans and seeks)
Nested loops and join operators
Traversal operators
Union operators
Aggregation operators
Filter, order, and projection operators
Sort and limit operators
Data modification operators
Schema and system operators

Certain operators are only used by particular runtime. If so, the example queries will be prefixed with a runtime selection.

Leaf operators (scans and seeks)

Leaf operators are the initial operations in a query execution plan that directly access data. They are called "leaf" because they are at the outermost level of the query execution tree, with no operations below them. Once these operators retrieve the necessary data, the results are passed to subsequent operators that perform more complex operations.

Seek operators target specific data items (indexed or otherwise) while scan operators iterate through the data (indexed or otherwise) to find matches.

All Nodes Scan

The AllNodesScan operator reads all nodes from the node store. The variable that will contain the nodes is seen in the arguments. Any query using this operator is likely to encounter performance problems on a non-trivial database.

Example 1. AllNodesScan

Query

PROFILE
MATCH (n)
RETURN n

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+---------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+---------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | n       |             35 |   35 |       0 |                |                        |           |                     |
| |               +---------+----------------+------+---------+----------------+                        |           |                     |
| +AllNodesScan   | n       |             35 |   35 |      36 |            120 |                    3/0 |     0.354 | Fused in Pipeline 0 |
+-----------------+---------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 36, total allocated memory: 184

Partitioned All Nodes Scan

The PartitionedAllNodesScan is a variant of the AllNodesScan operator used by the parallel runtime. It allows the store to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 2. PartitionedAllNodesScan

Query

CYPHER runtime=parallel
PROFILE
MATCH (n)
RETURN n

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+--------------------------+----+---------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                 | Id | Details | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+--------------------------+----+---------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults          |  0 | n       |             35 |   35 |      71 |                    2/0 |     6.470 | In Pipeline 1 |
| |                        +----+---------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedAllNodesScan |  1 | n       |             35 |   35 |      35 |                    3/0 |     1.491 | In Pipeline 0 |
+--------------------------+----+---------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 106

Node By Label Scan

The NodeByLabelScan operator fetches all nodes with a specific label from the node label index.

Example 3. NodeByLabelScan

Query

PROFILE
MATCH (person:Person)
RETURN person

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Details       | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------+---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  | person        |             14 |   14 |       0 |                |                        |           |                     |
| |                +---------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan | person:Person |             14 |   14 |      15 |            120 |                    2/1 |     0.522 | Fused in Pipeline 0 |
+------------------+---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 15, total allocated memory: 184

Partitioned Node By Label Scan

The PartitionedNodeByLabelScan is a variant of the NodeByLabelScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 4. PartitionedNodeByLabelScan

Query

CYPHER runtime=parallel
PROFILE
MATCH (person:Person)
RETURN person

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+-----------------------------+----+---------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                    | Id | Details       | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+-----------------------------+----+---------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults             |  0 | person        |             14 |   14 |      28 |                    2/0 |     0.623 | In Pipeline 1 |
| |                           +----+---------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedNodeByLabelScan |  1 | person:Person |             14 |   14 |      15 |                    1/0 |     0.094 | In Pipeline 0 |
+-----------------------------+----+---------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 43

Intersection Node By Labels Scan

The IntersectionNodeByLabelsScan operator fetches all nodes that have all of the provided labels from the node label index.

Query

PROFILE
MATCH (countryAndLocation:Country&Location)
RETURN countryAndLocation

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128


+-------------------------------+----+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                      | Id | Details                             | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-------------------------------+----+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults               |  0 | countryAndLocation                  |             10 |    0 |       0 |                |                        |           |                     |
| |                             +----+-------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +IntersectionNodeByLabelsScan |  1 | countryAndLocation:Country&Location |             10 |    0 |       0 |            120 |                    0/0 |     1.011 | Fused in Pipeline 0 |
+-------------------------------+----+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 13, total allocated memory: 184

Partitioned Intersection Node By Labels Scan

The PartitionedIntersectionNodeByLabelsScan is a variant of the IntersectionNodeByLabelsScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Query

CYPHER runtime=parallel
PROFILE
MATCH (countryAndLocation:Country&Location)
RETURN countryAndLocation

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128


------------------------------------------+----+-------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                                 | Id | Details                             | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+------------------------------------------+----+-------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                          |  0 | countryAndLocation                  |              3 |    0 |       0 |                    0/0 |     0.018 | In Pipeline 1 |
| |                                        +----+-------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedIntersectionNodeByLabelsScan |  1 | countryAndLocation:Country&Location |              3 |    0 |      13 |                    2/0 |     0.770 | In Pipeline 0 |
+------------------------------------------+----+-------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 13

Subtraction Node By Labels Scan

The SubtractionNodeByLabelsScan operator fetches all nodes that have all of the first set of provided labels and none of the second provided set of labels from the node label index. In the example below, SubtractionNodeByLabelsScan retrieves all nodes that have the Location label but do not have the Country label.

Query

PROFILE
MATCH (locationNotCountry:Location&!Country)
RETURN locationNotCountry

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------------------+----+--------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                     | Id | Details                              | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------------------+----+--------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults              |  0 | locationNotCountry                   |              7 |   10 |       0 |              0 |                        |           |                     |
| |                            +----+--------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +SubtractionNodeByLabelsScan |  1 | locationNotCountry:Location&!Country |              7 |   10 |      13 |            248 |                    2/0 |     3.081 | Fused in Pipeline 0 |
+------------------------------+----+--------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 13, total allocated memory: 312

Partitioned Subtraction Node By Labels Scan

The PartitionedSubtractionNodeByLabelsScan is a variant of the SubtractionNodeByLabelsScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Query

CYPHER runtime=parallel
PROFILE
MATCH (locationNotCountry:Location&!Country)
RETURN locationNotCountry

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+-----------------------------------------+----+--------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| Operator                                | Id | Details                              | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+-----------------------------------------+----+--------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +ProduceResults                         |  0 | locationNotCountry                   |              7 |   10 |       0 |            136 |                    0/0 |     0.614 | In Pipeline 1 |
| |                                       +----+--------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +PartitionedSubtractionNodeByLabelsScan |  1 | locationNotCountry:Location&!Country |              7 |   10 |      13 |            120 |                    2/0 |     5.173 | In Pipeline 0 |
+-----------------------------------------+----+--------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+

Total database accesses: 13, total allocated memory: 262144

Union Node By Labels Scan

The UnionNodeByLabelsScan operator fetches all nodes that have at least one of the provided labels from the node label index.

Query

PROFILE
MATCH (countryOrLocation:Country|Location)
RETURN countryOrLocation

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------------+------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+
| Operator               | Details                            | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by            | Pipeline            |
+------------------------+------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+
| +ProduceResults        | countryOrLocation                  |             17 |   11 |       0 |                |                        |           |                       |                     |
| |                      +------------------------------------+----------------+------+---------+----------------+                        |           |                       |                     |
| +UnionNodeByLabelsScan | countryOrLocation:Country|Location |             17 |   11 |      13 |            120 |                    3/1 |     0.660 | countryOrLocation ASC | Fused in Pipeline 0 |
+------------------------+------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+

Total database accesses: 13, total allocated memory: 184

Partitioned Union Node By Labels Scan

The PartitionedUnionNodeByLabelsScan is a variant of the UnionNodeByLabelsScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Query

CYPHER runtime=parallel
PROFILE
MATCH (countryOrLocation:Country|Location)
RETURN countryOrLocation

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+-----------------------------------+----+------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                          | Id | Details                            | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+-----------------------------------+----+------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                   |  0 | countryOrLocation                  |             17 |   11 |      22 |                    2/0 |     1.548 | In Pipeline 1 |
| |                                 +----+------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedUnionNodeByLabelsScan |  1 | countryOrLocation:Country|Location |             17 |   11 |      13 |                    2/0 |     1.976 | In Pipeline 0 |
+-----------------------------------+----+------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 35

Node Index Contains Scan

The NodeIndexContainsScan operator examines all values stored in an index, searching for entries containing a specific STRING; for example, in queries including CONTAINS. Although this is slower than an index seek (since all entries need to be examined), it is still faster than the indirection resulting from a label scan using NodeByLabelScan, and a property store filter.

Example 5. NodeIndexContainsScan

Query

PROFILE
MATCH (l:Location)
WHERE l.name CONTAINS 'al'
RETURN l

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------------+---------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator               | Details                                                       | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------------+---------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults        | l                                                             |              0 |    2 |       0 |                |                        |           |                     |
| |                      +---------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexContainsScan | TEXT INDEX l:Location(name) WHERE name CONTAINS $autostring_0 |              0 |    2 |       3 |            120 |                    2/0 |     1.305 | Fused in Pipeline 0 |
+------------------------+---------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 3, total allocated memory: 184

Node Index Ends With Scan

The NodeIndexEndsWithScan operator examines all values stored in an index, searching for entries ending in a specific STRING; for example, in queries containing ENDS WITH. Although this is slower than an index seek (since all entries need to be examined), it is still faster than the indirection resulting from a label scan using NodeByLabelScan, and a property store filter.

Example 6. NodeIndexEndsWithScan

Query

PROFILE
MATCH (l:Location)
WHERE l.name ENDS WITH 'al'
RETURN l

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------------+----------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator               | Details                                                        | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------------+----------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults        | l                                                              |              0 |    0 |       0 |                |                        |           |                     |
| |                      +----------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexEndsWithScan | TEXT INDEX l:Location(name) WHERE name ENDS WITH $autostring_0 |              0 |    0 |       1 |            120 |                    0/0 |     4.409 | Fused in Pipeline 0 |
+------------------------+----------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 1, total allocated memory: 184

Node Index Scan

The NodeIndexScan operator examines all values stored in an index, returning all nodes with a particular label and a specified property.

Example 7. NodeIndexScan

Query

PROFILE
MATCH (l:Location)
WHERE l.name IS NOT NULL
RETURN l

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+-----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                             | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+-----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | l                                                   |             10 |   10 |       0 |                |                        |           |                     |
| |               +-----------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexScan  | RANGE INDEX l:Location(name) WHERE name IS NOT NULL |             10 |   10 |      11 |            120 |                    2/1 |     0.557 | Fused in Pipeline 0 |
+-----------------+-----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 11, total allocated memory: 184

Partitioned Node Index Scan

The PartitionedNodeIndexScan is a variant of the NodeIndexScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 8. PartitionedNodeIndexScan

Query

CYPHER runtime=parallel
PROFILE
MATCH (l:Location)
WHERE l.name IS NOT NULL
RETURN l

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+---------------------------+----+-----------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                  | Id | Details                                             | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+---------------------------+----+-----------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults           |  0 | l                                                   |              1 |   10 |      20 |                    2/0 |     0.472 | In Pipeline 1 |
| |                         +----+-----------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedNodeIndexScan |  1 | RANGE INDEX l:Location(name) WHERE name IS NOT NULL |              1 |   10 |      11 |                    1/0 |     0.187 | In Pipeline 0 |
+---------------------------+----+-----------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 31

Node By ElementId Seek

The NodeByElementIdSeek operator reads one or more nodes by ID from the node store, specified via the function elementId().

Example 9. NodeByElementIdSeek

Query

PROFILE
MATCH (n)
WHERE elementId(n) = 0
RETURN n

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator               | Details                           | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults        | n                                 |              1 |    1 |       0 |                |                        |           |                     |
| |                      +-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+----------------------                        |           |                     |
| +NodeByElementIdSeek   | n WHERE elementId(n) = $autoint_0 |              1 |    1 |       1 |            120 |                    3/0 |     2.108 | Fused in Pipeline 0 |
+------------------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 1, total allocated memory: 184

Node By Id Seek

The NodeByIdSeek operator reads one or more nodes by id from the node store, specified via the function id().

Example 10. NodeByIdSeek

Query

PROFILE
MATCH (n)
WHERE id(n) = 0
RETURN n

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----+----------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Id | Details                    | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----+----------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults |  0 | n                          |              1 |    1 |       2 |              0 |                        |           |                     |
| |               +----+----------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByIdSeek   |  1 | n WHERE id(n) = $autoint_0 |              1 |    1 |       1 |            248 |                    2/0 |     1.109 | Fused in Pipeline 0 |
+-----------------+----+----------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 3, total allocated memory: 312

Node Index Seek

The NodeIndexSeek operator finds nodes using an index seek. The node variable and the index used are shown in the arguments of the operator. If the index is backs up a property uniqueness constraint, the operator is instead called NodeUniqueIndexSeek.

Example 11. NodeIndexSeek

Query

PROFILE
MATCH (location:Location {name: 'Malmo'})
RETURN location

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                                        | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | location                                                       |              1 |    1 |       0 |                |                        |           |                     |
| |               +----------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexSeek  | RANGE INDEX location:Location(name) WHERE name = $autostring_0 |              1 |    1 |       2 |            120 |                    2/1 |     0.401 | Fused in Pipeline 0 |
+-----------------+----------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 2, total allocated memory: 184

Partitioned Node Index Seek

The PartitionedNodeIndexSeek is a variant of the NodeIndexSeek operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 12. PartitionedNodeIndexSeek

Query

CYPHER runtime=parallel
PROFILE
MATCH (location:Location {name: 'Malmo'})
RETURN location

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+---------------------------+----+----------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                  | Id | Details                                                        | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+---------------------------+----+----------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults           |  0 | location                                                       |              0 |    1 |       2 |                    2/0 |     0.179 | In Pipeline 1 |
| |                         +----+----------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedNodeIndexSeek |  1 | RANGE INDEX location:Location(name) WHERE name = $autostring_0 |              0 |    1 |       2 |                    1/0 |     0.167 | In Pipeline 0 |
+---------------------------+----+----------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 4

Node Unique Index Seek

The NodeUniqueIndexSeek operator finds nodes using an index seek within an index backing up a property uniqueness constraint. The node variable and the index used are shown in the arguments of the operator. If the index does not back up a property uniqueness constraint, the operator is instead called NodeIndexSeek. If the index seek is used to solve a MERGE clause, it will also be marked with (Locking). This makes it clear that any nodes returned from the index will be locked in order to prevent concurrent conflicting updates.

Example 13. NodeUniqueIndexSeek

Query

PROFILE
MATCH (t:Team {name: 'Malmo'})
RETURN t

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+----------------------+------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator             | Details                                        | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+----------------------+------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults      | t                                              |              1 |    0 |       0 |                |                        |           |                     |
| |                    +------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeUniqueIndexSeek | UNIQUE t:Team(name) WHERE name = $autostring_0 |              1 |    0 |       1 |            120 |                    0/1 |     0.280 | Fused in Pipeline 0 |
+----------------------+------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 1, total allocated memory: 184

Multi Node Index Seek

The MultiNodeIndexSeek operator finds nodes using multiple index seeks. It supports using multiple distinct indexes for different nodes in the query. The node variables and the indexes used are shown in the arguments of the operator.

The operator yields a cartesian product of all index seeks. For example, if the operator does two seeks and the first seek finds the nodes a1, a2 and the second b1, b2, b3, the MultiNodeIndexSeek will yield the rows (a1, b1), (a1, b2), (a1, b3), (a2, b1), (a2, b2), (a2, b3).

Example 14. MultiNodeIndexSeek

Query

PROFILE
CYPHER runtime=pipelined
MATCH
  (location:Location {name: 'Malmo'}),
  (person:Person {name: 'Bob'})
RETURN location, person

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------+-----------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator            | Details                                                         | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+---------------------+-----------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults     | location, person                                                |              1 |    1 |       0 |                |                        |           |                     |
| |                   +-----------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +MultiNodeIndexSeek | RANGE INDEX location:Location(name) WHERE name = $autostring_0, |              1 |    0 |       0 |            120 |                    2/2 |     1.910 | Fused in Pipeline 0 |
|                     | RANGE INDEX person:Person(name) WHERE name = $autostring_1      |                |      |         |                |                        |           |                     |
+---------------------+-----------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 0, total allocated memory: 184

Asserting Multi Node Index Seek

The AssertingMultiNodeIndexSeek operator is used to ensure that no property uniqueness constraints are violated. The example looks for the presence of a team with the supplied name and id, and if one does not exist, it will be created. Owing to the existence of two property uniqueness constraints on :Team(name) and :Team(id), any node that would be found by the UniqueIndexSeek operator must be the very same node or the constraints would be violated.

Example 15. AssertingMultiNodeIndexSeek

Query

PROFILE
MERGE (t:Team {name: 'Engineering', id: 42})

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------------------+-----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                     | Details                                                                                 | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------------------+-----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults              |                                                                                         |              1 |    0 |       0 |                |                        |           |                     |
| |                            +-----------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult                 |                                                                                         |              1 |    0 |       0 |                |                        |           |                     |
| |                            +-----------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Merge                       | CREATE (t:Team {name: $autostring_0, id: $autoint_1})                                   |              1 |    1 |       0 |                |                        |           |                     |
| |                            +-----------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +AssertingMultiNodeIndexSeek | UNIQUE t:Team(name) WHERE name = $autostring_0, UNIQUE t:Team(id) WHERE id = $autoint_1 |              0 |    2 |       4 |            120 |                    0/2 |     1.584 | Fused in Pipeline 0 |
+------------------------------+-----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 4, total allocated memory: 184

Node Index Seek By Range

The NodeIndexSeekByRange operator finds nodes using an index seek where the value of the property matches a given prefix STRING. NodeIndexSeekByRange can be used for STARTS WITH and comparison operators such as <, >, <= and >=. If the index is a unique index, the operator is instead called NodeUniqueIndexSeekByRange.

Example 16. NodeIndexSeekByRange

Query

PROFILE
MATCH (l:Location)
WHERE l.name STARTS WITH 'Lon'
RETURN l

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------+-------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator              | Details                                                           | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------------+-------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults       | l                                                                 |              2 |    1 |       0 |                |                        |           |                     |
| |                     +-------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexSeekByRange | RANGE INDEX l:Location(name) WHERE name STARTS WITH $autostring_0 |              2 |    1 |       2 |            120 |                    3/0 |     0.825 | Fused in Pipeline 0 |
+-----------------------+-------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 2, total allocated memory: 184

Partitioned Node Index Seek By Range

The PartitionedNodeIndexSeekByRange is a variant of the NodeIndexSeekByRange operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 17. PartitionedNodeIndexSeekByRange

Query

CYPHER runtime=parallel
PROFILE
MATCH (l:Location)
WHERE l.name STARTS WITH 'Lon'
RETURN l

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+----------------------------------+----+-------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                         | Id | Details                                                           | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+----------------------------------+----+-------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                  |  0 | l                                                                 |              0 |    1 |       2 |                    2/0 |     0.191 | In Pipeline 1 |
| |                                +----+-------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedNodeIndexSeekByRange |  1 | RANGE INDEX l:Location(name) WHERE name STARTS WITH $autostring_0 |              0 |    1 |       2 |                    1/0 |     0.087 | In Pipeline 0 |
+----------------------------------+----+-------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 4

Node Unique Index Seek By Range

The NodeUniqueIndexSeekByRange operator finds nodes using an index seek within a unique index, where the value of the property matches a given prefix STRING. NodeUniqueIndexSeekByRange is used by STARTS WITH and comparison operators such as <, >, <=, and >=. If the index is not unique, the operator is instead called NodeIndexSeekByRange.

Example 18. NodeUniqueIndexSeekByRange

Query

PROFILE
MATCH (t:Team)
WHERE t.name STARTS WITH 'Ma'
RETURN t

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                    | Details                                                  | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults             | t                                                        |              2 |    0 |       0 |                |                        |           |                     |
| |                           +----------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeUniqueIndexSeekByRange | UNIQUE t:Team(name) WHERE name STARTS WITH $autostring_0 |              2 |    0 |       1 |            120 |                    1/0 |     0.623 | Fused in Pipeline 0 |
+-----------------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 1, total allocated memory: 184

Directed All Relationships Scan

The DirectedAllRelationshipsScan operator fetches all relationships and their start and end nodes in the database.

Query

PROFILE
MATCH ()-[r]->() RETURN r

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-------------------------------+------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                      | Details                | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-------------------------------+------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults               | r                      |             28 |   28 |       0 |                |                        |           |                     |
| |                             +------------------------+----------------+------+---------+----------------+                        |           |                     |
| +DirectedAllRelationshipsScan | (anon_0)-[r]->(anon_1) |             28 |   28 |      28 |            120 |                    3/0 |     0.502 | Fused in Pipeline 0 |
+-------------------------------+------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 28, total allocated memory: 184

Partitioned Directed All Relationships Scan

The PartitionedDirectedAllRelationshipsScan is a variant of the DirectedAllRelationshipsScan operator used by the parallel runtime. It allows the store to be partitioned into different segments where each segment can be scanned independently in parallel.

Query

CYPHER runtime=parallel
PROFILE
MATCH ()-[r]->() RETURN r

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

| Operator                                 | Id | Details                | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+------------------------------------------+----+------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                          |  0 | r                      |             28 |   28 |      83 |                    2/0 |     3.872 | In Pipeline 1 |
| |                                        +----+------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedDirectedAllRelationshipsScan |  1 | (anon_0)-[r]->(anon_1) |             28 |   28 |      28 |                    3/0 |     1.954 | In Pipeline 0 |
+------------------------------------------+----+------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 111

Undirected All Relationships Scan

The UndirectedAllRelationshipsScan operator fetches all relationships and their start and end nodes in the database.

Query

PROFILE
MATCH ()-[r]-() RETURN r

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------------------+-----------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                        | Details               | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+---------------------------------+-----------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                 | r                     |             56 |   56 |       0 |                |                        |           |                     |
| |                               +-----------------------+----------------+------+---------+----------------+                        |           |                     |
| +UndirectedAllRelationshipsScan | (anon_0)-[r]-(anon_1) |             56 |   56 |      28 |            120 |                    3/0 |     1.110 | Fused in Pipeline 0 |
+---------------------------------+-----------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 28, total allocated memory: 184

Partitioned Undirected All Relationships Scan

The PartitionedUndirectedAllRelationshipsScan is a variant of the UndirectedAllRelationshipsScan operator used by the parallel runtime. It allows the store to be partitioned into different segments where each segment can be scanned independently in parallel.

Query

CYPHER runtime=parallel
PROFILE
MATCH ()-[r]-() RETURN r

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+--------------------------------------------+----+-----------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                                   | Id | Details               | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+--------------------------------------------+----+-----------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                            |  0 | r                     |             56 |   56 |     166 |                    2/0 |     4.905 | In Pipeline 1 |
| |                                          +----+-----------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedUndirectedAllRelationshipsScan |  1 | (anon_0)-[r]-(anon_1) |             56 |   56 |      28 |                    9/0 |     1.573 | In Pipeline 0 |
+--------------------------------------------+----+-----------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 194

Directed Relationship Type Scan

The DirectedRelationshipTypeScan operator fetches all relationships and their start and end nodes with a specific type from the relationship type index.

Example 19. DirectedRelationshipTypeScan

Query

PROFILE
MATCH ()-[r: FRIENDS_WITH]->()
RETURN r

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-------------------------------+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                      | Details                             | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-------------------------------+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults               | r                                   |             12 |   12 |       0 |                |                        |           |                     |
| |                             +-------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +DirectedRelationshipTypeScan | (anon_0)-[r:FRIENDS_WITH]->(anon_1) |             12 |   12 |      13 |            120 |                    2/1 |     0.557 | Fused in Pipeline 0 |
+-------------------------------+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 13, total allocated memory: 184

Partitioned Directed Relationship Type Scan

The PartitionedDirectedRelationshipTypeScan is a variant of the DirectedRelationshipTypeScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 20. PartitionedDirectedRelationshipTypeScan

Query

CYPHER runtime=parallel
PROFILE
MATCH ()-[r: FRIENDS_WITH]->()
RETURN r

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

++------------------------------------------+----+-------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
 | Operator                                 | Id | Details                             | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
 +------------------------------------------+----+-------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
 | +ProduceResults                          |  0 | r                                   |             12 |   12 |      12 |                    0/0 |     0.560 | In Pipeline 1 |
 | |                                        +----+-------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
 | +PartitionedDirectedRelationshipTypeScan |  1 | (anon_0)-[r:FRIENDS_WITH]->(anon_1) |             12 |   12 |      13 |                    2/0 |     0.167 | In Pipeline 0 |
 +------------------------------------------+----+-------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

 Total database accesses: 25

Undirected Relationship Type Scan

The UndirectedRelationshipTypeScan operator fetches all relationships and their start and end nodes with a specific type from the relationship type index.

Query

PROFILE
MATCH ()-[r: FRIENDS_WITH]-()
RETURN r

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------------------+------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                        | Details                            | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+---------------------------------+------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                 | r                                  |             24 |   24 |       0 |                |                        |           |                     |
| |                               +------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +UndirectedRelationshipTypeScan | (anon_0)-[r:FRIENDS_WITH]-(anon_1) |             24 |   24 |      13 |            120 |                    2/1 |     0.749 | Fused in Pipeline 0 |
+---------------------------------+------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 13, total allocated memory: 184

Partitioned Undirected Relationship Type Scan

The PartitionedUndirectedRelationshipTypeScan is a variant of the UndirectedRelationshipTypeScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Query

CYPHER runtime=parallel
PROFILE
MATCH ()-[r: FRIENDS_WITH]-()
RETURN r

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+--------------------------------------------+----+------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                                   | Id | Details                            | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+--------------------------------------------+----+------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                            |  0 | r                                  |             24 |   24 |      24 |                    1/0 |     1.466 | In Pipeline 1 |
| |                                          +----+------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedUndirectedRelationshipTypeScan |  1 | (anon_0)-[r:FRIENDS_WITH]-(anon_1) |             24 |   24 |      13 |                    2/0 |     0.171 | In Pipeline 0 |
+--------------------------------------------+----+------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 37

Directed Union Relationship Types Scan

The DirectedUnionRelationshipTypesScan operator fetches all relationships and their start and end nodes with at least one of the provided types from the relationship type index.

As the block storage format becomes the default, this operator will cease to be used in generating plans. Please refer to Operations Manual → Store formats for futher details on the various store formats available.

Example 21. DirectedUnionRelationshipTypesScan

Query

PROFILE
MATCH ()-[friendOrFoe: FRIENDS_WITH|FOE]->()
RETURN friendOrFoe

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-------------------------------------+---------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------+---------------------+
| Operator                            | Details                                           | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by      | Pipeline            |
+-------------------------------------+---------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------+---------------------+
| +ProduceResults                     | friendOrFoe                                       |             15 |   12 |       0 |                |                        |           |                 |                     |
| |                                   +---------------------------------------------------+----------------+------+---------+----------------+                        |           |                 |                     |
| +DirectedUnionRelationshipTypesScan | (anon_0)-[friendOrFoe:FRIENDS_WITH|FOE]->(anon_1) |             15 |   12 |      14 |            120 |                    3/1 |     2.027 | friendOrFoe ASC | Fused in Pipeline 0 |
+-------------------------------------+---------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------+---------------------+

Total database accesses: 14, total allocated memory: 184

Partitioned Directed Union Relationship Types Scan

The PartitionedDirectedUnionRelationshipTypeScan is a variant of the DirectedUnionRelationshipTypesScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 22. PartitionedDirectedUnionRelationshipTypesScan

Query

CYPHER runtime=parallel
PROFILE
MATCH ()-[friendOrFoe: FRIENDS_WITH|FOE]->()
RETURN friendOrFoe

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+------------------------------------------------+----+---------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                                       | Id | Details                                           | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+------------------------------------------------+----+---------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                                |  0 | friendOrFoe                                       |             15 |   12 |      12 |                    0/0 |     0.570 | In Pipeline 1 |
| |                                              +----+---------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedDirectedUnionRelationshipTypesScan |  1 | (anon_0)-[friendOrFoe:FRIENDS_WITH|FOE]->(anon_1) |             15 |   12 |      13 |                    2/0 |     0.170 | In Pipeline 0 |
+------------------------------------------------+----+---------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 25

Undirected Union Relationship Types Scan

The UndirectedUnionRelationshipTypesScan operator fetches all relationships and their start and end nodes with at least one of the provided types from the relationship type index.

Example 23. UndirectedUnionRelationshipTypeScan

Query

PROFILE
MATCH ()-[friendOrFoe: FRIENDS_WITH|FOE]-()
RETURN friendOrFoe

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------------------------+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------+---------------------+
| Operator                              | Details                                          | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by      | Pipeline            |
+---------------------------------------+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------+---------------------+
| +ProduceResults                       | friendOrFoe                                      |             30 |   24 |       0 |                |                        |           |                 |                     |
| |                                     +--------------------------------------------------+----------------+------+---------+----------------+                        |           |                 |                     |
| +UndirectedUnionRelationshipTypesScan | (anon_0)-[friendOrFoe:FRIENDS_WITH|FOE]-(anon_1) |             30 |   24 |      14 |            120 |                    3/1 |     0.887 | friendOrFoe ASC | Fused in Pipeline 0 |
+---------------------------------------+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------+---------------------+

Total database accesses: 14, total allocated memory: 184

Partitioned Undirected Union Relationship Types Scan

The PartitionedUndirectedUnionRelationshipTypeScan is a variant of the UndirectedUnionRelationshipTypesScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 24. PartitionedUndirectedUnionRelationshipTypesScan

Query

CYPHER runtime=parallel
PROFILE
MATCH ()-[friendOrFoe: FRIENDS_WITH|FOE]-()
RETURN friendOrFoe

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+--------------------------------------------------+----+--------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                                         | Id | Details                                          | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+--------------------------------------------------+----+--------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                                  |  0 | friendOrFoe                                      |             30 |   24 |      24 |                    0/0 |     0.896 | In Pipeline 1 |
| |                                                +----+--------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedUndirectedUnionRelationshipTypesScan |  1 | (anon_0)-[friendOrFoe:FRIENDS_WITH|FOE]-(anon_1) |             30 |   24 |      13 |                    2/0 |     0.818 | In Pipeline 0 |
+--------------------------------------------------+----+--------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 37

Directed Relationship Index Scan

The DirectedRelationshipIndexScan operator examines all values stored in an index, returning all relationships and their start and end nodes with a particular relationship type and a specified property.

Example 25. DirectedRelationshipIndexScan

Query

PROFILE
MATCH ()-[r: WORKS_IN]->()
WHERE r.title IS NOT NULL
RETURN r

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+--------------------------------+----------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                       | Details                                                                    | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+--------------------------------+----------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                | r                                                                          |             15 |   15 |       0 |                |                        |           |                     |
| |                              +----------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +DirectedRelationshipIndexScan | RANGE INDEX (anon_0)-[r:WORKS_IN(title)]->(anon_1) WHERE title IS NOT NULL |             15 |   15 |      16 |            120 |                    3/1 |     2.464 | Fused in Pipeline 0 |
+--------------------------------+----------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 16, total allocated memory: 184

Partitioned Directed Relationship Index Scan

The PartitionedDirectedRelationshipIndexScan is a variant of the DirectedRelationshipIndexScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 26. PartitionedDirectedRelationshipIndexScan

Query

CYPHER runtime=parallel
PROFILE
MATCH ()-[r: WORKS_IN]->()
WHERE r.title IS NOT NULL
RETURN r

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+-------------------------------------------+----+----------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                                  | Id | Details                                                                    | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+-------------------------------------------+----+----------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                           |  0 | r                                                                          |             15 |   15 |      70 |                    1/0 |     2.865 | In Pipeline 1 |
| |                                         +----+----------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedDirectedRelationshipIndexScan |  1 | RANGE INDEX (anon_0)-[r:WORKS_IN(title)]->(anon_1) WHERE title IS NOT NULL |             15 |   15 |      16 |                    2/0 |     0.527 | In Pipeline 0 |
+-------------------------------------------+----+----------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 86

Undirected Relationship Index Scan

The UndirectedRelationshipIndexScan operator examines all values stored in an index, returning all relationships and their start and end nodes with a particular relationship type and a specified property.

Example 27. UndirectedRelationshipIndexScan

Query

PROFILE
MATCH ()-[r: WORKS_IN]-()
WHERE r.title IS NOT NULL
RETURN r

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+----------------------------------+---------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                         | Details                                                                   | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+----------------------------------+---------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                  | r                                                                         |             30 |   30 |       0 |                |                        |           |                     |
| |                                +---------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +UndirectedRelationshipIndexScan | RANGE INDEX (anon_0)-[r:WORKS_IN(title)]-(anon_1) WHERE title IS NOT NULL |             30 |   30 |      16 |            120 |                    3/1 |     1.266 | Fused in Pipeline 0 |
+----------------------------------+---------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 16, total allocated memory: 184

Partitioned Undirected Relationship Index Scan

The PartitionedUndirectedRelationshipIndexScan is a variant of the UndirectedRelationshipIndexScan operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 28. PartitionedUndirectedRelationshipIndexScan

Query

CYPHER runtime=parallel
PROFILE
MATCH ()-[r: WORKS_IN]-()
WHERE r.title IS NOT NULL
RETURN r

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+---------------------------------------------+----+---------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                                    | Id | Details                                                                   | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+---------------------------------------------+----+---------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                             |  0 | r                                                                         |             30 |   30 |     140 |                    1/0 |     3.088 | In Pipeline 1 |
| |                                           +----+---------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedUndirectedRelationshipIndexScan |  1 | RANGE INDEX (anon_0)-[r:WORKS_IN(title)]-(anon_1) WHERE title IS NOT NULL |             30 |   30 |      16 |                    2/0 |     0.572 | In Pipeline 0 |
+---------------------------------------------+----+---------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 156

Directed Relationship Index Contains Scan

The DirectedRelationshipIndexContainsScan operator examines all values stored in an index, searching for entries containing a specific STRING; for example, in queries including CONTAINS. Although this is slower than an index seek (since all entries need to be examined), it is still faster than the indirection resulting from a type scan using DirectedRelationshipTypeScan, and a property store filter.

Example 29. DirectedRelationshipIndexContainsScan

Query

PROFILE
MATCH ()-[r: WORKS_IN]->()
WHERE r.title CONTAINS 'senior'
RETURN r

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+----------------------------------------+--------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                               | Details                                                                              | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+----------------------------------------+--------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                        | r                                                                                    |              0 |    4 |       0 |                |                        |           |                     |
| |                                      +--------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +DirectedRelationshipIndexContainsScan | TEXT INDEX (anon_0)-[r:WORKS_IN(title)]->(anon_1) WHERE title CONTAINS $autostring_0 |              0 |    4 |       5 |            120 |                    3/0 |     1.051 | Fused in Pipeline 0 |
+----------------------------------------+--------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 5, total allocated memory: 184

Undirected Relationship Index Contains Scan

The UndirectedRelationshipIndexContainsScan operator examines all values stored in an index, searching for entries containing a specific STRING; for example, in queries including CONTAINS. Although this is slower than an index seek (since all entries need to be examined), it is still faster than the indirection resulting from a type scan using DirectedRelationshipTypeScan, and a property store filter.

Example 30. UndirectedRelationshipIndexContainsScan

Query

PROFILE
MATCH ()-[r: WORKS_IN]-()
WHERE r.title CONTAINS 'senior'
RETURN r

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------------------------------+-------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                                 | Details                                                                             | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------------------------------+-------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                          | r                                                                                   |              0 |    8 |       0 |                |                        |           |                     |
| |                                        +-------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +UndirectedRelationshipIndexContainsScan | TEXT INDEX (anon_0)-[r:WORKS_IN(title)]-(anon_1) WHERE title CONTAINS $autostring_0 |              0 |    8 |       5 |            120 |                    3/0 |     2.684 | Fused in Pipeline 0 |
+------------------------------------------+-------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 5, total allocated memory: 184

Directed Relationship Index Ends With Scan

The DirectedRelationshipIndexEndsWithScan operator examines all values stored in an index, searching for entries ending in a specific STRING; for example, in queries containing ENDS WITH. Although this is slower than an index seek (since all entries need to be examined), it is still faster than the indirection resulting from a label scan using NodeByLabelScan, and a property store filter.

Example 31. DirectedRelationshipIndexEndsWithScan

Query

PROFILE
MATCH ()-[r: WORKS_IN]->()
WHERE r.title ENDS WITH 'developer'
RETURN r

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+----------------------------------------+---------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                               | Details                                                                               | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+----------------------------------------+---------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                        | r                                                                                     |              0 |    8 |       0 |                |                        |           |                     |
| |                                      +---------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +DirectedRelationshipIndexEndsWithScan | TEXT INDEX (anon_0)-[r:WORKS_IN(title)]->(anon_1) WHERE title ENDS WITH $autostring_0 |              0 |    8 |       9 |            120 |                    3/0 |     1.887 | Fused in Pipeline 0 |
+----------------------------------------+---------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 9, total allocated memory: 184

Undirected Relationship Index Ends With Scan

The UndirectedRelationshipIndexEndsWithScan operator examines all values stored in an index, searching for entries ending in a specific STRING; for example, in queries containing ENDS WITH. Although this is slower than an index seek (since all entries need to be examined), it is still faster than the indirection resulting from a label scan using NodeByLabelScan, and a property store filter.

Example 32. UndirectedRelationshipIndexEndsWithScan

Query

PROFILE
MATCH ()-[r: WORKS_IN]-()
WHERE r.title ENDS WITH 'developer'
RETURN r

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------------------------------+--------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                                 | Details                                                                              | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------------------------------+--------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                          | r                                                                                    |              0 |   16 |       0 |                |                        |           |                     |
| |                                        +--------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +UndirectedRelationshipIndexEndsWithScan | TEXT INDEX (anon_0)-[r:WORKS_IN(title)]-(anon_1) WHERE title ENDS WITH $autostring_0 |              0 |   16 |       9 |            120 |                    3/0 |     1.465 | Fused in Pipeline 0 |
+------------------------------------------+--------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 9, total allocated memory: 184

Directed Relationship Index Seek

The DirectedRelationshipIndexSeek operator finds relationships and their start and end nodes using an index seek. The relationship variable and the index used are shown in the arguments of the operator.

Example 33. DirectedRelationshipIndexSeek

Query

PROFILE
MATCH (candidate)-[r:WORKS_IN]->()
WHERE r.title = 'chief architect'
RETURN candidate

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+--------------------------------+-----------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                       | Details                                                                           | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+--------------------------------+-----------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                | candidate                                                                         |              2 |    1 |       0 |                |                        |           |                     |
| |                              +-----------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +DirectedRelationshipIndexSeek | RANGE INDEX (candidate)-[r:WORKS_IN(title)]->(anon_0) WHERE title = $autostring_0 |              2 |    1 |       2 |            120 |                    3/1 |     0.591 | Fused in Pipeline 0 |
+--------------------------------+-----------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 2, total allocated memory: 184

Partitioned Directed Relationship Index Seek

The PartitionedDirectedRelationshipIndexSeek is a variant of the the DirectedRelationshipIndexSeek operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 34. PartitionedDirectedRelationshipIndexSeek

Query

CYPHER runtime=parallel
PROFILE
MATCH (candidate)-[r:WORKS_IN]->()
WHERE r.title = 'chief architect'
RETURN candidate

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+-------------------------------------------+----+-----------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                                  | Id | Details                                                                           | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+-------------------------------------------+----+-----------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                           |  0 | candidate                                                                         |              2 |    1 |       2 |                    2/0 |     0.284 | In Pipeline 1 |
| |                                         +----+-----------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedDirectedRelationshipIndexSeek |  1 | RANGE INDEX (candidate)-[r:WORKS_IN(title)]->(anon_0) WHERE title = $autostring_0 |              2 |    1 |       2 |                    2/0 |     0.148 | In Pipeline 0 |
+-------------------------------------------+----+-----------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 4

Undirected Relationship Index Seek

The UndirectedRelationshipIndexSeek operator finds relationships and their start and end nodes using an index seek. The relationship variable and the index used are shown in the arguments of the operator.

Example 35. UndirectedRelationshipIndexSeek

Query

PROFILE
MATCH (candidate)-[r:WORKS_IN]-()
WHERE r.title = 'chief architect'
RETURN candidate

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+----------------------------------+----------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                         | Details                                                                          | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+----------------------------------+----------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                  | candidate                                                                        |              4 |    2 |       0 |                |                        |           |                     |
| |                                +----------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +UndirectedRelationshipIndexSeek | RANGE INDEX (candidate)-[r:WORKS_IN(title)]-(anon_0) WHERE title = $autostring_0 |              4 |    2 |       2 |            120 |                    3/1 |     0.791 | Fused in Pipeline 0 |
+----------------------------------+----------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 2, total allocated memory: 184

Partitioned Undirected Relationship Index Seek

The PartitionedUndirectedRelationshipIndexSeek operator finds relationships and their start and end nodes using an index seek. The relationship variable and the index used are shown in the arguments of the operator.

Example 36. PartitionedUndirectedRelationshipIndexSeek

Query

CYPHER runtime=parallel
PROFILE
MATCH (candidate)-[r:WORKS_IN]-()
WHERE r.title = 'chief architect'
RETURN candidate

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+---------------------------------------------+----+----------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| Operator                                    | Id | Details                                                                          | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+---------------------------------------------+----+----------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +ProduceResults                             |  0 | candidate                                                                        |              4 |    2 |       4 |                    2/0 |     0.333 | In Pipeline 1 |
| |                                           +----+----------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+
| +PartitionedUndirectedRelationshipIndexSeek |  1 | RANGE INDEX (candidate)-[r:WORKS_IN(title)]-(anon_0) WHERE title = $autostring_0 |              4 |    2 |       2 |                    2/0 |     0.151 | In Pipeline 0 |
+---------------------------------------------+----+----------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------+

Total database accesses: 6

Directed Relationship By Element Id Seek

The DirectedRelationshipByElementIdSeek operator reads one or more relationships by element id from the relationship store (specified via the function elementId()) and produces the relationship as well as the source and target node of the relationship.

Example 37. DirectedRelationshipByElementIdSeek

Query

PROFILE
MATCH (n1)-[r]->()
WHERE elementId(r) = 0
RETURN r, n1

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+--------------------------------------+----+----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| Operator                             | Id | Details                                            | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+--------------------------------------+----+----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +ProduceResults                      |  0 | r, n1                                              |              1 |    0 |       0 |              0 |                    0/0 |     0.314 |               |
| |                                    +----+----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+               |
| +DirectedRelationshipByElementIdSeek |  1 | (n1)-[r]->(anon_0) WHERE elementId(r) = $autoint_0 |              1 |    0 |       0 |            248 |                    0/0 |     2.337 | In Pipeline 0 |
+--------------------------------------+----+----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+

Total database accesses: 0, total allocated memory: 312

Directed Relationship By Id Seek

The DirectedRelationshipByIdSeek operator reads one or more relationships by id from the relationship store, and produces the relationship as well as the source and target node of the relationship.

Example 38. DirectedRelationshipByIdSeek

Query

PROFILE
MATCH (n1)-[r]->()
WHERE id(r) = 0
RETURN r, n1

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-------------------------------+----+---------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                      | Id | Details                                     | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-------------------------------+----+---------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults               |  0 | r, n1                                       |              1 |    1 |       7 |              0 |                        |           |                     |
| |                             +----+---------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +DirectedRelationshipByIdSeek |  1 | (n1)-[r]->(anon_0) WHERE id(r) = $autoint_0 |              1 |    1 |       1 |            248 |                    3/0 |     0.483 | Fused in Pipeline 0 |
+-------------------------------+----+---------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 8, total allocated memory: 312

Undirected Relationship By Element Id Seek

The UndirectedRelationshipByElementIdSeek operator reads one or more relationships by element id from the relationship store (specified via the function elementId()). As the direction is unspecified, two rows are produced for each relationship as a result of alternating the combination of the start and end node.

Example 39. UndirectedRelationshipByElementIdSeek

Query

PROFILE
MATCH (n1)-[r]-()
WHERE elementId(r) = 1
RETURN r, n1

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------------------------+--------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                              | Details                                    | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+---------------------------------------+--------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                       | r, n1                                      |              2 |    2 |       0 |                |                        |           |                     |
| |                                     +--------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +UndirectedRelationshipByElementIdSeek| (n1)-[r]-(anon_0) WHERE elementId(r) = $autoint_0 |       2 |    2 |       1 |            120 |                    4/0 |     0.332 | Fused in Pipeline 0 |
+---------------------------------+--------------------------------------------+-----+---------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 1, total allocated memory: 184

Undirected Relationship By Id Seek

The UndirectedRelationshipByIdSeek operator reads one or more relationships by id from the relationship store (specified via the function Id()). As the direction is unspecified, two rows are produced for each relationship as a result of alternating the combination of the start and end node.

Example 40. UndirectedRelationshipByIdSeek

Query

PROFILE
MATCH (n1)-[r]-()
WHERE id(r) = 1
RETURN r, n1

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------------------+----+--------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                        | Id | Details                                    | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+---------------------------------+----+--------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                 |  0 | r, n1                                      |              2 |    2 |      14 |              0 |                        |           |                     |
| |                               +----+--------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +UndirectedRelationshipByIdSeek |  1 | (n1)-[r]-(anon_0) WHERE id(r) = $autoint_0 |              2 |    2 |       1 |            248 |                    3/0 |     1.005 | Fused in Pipeline 0 |
+---------------------------------+----+--------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 15, total allocated memory: 312

Directed Relationship Index Seek By Range

The DirectedRelationshipIndexSeekByRange operator finds relationships and their start and end nodes using an index seek where the value of the property matches a given prefix STRING. DirectedRelationshipIndexSeekByRange can be used for STARTS WITH and comparison operators such as <, >, <= and >=.

Example 41. DirectedRelationshipIndexSeekByRange

Query

PROFILE
MATCH (candidate: Person)-[r:WORKS_IN]->(location)
WHERE r.duration > 100
RETURN candidate

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                              | Details                                                                                | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+---------------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                       | candidate                                                                              |              4 |   15 |       0 |                |                        |           |                     |
| |                                     +----------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Filter                               | candidate:Person                                                                       |              4 |   15 |      30 |                |                        |           |                     |
| |                                     +----------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +DirectedRelationshipIndexSeekByRange | RANGE INDEX (candidate)-[r:WORKS_IN(duration)]->(location) WHERE duration > $autoint_0 |              4 |   15 |      16 |            120 |                    4/1 |     0.703 | Fused in Pipeline 0 |
+---------------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 46, total allocated memory: 184

Partitioned Directed Relationship Index Seek By Range

The PartitionedDirectedRelationshipIndexSeekByRange is a variant of the DirectedRelationshipIndexSeekByRange operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 42. PartitionedDirectedRelationshipIndexSeekByRange

Query

CYPHER runtime=parallel
PROFILE
MATCH (candidate: Person)-[r:WORKS_IN]->(location)
WHERE r.duration > 100
RETURN candidate

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+--------------------------------------------------+----+----------------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| Operator                                         | Id | Details                                                                                | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+--------------------------------------------------+----+----------------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| +ProduceResults                                  |  0 | candidate                                                                              |              4 |   15 |      30 |                    1/0 |     1.031 | In Pipeline 1       |
| |                                                +----+----------------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| +Filter                                          |  1 | candidate:Person                                                                       |              4 |   15 |      30 |                        |           |                     |
| |                                                +----+----------------------------------------------------------------------------------------+----------------+------+---------+                        |           |                     |
| +PartitionedDirectedRelationshipIndexSeekByRange |  2 | RANGE INDEX (candidate)-[r:WORKS_IN(duration)]->(location) WHERE duration > $autoint_0 |              4 |   15 |      16 |                    3/0 |     0.203 | Fused in Pipeline 0 |
+--------------------------------------------------+----+----------------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+

Total database accesses: 76

Undirected Relationship Index Seek By Range

The UndirectedRelationshipIndexSeekByRange operator finds relationships and their start and end nodes using an index seek where the value of the property matches a given prefix STRING. UndirectedRelationshipIndexSeekByRange can be used for STARTS WITH and comparison operators such as <, >, <= and >=.

Example 43. UndirectedRelationshipIndexSeekByRange

Query

PROFILE
MATCH (candidate: Person)-[r:WORKS_IN]-(location)
WHERE r.duration > 100
RETURN candidate

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------------------------+---------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                                | Details                                                                               | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------------------------------+---------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                         | candidate                                                                             |              5 |   15 |       0 |                |                        |           |                     |
| |                                       +---------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Filter                                 | candidate:Person                                                                      |              5 |   15 |      60 |                |                        |           |                     |
| |                                       +---------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +UndirectedRelationshipIndexSeekByRange | RANGE INDEX (candidate)-[r:WORKS_IN(duration)]-(location) WHERE duration > $autoint_0 |              8 |   30 |      16 |            120 |                    4/1 |     1.214 | Fused in Pipeline 0 |
+-----------------------------------------+---------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 76, total allocated memory: 184

Partitioned Undirected Relationship Index Seek By Range

The PartitionedUndirectedRelationshipIndexSeekByRange is a variant of the UndirectedRelationshipIndexSeekByRange operator used by the parallel runtime. It allows the store to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 44. PartitionedUndirectedRelationshipIndexSeekByRange

Query

CYPHER runtime=parallel
PROFILE
MATCH (candidate: Person)-[r:WORKS_IN]-(location)
WHERE r.duration > 100
RETURN candidate

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+----------------------------------------------------+----+---------------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| Operator                                           | Id | Details                                                                               | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+----------------------------------------------------+----+---------------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| +ProduceResults                                    |  0 | candidate                                                                             |              5 |   15 |      30 |                    1/0 |     0.918 | In Pipeline 1       |
| |                                                  +----+---------------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| +Filter                                            |  1 | candidate:Person                                                                      |              5 |   15 |      60 |                        |           |                     |
| |                                                  +----+---------------------------------------------------------------------------------------+----------------+------+---------+                        |           |                     |
| +PartitionedUndirectedRelationshipIndexSeekByRange |  2 | RANGE INDEX (candidate)-[r:WORKS_IN(duration)]-(location) WHERE duration > $autoint_0 |              8 |   30 |      16 |                    3/0 |     0.413 | Fused in Pipeline 0 |
+----------------------------------------------------+----+---------------------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+

Total database accesses: 106

Nested loops and join operators

Nested loop operators process data by iterating over the right-hand side (RHS) for each row from the left-hand side (LHS). Each row from the LHS triggers the execution of the RHS, effectively creating a loop over the RHS for each LHS element.

Apply

All the different Apply operators (listed below) share the same basic functionality: they perform a nested loop by taking a single row from the left-hand side, and using the Argument operator on the right-hand side, execute the operator tree on the right-hand side. The versions of the Apply operators differ in how the results are managed. The Apply operator (i.e. the standard version) takes the row produced by the right-hand side — which at this point contains data from both the left-hand and right-hand sides — and yields it.

Example 45. Apply

Query

PROFILE
MATCH (p:Person {name: 'me'})
MATCH (q:Person {name: p.secondName})
RETURN p, q

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Details                                               | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  | p, q                                                  |              1 |    0 |       0 |                |                        |           |                     |
| |                +-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Apply           |                                                       |              1 |    0 |       0 |                |                        |           |                     |
| |\               +-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +NodeIndexSeek | RANGE INDEX q:Person(name) WHERE name = p.secondName  |              1 |    0 |       0 |           2152 |                    0/0 |     0.219 | Fused in Pipeline 1 |
| |                +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeIndexSeek   | RANGE INDEX p:Person(name) WHERE name = $autostring_0 |              1 |    1 |       2 |            120 |                    0/1 |     0.236 | In Pipeline 0       |
+------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 2, total allocated memory: 2216

Semi Apply

The SemiApply operator tests for the presence of a pattern predicate, and is a variation of the Apply operator. If the right-hand side operator yields at least one row, the row from the left-hand side operator is yielded by the SemiApply operator. This makes SemiApply a filtering operator, used mostly for pattern predicates in queries.

Example 46. SemiApply

Query

PROFILE
CYPHER runtime=slotted
MATCH (p:Person)
WHERE (p)-[:FRIENDS_WITH]->(:Person)
RETURN p.name

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-----------------+-------------------------------------+----------------+------+---------+------------------------+
| Operator        | Details                             | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-----------------+-------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults | `p.name`                            |             11 |   10 |       0 |                    0/0 |
| |               +-------------------------------------+----------------+------+---------+------------------------+
| +Projection     | p.name AS `p.name`                  |             11 |   10 |      10 |                    1/0 |
| |               +-------------------------------------+----------------+------+---------+------------------------+
| +SemiApply      |                                     |             11 |   10 |       0 |                    0/0 |
| |\              +-------------------------------------+----------------+------+---------+------------------------+
| | +Filter       | anon_3:Person                       |             12 |    0 |      10 |                    0/0 |
| | |             +-------------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)  | (p)-[anon_2:FRIENDS_WITH]->(anon_3) |             12 |   10 |      51 |                   28/0 |
| | |             +-------------------------------------+----------------+------+---------+------------------------+
| | +Argument     | p                                   |             14 |   14 |       0 |                    0/0 |
| |               +-------------------------------------+----------------+------+---------+------------------------+
| +NodeByLabelScan| p:Person                            |             14 |   14 |      35 |                    1/0 |
+-----------------+-------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 142, total allocated memory: 64

Anti Semi Apply

The AntiSemiApply operator tests for the absence of a pattern, and is a variation of the Apply operator. If the right-hand side operator yields no rows, the row from the left-hand side operator is yielded by the AntiSemiApply operator. This makes AntiSemiApply a filtering operator, used for pattern predicates in queries.

Example 47. AntiSemiApply

Query

PROFILE
CYPHER runtime=slotted
MATCH
  (me:Person {name: 'me'}),
  (other:Person)
WHERE NOT (me)-[:FRIENDS_WITH]->(other)
RETURN other.name

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-------------------+--------------------------------------------------------+----------------+------+---------+----------------+------------------------+
| Operator          | Details                                                | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses |
+-------------------+--------------------------------------------------------+----------------+------+---------+----------------+------------------------+
| +ProduceResults   | `other.name`                                           |              4 |   12 |       0 |                |                    0/0 |
| |                 +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+
| +Projection       | other.name AS `other.name`                             |              4 |   12 |      12 |                |                    1/0 |
| |                 +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+
| +AntiSemiApply    |                                                        |              4 |   12 |       0 |                |                    0/0 |
| |\                +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+
| | +Expand(Into)   | (me)-[anon_2:FRIENDS_WITH]->(other)                    |              1 |    0 |      81 |            896 |                   28/0 |
| | |               +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+
| | +Argument       | me, other                                              |             14 |   14 |       0 |                |                    0/0 |
| |                 +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+
| +CartesianProduct |                                                        |             14 |   14 |       0 |                |                    0/0 |
| |\                +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+
| | +NodeByLabelScan| other:Person                                           |             14 |   14 |      35 |                |                    1/0 |
| |                 +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+
| +NodeIndexSeek    | RANGE INDEX me:Person(name) WHERE name = $autostring_0 |              1 |    1 |       2 |                |                    0/1 |
+-------------------+--------------------------------------------------------+----------------+------+---------+----------------+------------------------+

Total database accesses: 166, total allocated memory: 976

Let Semi Apply

The LetSemiApply operator tests for the presence of a pattern predicate, and is a variation of the Apply operator. When a query contains multiple pattern predicates separated with OR, LetSemiApply will be used to evaluate the first of these. It will record the result of evaluating the predicate but will leave any filtering to another operator. In the example, LetSemiApply will be used to check for the presence of the FRIENDS_WITH relationship from each person.

Example 48. LetSemiApply

Query

PROFILE
CYPHER runtime=slotted
MATCH (other:Person)
WHERE (other)-[:FRIENDS_WITH]->(:Person) OR (other)-[:WORKS_IN]->(:Location)
RETURN other.name

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+--------------------+-----------------------------------------+----------------+------+---------+------------------------+
| Operator           | Details                                 | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+--------------------+-----------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults    | `other.name`                            |             13 |   14 |       0 |                    0/0 |
| |                  +-----------------------------------------+----------------+------+---------+------------------------+
| +Projection        | other.name AS `other.name`              |             13 |   14 |      14 |                    1/0 |
| |                  +-----------------------------------------+----------------+------+---------+------------------------+
| +SelectOrSemiApply | anon_9                                  |             14 |   14 |       0 |                    0/0 |
| |\                 +-----------------------------------------+----------------+------+---------+------------------------+
| | +Filter          | anon_7:Location                         |             14 |    0 |       4 |                    0/0 |
| | |                +-----------------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)     | (other)-[anon_6:WORKS_IN]->(anon_7)     |             14 |    4 |      15 |                    8/0 |
| | |                +-----------------------------------------+----------------+------+---------+------------------------+
| | +Argument        | other                                   |             14 |    4 |       0 |                    0/0 |
| |                  +-----------------------------------------+----------------+------+---------+------------------------+
| +LetSemiApply      |                                         |             14 |   14 |       0 |                    0/0 |
| |\                 +-----------------------------------------+----------------+------+---------+------------------------+
| | +Filter          | anon_5:Person                           |             12 |    0 |      10 |                    0/0 |
| | |                +-----------------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)     | (other)-[anon_4:FRIENDS_WITH]->(anon_5) |             12 |   10 |      51 |                   28/0 |
| | |                +-----------------------------------------+----------------+------+---------+------------------------+
| | +Argument        | other                                   |             14 |   14 |       0 |                    0/0 |
| |                  +-----------------------------------------+----------------+------+---------+------------------------+
| +NodeByLabelScan   | other:Person                            |             14 |   14 |      35 |                    1/0 |
+--------------------+-----------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 165, total allocated memory: 64

Let Anti Semi Apply

The LetAntiSemiApply operator tests for the absence of a pattern, and is a variation of the Apply operator. When a query contains multiple negated pattern predicates — i.e. predicates separated with OR, where at least one predicate contains NOT — LetAntiSemiApply will be used to evaluate the first of these. It will record the result of evaluating the predicate but will leave any filtering to another operator. In the example, LetAntiSemiApply will be used to check for the absence of the FRIENDS_WITH relationship from each person.

Example 49. LetAntiSemiApply

Query

PROFILE
CYPHER runtime=slotted
MATCH (other:Person)
WHERE NOT ((other)-[:FRIENDS_WITH]->(:Person)) OR (other)-[:WORKS_IN]->(:Location)
RETURN other.name

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26
+--------------------+-----------------------------------------+----------------+------+---------+------------------------+
| Operator           | Details                                 | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+--------------------+-----------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults    | `other.name`                            |             11 |   14 |       0 |                    0/0 |
| |                  +-----------------------------------------+----------------+------+---------+------------------------+
| +Projection        | other.name AS `other.name`              |             11 |   14 |      14 |                    1/0 |
| |                  +-----------------------------------------+----------------+------+---------+------------------------+
| +SelectOrSemiApply | anon_9                                  |             14 |   14 |       0 |                    0/0 |
| |\                 +-----------------------------------------+----------------+------+---------+------------------------+
| | +Filter          | anon_7:Location                         |             14 |    0 |      10 |                    0/0 |
| | |                +-----------------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)     | (other)-[anon_6:WORKS_IN]->(anon_7)     |             14 |   10 |      38 |                   20/0 |
| | |                +-----------------------------------------+----------------+------+---------+------------------------+
| | +Argument        | other                                   |             14 |   10 |       0 |                    0/0 |
| |                  +-----------------------------------------+----------------+------+---------+------------------------+
| +LetAntiSemiApply  |                                         |             14 |   14 |       0 |                    0/0 |
| |\                 +-----------------------------------------+----------------+------+---------+------------------------+
| | +Filter          | anon_5:Person                           |             12 |    0 |      10 |                    0/0 |
| | |                +-----------------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)     | (other)-[anon_4:FRIENDS_WITH]->(anon_5) |             12 |   10 |      51 |                   28/0 |
| | |                +-----------------------------------------+----------------+------+---------+------------------------+
| | +Argument        | other                                   |             14 |   14 |       0 |                    0/0 |
| |                  +-----------------------------------------+----------------+------+---------+------------------------+
| +NodeByLabelScan   | p:Person                                |             14 |   14 |      35 |                    1/0 |
+--------------------+-----------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 142, total allocated memory: 64

Select Or Semi Apply

The SelectOrSemiApply operator tests for the presence of a pattern predicate and evaluates a predicate, and is a variation of the Apply operator. This operator allows for the mixing of normal predicates and pattern predicates that check for the presence of a pattern. First, the normal expression predicate is evaluated, and, only if it returns false, is the costly pattern predicate evaluated.

Example 50. SelectOrSemiApply

Query

PROFILE
MATCH (other:Person)
WHERE other.age > 25 OR (other)-[:FRIENDS_WITH]->(:Person)
RETURN other.name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+--------------------+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator           | Details                                 | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+--------------------+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults    | `other.name`                            |             11 |   10 |       0 |                |                        |           |                     |
| |                  +-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Projection        | other.name AS `other.name`              |             11 |   10 |      20 |                |                        |           |                     |
| |                  +-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +SelectOrSemiApply | other.age > $autoint_0                  |             14 |   10 |       0 |            392 |                    0/0 |     0.190 | Fused in Pipeline 2 |
| |\                 +-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Limit           | 1                                       |             14 |   10 |       0 |            752 |                        |           |                     |
| | |                +-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Filter          | anon_3:Person                           |             12 |   10 |      20 |                |                        |           |                     |
| | |                +-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(All)     | (other)-[anon_2:FRIENDS_WITH]->(anon_3) |             12 |   10 |      37 |                |                        |           |                     |
| | |                +-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument        | other                                   |             14 |   14 |       0 |           2168 |                    2/0 |     0.435 | Fused in Pipeline 1 |
| |                  +-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeByLabelScan   | other:Person                            |             14 |   14 |      35 |                |                        |           | Fused in Pipeline 0 |
+--------------------+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 148, total allocated memory: 2952

Select Or Anti Semi Apply

The SelectOrAntiSemiApply operator is used to evaluate OR between a predicate and a negative pattern predicate (i.e. a pattern predicate preceded with NOT), and is a variation of the Apply operator. If the predicate returns true, the pattern predicate is not tested. If the predicate returns false or null, SelectOrAntiSemiApply will instead test the pattern predicate.

Example 51. SelectOrAntiSemiApply

Query

PROFILE
MATCH (other:Person)
WHERE other.age > 25 OR NOT (other)-[:FRIENDS_WITH]->(:Person)
RETURN other.name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------------+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator               | Details                                 | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------------+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults        | `other.name`                            |              4 |    4 |       0 |                |                        |           |                     |
| |                      +-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Projection            | other.name AS `other.name`              |              4 |    4 |       8 |                |                        |           |                     |
| |                      +-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +SelectOrAntiSemiApply | other.age > $autoint_0                  |             14 |    4 |       0 |            200 |                    0/0 |     0.155 | Fused in Pipeline 3 |
| |\                     +-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Anti                |                                         |             14 |    4 |       0 |           1256 |                    0/0 |     0.170 | In Pipeline 2       |
| | |                    +-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Limit               | 1                                       |              0 |   10 |       0 |            752 |                        |           |                     |
| | |                    +-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Filter              | anon_3:Person                           |             12 |   10 |      20 |                |                        |           |                     |
| | |                    +-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(All)         | (other)-[anon_2:FRIENDS_WITH]->(anon_3) |             12 |   10 |      37 |                |                        |           |                     |
| | |                    +-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument            | other                                   |             14 |   14 |       0 |           2168 |                    2/0 |     0.449 | Fused in Pipeline 1 |
| |                      +-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeByLabelScan       | other:Person                            |             14 |   14 |      35 |                |                        |           | Fused in Pipeline 0 |
+------------------------+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 136, total allocated memory: 4208

Let Select Or Semi Apply

The LetSelectOrSemiApply operator is planned for pattern predicates that are combined with other predicates using OR. This is a variation of the Apply operator.

Example 52. LetSelectOrSemiApply

Query

PROFILE
CYPHER runtime=slotted
MATCH (other:Person)
WHERE (other)-[:FRIENDS_WITH]->(:Person) OR (other)-[:WORKS_IN]->(:Location) OR other.age = 5
RETURN other.name

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-----------------------+-----------------------------------------+----------------+------+---------+------------------------+
| Operator              | Details                                 | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-----------------------+-----------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults       | `other.name`                            |             13 |   14 |       0 |                    0/0 |
| |                     +-----------------------------------------+----------------+------+---------+------------------------+
| +Projection           | other.name AS `other.name`              |             13 |   14 |      14 |                    1/0 |
| |                     +-----------------------------------------+----------------+------+---------+------------------------+
| +SelectOrSemiApply    | anon_9                                  |             14 |   14 |       0 |                    0/0 |
| |\                    +-----------------------------------------+----------------+------+---------+------------------------+
| | +Filter             | anon_7:Location                         |             14 |    0 |       4 |                    0/0 |
| | |                   +-----------------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)        | (other)-[anon_6:WORKS_IN]->(anon_7)     |             14 |    4 |      15 |                    8/0 |
| | |                   +-----------------------------------------+----------------+------+---------+------------------------+
| | +Argument           | other                                   |             14 |    4 |       0 |                    0/0 |
| |                     +-----------------------------------------+----------------+------+---------+------------------------+
| +LetSelectOrSemiApply | other.age = $autoint_0                  |             14 |   14 |      14 |                    0/0 |
| |\                    +-----------------------------------------+----------------+------+---------+------------------------+
| | +Filter             | anon_5:Person                           |             12 |    0 |      10 |                    0/0 |
| | |                   +-----------------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)        | (other)-[anon_4:FRIENDS_WITH]->(anon_5) |             12 |   10 |      51 |                   28/0 |
| | |                   +-----------------------------------------+----------------+------+---------+------------------------+
| | +Argument           | other                                   |             14 |   14 |       0 |                    0/0 |
| |                     +-----------------------------------------+----------------+------+---------+------------------------+
| +NodeByLabelScan      | other:Person                            |             14 |   14 |      35 |                    1/0 |
+-----------------------+-----------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 179, total allocated memory: 64

Let Select Or Anti Semi Apply

The LetSelectOrAntiSemiApply operator is planned for negated pattern predicates — i.e. pattern predicates preceded with NOT — that are combined with other predicates using OR. This operator is a variation of the Apply operator.

Example 53. LetSelectOrAntiSemiApply

Query

PROFILE
CYPHER runtime=slotted
MATCH (other:Person)
WHERE NOT (other)-[:FRIENDS_WITH]->(:Person) OR (other)-[:WORKS_IN]->(:Location) OR other.age = 5
RETURN other.name

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+---------------------------+-----------------------------------------+----------------+------+---------+------------------------+
| Operator                  | Details                                 | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+---------------------------+-----------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults           | `other.name`                            |             12 |   14 |       0 |                    0/0 |
| |                         +-----------------------------------------+----------------+------+---------+------------------------+
| +Projection               | other.name AS `other.name`              |             12 |   14 |      14 |                    1/0 |
| |                         +-----------------------------------------+----------------+------+---------+------------------------+
| +SelectOrSemiApply        | anon_9                                  |             14 |   14 |       0 |                    0/0 |
| |\                        +-----------------------------------------+----------------+------+---------+------------------------+
| | +Filter                 | anon_7:Location                         |             14 |    0 |      10 |                    0/0 |
| | |                       +-----------------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)            | (other)-[anon_6:WORKS_IN]->(anon_7)     |             14 |   10 |      38 |                   20/0 |
| | |                       +-----------------------------------------+----------------+------+---------+------------------------+
| | +Argument               | other                                   |             14 |   10 |       0 |                    0/0 |
| |                         +-----------------------------------------+----------------+------+---------+------------------------+
| +LetSelectOrAntiSemiApply | other.age = $autoint_0                  |             14 |   14 |      14 |                    0/0 |
| |\                        +-----------------------------------------+----------------+------+---------+------------------------+
| | +Filter                 | anon_5:Person                           |             12 |    0 |      10 |                    0/0 |
| | |                       +-----------------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)            | (other)-[anon_4:FRIENDS_WITH]->(anon_5) |             12 |   10 |      51 |                   28/0 |
| | |                       +-----------------------------------------+----------------+------+---------+------------------------+
| | +Argument               | other                                   |             14 |   14 |       0 |                    0/0 |
| |                         +-----------------------------------------+----------------+------+---------+------------------------+
| +NodeByLabelScan          | other:Person                            |             14 |   14 |      35 |                    1/0 |
+---------------------------+-----------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 208, total allocated memory: 64

Roll Up Apply

The RollUpApply operator is used to execute an expression which takes as input a pattern, and returns a list with content from the matched pattern; for example, when using a pattern expression or pattern comprehension in a query. This operator is a variation of the Apply operator.

Example 54. RollUpApply

Query

PROFILE
CYPHER runtime=slotted
MATCH (p:Person)
RETURN p.name, [(p)-[:WORKS_IN]->(location) | location.name] AS cities

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-----------------+-----------------------------------+----------------+------+---------+------------------------+
| Operator        | Details                           | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-----------------+-----------------------------------+----------------+------+---------+------------------------+
| +ProduceResults | `p.name`, cities                  |             14 |   14 |       0 |                    0/0 |
| |               +-----------------------------------+----------------+------+---------+------------------------+
| +Projection     | p.name AS `p.name`                |             14 |   14 |      14 |                    0/0 |
| |               +-----------------------------------+----------------+------+---------+------------------------+
| +RollUpApply    | cities, anon_0                    |             14 |   14 |       0 |                    0/0 |
| |\              +-----------------------------------+----------------+------+---------+------------------------+
| | +Projection   | location.name AS anon_0           |             15 |   15 |      15 |                    1/0 |
| | |             +-----------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)  | (p)-[anon_2:WORKS_IN]->(location) |             15 |   15 |      53 |                   28/0 |
| | |             +-----------------------------------+----------------+------+---------+------------------------+
| | +Argument     | p                                 |             14 |   14 |       0 |                    0/0 |
| |               +-----------------------------------+----------------+------+---------+------------------------+
| +NodeByLabelScan| p:Person                          |             14 |   14 |      35 |                    1/0 |
+-----------------+-----------------------------------+----------------+------+---------+------------------------+

Total database accesses: 153, total allocated memory: 64

TransactionApply

TransactionApply works like the Apply operator but will commit the current transaction after a specified number of rows.

Example 55. TransactionApply

The below query uses a variable scope clause (introduced in Neo4j 5.23) to import variables into the CALL subquery. If you are using an older version of Neo4j, use an importing WITH clause instead.

Query

PROFILE
LOAD CSV FROM 'https://neo4j.com/docs/cypher-refcard/3.3/csv/artists.csv' AS line
CALL (line) {
  CREATE (a: Artist {name: line[0]})
  RETURN a
} IN TRANSACTIONS OF 100 ROWS
RETURN a;

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-------------------+----+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator          | Id | Details                                          | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-------------------+----+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults   |  0 | a                                                |             10 |    4 |       8 |              0 |                        |           |                     |
| |                 +----+--------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +TransactionApply |  1 | IN TRANSACTIONS OF $autoint_1 ROWS ON ERROR FAIL |             10 |    4 |       0 |           2152 |                    0/0 |     2.036 | Fused in Pipeline 3 |
| |\                +----+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Create         |  2 | (a:Artist {name: line[$autoint_0]})              |             10 |    4 |      16 |                |                        |           |                     |
| | |               +----+--------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument       |  3 | line                                             |             10 |    4 |       0 |           3472 |                    0/0 |    32.746 | Fused in Pipeline 2 |
| |                 +----+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +LoadCSV          |  4 | line                                             |             10 |    4 |       0 |            328 |                        |           | In Pipeline 1       |
+-------------------+----+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 24, total allocated memory: 5472

Argument

The Argument operator indicates the variable to be used as an argument to the right-hand side of an Apply operator.

Example 56. Argument

Query

PROFILE
MATCH (s:Person {name: 'me'})
MERGE (s)-[:FRIENDS_WITH]->(s)

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Id | Details                                               | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults |  0 |                                                       |              1 |    0 |       0 |                |                        |           |                     |
| |               +----+-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult    |  1 |                                                       |              1 |    0 |       0 |                |                        |           |                     |
| |               +----+-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Apply          |  2 |                                                       |              1 |    1 |       0 |                |                        |           |                     |
| |\              +----+-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +LockingMerge |  3 | CREATE (s)-[anon_0:FRIENDS_WITH]->(s), LOCK(s)        |              1 |    1 |       1 |                |                        |           |                     |
| | |             +----+-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(Into) |  4 | (s)-[anon_0:FRIENDS_WITH]->(s)                        |              0 |    0 |      10 |            904 |                        |           |                     |
| | |             +----+-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument     |  5 | s                                                     |              1 |    3 |       0 |           2280 |                    2/0 |     0.460 | Fused in Pipeline 1 |
| |               +----+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeIndexSeek  |  6 | RANGE INDEX s:Person(name) WHERE name = $autostring_0 |              1 |    1 |       2 |            376 |                    1/0 |     0.211 | In Pipeline 0       |
+-----------------+----+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 15, total allocated memory: 2232

Argument Tracker

The ArgumentTracker operator is used to ensure row-by-row semantics. This restricts the Cypher runtime to not batch operations in larger chunks.

Example 57. ArgumentTracker

Query

PROFILE
MATCH (s:Person {name: 'me'})
CALL (s) {
   SET s.seen = coalesce(s.seen + 1,1)
   RETURN s.seen AS result
}
RETURN result;

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

+--------------------+----+---------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator           | Id | Details                                                 | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+--------------------+----+---------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults    |  0 | result                                                  |              1 |    1 |       0 |              0 |                        |           |                     |
| |                  +----+---------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Apply             |  1 |                                                         |              1 |    0 |       0 |                |                        |           |                     |
| |\                 +----+---------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +ArgumentTracker |  7 |                                                         |              1 |    0 |       0 |            736 |                        |           |                     |
| | |                +----+---------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Projection      |  2 | s.seen AS result                                        |              1 |    1 |       2 |                |                        |           |                     |
| | |                +----+---------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Eager           |  3 | read/set conflict for property: seen (Operator: 4 vs 2) |              1 |    1 |       0 |            976 |                    0/0 |     0.298 | Fused in Pipeline 2 |
| | |                +----+---------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +SetProperty     |  4 | s.seen = coalesce(s.seen + $autoint_1, $autoint_2)      |              1 |    1 |       2 |                |                        |           |                     |
| | |                +----+---------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument        |  5 | s                                                       |              1 |    1 |       0 |           2408 |                    2/0 |     1.734 | Fused in Pipeline 1 |
| |                  +----+---------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeIndexSeek     |  6 | RANGE INDEX s:Person(name) WHERE name = $autostring_0   |              1 |    1 |       2 |            368 |                    1/0 |     0.183 | In Pipeline 0       |
+--------------------+----+---------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 6, total allocated memory: 4136

Cartesian Product

The CartesianProduct operator produces a cartesian product of the two inputs — each row coming from the left child operator will be combined with all the rows from the right child operator. CartesianProduct generally exhibits bad performance and ought to be avoided if possible.

Example 58. CartesianProduct

Query

PROFILE
MATCH
  (p:Person),
  (t:Team)
RETURN p, t

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+--------------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator           | Details  | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+--------------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults    | p, t     |            140 |  140 |       0 |                |                    2/0 |     1.917 |                     |
| |                  +----------+----------------+------+---------+----------------+------------------------+-----------+                     |
| +CartesianProduct  |          |            140 |  140 |       0 |           1736 |                        |     1.209 | In Pipeline 2       |
| |\                 +----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +NodeByLabelScan | t:Team   |             10 |   10 |      11 |            136 |                    1/0 |     1,145 | In Pipeline 1       |
| |                  +----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeByLabelScan   | p:Person |             15 |   15 |      16 |            120 |                    1/0 |     0,409 | In Pipeline 0       |
+--------------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 142, total allocated memory: 1816

Hash joins in general

Hash joins have two inputs: the build input and probe input. The query planner assigns these roles so that the smaller of the two inputs is the build input. The build input is pulled in eagerly, and is used to build a probe table. Once this is complete, the probe table is checked for each row coming from the probe input side.

In query plans, the build input is always the left operator, and the probe input the right operator.

Node Hash Join

The NodeHashJoin operator is a variation of the hash join. NodeHashJoin executes the hash join on node ids. As primitive types and arrays can be used, it can be done very efficiently.

Example 59. NodeHashJoin

Query

PROFILE
MATCH (bob:Person {name: 'Bob'})-[:WORKS_IN]->(loc)<-[:WORKS_IN]-(matt:Person {name: 'Mattias'})
USING JOIN ON loc
RETURN loc.name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Details                                                  | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  | `loc.name`                                               |             10 |    0 |       0 |                |                    0/0 |     0.000 |                     |
| |                +----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+                     |
| +Projection      | loc.name AS `loc.name`                                   |             10 |    0 |       0 |                |                    0/0 |     0.000 |                     |
| |                +----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+                     |
| +Filter          | not anon_0 = anon_1                                      |             10 |    0 |       0 |                |                    0/0 |     0.000 |                     |
| |                +----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+                     |
| +NodeHashJoin    | loc                                                      |             10 |    0 |       0 |           3688 |                        |     0.053 | In Pipeline 2       |
| |\               +----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Expand(All)   | (matt)-[anon_1:WORKS_IN]->(loc)                          |             19 |    0 |       0 |                |                        |           |                     |
| | |              +----------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +NodeIndexSeek | RANGE INDEX matt:Person(name) WHERE name = $autostring_1 |              1 |    0 |       1 |            120 |                    1/0 |     0.288 | Fused in Pipeline 1 |
| |                +----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Expand(All)     | (bob)-[anon_0:WORKS_IN]->(loc)                           |             19 |    1 |       4 |                |                        |           |                     |
| |                +----------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexSeek   | RANGE INDEX bob:Person(name) WHERE name = $autostring_0  |              1 |    1 |       2 |            120 |                    3/0 |     0.556 | Fused in Pipeline 0 |
+------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 7, total allocated memory: 3888

Value Hash Join

The ValueHashJoin operator is a variation of the hash join. This operator allows for arbitrary values to be used as the join key. It is most frequently used to solve predicates of the form: n.prop1 = m.prop2 (i.e. equality predicates between two property columns).

Example 60. ValueHashJoin

Query

PROFILE
MATCH
  (p:Person),
  (q:Person)
WHERE p.age = q.age
RETURN p, q

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-------------------+---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator          | Details       | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-------------------+---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults    | p, q         |             10 |    0 |       0 |                |                    0/0 |     0.000 |                     |
| |                 +---------------+----------------+------+---------+----------------+------------------------+-----------+                     |
| +ValueHashJoin     | p.age = q.age|             10 |    0 |       0 |            344 |                        |           | In Pipeline 2       |
| |\                +---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +NodeByLabelScan| q:Person      |             15 |    0 |       0 |            120 |                    0/0 |     0,000 | In Pipeline 1       |
| |                 +---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeByLabelScan  | p:Person      |             15 |   15 |      16 |            120 |                    1/0 |     0,211 | In Pipeline 0       |
+-------------------+---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 71, total allocated memory: 664

Node Left/Right Outer Hash Join

The NodeLeftOuterHashJoin and NodeRightOuterHashJoin operators are variations of the hash join. The query below can be planned with either a left or a right outer join. The decision depends on the cardinalities of the left-hand and right-hand sides; i.e. how many rows would be returned, respectively, for (a:Person) and (a)-→(b:Person). If (a:Person) returns fewer results than (a)-→(b:Person), a left outer join — indicated by NodeLeftOuterHashJoin — is planned. On the other hand, if (a:Person) returns more results than (a)-→(b:Person), a right outer join — indicated by NodeRightOuterHashJoin — is planned instead.

Example 61. NodeRightOuterHashJoin

Query

PROFILE
MATCH (a:Person)
OPTIONAL MATCH (a)-->(b:Person)
USING JOIN ON a
RETURN a.name, b.name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-------------------------+------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                | Details                                              | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-------------------------+------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults         | `a.name`, `b.name`                                   |             14 |   16 |       0 |                |                    0/0 |     0.102 |                     |
| |                       +------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+                     |
| +Projection             | cache[a.name] AS `a.name`, cache[b.name] AS `b.name` |             14 |   16 |       8 |                |                    0/0 |     0.055 |                     |
| |                       +------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+                     |
| +NodeRightOuterHashJoin | a                                                    |             14 |   16 |       0 |           4232 |                        |     0.269 | In Pipeline 2       |
| |\                      +------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +NodeByLabelScan      | a:Person                                             |             15 |   15 |      16 |            120 |                    1/0 |     0,049 | In Pipeline 1       |
| |                       +------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +CacheProperties        | cache[b.name], cache[a.name]                         |             13 |   13 |      39 |                |                        |           |                     |
| |                       +------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Expand(All)            | (b)<-[anon_0]-(a)                                    |             13 |   13 |      55 |                |                        |           |                     |
| |                       +------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan        | b:Person                                             |             15 |   15 |      16 |            120 |                    5/0 |     1,150 | Fused in Pipeline 0 |
+-------------------------+------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 211, total allocated memory: 4312

Traversal operators

Traversal operators enable complex graph traversals by defining how nodes and relationships are connected in a given pattern. They allow Cypher to express and match various pattern types, such as fixed-length patterns, variable-length patterns, shortest paths, and non-linear patterns.

Anti

The Anti operator tests for the absence of a pattern. If there are incoming rows, the Anti operator will yield no rows. If there are no incoming rows, the Anti operator will yield a single row.

Example 62. Anti

Query

PROFILE
CYPHER runtime=pipelined
MATCH
  (me:Person {name: 'me'}),
  (other:Person)
WHERE NOT (me)-[:FRIENDS_WITH]->(other)
RETURN other.name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-------------------+--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator          | Details                                                | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-------------------+--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults   | `other.name`                                           |              4 |   12 |       0 |                |                    0/0 |     0.068 |                     |
| |                 +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+                     |
| +Projection       | other.name AS `other.name`                             |              4 |   12 |      24 |                |                    2/0 |     0.111 | In Pipeline 4       |
| |                 +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Apply            |                                                        |              4 |   12 |       0 |                |                    0/0 |           |                     |
| |\                +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Anti           |                                                        |              4 |   12 |       0 |           1256 |                    0/0 |     0.084 | In Pipeline 4       |
| | |               +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Limit          | 1                                                      |             11 |    2 |       0 |            752 |                        |           |                     |
| | |               +--------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(Into)   | (me)-[anon_2:FRIENDS_WITH]->(other)                    |              1 |    2 |      81 |           2632 |                        |           |                     |
| | |               +--------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument       | me, other                                              |             14 |   14 |       0 |           3192 |                    1/0 |     0.904 | Fused in Pipeline 3 |
| |                 +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +CartesianProduct |                                                        |             14 |   14 |       0 |           3672 |                        |     1.466 | In Pipeline 2       |
| |\                +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +NodeByLabelScan| other:Person                                           |             14 |   14 |      35 |                |                        |           |                     |
| |                 +--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeIndexSeek    | RANGE INDEX me:Person(name) WHERE name = $autostring_0 |              1 |    1 |       2 |            120 |                    0/1 |     0.493 | In Pipeline 0       |
+-------------------+--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 178, total allocated memory: 6744

Optional

The Optional operator is used to solve some OPTIONAL MATCH queries. It will pull data from its source, simply passing it through if any data exists. However, if no data is returned by its source, Optional will yield a single row with all columns set to null.

Example 63. Optional

Query

PROFILE
MATCH (p:Person {name: 'me'})
OPTIONAL MATCH (q:Person {name: 'Lulu'})
RETURN p, q

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| Operator         | Details                                               | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +ProduceResults  | p, q                                                  |              1 |    1 |       0 |                |                    2/0 |     0.079 | In Pipeline 2 |
| |                +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +Apply           |                                                       |              1 |    1 |       0 |                |                    0/0 |     0.096 |               |
| |\               +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| | +Optional      | p                                                     |              1 |    1 |       0 |            768 |                    0/0 |     0.043 | In Pipeline 2 |
| | |              +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| | +NodeIndexSeek | RANGE INDEX q:Person(name) WHERE name = $autostring_1 |              1 |    0 |       1 |           2152 |                    1/0 |     0.098 | In Pipeline 1 |
| |                +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +NodeIndexSeek   | RANGE INDEX p:Person(name) WHERE name = $autostring_0 |              1 |    1 |       2 |            120 |                    0/1 |     0.364 | In Pipeline 0 |
+------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+

Total database accesses: 3, total allocated memory: 3000

Expand All

Given a start node, and depending on the pattern relationship, the Expand(All) operator will traverse incoming or outgoing relationships.

Example 64. Expand(All)

Query

PROFILE
MATCH (p:Person {name: 'me'})-[:FRIENDS_WITH]->(fof)
RETURN fof

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                               | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | fof                                                   |              1 |    2 |       0 |                |                        |           |                     |
| |               +-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Expand(All)    | (p)-[anon_0:FRIENDS_WITH]->(fof)                      |              1 |    2 |       5 |                |                        |           |                     |
| |               +-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexSeek  | RANGE INDEX p:Person(name) WHERE name = $autostring_0 |              1 |    1 |       2 |            120 |                    4/1 |     1.137 | Fused in Pipeline 0 |
+-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 7, total allocated memory: 184

Expand Into

When both the start and end node have already been found, the Expand(Into) operator is used to find all relationships connecting the two nodes. As both the start and end node of the relationship are already in scope, the node with the smallest degree will be used. This can make a noticeable difference when dense nodes appear as end points.

Example 65. Expand(Into)

Query

PROFILE
MATCH (p:Person {name: 'me'})-[:FRIENDS_WITH]->(fof)-->(p)
RETURN fof

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                               | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | fof                                                   |              0 |    0 |       0 |                |                        |           |                     |
| |               +-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Filter         | not anon_1 = anon_0                                   |              0 |    0 |       0 |                |                        |           |                     |
| |               +-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Expand(Into)   | (p)-[anon_0:FRIENDS_WITH]->(fof)                      |              0 |    0 |       6 |            896 |                        |           |                     |
| |               +-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Expand(All)    | (p)<-[anon_1]-(fof)                                   |              1 |    1 |       5 |                |                        |           |                     |
| |               +-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexSeek  | RANGE INDEX p:Person(name) WHERE name = $autostring_0 |              1 |    1 |       2 |            120 |                    4/1 |     0.546 | Fused in Pipeline 0 |
+-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 13, total allocated memory: 976

Optional Expand All

The OptionalExpand(All) operator is analogous to Expand(All), apart from when no relationships match the direction, type and property predicates. In this situation, OptionalExpand(all) will return a single row with the relationship and end node set to null.

Example 66. OptionalExpand(All)

Query

PROFILE
MATCH (p:Person)
OPTIONAL MATCH (p)-[works_in:WORKS_IN]->(l)
  WHERE works_in.duration > 180
RETURN p, l

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+----------------------+-------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator             | Details                                                           | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+----------------------+-------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults      | p, l                                                              |             14 |   15 |       1 |                |                        |           |                     |
| |                    +-------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +OptionalExpand(All) | (p)-[works_in:WORKS_IN]->(l) WHERE works_in.duration > $autoint_0 |             14 |   15 |      53 |                |                        |           |                     |
| |                    +-------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan     | p:Person                                                          |             14 |   14 |      15 |            120 |                    5/0 |     1,233 | Fused in Pipeline 0 |
+----------------------+-------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 125, total allocated memory: 184

Optional Expand Into

The OptionalExpand(Into) operator is analogous to Expand(Into), apart from when no matching relationships are found. In this situation, OptionalExpand(Into) will return a single row with the relationship and end node set to null. As both the start and end node of the relationship are already in scope, the node with the smallest degree will be used. This can make a noticeable difference when dense nodes appear as end points.

Example 67. OptionalExpand(Into)

Query

PROFILE
MATCH (p:Person)-[works_in:WORKS_IN]->(l)
OPTIONAL MATCH (l)-->(p)
RETURN p

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------+------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator              | Details                      | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------------+------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults       | p                            |             15 |   15 |       0 |                |                        |           |                     |
| |                     +------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +OptionalExpand(Into) | (l)-[anon_0]->(p)            |             15 |   15 |     105 |           3360 |                        |           |                     |
| |                     +------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Expand(All)          | (p)-[works_in:WORKS_IN]->(l) |             15 |   15 |      39 |                |                        |           |                     |
| |                     +------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan      | p:Person                     |             14 |   14 |      15 |            120 |                    7/0 |     3,925 | Fused in Pipeline 0 |
+-----------------------+--- --------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 215, total allocated memory: 3440

VarLength Expand All

Given a start node, the VarLengthExpand(All) operator will traverse variable-length and quantified relationships.

Example 68. VarLengthExpand(All)

Query

PROFILE
MATCH (p:Person)-[:FRIENDS_WITH *1..2]-(q:Person)
RETURN p, q

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator              | Details                           | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults       | p, q                              |             40 |   48 |       0 |                |                        |           |                     |
| |                     +-----------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Filter               | q:Person                          |             40 |   48 |      96 |                |                        |           |                     |
| |                     +-----------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +VarLengthExpand(All) | (p)-[anon_0:FRIENDS_WITH*..2]-(q) |             40 |   48 |     151 |            128 |                        |           |                     |
| |                     +-----------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan      | p:Person                          |             14 |   14 |      15 |            120 |                    6/0 |    10,457 | Fused in Pipeline 0 |
+-----------------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 318, total allocated memory: 208

VarLength Expand Into

When both the start and end node have already been found, the VarLengthExpand(Into) operator is used to find all variable-length and quantified relationships connecting the two nodes.

Example 69. VarLengthExpand(Into)

Query

PROFILE
MATCH (p:Person)-[:FRIENDS_WITH *1..2]-(p:Person)
RETURN p

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator               | Details                           | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults        | p                                 |              3 |    4 |       0 |                |                        |           |                     |
| |                      +-----------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +VarLengthExpand(Into) | (p)-[anon_0:FRIENDS_WITH*..2]-(p) |              3 |    4 |     151 |            128 |                        |           |                     |
| |                      +-----------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan       | p:Person                          |             14 |   14 |      15 |            120 |                    6/0 |     0,797 | Fused in Pipeline 0 |
+------------------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+


Total database accesses: 222, total allocated memory: 192

VarLength Expand Pruning

Given a start node, the VarLengthExpand(Pruning) operator will traverse variable-length and quantified relationships much like the VarLengthExpand(All) operator. However, as an optimization, some paths will not be explored if they are guaranteed to produce an end node that has already been found (by means of a previous path traversal).

This kind of expand is only planned when:

The individual paths are not of interest.
The relationships have an upper bound.

The VarLengthExpand(Pruning) operator guarantees that all the end nodes produced will be unique.

Example 70. VarLengthExpand(Pruning)

Query

PROFILE
MATCH (p:Person)-[:FRIENDS_WITH *3..4]-(q:Person)
RETURN DISTINCT p, q

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------------+----+------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------+
| Operator                  | Id | Details                      | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by | Pipeline      |
+---------------------------+----+------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------+
| +ProduceResults           |  0 | p, q                         |              0 |    0 |       0 |                |                    0/0 |     0.005 |            |               |
| |                         +----+------------------------------+----------------+------+---------+----------------+------------------------+-----------+            |               |
| +OrderedDistinct          |  1 | p, q                         |              0 |    0 |       0 |             40 |                    0/0 |     0.014 |            |               |
| |                         +----+------------------------------+----------------+------+---------+----------------+------------------------+-----------+            |               |
| +Filter                   |  2 | q:Person                     |              0 |    0 |       0 |                |                    0/0 |     0.014 |            |               |
| |                         +----+------------------------------+----------------+------+---------+----------------+------------------------+-----------+            |               |
| +VarLengthExpand(Pruning) |  3 | (p)-[:FRIENDS_WITH*3..4]-(q) |              1 |    0 |      15 |            400 |                        |           |            | In Pipeline 1 |
| |                         +----+------------------------------+----------------+------+---------+----------------+------------------------+-----------+            +---------------+
| +NodeByLabelScan          |  4 | p:Person                     |             14 |   14 |      15 |            120 |                    1/0 |     0.020 | p ASC      | In Pipeline 0 |
+---------------------------+----+------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------+

Total database accesses: 30, total allocated memory: 480

Breadth First VarLength Expand Pruning

Given a start node, the VarLengthExpand(Pruning,BFS,All) operator traverses variable-length and quantified relationships much like the VarLengthExpand(All) operator. However, as an optimization, it instead performs a breadth-first search (BFS) and while expanding, some paths are not explored if they are guaranteed to produce an end node that has already been found (by means of a previous path traversal). This is only used in cases where the individual paths are not of interest.

This kind of expand is only planned when:

The individual paths are not of interest.
The lower bound is either 0 or 1 (default).

This operator guarantees that all the end nodes produced are unique.

Query

PROFILE
MATCH (p:Person)-[:FRIENDS_WITH *..4]-(q:Person)
RETURN DISTINCT p, q

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------------------+----+------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| Operator                          | Id | Details                      | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by | Pipeline            |
+-----------------------------------+----+------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| +ProduceResults                   |  0 | p, q                         |             12 |    0 |       0 |              0 |                        |           |            |                     |
| |                                 +----+------------------------------+----------------+------+---------+----------------+                        |           |            |                     |
| +OrderedDistinct                  |  1 | p, q                         |             12 |    0 |       0 |             40 |                        |           |            |                     |
| |                                 +----+------------------------------+----------------+------+---------+----------------+                        |           |            |                     |
| +Filter                           |  2 | q:Person                     |             13 |    0 |       0 |                |                        |           |            |                     |
| |                                 +----+------------------------------+----------------+------+---------+----------------+                        |           |            |                     |
| +VarLengthExpand(Pruning,BFS,All) |  3 | (p)-[:FRIENDS_WITH*..4]-(q)  |             13 |    0 |      38 |            952 |                        |           |            |                     |
| |                                 +----+------------------------------+----------------+------+---------+----------------+                        |           |            |                     |
| +NodeByLabelScan                  |  4 | p:Person                     |             10 |   10 |      11 |            248 |                    3/0 |     4.662 | p ASC      | Fused in Pipeline 0 |
+-----------------------------------+----+------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+

Total database accesses: 49, total allocated memory: 1200

Repeat (Trail)

Given a start node, the Repeat(Trail) operator will traverse quantified path patterns that cannot be solved (or solved efficiently) with the VarLengthExpand(All) operator. Similar to an Apply operator, it takes a single row from the left-hand side and applies the operators on the right-hand side. In contrast to Apply, however, it repeatedly applies these operators in accordance with the quantifiers on the quantified path pattern. In the following example, the operator will repeat twice and produce rows for both repetitions.

Example 71. Repeat(Trail)

Query

PROFILE
MATCH (me:Person) ((a)-[:FRIENDS_WITH]-(b)-[:FRIENDS_WITH]-(c) WHERE a.name <> b.name AND a.name <> c.name AND b.name <> c.name){1,2} (friend:Person)
RETURN me, friend

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Id | Details                                                                                              | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  |  0 | me, friend                                                                                           |              2 |   34 |     136 |              0 |                        |           |                     |
| |                +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Filter          |  1 | friend:Person                                                                                        |              2 |   34 |      68 |                |                        |           |                     |
| |                +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NullifyMetadata |  9 |                                                                                                      |              2 |   34 |       0 |                |                        |           |                     |
| |                +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Repeat(Trail)   |  2 | (me) (...){1, 2} (friend)                                                                            |              2 |   34 |       0 |          29792 |                    0/0 |     1.696 | Fused in Pipeline 2 |
| |\               +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Filter        |  3 | NOT anon_5 = anon_3 AND (NOT cache[a.name] = cache[c.name] AND NOT cache[b.name] = cache[c.name]) AN |              1 |   34 |      92 |                |                        |           |                     |
| | |              |    | D isRepeatTrailUnique(anon_5)                                                                        |                |      |         |                |                        |           |                     |
| | |              +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(All)   |  4 | (b)-[anon_5:FRIENDS_WITH]-(c)                                                                        |              3 |   92 |     138 |                |                        |           |                     |
| | |              +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Filter        |  5 | NOT cache[a.name] = cache[b.name] AND isRepeatTrailUnique(anon_3)                                    |              5 |   46 |     198 |                |                        |           |                     |
| | |              +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(All)   |  6 | (a)-[anon_3:FRIENDS_WITH]-(b)                                                                        |             10 |   66 |     100 |                |                        |           |                     |
| | |              +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument      |  7 | a                                                                                                    |             15 |   34 |       0 |          15672 |                    2/0 |     3.245 | Fused in Pipeline 1 |
| |                +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeByLabelScan |  8 | me:Person                                                                                            |             14 |   14 |      15 |            376 |                    1/0 |     0.107 | In Pipeline 0       |
+------------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 747, total allocated memory: 45832

Nullify Metadata

NullifyMetadata is responsible for cleaning up the state produced by Repeat(Trail). It is only planned directly after Repeat(Trail).

Example 72. NullifyMetadata

Query

PROFILE
MATCH (me:Person) ((a)-[:FRIENDS_WITH]-(b)-[:FRIENDS_WITH]-(c) WHERE a.name <> b.name AND a.name <> c.name AND b.name <> c.name){1,2} (friend:Person)
RETURN me, friend

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Id | Details                                                                                              | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  |  0 | me, friend                                                                                           |              2 |   34 |     136 |              0 |                        |           |                     |
| |                +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Filter          |  1 | friend:Person                                                                                        |              2 |   34 |      68 |                |                        |           |                     |
| |                +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NullifyMetadata |  9 |                                                                                                      |              2 |   34 |       0 |                |                        |           |                     |
| |                +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Repeat(Trail)   |  2 | (me) (...){1, 2} (friend)                                                                            |              2 |   34 |       0 |          29792 |                    0/0 |     1.696 | Fused in Pipeline 2 |
| |\               +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Filter        |  3 | NOT anon_5 = anon_3 AND (NOT cache[a.name] = cache[c.name] AND NOT cache[b.name] = cache[c.name]) AN |              1 |   34 |      92 |                |                        |           |                     |
| | |              |    | D isRepeatTrailUnique(anon_5)                                                                        |                |      |         |                |                        |           |                     |
| | |              +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(All)   |  4 | (b)-[anon_5:FRIENDS_WITH]-(c)                                                                        |              3 |   92 |     138 |                |                        |           |                     |
| | |              +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Filter        |  5 | NOT cache[a.name] = cache[b.name] AND isRepeatTrailUnique(anon_3)                                    |              5 |   46 |     198 |                |                        |           |                     |
| | |              +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(All)   |  6 | (a)-[anon_3:FRIENDS_WITH]-(b)                                                                        |             10 |   66 |     100 |                |                        |           |                     |
| | |              +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument      |  7 | a                                                                                                    |             15 |   34 |       0 |          15672 |                    2/0 |     3.245 | Fused in Pipeline 1 |
| |                +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeByLabelScan |  8 | me:Person                                                                                            |             14 |   14 |      15 |            376 |                    1/0 |     0.107 | In Pipeline 0       |
+------------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 747, total allocated memory: 45832

Shortest path

The ShortestPath operator finds one or all shortest paths between two previously matched node variables. This operator is used for the shortestPath() and allShortestPaths functions.

Example 73. ShortestPath

Query

PROFILE
MATCH
  (andy:Person {name: 'Andy'}),
  (mattias:Person {name: 'Mattias'}),
  p = shortestPath((andy)-[*]-(mattias))
RETURN p

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------+-------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| Operator            | Details                                                     | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+---------------------+-------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +ProduceResults     | p                                                           |              1 |    1 |       0 |                |                    1/0 |     0.241 |               |
| |                   +-------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+               |
| +ShortestPath       | p = (andy)-[anon_0*]-(mattias)                              |              1 |    1 |       1 |           1424 |                        |           | In Pipeline 1 |
| |                   +-------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +MultiNodeIndexSeek | RANGE INDEX andy:Person(name) WHERE name = $autostring_0,   |              1 |    1 |       4 |            120 |                    1/1 |     0.308 | In Pipeline 0 |
|                     | RANGE INDEX mattias:Person(name) WHERE name = $autostring_1 |                |      |         |                |                        |           |               |
+---------------------+-------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+

Total database accesses: 5, total allocated memory: 1488

StatefulShortestPath(Into)

The StatefulShortestPath(Into) operator finds shortest paths between a start node and a single target node. It uses a bidirectional breadth-first search (BFS) algorithm, which performs two BFS invocations at the same time, one from the left boundary node and one from the right boundary node. Once a node is found by both BFS invocations, which indicates that it can be reached from both boundary nodes, the algorithm successfully terminates. If one of the BFS invocations exhausts its search before intersecting, either because no further nodes can be reached or because the maximum number of hops has been reached, then there is no valid path between the boundary nodes and the algorithm terminates.

Example 74. StatefulShortestPath(Into)

Query

PROFILE
MATCH
  p = ALL SHORTEST (chris:Person {name: 'Chris'})(()-[]-()-[]-()){1,}(stefan:Person {name: 'Stefan'})
RETURN p

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------------+----+--------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| Operator                    | Id | Details                                                                                          | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+-----------------------------+----+--------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +ProduceResults             |  0 | p                                                                                                |              2 |    2 |       0 |              0 |                    0/0 |     0.039 |               |
| |                           +----+--------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+               |
| +Projection                 |  1 | (chris) ((anon_12)-[anon_14]-(anon_13)-[anon_11]-())* (stefan) AS p                              |              2 |    2 |       0 |                |                    0/0 |     1.365 |               |
| |                           +----+--------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+               |
| +StatefulShortestPath(Into) |  2 | SHORTEST 1 GROUPS (chris) ((`anon_5`)-[`anon_6`]-(`anon_7`)-[`anon_8`]-(`anon_9`)){1, } (stefan) |              2 |    2 |      39 |          22237 |                    1/0 |    37.376 | In Pipeline 1 |
| |                           +----+--------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +MultiNodeIndexSeek         |  3 | UNIQUE chris:Person(name) WHERE name = $autostring_0,                                            |              1 |    1 |       4 |            376 |                    1/1 |    10.245 | In Pipeline 0 |
|                             |    | UNIQUE stefan:Person(name) WHERE name = $autostring_1                                            |                |      |         |                |                        |           |               |
+-----------------------------+----+--------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+

Total database accesses: 43, total allocated memory: 22557

StatefulShortestPath(All)

The StatefulShortestPath(All) operator finds shortest paths from a single node to multiple target nodes. It uses a breadth-first search algorithm.

Example 75. StatefulShortestPath(All)

Query

PROFILE
MATCH
  p = ALL SHORTEST (chris:Person {name:'Chris'})(()-[]-()-[]-()){1,}(location:Location)
RETURN length(p) AS pathLength, location.name AS locationName

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+----------------------------+----+----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| Operator                   | Id | Details                                                                                            | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+----------------------------+----+----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +ProduceResults            |  0 | pathLength, locationName                                                                           |             14 |   20 |       0 |              0 |                    0/0 |     0.074 |               |
| |                          +----+----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+               |
| +Projection                |  1 | length((chris) ((anon_12)-[anon_14]-(anon_13)-[anon_11]-())* (location)) AS pathLength,            |             14 |   20 |      40 |                |                    1/0 |     6.828 |               |
| |                          |    | location.name AS locationName                                                                      |                |      |         |                |                        |           |               |
| |                          +----+----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+               |
| +StatefulShortestPath(All) |  2 | SHORTEST 1 GROUPS (chris) ((`anon_5`)-[`anon_6`]-(`anon_7`)-[`anon_8`]-(`anon_9`)){1, } (location) |             14 |   20 |     179 |          37663 |                    1/0 |    52.849 | In Pipeline 1 |
| |                          |    |         expanding from: chris                                                                      |                |      |         |                |                        |           |               |
| |                          |    |     inlined predicates: location:Location                                                          |                |      |         |                |                        |           |               |
| |                          +----+----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +NodeUniqueIndexSeek       |  3 | UNIQUE chris:Person(name) WHERE name = $autostring_0                                               |              1 |    1 |       2 |            376 |                    0/1 |     9.078 | In Pipeline 0 |
+----------------------------+----+----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------+

Total database accesses: 221, total allocated memory: 37983

Triadic Selection

The TriadicSelection operator is used to solve triangular queries, such as the very common 'find my friends-of-friends that are not already my friend'. It does so by putting all the friends into a set, and uses the set to check if the friends-of-friends are already connected to me. The example finds the names of all friends of my friends that are not already my friends.

Example 76. TriadicSelection

Query

PROFILE
CYPHER runtime=slotted
MATCH (me:Person)-[:FRIENDS_WITH]-()-[:FRIENDS_WITH]-(other)
WHERE NOT (me)-[:FRIENDS_WITH]-(other)
RETURN other.name

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-------------------+----------------------------------------+----------------+------+---------+------------------------+
| Operator          | Details                                | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-------------------+----------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults   | `other.name`                           |              4 |   24 |       0 |                    0/0 |
| |                 +----------------------------------------+----------------+------+---------+------------------------+
| +Projection       | other.name AS `other.name`             |              4 |   24 |      24 |                    1/0 |
| |                 +----------------------------------------+----------------+------+---------+------------------------+
| +Filter           | not anon_2 = anon_4                    |             16 |   24 |       0 |                    0/0 |
| |                 +----------------------------------------+----------------+------+---------+------------------------+
| +TriadicSelection | WHERE NOT (me)--(other)                |              4 |   24 |       0 |                    0/0 |
| |\                +----------------------------------------+----------------+------+---------+------------------------+
| | |               +----------------------------------------+----------------+------+---------+------------------------+
| | +Expand(All)    | (anon_3)-[anon_4:FRIENDS_WITH]-(other) |             16 |   48 |      98 |                   48/0 |
| | |               +----------------------------------------+----------------+------+---------+------------------------+
| | +Argument       | anon_3, anon_2                         |             24 |   24 |       0 |                    0/0 |
| |                 +----------------------------------------+----------------+------+---------+------------------------+
| +Expand(All)      | (me)-[anon_2:FRIENDS_WITH]-(anon_3)    |             24 |   24 |      53 |                   28/0 |
| |                 +----------------------------------------+----------------+------+---------+------------------------+
| +NodeByLabelScan  | me:Person                              |             15 |   15 |      16 |                    1/0 |
+-------------------+----------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 246, total allocated memory: 64

Triadic Build

The TriadicBuild operator is used in conjunction with TriadicFilter to solve triangular queries, such as the very common 'find my friend-of-friends that are not already my friend'. These two operators are specific to Pipelined runtime and together perform the same logic as TriadicSelection does for other runtimes. TriadicBuild builds a set of all friends, which is later used by TriadicFilter. The example finds the names of all friends of my friends that are not already my friends.

Example 77. TriadicBuild

Query

PROFILE
CYPHER runtime=pipelined
MATCH (me:Person)-[:FRIENDS_WITH]-()-[:FRIENDS_WITH]-(other)
WHERE NOT (me)-[:FRIENDS_WITH]-(other)
RETURN other.name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | `other.name`                           |              4 |   24 |       0 |                |                    0/0 |     0.133 |                     |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+                     |
| +Projection     | other.name AS `other.name`             |              4 |   24 |      48 |                |                    2/0 |     0.056 |                     |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Filter         | not anon_2 = anon_4                    |             16 |   24 |       0 |                |                        |           |                     |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +TriadicFilter  | WHERE NOT (me)--(other)                |              4 |   24 |       0 |           4136 |                    0/0 |     0.195 | In Pipeline 3       |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Apply          |                                        |             16 |   24 |       0 |                |                    0/0 |           |                     |
| |\              +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | |             +----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(All)  | (anon_3)-[anon_4:FRIENDS_WITH]-(other) |             16 |   48 |      98 |                |                        |           |                     |
| | |             +----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument     | anon_3, anon_2                         |             24 |   24 |       0 |           4200 |                    0/0 |     0.397 | Fused in Pipeline 2 |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +TriadicBuild   | (me)--(anon_3)                         |             24 |   24 |       0 |            888 |                    0/0 |     1.427 | In Pipeline 1       |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Expand(All)    | (me)-[anon_2:FRIENDS_WITH]-(anon_3)    |             24 |   24 |      39 |                |                        |           |                     |
| |               +----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan| me:Person                               |             15 |   15 |      16 |            120 |                    3/0 |     0,200 | Fused in Pipeline 0 |
+-----------------+----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 256, total allocated memory: 7376

Triadic Filter

The TriadicFilter operator is used in conjunction with TriadicBuild to solve triangular queries, such as the very common 'find my friend-of-friends that are not already my friend'. These two operators are specific to Pipelined runtime and together perform the same logic as TriadicSelection does for other runtimes. TriadicFilter uses a set of friends previously built by TriadicBuild to check if the friend-of-friends are already connected to me. The example finds the names of all friends of my friends that are not already my friends.

Example 78. TriadicFilter

Query

PROFILE
CYPHER runtime=pipelined
MATCH (me:Person)-[:FRIENDS_WITH]-()-[:FRIENDS_WITH]-(other)
WHERE NOT (me)-[:FRIENDS_WITH]-(other)
RETURN other.name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | `other.name`                           |              4 |   24 |       0 |                |                    0/0 |     0.189 |                     |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+                     |
| +Projection     | other.name AS `other.name`             |              4 |   24 |      48 |                |                    2/0 |     0.381 |                     |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Filter         | not anon_2 = anon_4                    |             16 |   24 |       0 |                |                        |           |                     |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +TriadicFilter  | WHERE NOT (me)--(other)                |              4 |   24 |       0 |           4136 |                    0/0 |     0.685 | In Pipeline 3       |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Apply          |                                        |             16 |   24 |       0 |                |                    0/0 |           |                     |
| |\              +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | |             +----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(All)  | (anon_3)-[anon_4:FRIENDS_WITH]-(other) |             16 |   48 |      98 |                |                        |           |                     |
| | |             +----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument     | anon_3, anon_2                         |             24 |   24 |       0 |           4200 |                    0/0 |     0.496 | Fused in Pipeline 2 |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +TriadicBuild   | (me)--(anon_3)                         |             24 |   24 |       0 |            888 |                    0/0 |     3.268 | In Pipeline 1       |
| |               +----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Expand(All)    | (me)-[anon_2:FRIENDS_WITH]-(anon_3)    |             24 |   24 |      39 |                |                        |           |                     |
| |               +----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan| me:Person                              |             15 |   15 |      16 |            120 |                    3/0 |     0,481 | Fused in Pipeline 0 |
+-----------------+----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 256, total allocated memory: 7376

Union operators

Union operators in Cypher combine the results from multiple query parts by merging their rows. For more information, see the page about the UNION clause.

Union

The Union operator concatenates the results from the right child operator with the results from the left child operator.

Example 79. Union

Query

PROFILE
MATCH (p:Location)
  RETURN p.name
UNION ALL
MATCH (p:Country)
  RETURN p.name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+--------------------+----+--------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator           | Id | Details            | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+--------------------+----+--------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults    |  0 | `p.name`           |             20 |    0 |       0 |                |                        |           |                     |
| |                  +----+--------------------+----------------+------+---------+----------------+                        |           |                     |
| +Union             |  1 |                    |             20 |    0 |       0 |              0 |                    0/0 |     0.000 | Fused in Pipeline 2 |
| |\                 +----+--------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Projection      |  2 | `p.name`           |             10 |    0 |       0 |                |                        |           |                     |
| | |                +----+--------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Projection      |  3 | p.name AS `p.name` |             10 |    0 |       0 |                |                        |           |                     |
| | |                +----+--------------------+----------------+------+---------+----------------+                        |           |                     |
| | +NodeByLabelScan |  4 | p:Country          |             10 |    0 |       0 |            120 |                    0/0 |     0.049 | Fused in Pipeline 1 |
| |                  +----+--------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Projection        |  5 | `p.name`           |             10 |    0 |       0 |                |                        |           |                     |
| |                  +----+--------------------+----------------+------+---------+----------------+                        |           |                     |
| +Projection        |  6 | p.name AS `p.name` |             10 |    0 |       0 |                |                        |           |                     |
| |                  +----+--------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan   |  7 | p:Location         |             10 |    0 |       0 |            120 |                    0/0 |     0.077 | Fused in Pipeline 0 |
+--------------------+----+--------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 0, total allocated memory: 320

Aggregation operators

Aggregation operators are used to compute summary statistics over groups of graph data, enabling operations such as counting nodes, relationships or properties.

Eager Aggregation

The EagerAggregation operator evaluates a grouping expression and uses the result to group rows into different groupings. For each of these groupings, EagerAggregation will then evaluate all aggregation functions and return the result. To do this, EagerAggregation, as the name implies, needs to pull in all data eagerly from its source and build up state, which leads to increased memory pressure in the system.

Example 80. EagerAggregation

Query

PROFILE
MATCH (l:Location)<-[:WORKS_IN]-(p:Person)
RETURN
  l.name AS location,
  collect(p.name) AS people

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-------------------+----+------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator          | Id | Details                                              | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-------------------+----+------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults   |  0 | location, people                                     |              4 |    6 |       0 |                |                    0/0 |     0.022 | In Pipeline 1       |
| |                 +----+------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +EagerAggregation |  1 | cache[l.name] AS location, collect(p.name) AS people |              4 |    6 |      30 |           2584 |                        |           |                     |
| |                 +----+------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Filter           |  2 | p:Person                                             |             15 |   15 |      30 |                |                        |           |                     |
| |                 +----+------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Expand(All)      |  3 | (l)<-[anon_0:WORKS_IN]-(p)                           |             15 |   15 |      26 |                |                        |           |                     |
| |                 +----+------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +CacheProperties  |  4 | cache[l.name]                                        |             10 |   10 |      20 |                |                        |           |                     |
| |                 +----+------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan  |  5 | l:Location                                           |             10 |   10 |      11 |            120 |                    4/0 |     0.813 | Fused in Pipeline 0 |
+-------------------+----+------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 117, total allocated memory: 2664

Ordered Aggregation

The OrderedAggregation operator is an optimization of the EagerAggregation operator that takes advantage of the ordering of the incoming rows. This operator uses lazy evaluation and has a lower memory pressure in the system than the EagerAggregation operator.

Example 81. OrderedAggregation

Query

PROFILE
MATCH (p:Person)
WHERE p.name STARTS WITH 'P'
RETURN p.name, count(*) AS count

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+--------------+---------------+
| Operator              | Details                                                                        | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by   | Pipeline      |
+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+--------------+---------------+
| +ProduceResults       | `p.name`, count                                                                |              0 |    2 |       0 |                |                    0/0 |     0.045 |              |               |
| |                     +--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+              |               |
| +OrderedAggregation   | cache[p.name] AS `p.name`, count(*) AS count                                   |              0 |    2 |       0 |            288 |                    0/0 |     0.175 | `p.name` ASC | In Pipeline 1 |
| |                     +--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+--------------+---------------+
| +NodeIndexSeekByRange | RANGE INDEX p:Person(name) WHERE name STARTS WITH $autostring_0, cache[p.name] |              0 |    2 |       3 |            120 |                    0/1 |     0.529 | p.name ASC   | In Pipeline 0 |
+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+--------------+---------------+

Total database accesses: 3, total allocated memory: 352

Node Count From Count Store

The NodeCountFromCountStore operator uses the count store to answer questions about node counts. This is much faster than the EagerAggregation operator which achieves the same result by actually counting. However, as the count store only stores a limited range of combinations, EagerAggregation will still be used for more complex queries. For example, we can get counts for all nodes, and nodes with a label, but not nodes with more than one label.

Example 82. NodeCountFromCountStore

Query

PROFILE
MATCH (p:Person)
RETURN count(p) AS people

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+--------------------------+------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                 | Details                      | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+--------------------------+------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults          | people                       |              1 |    1 |       0 |                |                        |           |                     |
| |                        +------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeCountFromCountStore | count( (:Person) ) AS people |              1 |    1 |       1 |            120 |                    0/0 |     0.169 | Fused in Pipeline 0 |
+--------------------------+------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 1, total allocated memory: 184

Relationship Count From Count Store

The RelationshipCountFromCountStore operator uses the count store to answer questions about relationship counts. This is much faster than the EagerAggregation operator which achieves the same result by actually counting. However, as the count store only stores a limited range of combinations, EagerAggregation will still be used for more complex queries. For example, we can get counts for all relationships, relationships with a type, relationships with a label on one end, but not relationships with labels on both end nodes.

Example 83. RelationshipCountFromCountStore

Query

PROFILE
MATCH (p:Person)-[r:WORKS_IN]->()
RETURN count(r) AS jobs

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+----------------------------------+--------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                         | Details                                    | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+----------------------------------+--------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                  | jobs                                       |              1 |    1 |       0 |                |                        |           |                     |
| |                                +--------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +RelationshipCountFromCountStore | count( (:Person)-[:WORKS_IN]->() ) AS jobs |              1 |    1 |       1 |            120 |                    0/0 |     0.625 | Fused in Pipeline 0 |
+----------------------------------+--------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 1, total allocated memory: 184

Filter, order, and projection operators

The operators in this group handle how rows of data are transformed, filtered, and finalized for query results. They are responsible for crafting the structure of the returned data,

Empty Result

The EmptyResult operator eagerly loads all incoming data and discards it.

Example 84. EmptyResult

Query

PROFILE
CREATE (:Person)

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+-----------------+----------------+------+---------+------------------------+-----------+---------------------+
| Operator        | Details         | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+-----------------+----------------+------+---------+------------------------+-----------+---------------------+
| +ProduceResults |                 |              1 |    0 |       0 |                        |           |                     |
| |               +-----------------+----------------+------+---------+                        |           |                     |
| +EmptyResult    |                 |              1 |    0 |       0 |                        |           |                     |
| |               +-----------------+----------------+------+---------+                        |           |                     |
| +Create         | (anon_0:Person) |              1 |    1 |       1 |                    0/0 |     0.000 | Fused in Pipeline 0 |
+-----------------+-----------------+----------------+------+---------+------------------------+-----------+---------------------+

Total database accesses: 1, total allocated memory: 184

Produce Results

The ProduceResults operator prepares the result so that it is consumable by the user, such as transforming internal values to user values. It is present in every single query that returns data to the user, and has little bearing on performance optimisation.

Example 85. ProduceResults

Query

PROFILE
MATCH (n)
RETURN n

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+---------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+---------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | n       |             35 |   35 |       0 |                |                        |           |                     |
| |               +---------+----------------+------+---------+----------------+                        |           |                     |
| +AllNodesScan   | n       |             35 |   35 |      36 |            120 |                    3/0 |     0.508 | Fused in Pipeline 0 |
+-----------------+---------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 36, total allocated memory: 184

Filter

The Filter operator filters each row coming from the child operator, only passing through rows that evaluate the predicates to true.

Example 86. Filter

Query

PROFILE
MATCH (p:Person)
WHERE p.name =~ '^a.*'
RETURN p

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                                          | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | p                                                                |             14 |    0 |       0 |                |                        |           |                     |
| |               +------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Filter         | cache[p.name] =~ $autostring_0                                   |             14 |    0 |       0 |                |                        |           |                     |
| |               +------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexScan  | RANGE INDEX p:Person(name) WHERE name IS NOT NULL, cache[p.name] |             14 |   14 |      15 |            120 |                    0/1 |     0.763 | Fused in Pipeline 0 |
+-----------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 15, total allocated memory: 184

Empty Row

The EmptyRow operator returns a single row with no columns.

Example 87. EmptyRow

Query

PROFILE
CYPHER runtime=slotted
FOREACH (value IN [1,2,3] | MERGE (:Person {age: value}))

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-----------------+--------------------------------------+----------------+------+---------+------------------------+
| Operator        | Details                              | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+--------------------+--------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults    |                                      |              1 |    0 |       0 |                    0/0 |
| |                  +--------------------------------------+----------------+------+---------+------------------------+
| +EmptyResult       |                                      |              1 |    0 |       0 |                    0/0 |
| |                  +--------------------------------------+----------------+------+---------+------------------------+
| +Foreach           | value IN [1, 2, 3]                   |              1 |    1 |       0 |                    0/0 |
| |\                 +--------------------------------------+----------------+------+---------+------------------------+
| | +Merge           | CREATE (anon_0:Person {age: value})  |              1 |    3 |       9 |                    0/0 |
| | |                +--------------------------------------+----------------+------+---------+------------------------+
| | +Filter          | anon_0.age = value                   |              1 |    0 |     184 |                    2/0 |
| | |                +--------------------------------------+----------------+------+---------+------------------------+
| | +NodeByLabelScan | anon_0:Person                        |             35 |  108 |     111 |                    3/0 |
| |                  +--------------------------------------+----------------+------+---------+------------------------+
| +EmptyRow          |                                      |              1 |    1 |       0 |                    0/0 |
+--------------------+--------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 304, total allocated memory: 64

Cache Properties

The CacheProperties operator reads nodes and relationship properties and caches them in the current row. Future accesses to these properties can avoid reading from the store which will speed up the query. In the plan below we will cache l.name before Expand(All) where there are fewer rows.

Example 88. CacheProperties

Query

PROFILE
MATCH (l:Location)<-[:WORKS_IN]-(p:Person)
RETURN
  l.name AS location,
  p.name AS name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+----+-------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Id | Details                                   | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------+----+-------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  |  0 | location, name                            |             13 |   13 |       0 |                |                        |           |                     |
| |                +----+-------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Projection      |  1 | cache[l.name] AS location, p.name AS name |             13 |   13 |      26 |                |                        |           |                     |
| |                +----+-------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Filter          |  2 | p:Person                                  |             13 |   13 |      26 |                |                        |           |                     |
| |                +----+-------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Expand(All)     |  3 | (l)<-[anon_0:WORKS_IN]-(p)                |             13 |   13 |      24 |                |                        |           |                     |
| |                +----+-------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +CacheProperties |  4 | cache[l.name]                             |             10 |   10 |      20 |                |                        |           |                     |
| |                +----+-------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan |  5 | l:Location                                |             10 |   10 |      11 |            120 |                    4/0 |     0.344 | Fused in Pipeline 0 |
+------------------+----+-------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 107, total allocated memory: 200

Projection

For each incoming row, the Projection operator evaluates a set of expressions and produces a row with the results of the expressions.

Example 89. Projection

Query

PROFILE
RETURN 'hello' AS greeting

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+---------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| Operator        | Details                   | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+---------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| +ProduceResults | greeting                  |              1 |    1 |       0 |                        |           |                     |
| |               +---------------------------+----------------+------+---------+                        |           |                     |
| +Projection     | $autostring_0 AS greeting |              1 |    1 |       0 |                    0/0 |     0.000 | Fused in Pipeline 0 |
+-----------------+---------------------------+----------------+------+---------+------------------------+-----------+---------------------+

Total database accesses: 0, total allocated memory: 184

Project Endpoints

The ProjectEndpoints operator projects the start and end node of a relationship.

Example 90. ProjectEndpoints

Query

PROFILE
CREATE (n)-[p:KNOWS]->(m)
WITH p AS r
MATCH (u)-[r]->(v)
RETURN u, v

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------+----+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator            | Id | Details                                 | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+---------------------+----+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults     |  0 | u, v                                    |              1 |    1 |       2 |              0 |                        |           |                     |
| |                   +----+-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Apply              |  1 |                                         |              1 |    1 |       0 |                |                        |           |                     |
| |\                  +----+-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +ProjectEndpoints |  2 | (u)-[r]->(v)                            |              1 |    1 |       0 |                |                        |           |                     |
| | |                 +----+-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument         |  3 | r                                       |              1 |    1 |       0 |           4328 |                    0/0 |     0.194 | Fused in Pipeline 2 |
| |                   +----+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Eager              |  4 | read/create conflict (Operator: 6 vs 2) |              1 |    1 |       0 |            368 |                    0/0 |     0.025 | In Pipeline 1       |
| |                   +----+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Projection         |  5 | p AS r                                  |              1 |    1 |       0 |                |                        |           |                     |
| |                   +----+-----------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Create             |  6 | (n), (m), (n)-[p:KNOWS]->(m)            |              1 |    1 |       3 |                |                    0/0 |     0.000 | Fused in Pipeline 0 |
+---------------------+----+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 5, total allocated memory: 4920

Distinct

The Distinct operator removes duplicate rows from the incoming stream of rows. To ensure only distinct elements are returned, Distinct will pull in data lazily from its source and build up state. This may lead to increased memory pressure in the system.

Example 91. Distinct

Query

PROFILE
MATCH (l:Location)<-[:WORKS_IN]-(p:Person)
RETURN DISTINCT p

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+----+----------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Id | Details                    | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------+----+----------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  |  0 | p                          |             14 |   14 |      29 |              0 |                        |           |                     |
| |                +----+----------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Distinct        |  1 | p                          |             14 |   14 |       0 |            352 |                        |           |                     |
| |                +----+----------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Filter          |  2 | p:Person                   |             15 |   15 |      30 |                |                        |           |                     |
| |                +----+----------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Expand(All)     |  3 | (l)<-[anon_0:WORKS_IN]-(p) |             15 |   15 |      25 |                |                        |           |                     |
| |                +----+----------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan |  4 | l:Location                 |             10 |   10 |      11 |            248 |                    8/0 |     0.758 | Fused in Pipeline 0 |
+------------------+----+----------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 95, total allocated memory: 432

Ordered Distinct

The OrderedDistinct operator is an optimization of the Distinct operator that takes advantage of the ordering of the incoming rows. This operator has a lower memory pressure in the system than the Distinct operator.

Example 92. OrderedDistinct

Query

PROFILE
MATCH (p:Person)
WHERE p.name STARTS WITH 'P'
RETURN DISTINCT p.name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+--------------+---------------+
| Operator              | Details                                                                        | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by   | Pipeline      |
+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+--------------+---------------+
| +ProduceResults       | `p.name`                                                                       |              0 |    2 |       0 |                |                    0/0 |     0.046 |              |               |
| |                     +--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+              |               |
| +OrderedDistinct      | cache[p.name] AS `p.name`                                                      |              0 |    2 |       0 |             32 |                    0/0 |     0.090 | `p.name` ASC |               |
| |                     +--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+--------------+               |
| +NodeIndexSeekByRange | RANGE INDEX p:Person(name) WHERE name STARTS WITH $autostring_0, cache[p.name] |              0 |    2 |       3 |            120 |                    0/1 |     0.493 | p.name ASC   | In Pipeline 0 |
+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+--------------+---------------+

Total database accesses: 3, total allocated memory: 184

Procedure Call

The ProcedureCall operator indicates an invocation to a procedure.

Example 93. ProcedureCall

Query

PROFILE
CALL db.labels() YIELD label
RETURN *
ORDER BY label

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| Operator        | Details                           | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by | Pipeline            |
+-----------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| +ProduceResults | label                             |             10 |    4 |       0 |                |                    0/0 |     0.091 |            |                     |
| |               +-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+            |                     |
| +Sort           | label ASC                         |             10 |    4 |       0 |            536 |                    0/0 |     0.178 | label ASC  | In Pipeline 1       |
| |               +-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| +ProcedureCall  | db.labels() :: (label :: STRING)  |             10 |    4 |         |                |                        |           |            | Fused in Pipeline 0 |
+-----------------+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+

Total database accesses: ?, total allocated memory: 600

Unwind

The Unwind operator returns one row per item in a list.

Example 94. Unwind

Query

PROFILE
UNWIND range(1, 5) AS value
RETURN value

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| Operator        | Details                                | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| +ProduceResults | value                                  |             10 |    5 |       0 |                        |           |                     |
| |               +----------------------------------------+----------------+------+---------+                        |           |                     |
| +Unwind         | range($autoint_0, $autoint_1) AS value |             10 |    5 |       0 |                    0/0 |     0.000 | Fused in Pipeline 0 |
+-----------------+----------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+

Total database accesses: 0, total allocated memory: 184

Partitioned Unwind

The PartitionedUnwind is a variant of the Unwind operator used by the parallel runtime. It allows the index to be partitioned into different segments where each segment can be scanned independently in parallel.

Example 95. PartitionedUnwind

Query

CYPHER runtime=parallel
PROFILE
UNWIND range(1, 5) AS value
RETURN value

Query Plan

Planner COST

Runtime PARALLEL

Runtime version 5.26

Batch size 128

+--------------------+----+----------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| Operator           | Id | Details                                | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+--------------------+----+----------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| +ProduceResults    |  0 | value                                  |             10 |    5 |       0 |                    0/0 |     0.119 | In Pipeline 1       |
| |                  +----+----------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| +PartitionedUnwind |  1 | range($autoint_0, $autoint_1) AS value |             10 |    5 |       0 |                        |           | Fused in Pipeline 0 |
+--------------------+----+----------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+

Total database accesses: 0

Sort and limit operators

The operators in this group manage result set ordering and control the flow of data by limiting or skipping rows. They optimize query performance and ensure users receive results in the desired order and quantity.

Sort

The Sort operator sorts rows by a provided key. In order to sort the data, all data from the source operator needs to be pulled in eagerly and kept in the query state, which will lead to increased memory pressure in the system.

Example 96. Sort

Query

PROFILE
MATCH (p:Person)
RETURN p
ORDER BY p.name

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+--------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| Operator         | Details            | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by | Pipeline            |
+------------------+--------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| +ProduceResults  | p                  |             14 |   14 |       0 |                |                    2/0 |     0.178 |            |                     |
| |                +--------------------+----------------+------+---------+----------------+------------------------+-----------+            |                     |
| +Sort            | `p.name` ASC       |             14 |   14 |       0 |           1192 |                    0/0 |     0.107 | p.name ASC | In Pipeline 1       |
| |                +--------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| +Projection      | p.name AS `p.name` |             14 |   14 |      14 |                |                        |           |            |                     |
| |                +--------------------+----------------+------+---------+----------------+                        |           +------------+                     |
| +NodeByLabelScan |p:Person            |             14 |   14 |      35 |            120 |                    3/0 |     0,221 |            | Fused in Pipeline 0 |
+------------------+--------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+

Total database accesses: 85, total allocated memory: 1272

Partial Sort

The PartialSort operator is an optimization of the Sort operator that takes advantage of the ordering of the incoming rows. This operator uses lazy evaluation and has a lower memory pressure in the system than the Sort operator. Partial sort is only applicable when sorting on multiple columns.

Example 97. PartialSort

Query

PROFILE
MATCH (p:Person)
WHERE p.name STARTS WITH 'P'
RETURN p
ORDER BY p.name, p.age

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+
| Operator              | Details                                                                        | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by            | Pipeline            |
+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+
| +ProduceResults       | p                                                                              |              0 |    2 |       0 |                |                    2/0 |     0.087 |                       |                     |
| |                     +--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+                       |                     |
| +PartialSort          | `p.name` ASC, `p.age` ASC                                                      |              0 |    2 |       0 |            544 |                    0/0 |     0.184 | p.name ASC, p.age ASC | In Pipeline 1       |
| |                     +--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+
| +Projection           | cache[p.name] AS `p.name`, p.age AS `p.age`                                    |              0 |    2 |       0 |                |                        |           | `p.name` ASC          |                     |
| |                     +--------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +-----------------------+                     |
| +NodeIndexSeekByRange | RANGE INDEX p:Person(name) WHERE name STARTS WITH $autostring_0, cache[p.name] |              0 |    2 |       3 |            120 |                    0/1 |     0.362 | p.name ASC            | Fused in Pipeline 0 |
+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+

Total database accesses: 3, total allocated memory: 608

Top

The Top operator returns the first n rows sorted by a provided key. Instead of sorting the entire input, only the top n rows are retained.

Example 98. Top

Query

PROFILE
MATCH (p:Person)
RETURN p
ORDER BY p.name
LIMIT 2

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+----------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| Operator         | Details              | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by | Pipeline            |
+------------------+----------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| +ProduceResults  | p                    |              2 |    2 |       0 |                |                    2/0 |     0.093 |            |                     |
| |                +----------------------+----------------+------+---------+----------------+------------------------+-----------+            |                     |
| +Top             | `p.name` ASC LIMIT 2 |              2 |    2 |       0 |           1184 |                    0/0 |     0.295 | p.name ASC | In Pipeline 1       |
| |                +----------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| +Projection      | p.name AS `p.name`   |             14 |   14 |      14 |                |                        |           |            |                     |
| |                +----------------------+----------------+------+---------+----------------+                        |           +------------+                     |
| +NodeByLabelScan | p:Person             |             14 |   14 |      35 |            120 |                    3/0 |     0,166 |            | Fused in Pipeline 0 |
+------------------+----------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+

Total database accesses: 85, total allocated memory: 1264

Partial Top

The PartialTop operator is an optimization of the Top operator that takes advantage of the ordering of the incoming rows. This operator uses lazy evaluation and has a lower memory pressure in the system than the Top operator. Partial top is only applicable when sorting on multiple columns.

Example 99. PartialTop

Query

PROFILE
MATCH (p:Person)
WHERE p.name STARTS WITH 'P'
RETURN p
ORDER BY p.name, p.age
LIMIT 2

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+
| Operator              | Details                                                                        | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by            | Pipeline            |
+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+
| +ProduceResults       | p                                                                              |              0 |    2 |       0 |                |                    2/0 |     0.093 |                       |                     |
| |                     +--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+                       |                     |
| +PartialTop           | `p.name` ASC, `p.age` ASC LIMIT 2                                              |              0 |    2 |       0 |            640 |                    0/0 |     0.870 | p.name ASC, p.age ASC | In Pipeline 1       |
| |                     +--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+
| +Projection           | cache[p.name] AS `p.name`, p.age AS `p.age`                                    |              0 |    2 |       0 |                |                        |           | `p.name` ASC          |                     |
| |                     +--------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +-----------------------+                     |
| +NodeIndexSeekByRange | RANGE INDEX p:Person(name) WHERE name STARTS WITH $autostring_0, cache[p.name] |              0 |    2 |       3 |            120 |                    0/1 |     0.556 | p.name ASC            | Fused in Pipeline 0 |
+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+-----------------------+---------------------+

Total database accesses: 3, total allocated memory: 704

Limit

The Limit operator returns the first n rows from the incoming input.

Example 100. Limit

Query

PROFILE
MATCH (p:Person)
RETURN p
LIMIT 3

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details  | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | p        |              3 |    3 |       0 |                |                        |           |                     |
| |               +----------+----------------+------+---------+----------------+                        |           |                     |
| +Limit          | 3        |              3 |    3 |       0 |             32 |                        |           |                     |
| |               +----------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan| p:Person |              3 |    4 |       5 |            120 |                    3/0 |     0,540 | Fused in Pipeline 0 |
+-----------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 8, total allocated memory: 184

Exhaustive Limit

The ExhaustiveLimit operator is similar to the Limit operator but will always exhaust the input. Used when combining LIMIT and updates

Example 101. ExhaustiveLimit

Query

PROFILE
MATCH (p:Person)
SET p.seen = true
RETURN p
LIMIT 3

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+----+---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Id | Details       | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------+----+---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  |  0 | p             |              3 |    3 |      10 |              0 |                        |           |                     |
| |                +----+---------------+----------------+------+---------+----------------+                        |           |                     |
| +ExhaustiveLimit |  1 | 3             |              3 |    3 |       0 |             32 |                        |           |                     |
| |                +----+---------------+----------------+------+---------+----------------+                        |           |                     |
| +SetProperty     |  2 | p.seen = true |             17 |   17 |      34 |                |                        |           |                     |
| |                +----+---------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan |  3 | p:Person      |             17 |   17 |      18 |            240 |                    3/0 |     1.966 | Fused in Pipeline 0 |
+------------------+----+---------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 62, total allocated memory: 304

Skip

The Skip operator skips n rows from the incoming rows.

Example 102. Skip

Query

PROFILE
MATCH (p:Person)
RETURN p
ORDER BY p.id
SKIP 1

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+----------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| Operator         | Details        | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by | Pipeline            |
+------------------+----------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| +ProduceResults  | p              |             13 |   13 |       0 |                |                    2/0 |     0.165 |            |                     |
| |                +----------------+----------------+------+---------+----------------+------------------------+-----------+            |                     |
| +Skip            | $autoint_0     |             13 |   13 |       0 |             32 |                    0/0 |     0.043 |            |                     |
| |                +----------------+----------------+------+---------+----------------+------------------------+-----------+            |                     |
| +Sort            | `p.id` ASC     |             14 |   14 |       0 |            400 |                    0/0 |     0.155 | p.id ASC   | In Pipeline 1       |
| |                +----------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+
| +Projection      | p.id AS `p.id` |             14 |   14 |       0 |                |                        |           |            |                     |
| |                +----------------+----------------+------+---------+----------------+                        |           +------------+                     |
| +NodeByLabelScan | p:Person       |             18 |   18 |      19 |            120 |                    3/0 |     0,157 |            | Fused in Pipeline 0 |
+------------------+----------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+

Total database accesses: 71, total allocated memory: 512

Data modification operators

The operators in this group modify graph data by creating, deleting, and altering nodes, relationships, and properties.

Create

The Create operator is used to create nodes and relationships.

Example 103. Create

Query

PROFILE
CREATE
  (max:Person {name: 'Max'}),
  (chris:Person {name: 'Chris'})
CREATE (max)-[:FRIENDS_WITH]->(chris)

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+---------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| Operator        | Details                                                                   | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+---------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+
| +ProduceResults |                                                                           |              1 |    0 |       0 |                        |           |                     |
| |               +---------------------------------------------------------------------------+----------------+------+---------+                        |           |                     |
| +EmptyResult    |                                                                           |              1 |    0 |       0 |                        |           |                     |
| |               +---------------------------------------------------------------------------+----------------+------+---------+                        |           |                     |
| +Create         | (max:Person {name: $autostring_0}), (chris:Person {name: $autostring_1}), |              1 |    1 |       7 |                    0/0 |     0.000 | Fused in Pipeline 0 |
|                 | (max)-[anon_0:FRIENDS_WITH]->(chris)                                      |                |      |         |                        |           |                     |
+-----------------+---------------------------------------------------------------------------+----------------+------+---------+------------------------+-----------+---------------------+

Total database accesses: 7, total allocated memory: 184

Delete

The Delete operator is used to delete a node or a relationship.

Example 104. Delete

Query

PROFILE
MATCH (you:Person {name: 'you'})
DELETE you

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----+--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Id | Details                                                | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----+--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults |  0 |                                                        |              0 |    0 |       0 |                |                        |           |                     |
| |               +----+--------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult    |  1 |                                                        |              0 |    0 |       0 |                |                        |           |                     |
| |               +----+--------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Delete         |  2 | you                                                     |              0 |    0 |       0 |                |                        |           |                     |
| |               +----+--------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexSeek  |  3 | RANGE INDEX you:Person(name) WHERE name = $autostring_0 |              0 |    0 |       1 |            120 |                    1/0 |     0.330 | Fused in Pipeline 0 |
+-----------------+----+--------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 13, total allocated memory: 216

Detach Delete

The DetachDelete operator is used in all queries containing the DETACH DELETE clause, when deleting nodes and their relationships.

Example 105. DetachDelete

Query

PROFILE
MATCH (p:Person)
DETACH DELETE p

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Details  | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  |          |             14 |    0 |       0 |                |                        |           |                     |
| |                +----------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult     |          |             14 |    0 |       0 |                |                        |           |                     |
| |                +----------+----------------+------+---------+----------------+                        |           |                     |
| +DetachDelete    | p        |             14 |   14 |      41 |                |                        |           |                     |
| |                +----------+----------------+------+---------+----------------+                        |           |                     |
| +NodeByLabelScan | p:Person |             14 |   14 |      35 |            120 |                   21/0 |    12,439 | Fused in Pipeline 0 |
+------------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 112, total allocated memory: 200

Merge

The Merge operator will either read or create nodes and/or relationships.

If matches are found it will execute the provided ON MATCH operations foreach incoming row. If no matches are found instead nodes and relationships are created and all ON CREATE operations are run.

Example 106. Merge

Query

PROFILE
MERGE (p:Person {name: 'Andy'})
ON MATCH SET p.existed = true
ON CREATE SET p.existed = false

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+-------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                                                 | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+-------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults |                                                                         |              1 |    0 |       0 |                |                        |           |                     |
| |               +-------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult    |                                                                         |              1 |    0 |       0 |                |                        |           |                     |
| |               +-------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Merge          | CREATE (p:Person {name: $autostring_0}), ON MATCH SET p.existed = true, |              1 |    1 |       2 |                |                        |           |                     |
| |               | ON CREATE SET p.existed = false                                         |                |      |         |                |                        |           |                     |
| |               +-------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +NodeIndexSeek  | RANGE INDEX p:Person(name) WHERE name = $autostring_0                   |              1 |    1 |       2 |            120 |                    2/1 |     0.749 | Fused in Pipeline 0 |
+-----------------+-------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 4, total allocated memory: 184

Locking Merge

The LockingMerge operator is similar to the Merge operator but will lock the start and end node when creating a relationship if necessary.

Example 107. LockingMerge

Query

PROFILE
MATCH (s:Person {name: 'me'})
MERGE (s)-[:FRIENDS_WITH]->(s)

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Id | Details                                               | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults |  0 |                                                       |              1 |    0 |       0 |                |                        |           |                     |
| |               +----+-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult    |  1 |                                                       |              1 |    0 |       0 |                |                        |           |                     |
| |               +----+-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Apply          |  2 |                                                       |              1 |    1 |       0 |                |                        |           |                     |
| |\              +----+-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +LockingMerge |  3 | CREATE (s)-[anon_0:FRIENDS_WITH]->(s), LOCK(s)        |              1 |    1 |       1 |                |                        |           |                     |
| | |             +----+-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Expand(Into) |  4 | (s)-[anon_0:FRIENDS_WITH]->(s)                        |              0 |    0 |      10 |            904 |                        |           |                     |
| | |             +----+-------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument     |  5 | s                                                     |              1 |    3 |       0 |           2280 |                    2/0 |     0.460 | Fused in Pipeline 1 |
| |               +----+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeIndexSeek  |  6 | RANGE INDEX s:Person(name) WHERE name = $autostring_0 |              1 |    1 |       2 |            376 |                    1/0 |     0.211 | In Pipeline 0       |
+-----------------+----+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 15, total allocated memory: 2232

Foreach

The Foreach operator executes a nested loop between the left child operator and the right child operator. In an analogous manner to the Apply operator, it takes a row from the left-hand side and, using the Argument operator, provides it to the operator tree on the right-hand side. Foreach will yield all the rows coming in from the left-hand side; all results from the right-hand side are pulled in and discarded.

Example 108. Foreach

Query

PROFILE
FOREACH (value IN [1,2,3] | CREATE (:Person {age: value}))

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-----------------+---------------------------------------------------------+----------------+------+---------+------------------------+
| Operator        | Details                                                 | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-----------------+---------------------------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults |                                                         |              1 |    0 |       0 |                    0/0 |
| |               +---------------------------------------------------------+----------------+------+---------+------------------------+
| +EmptyResult    |                                                         |              1 |    0 |       0 |                    0/0 |
| |               +---------------------------------------------------------+----------------+------+---------+------------------------+
| +Foreach        | value IN [1, 2, 3], CREATE (anon_0:Person {age: value}) |              1 |    1 |       9 |                    0/0 |
+-----------------+---------------------------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 9, total allocated memory: 64

SubqueryForeach

SubqueryForeach works like the Foreachoperator but it is only used for executing subqueries.

Example 109. SubqueryForeach

Query

PROFILE
LOAD CSV FROM 'https://neo4j.com/docs/cypher-refcard/3.3/csv/artists.csv' AS line
CALL (line) {
  CREATE (a: Artist {name: line[0]})
}

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+------------------+----+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Id | Details                             | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+------------------+----+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  |  0 |                                     |             10 |    0 |       0 |              0 |                        |           |                     |
| |                +----+-------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult     |  1 |                                     |             10 |    0 |       0 |                |                    0/0 |     0.000 | Fused in Pipeline 3 |
| |                +----+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +SubqueryForeach |  2 |                                     |             10 |    4 |       0 |           4080 |                        |           |                     |
| |\               +----+-------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Create        |  3 | (a:Artist {name: line[$autoint_0]}) |             10 |    4 |      12 |                |                        |           |                     |
| | |              +----+-------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Argument      |  4 | line                                |             10 |    4 |       0 |           3472 |                    0/0 |     0.852 | Fused in Pipeline 2 |
| |                +----+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +LoadCSV         |  5 | line                                |             10 |    4 |       0 |            328 |                        |           | In Pipeline 1       |
+------------------+----+-------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 12, total allocated memory: 4928

TransactionForeach

TransactionForeach works like the Foreach operator but will commit the current transaction after a specified number of rows.

Example 110. TransactionForeach

Query

PROFILE
LOAD CSV FROM 'https://neo4j.com/docs/cypher-refcard/3.3/csv/artists.csv' AS line
CALL (line) {
  CREATE (a: Artist {name: line[0]})
} IN TRANSACTIONS OF 100 ROWS

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

++---------------------+----+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
 | Operator            | Id | Details                                          | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
 +---------------------+----+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
 | +ProduceResults     |  0 |                                                  |             10 |    0 |       0 |              0 |                        |           |                     |
 | |                   +----+--------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
 | +EmptyResult        |  1 |                                                  |             10 |    0 |       0 |                |                    0/0 |     0.000 | Fused in Pipeline 3 |
 | |                   +----+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
 | +TransactionForeach |  2 | IN TRANSACTIONS OF $autoint_1 ROWS ON ERROR FAIL |             10 |    4 |       0 |           4856 |                        |           |                     |
 | |\                  +----+--------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
 | | +Create           |  3 | (a:Artist {name: line[$autoint_0]})              |             10 |    4 |      12 |                |                        |           |                     |
 | | |                 +----+--------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
 | | +Argument         |  4 | line                                             |             10 |    4 |       0 |           3472 |                    0/0 |     0.712 | Fused in Pipeline 2 |
 | |                   +----+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
 | +LoadCSV            |  5 | line                                             |             10 |    4 |       0 |            328 |                        |           | In Pipeline 1       |
 +---------------------+----+--------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

 Total database accesses: 12, total allocated memory: 5704

Set Labels

The SetLabels operator is used when setting labels on a node.

Example 111. SetLabels

Query

PROFILE
MATCH (n)
SET n:Person

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details  | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults |          |             35 |    0 |       0 |                |                        |           |                     |
| |               +----------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult    |          |             35 |    0 |       0 |                |                        |           |                     |
| |               +----------+----------------+------+---------+----------------+                        |           |                     |
| +SetLabels      | n:Person |             35 |   35 |      22 |                |                        |           |                     |
| |               +----------+----------------+------+---------+----------------+                        |           |                     |
| +AllNodesScan   | n        |             35 |   35 |      36 |            120 |                    3/0 |     0.873 | Fused in Pipeline 0 |
+-----------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 58, total allocated memory: 184

Remove Labels

The RemoveLabels operator is used when deleting labels from a node.

Example 112. RemoveLabels

Query

PROFILE
MATCH (n)
REMOVE n:Person

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details  | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults |          |             35 |    0 |       0 |                |                        |           |                     |
| |               +----------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult    |          |             35 |    0 |       0 |                |                        |           |                     |
| |               +----------+----------------+------+---------+----------------+                        |           |                     |
| +RemoveLabels   | n:Person |             35 |   35 |      15 |                |                        |           |                     |
| |               +----------+----------------+------+---------+----------------+                        |           |                     |
| +AllNodesScan   | n        |             35 |   35 |      36 |            120 |                    3/0 |     0.765 | Fused in Pipeline 0 |
+-----------------+----------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 51, total allocated memory: 184

Set Node Properties From Map

The SetNodePropertiesFromMap operator is used when setting properties from a map on a node.

Example 113. SetNodePropertiesFromMap

Query

PROFILE
MATCH (n)
SET n = {weekday: 'Monday', meal: 'Lunch'}

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------------+---------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                  | Details                                           | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+---------------------------+---------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults           |                                                   |             35 |    0 |       0 |                |                        |           |                     |
| |                         +---------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult              |                                                   |             35 |    0 |       0 |                |                        |           |                     |
| |                         +---------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +SetNodePropertiesFromMap | n = {weekday: $autostring_0, meal: $autostring_1} |             35 |   35 |     105 |                |                        |           |                     |
| |                         +---------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +AllNodesScan             | n                                                 |             35 |   35 |      36 |            120 |                    5/0 |     3.954 | Fused in Pipeline 0 |
+---------------------------+---------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 141, total allocated memory: 184

Set Relationship Properties From Map

The SetRelationshipPropertiesFromMap operator is used when setting properties from a map on a relationship.

Example 114. SetRelationshipPropertiesFromMap

Query

PROFILE
MATCH (n)-[r]->(m)
SET r = {weight: 5, unit: 'kg'}

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------------------------+-----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                          | Details                                       | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------------------------+-----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                   |                                               |             28 |    0 |       0 |                |                        |           |                     |
| |                                 +-----------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult                      |                                               |             28 |    0 |       0 |                |                        |           |                     |
| |                                 +-----------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +SetRelationshipPropertiesFromMap | r = {weight: $autoint_0, unit: $autostring_1} |             28 |   28 |      84 |                |                        |           |                     |
| |                                 +-----------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +DirectedAllRelationshipsScan     | (n)-[r]->(m)                                  |             28 |   28 |      28 |            120 |                    5/0 |    15.278 | Fused in Pipeline 0 |
+-----------------------------------+-----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 112, total allocated memory: 184

Set Property

The SetProperty operator is used when setting a property on a node or relationship.

Example 115. SetProperty

Query

PROFILE
MATCH (n)
SET n.checked = true

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details          | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults |                  |             35 |    0 |       0 |                |                        |           |                     |
| |               +------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult    |                  |             35 |    0 |       0 |                |                        |           |                     |
| |               +------------------+----------------+------+---------+----------------+                        |           |                     |
| +SetProperty    | n.checked = true |             35 |   35 |      70 |                |                        |           |                     |
| |               +------------------+----------------+------+---------+----------------+                        |           |                     |
| +AllNodesScan   | n                |             35 |   35 |      36 |            120 |                    3/0 |     0.753 | Fused in Pipeline 0 |
+-----------------+------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 106, total allocated memory: 184

Set Properties

The SetProperties operator is used when setting multiple properties on a node or relationship.

Example 116. SetProperties

Query

PROFILE
MATCH (n)
SET n.weekDay = 'Monday', n.meal = 'Lunch'

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+----+---------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Id | Details                                           | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+-----------------+----+---------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults |  0 |                                                   |             35 |    0 |       0 |              0 |                        |           |                     |
| |               +----+---------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult    |  1 |                                                   |             35 |    0 |       0 |                |                        |           |                     |
| |               +----+---------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +SetProperties  |  2 | n.weekDay = $autostring_0, n.meal = $autostring_1 |             35 |   35 |     105 |                |                        |           |                     |
| |               +----+---------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +AllNodesScan   |  3 | n                                                 |             35 |   35 |      36 |            248 |                    3/0 |   152.289 | Fused in Pipeline 0 |
+-----------------+----+---------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 141, total allocated memory: 312

Load CSV

The LoadCSV operator loads data from a CSV source into the query. It is used whenever the LOAD CSV clause is used in a query.

Example 117. LoadCSV

Query

PROFILE
LOAD CSV FROM 'https://neo4j.com/docs/cypher-refcard/3.3/csv/artists.csv' AS line
RETURN line

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+-----------------+---------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| Operator        | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline      |
+-----------------+---------+----------------+------+---------+----------------+------------------------+-----------+---------------+
| +ProduceResults | line    |             10 |    4 |       0 |                |                    0/0 |     0.210 |               |
| |               +---------+----------------+------+---------+----------------+------------------------+-----------+               |
| +LoadCSV        | line    |             10 |    4 |       0 |             72 |                        |           | In Pipeline 1 |
+-----------------+---------+----------------+------+---------+----------------+------------------------+-----------+---------------+

Total database accesses: 0, total allocated memory: 184

Eager

The Eager operator causes all preceding operators to execute fully, for the whole dataset, before continuing execution. This is done to ensure isolation between parts of the query plan that might otherwise affect each other.

Values from the graph are fetched in a lazy manner; i.e. a pattern matching might not be fully exhausted before updates are applied. To maintain correct semantics, the query planner will insert Eager operators into the query plan to prevent updates from influencing pattern matching, or other read operations. This scenario is exemplified by the query below, where the DELETE clause would otherwise influence both the MATCH clause and the MERGE clause. For more information on how the Eager operator can ensure correct semantics, see the section on Clause composition.

The Eager operator can cause high memory usage when importing data or migrating graph structures. In such cases, the operations should be split into simpler steps; e.g. importing nodes and relationships separately. Alternatively, the records to be updated can be returned, followed by an update statement.

Example 118. Eager

Query

PROFILE
MATCH (a:Person {name: 'me'}), (b:Person {name: 'Bob'})
DETACH DELETE a, b
MERGE (:Person {name: 'me'})

Query Plan

Planner COST

Runtime PIPELINED

Runtime version 5.26

Batch size 128

+---------------------+----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator            | Id | Details                                                                                        | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline            |
+---------------------+----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults     |  0 |                                                                                                |              0 |    0 |       0 |              0 |                        |           |                     |
| |                   +----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +EmptyResult        |  1 |                                                                                                |              0 |    0 |       0 |                |                        |           |                     |
| |                   +----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Apply              |  2 |                                                                                                |              0 |    1 |       0 |                |                        |           |                     |
| |\                  +----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +Merge            |  3 | CREATE (anon_0:Person {name: $autostring_2})                                                   |              0 |    1 |       3 |                |                        |           |                     |
| | |                 +----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| | +NodeIndexSeek    |  4 | RANGE INDEX anon_0:Person(name) WHERE name = $autostring_2                                     |              0 |    0 |       1 |           3304 |                    1/0 |     0.663 | Fused in Pipeline 3 |
| |                   +----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Eager              |  5 | read/delete conflict for variable: anon_0 (Operator: 6 vs 4, and 1 more conflicting operators) |              0 |    1 |       0 |            360 |                    0/0 |     0.008 | In Pipeline 2       |
| |                   +----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +DetachDelete       |  6 | b                                                                                              |              0 |    1 |       4 |                |                        |           |                     |
| |                   +----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +DetachDelete       |  7 | a                                                                                              |              0 |    1 |       5 |                |                        |           |                     |
| |                   +----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           |                     |
| +Eager              |  8 | read/delete conflict for variable: b (Operator: 6 vs 10, and 1 more conflicting operators),    |              0 |    1 |       0 |            360 |                    1/0 |     0.226 | Fused in Pipeline 1 |
| |                   |    | read/set conflict for label: Person (Operator: 3 vs 10),                                       |                |      |         |                |                        |           |                     |
| |                   |    | read/set conflict for property: name (Operator: 3 vs 10)                                       |                |      |         |                |                        |           |                     |
| |                   +----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +MultiNodeIndexSeek |  9 | RANGE INDEX a:Person(name) WHERE name = $autostring_0,                                         |              0 |    1 |       4 |            376 |                    2/0 |     0.218 | In Pipeline 0       |
|                     |    | RANGE INDEX b:Person(name) WHERE name = $autostring_1                                          |                |      |         |                |                        |           |                     |
+---------------------+----+------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 17, total allocated memory: 4184

Assert Same Node

The AssertSameNode operator is used to ensure that no node property uniqueness constraints are violated in the slotted and interpreted runtime. The example looks for the presence of a team node with the supplied name and id, and if one does not exist, it will be created. Owing to the existence of two node property uniqueness constraints on :Team(name) and :Team(id), any node that would be found by the UniqueIndexSeek operator must be the very same node or the constraints would be violated.

Example 119. AssertSameNode

Query

PROFILE
CYPHER runtime=slotted
MERGE (t:Team {name: 'Engineering', id: 42})

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+---------------------------------+-------------------------------------------------------+----------------+------+---------+------------------------+
| Operator                        | Details                                               | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+---------------------------------+-------------------------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults                 |                                                       |              1 |    0 |       0 |                    0/0 |
| |                               +-------------------------------------------------------+----------------+------+---------+------------------------+
| +EmptyResult                    |                                                       |              1 |    0 |       0 |                    0/0 |
| |                               +-------------------------------------------------------+----------------+------+---------+------------------------+
| +Merge                          | CREATE (t:Team {name: $autostring_0, id: $autoint_1}) |              1 |    1 |       0 |                    0/0 |
| |                               +-------------------------------------------------------+----------------+------+---------+------------------------+
| +AssertSameNode                 | t                                                     |              0 |    1 |       0 |                    0/0 |
| |\                              +-------------------------------------------------------+----------------+------+---------+------------------------+
| | +NodeUniqueIndexSeek(Locking) | UNIQUE t:Team(id) WHERE id = $autoint_1               |              1 |    1 |       1 |                    0/1 |
| |                               +-------------------------------------------------------+----------------+------+---------+------------------------+
| +NodeUniqueIndexSeek(Locking)   | UNIQUE t:Team(name) WHERE name = $autostring_0        |              1 |    1 |       1 |                    0/1 |
+---------------------------------+-------------------------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 2, total allocated memory: 64

Assert Same Relationship

The AssertSameRelationship operator is used to ensure that no relationship property uniqueness constraints are violated in the slotted and interpreted runtime. The example looks for the presence of a WORKS_IN relationship with the supplied id and badgeNumber. If it can’t be found, then it will be created. Owing to the existence of two property uniqueness constraints on :WORKS_IN(id) and :WORKS_IN(badgeNumber), any relationship that would be found by the DirectedRelationshipUniqueIndexSeek operator must be the very same relationship or the constraints would be violated.

Example 120. AssertSameRelationship

Query

PROFILE
CYPHER runtime=slotted
MERGE (person)-[work:WORKS_IN {id: 0, badgeNumber: 4332}]->(location)

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-------------------------------------------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| Operator                                        | Id | Details                                                                                              | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-------------------------------------------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults                                 |  0 |                                                                                                      |              1 |    0 |       0 |                    0/0 |
| |                                               +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| +EmptyResult                                    |  1 |                                                                                                      |              1 |    0 |       0 |                    0/0 |
| |                                               +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| +Merge                                          |  2 | CREATE (person), (location), (person)-[work:WORKS_IN {id: $autoint_0, badgeNumber: $autoint_1}]->(lo |              1 |    1 |       0 |                    0/0 |
| |                                               |    | cation)                                                                                              |                |      |         |                        |
| |                                               +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| +AssertSameRelationship                         |  3 | work                                                                                                 |              0 |    1 |       0 |                    0/0 |
| |\                                              +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| | +DirectedRelationshipUniqueIndexSeek(Locking) |  4 | RANGE INDEX (person)-[work:WORKS_IN(badgeNumber)]->(location) WHERE badgeNumber = $autoint_1         |              1 |    1 |       1 |                    0/1 |
| |                                               +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| +DirectedRelationshipUniqueIndexSeek(Locking)   |  5 | RANGE INDEX (person)-[work:WORKS_IN(id)]->(location) WHERE id = $autoint_0                           |              1 |    1 |       1 |                    1/1 |
+-------------------------------------------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 2, total allocated memory: 64

Schema and system operators

The operators in this group manage the schema and system-level operations within the database. They handle tasks such as creating or dropping constraints and indexes, showing or terminating transactions, and listing the available procedures, functions, and settings.

Create Constraint

The CreateConstraint operator creates a constraint.

This constraint can have any of the available constraint types:

Property uniqueness constraints
Property existence constraints Enterprise Edition
Property type constraints Enterprise Edition
Node or relationship key constraints Enterprise Edition

The following query will create a property uniqueness constraint with the name uniqueness on the name property of nodes with the Country label.

Example 121. CreateConstraint

Query

PROFILE
CREATE CONSTRAINT uniqueness
FOR (c:Country) REQUIRE c.name is UNIQUE

Query Plan

Planner ADMINISTRATION

Runtime SCHEMA

Runtime version 5.26

+-------------------+------------------------------------------------------------------+
| Operator          | Details                                                          |
+-------------------+------------------------------------------------------------------+
| +CreateConstraint | CONSTRAINT uniqueness FOR (c:Country) REQUIRE (c.name) IS UNIQUE |
+-------------------+------------------------------------------------------------------+

Total database accesses: ?

Do Nothing If Exists (constraint)

To not get an error creating the same constraint twice, we use the DoNothingIfExists operator for constraints. This will make sure no other constraint with the given name or another constraint of the same type and schema already exists before the specific CreateConstraint operator creates the constraint. If it finds a constraint with the given name or with the same type and schema it will stop the execution and no new constraint is created. The following query will create a property uniqueness constraint with the name uniqueness on the name property of nodes with the Country label only if no constraint named uniqueness or property uniqueness constraint on (:Country {name}) already exists.

Example 122. DoNothingIfExists(CONSTRAINT)

Query

PROFILE
CREATE CONSTRAINT uniqueness IF NOT EXISTS
FOR (c:Country) REQUIRE c.name is UNIQUE

Query Plan

Planner ADMINISTRATION

Runtime SCHEMA

Runtime version 5.26

+--------------------------------+------------------------------------------------------------------+
| Operator                       | Details                                                          |
+--------------------------------+------------------------------------------------------------------+
| +CreateConstraint              | CONSTRAINT uniqueness FOR (c:Country) REQUIRE (c.name) IS UNIQUE |
| |                              +------------------------------------------------------------------+
| +DoNothingIfExists(CONSTRAINT) | CONSTRAINT uniqueness FOR (c:Country) REQUIRE (c.name) IS UNIQUE |
+--------------------------------+------------------------------------------------------------------+

Total database accesses: ?

Drop Constraint

The DropConstraint operator removes a constraint using the name of the constraint, no matter the type.

Example 123. DropConstraint

Query

PROFILE
DROP CONSTRAINT uniqueness IF EXISTS

Query Plan

Planner ADMINISTRATION

Runtime SCHEMA

Runtime version 5.26

+-----------------+---------------------------------+
| Operator        | Details                         |
+-----------------+---------------------------------+
| +DropConstraint | CONSTRAINT uniqueness IF EXISTS |
+-----------------+---------------------------------+

Total database accesses: ?

Show Constraints

The ShowConstraints operator lists constraints. It may include filtering on constraint type and can have either default or full output.

Example 124. ShowConstraints

Query

PROFILE
SHOW CONSTRAINTS

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+------------------+-------------------------------------------------------------------+----------------+------+---------+------------------------+
| Operator         | Details                                                           | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+------------------+-------------------------------------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults  | id, name, type, entityType, labelsOrTypes, properties, ownedIndex |             10 |    3 |       0 |                    0/0 |
| |                +-------------------------------------------------------------------+----------------+------+---------+------------------------+
| +ShowConstraints | allConstraints, defaultColumns                                    |             10 |    3 |       2 |                    0/0 |
+------------------+-------------------------------------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 2, total allocated memory: 64

Create Index

The CreateIndex operator creates an index.

This index can either be a fulltext, point, range, text, vector, or lookup index.

Example 125. CreateIndex

The following query will create an index with the name my_index on the name property of nodes with the Country label.

Query

PROFILE
CREATE INDEX my_index
FOR (c:Country) ON (c.name)

Query Plan

Planner ADMINISTRATION

Runtime SCHEMA

Runtime version 5.26

+--------------+-----------------------------------------------+
| Operator     | Details                                       |
+--------------+-----------------------------------------------+
| +CreateIndex | RANGE INDEX my_index FOR (:Country) ON (name) |
+--------------+-----------------------------------------------+

Total database accesses: ?

Do Nothing If Exists (index)

To not get an error creating the same index twice, we use the DoNothingIfExists operator for indexes. This will make sure no other index with the given name or schema already exists before the CreateIndex operator creates an index. If it finds an index with the given name or schema it will stop the execution and no new index is created. The following query will create an index with the name my_index on the since property of relationships with the KNOWS relationship type only if no such index already exists.

Example 126. DoNothingIfExists(INDEX)

Query

PROFILE
CREATE INDEX my_index IF NOT EXISTS
FOR ()-[k:KNOWS]-() ON (k.since)

Query Plan

Planner ADMINISTRATION

Runtime SCHEMA

Runtime version 5.26

+---------------------------+----------------------------------------------------+
| Operator                  | Details                                            |
+---------------------------+----------------------------------------------------+
| +CreateIndex              | RANGE INDEX my_index FOR ()-[:KNOWS]-() ON (since) |
| |                         +----------------------------------------------------+
| +DoNothingIfExists(INDEX) | RANGE INDEX my_index FOR ()-[:KNOWS]-() ON (since) |
+---------------------------+----------------------------------------------------+

Total database accesses: ?

Drop Index

The DropIndex operator removes an index using the name of the index.

Example 127. DropIndex

Query

PROFILE
DROP INDEX my_index

Query Plan

Planner ADMINISTRATION

Runtime SCHEMA

Runtime version 5.26

+------------+---------------+
| Operator   | Details       |
+------------+---------------+
| +DropIndex | INDEX my_index|
+------------+---------------+

Total database accesses: ?

Show Indexes

The ShowIndexes operator lists indexes. It may include filtering on index type and can have either default or full output.

Example 128. ShowIndexes

Query

PROFILE
SHOW INDEXES

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-----------------+-------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| Operator        | Details                                                                                         | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-----------------+-------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults | id, name, state, populationPercent, type, entityType, labelsOrTypes, properties, indexProvider, |             10 |    9 |       0 |                    0/0 |
| |               | owningConstraint                                                                                |                |      |         |                        |
| |               +-------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| +ShowIndexes    | allIndexes, defaultColumns                                                                      |             10 |    9 |       2 |                    0/0 |
+-----------------+-------------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 2, total allocated memory: 64

Show Functions

The ShowFunctions operator lists functions. It may include filtering on built-in vs user-defined functions as well as if a given user can execute the function. The output can either be default or full output.

Example 129. ShowFunctions

Query

PROFILE
SHOW FUNCTIONS

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-----------------+-----------------------------------------------------+----------------+------+---------+------------------------+
| Operator        | Details                                             | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-----------------+-----------------------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults | name, category, description                         |             10 |  147 |       0 |                    0/0 |
| |               +-----------------------------------------------------+----------------+------+---------+------------------------+
| +ShowFunctions  | allFunctions, functionsForUser(all), defaultColumns |             10 |  147 |       0 |                    0/0 |
+-----------------+-----------------------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 0, total allocated memory: 64

Show Procedures

The ShowProcedures operator lists procedures. It may include filtering on whether a given user can execute the procedure and can have either default or full output.

Example 130. ShowProcedures

Query

PROFILE
SHOW PROCEDURES

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-----------------+----------------------------------------+----------------+------+---------+------------------------+
| Operator        | Details                                | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-----------------+----------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults | name, description, mode, worksOnSystem |             10 |   55 |       0 |                    0/0 |
| |               +----------------------------------------+----------------+------+---------+------------------------+
| +ShowProcedures | proceduresForUser(all), defaultColumns |             10 |   55 |       0 |                    0/0 |
+-----------------+----------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 0, total allocated memory: 64

Show Settings

The ShowSettings operator lists configuration settings.

Example 131. ShowSettings

Query

PROFILE
SHOW SETTINGS

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-----------------+---------------------------------------------------+----------------+------+---------+------------------------+
| Operator        | Details                                           | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-----------------+---------------------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults | name, value, isDynamic, defaultValue, description |             10 |  264 |       0 |                    0/0 |
| |               +---------------------------------------------------+----------------+------+---------+------------------------+
| +ShowSettings   | allSettings, defaultColumns                       |             10 |  264 |       0 |                    0/0 |
+-----------------+---------------------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 0, total allocated memory: 64

Show Transactions

The ShowTransactions operator lists transactions. It may include filtering on given ids and can have either default or full output.

Example 132. ShowTransactions

Query

PROFILE
SHOW TRANSACTIONS

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+-------------------+-----------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| Operator          | Details                                                                                       | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+-------------------+-----------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults   | database, transactionId, currentQueryId, connectionId, clientAddress, username, currentQuery, |             10 |    1 |       0 |                    0/0 |
| |                 | startTime, status, elapsedTime                                                                |                |      |         |                        |
| |                 +-----------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+
| +ShowTransactions | defaultColumns, allTransactions                                                               |             10 |    1 |       0 |                    0/0 |
+-------------------+-----------------------------------------------------------------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 0, total allocated memory: 64

Terminate Transactions

The TerminateTransactions operator terminates transactions by ID.

Example 133. TerminateTransactions

Query

PROFILE
TERMINATE TRANSACTIONS 'database-transaction-123'

Query Plan

Planner COST

Runtime SLOTTED

Runtime version 5.26

+------------------------+--------------------------------------------------------+----------------+------+---------+------------------------+
| Operator               | Details                                                | Estimated Rows | Rows | DB Hits | Page Cache Hits/Misses |
+------------------------+--------------------------------------------------------+----------------+------+---------+------------------------+
| +ProduceResults        | transactionId, username, message                       |             10 |    1 |       0 |                    0/0 |
| |                      +--------------------------------------------------------+----------------+------+---------+------------------------+
| +TerminateTransactions | defaultColumns, transactions(database-transaction-123) |             10 |    1 |       0 |                    0/0 |
+------------------------+--------------------------------------------------------+----------------+------+---------+------------------------+

Total database accesses: 0, total allocated memory: 64