3.7.4. Row operators

These operators take rows produced by another operator and transform them to a different set of rows

Operators:

3.7.4.1. Eager

For isolation purposes this operator makes sure that operations that affect subsequent operations are executed fully for the whole dataset before continuing execution. Otherwise it could trigger endless loops, matching data again, that was just created. The Eager operator can cause high memory usage when importing data or migrating graph structures. In such cases split up your operations into simpler steps e.g. you can import nodes and relationships separately. Alternatively return the records to be updated and run an update statement afterwards.

Query. 

MATCH (a)-[r]-(b)
DELETE r,a,b
MERGE ()

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+-------------------------+----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| Operator                | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables           | Other        |
+-------------------------+----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| +ProduceResults         |              1 |    0 |       0 |               0 |                 0 |               0.0000 | anon[38], a, b, r   |              |
| |                       +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| +EmptyResult            |              1 |    0 |       0 |            1440 |                 0 |               1.0000 | anon[38], a, b, r   |              |
| |                       +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| +Apply                  |              1 |  504 |       0 |            1440 |                 0 |               1.0000 | a, b, r -- anon[38] |              |
| |\                      +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| | +AntiConditionalApply |              1 |  504 |       0 |              20 |                 0 |               1.0000 | anon[38]            |              |
| | |\                    +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| | | +MergeCreateNode    |              1 |    0 |       0 |               0 |                 0 |               0.0000 | anon[38]            |              |
| | |                     +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| | +Optional             |             35 |  504 |       0 |              20 |                 0 |               1.0000 | anon[38]            |              |
| | |                     +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| | +AllNodesScan         |             35 |  504 |     540 |              40 |                 0 |               1.0000 | anon[38]            |              |
| |                       +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| +Eager                  |             36 |   36 |       0 |            1433 |                 0 |               1.0000 | a, b, r             |              |
| |                       +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| +Delete(3)              |             36 |   36 |      39 |             -21 |                 0 |               3.0000 | a, b, r             |              |
| |                       +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| +Eager                  |             36 |   36 |       0 |              34 |                 0 |               1.0000 | a, b, r             |              |
| |                       +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| +Expand(All)            |             36 |   36 |      71 |              41 |                 0 |               1.0000 | a, r -- b           | (b)-[r:]-(a) |
| |                       +----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+
| +AllNodesScan           |             35 |   35 |      36 |              42 |                 0 |               1.0000 | b                   |              |
+-------------------------+----------------+------+---------+-----------------+-------------------+----------------------+---------------------+--------------+

Total database accesses: 686

3.7.4.2. Distinct

Removes duplicate rows from the incoming stream of rows.

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+------------------+----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| Operator         | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables        | Other                |
+------------------+----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| +ProduceResults  |             14 |    6 |       0 |               2 |                 0 |               1.0000 | l                |                      |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| +Distinct        |             14 |    6 |       0 |               2 |                 0 |               1.0000 | l                | l                    |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| +Filter          |             15 |   15 |      15 |               2 |                 0 |               1.0000 | anon[19], l, p   | p:Person             |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| +Expand(All)     |             15 |   15 |      25 |               2 |                 0 |               1.0000 | anon[19], p -- l | (l)<-[:WORKS_IN]-(p) |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| +NodeByLabelScan |             10 |   10 |      11 |               3 |                 0 |               1.0000 | l                | :Location            |
+------------------+----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+

Total database accesses: 51

3.7.4.3. Eager Aggregation

Eagerly loads underlying results and stores it in a hash-map, using the grouping keys as the keys for the map.

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+-------------------+----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| Operator          | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables        | Other                |
+-------------------+----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| +ProduceResults   |              4 |    6 |       0 |               0 |                 0 |               0.0000 | location, people |                      |
| |                 +----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| +EagerAggregation |              4 |    6 |      30 |               3 |                 0 |               1.0000 | location, people | location             |
| |                 +----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| +Filter           |             15 |   15 |      15 |               3 |                 0 |               1.0000 | anon[19], l, p   | p:Person             |
| |                 +----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| +Expand(All)      |             15 |   15 |      25 |               3 |                 0 |               1.0000 | anon[19], p -- l | (l)<-[:WORKS_IN]-(p) |
| |                 +----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+
| +NodeByLabelScan  |             10 |   10 |      11 |               4 |                 0 |               1.0000 | l                | :Location            |
+-------------------+----------------+------+---------+-----------------+-------------------+----------------------+------------------+----------------------+

Total database accesses: 81

3.7.4.4. Node Count From Count Store

Use the count store to answer questions about node counts. This is much faster than eager aggregation which achieves the same result by actually counting. However the count store only saves a limited range of combinations, so eager aggregation 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.

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+--------------------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------------------------------+
| Operator                 | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables | Other                              |
+--------------------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------------------------------+
| +ProduceResults          |              4 |    1 |       0 |               0 |                 0 |               0.0000 | people    |                                    |
| |                        +----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------------------------------+
| +NodeCountFromCountStore |              4 |    1 |       1 |               0 |                 0 |               0.0000 | people    | count( (:Some(Person)) ) AS people |
+--------------------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------------------------------+

Total database accesses: 1

3.7.4.5. Relationship Count From Count Store

Use the count store to answer questions about relationship counts. This is much faster than eager aggregation which achieves the same result by actually counting. However the count store only saves a limited range of combinations, so eager aggregation 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.

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+----------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+--------------------------------------------+
| Operator                         | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables | Other                                      |
+----------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+--------------------------------------------+
| +ProduceResults                  |              4 |    1 |       0 |               0 |                 0 |               0.0000 | jobs      |                                            |
| |                                +----------------+------+---------+-----------------+-------------------+----------------------+-----------+--------------------------------------------+
| +RelationshipCountFromCountStore |              4 |    1 |       2 |               0 |                 0 |               0.0000 | jobs      | count( (:Person)-[:WORKS_IN]->() ) AS jobs |
+----------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+--------------------------------------------+

Total database accesses: 2

3.7.4.6. Filter

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

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+---------------------------+
| Operator        | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables | Other                     |
+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+---------------------------+
| +ProduceResults |             14 |    0 |       0 |               2 |                 0 |               1.0000 | p         |                           |
| |               +----------------+------+---------+-----------------+-------------------+----------------------+-----------+---------------------------+
| +Filter         |             14 |    0 |      14 |               2 |                 0 |               1.0000 | p         | p.name =~ {  AUTOSTRING0} |
| |               +----------------+------+---------+-----------------+-------------------+----------------------+-----------+---------------------------+
| +NodeIndexScan  |             14 |   14 |      15 |               2 |                 1 |               0.6667 | p         | :Person(name)             |
+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+---------------------------+

Total database accesses: 29

3.7.4.7. Limit

Returns the first 'n' rows from the incoming input.

Query. 

MATCH (p:Person)
RETURN p
LIMIT 3

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+------------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+---------+
| Operator         | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables | Other   |
+------------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+---------+
| +ProduceResults  |              3 |    3 |       0 |               0 |                 0 |               0.0000 | p         |         |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+-----------+---------+
| +Limit           |              3 |    3 |       0 |               0 |                 0 |               0.0000 | p         | 3       |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+-----------+---------+
| +NodeByLabelScan |             14 |    3 |       4 |               0 |                 0 |               0.0000 | p         | :Person |
+------------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+---------+

Total database accesses: 4

3.7.4.8. Projection

For each row from its input, projection evaluates a set of expressions and produces a row with the results of the expressions.

Query. 

RETURN 'hello' AS greeting

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------------------------+
| Operator        | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables | Other                        |
+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------------------------+
| +ProduceResults |              1 |    1 |       0 |               0 |                 0 |               0.0000 | greeting  |                              |
| |               +----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------------------------+
| +Projection     |              1 |    1 |       0 |               0 |                 0 |               0.0000 | greeting  | {greeting : {  AUTOSTRING0}} |
+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------------------------+

Total database accesses: 0

3.7.4.9. Skip

Skips 'n' rows from the incoming rows.

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+---------------+
| Operator         | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables                  | Other         |
+------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+---------------+
| +ProduceResults  |             14 |   13 |       0 |               0 |                 0 |               0.0000 | anon[35], anon[59], p, p   |               |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+---------------+
| +Projection      |             14 |   13 |       0 |               0 |                 0 |               0.0000 | p -- anon[35], anon[59], p | {p : }        |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+---------------+
| +Skip            |             14 |   13 |       0 |               0 |                 0 |               0.0000 | anon[35], anon[59], p      | {  AUTOINT0}  |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+---------------+
| +Sort            |             14 |   14 |       0 |               0 |                 0 |               0.0000 | anon[35], anon[59], p      | anon[59]      |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+---------------+
| +Projection      |             14 |   14 |      14 |               0 |                 0 |               0.0000 | anon[59] -- anon[35], p    | { : ,  : .id} |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+---------------+
| +Projection      |             14 |   14 |       0 |               0 |                 0 |               0.0000 | anon[35] -- p              | { :   p@7}    |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+---------------+
| +NodeByLabelScan |             14 |   14 |      15 |               1 |                 0 |               1.0000 | p                          | :Person       |
+------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+---------------+

Total database accesses: 29

3.7.4.10. Sort

Sorts rows by a provided key.

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| Operator         | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables                  | Other           |
+------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +ProduceResults  |             14 |   14 |       0 |               0 |                 0 |               0.0000 | anon[24], anon[37], p, p   |                 |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +Projection      |             14 |   14 |       0 |               0 |                 0 |               0.0000 | p -- anon[24], anon[37], p | {p : }          |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +Sort            |             14 |   14 |       0 |               2 |                 0 |               1.0000 | anon[24], anon[37], p      | anon[37]        |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +Projection      |             14 |   14 |      14 |               2 |                 0 |               1.0000 | anon[37] -- anon[24], p    | { : ,  : .name} |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +Projection      |             14 |   14 |       0 |               2 |                 0 |               1.0000 | anon[24] -- p              | { :   p@7}      |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +NodeByLabelScan |             14 |   14 |      15 |               3 |                 0 |               1.0000 | p                          | :Person         |
+------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+

Total database accesses: 29

3.7.4.11. Top

Returns the first 'n' rows sorted by a provided key. The physical operator is called Top. Instead of sorting the whole input, only the top X rows are kept.

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| Operator         | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables                  | Other           |
+------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +ProduceResults  |              2 |    2 |       0 |               0 |                 0 |               0.0000 | anon[24], anon[37], p, p   |                 |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +Projection      |              2 |    2 |       0 |               0 |                 0 |               0.0000 | p -- anon[24], anon[37], p | {p : }          |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +Top             |              2 |    2 |       0 |               2 |                 0 |               1.0000 | anon[24], anon[37], p      | anon[37]; 2     |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +Projection      |             14 |   14 |      14 |               2 |                 0 |               1.0000 | anon[37] -- anon[24], p    | { : ,  : .name} |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +Projection      |             14 |   14 |       0 |               2 |                 0 |               1.0000 | anon[24] -- p              | { :   p@7}      |
| |                +----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+
| +NodeByLabelScan |             14 |   14 |      15 |               3 |                 0 |               1.0000 | p                          | :Person         |
+------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------------+-----------------+

Total database accesses: 29

3.7.4.12. Union

Union concatenates the results from the right plan after the results of the left plan.

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+--------------------+----------------+------+---------+-----------------+-------------------+----------------------+-------------+------------------------+
| Operator           | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables   | Other                  |
+--------------------+----------------+------+---------+-----------------+-------------------+----------------------+-------------+------------------------+
| +ProduceResults    |             10 |   11 |       0 |               3 |                 0 |               1.0000 | p.name      |                        |
| |                  +----------------+------+---------+-----------------+-------------------+----------------------+-------------+------------------------+
| +Union             |             10 |   11 |       0 |               4 |                 0 |               1.0000 | p.name      |                        |
| |\                 +----------------+------+---------+-----------------+-------------------+----------------------+-------------+------------------------+
| | +Projection      |              1 |    1 |       1 |               0 |                 0 |               0.0000 | p.name -- p | {p.name :   p@83.name} |
| | |                +----------------+------+---------+-----------------+-------------------+----------------------+-------------+------------------------+
| | +NodeByLabelScan |              1 |    1 |       2 |               1 |                 0 |               1.0000 | p           | :Country               |
| |                  +----------------+------+---------+-----------------+-------------------+----------------------+-------------+------------------------+
| +Projection        |             10 |   10 |      10 |               2 |                 0 |               1.0000 | p.name -- p | {p.name :   p@7.name}  |
| |                  +----------------+------+---------+-----------------+-------------------+----------------------+-------------+------------------------+
| +NodeByLabelScan   |             10 |   10 |      11 |               3 |                 0 |               1.0000 | p           | :Location              |
+--------------------+----------------+------+---------+-----------------+-------------------+----------------------+-------------+------------------------+

Total database accesses: 24

3.7.4.13. Unwind

Takes a list of values and returns one row per item in the list.

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------+
| Operator        | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables | Other      |
+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------+
| +ProduceResults |             10 |    5 |       0 |               0 |                 0 |               0.0000 | value     |            |
| |               +----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------+
| +Unwind         |             10 |    5 |       0 |               0 |                 0 |               0.0000 | value     | range(1,5) |
+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+------------+

Total database accesses: 0

3.7.4.14. Call Procedure

Return all labels sorted by name

Query. 

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

Query Plan. 

Compiler CYPHER 3.3

Planner COST

Runtime INTERPRETED

+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+----------------------------------+
| Operator        | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Variables | Other                            |
+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+----------------------------------+
| +ProduceResults |          10000 |    4 |       0 |               0 |                 0 |               0.0000 | label     |                                  |
| |               +----------------+------+---------+-----------------+-------------------+----------------------+-----------+----------------------------------+
| +Sort           |          10000 |    4 |       0 |               0 |                 0 |               0.0000 | label     | label                            |
| |               +----------------+------+---------+-----------------+-------------------+----------------------+-----------+----------------------------------+
| +ProcedureCall  |          10000 |    4 |       1 |               0 |                 0 |               0.0000 | label     | db.labels() :: (label :: String) |
+-----------------+----------------+------+---------+-----------------+-------------------+----------------------+-----------+----------------------------------+

Total database accesses: 1