16.3. Combining operators
Node Hash Join
Using a hash table, a node hash join joins the inputs coming from the left with the inputs coming from the right. The join key is specified in the arguments of the operator.
Query
MATCH (andy:Person { name:'Andreas' })-[:WORKS_IN]->(loc)<-[:WORKS_IN]-(matt:Person { name:'Mattis' }) RETURN loc
Query Plan
+------------------+----------------+------+---------+-------------------------------------+---------------------------+ | Operator | Estimated Rows | Rows | DB Hits | Identifiers | Other | +------------------+----------------+------+---------+-------------------------------------+---------------------------+ | +ProduceResults | 35 | 0 | 0 | loc | loc | | | +----------------+------+---------+-------------------------------------+---------------------------+ | +Filter | 35 | 0 | 0 | anon[37], anon[56], andy, loc, matt | NOT(anon[37] == anon[56]) | | | +----------------+------+---------+-------------------------------------+---------------------------+ | +NodeHashJoin | 35 | 0 | 0 | anon[37], anon[56], andy, loc, matt | loc | | |\ +----------------+------+---------+-------------------------------------+---------------------------+ | | +Expand(All) | 35 | 0 | 0 | anon[56], loc, matt | (matt)-[:WORKS_IN]->(loc) | | | | +----------------+------+---------+-------------------------------------+---------------------------+ | | +NodeIndexSeek | 1 | 0 | 1 | matt | :Person(name) | | | +----------------+------+---------+-------------------------------------+---------------------------+ | +Expand(All) | 35 | 0 | 1 | anon[37], andy, loc | (andy)-[:WORKS_IN]->(loc) | | | +----------------+------+---------+-------------------------------------+---------------------------+ | +NodeIndexSeek | 1 | 1 | 2 | andy | :Person(name) | +------------------+----------------+------+---------+-------------------------------------+---------------------------+ Total database accesses: 4
Apply
Apply works by performing a nested loop. Every row being produced on the left hand side of the Apply operator will be fed to the Argument operator on the right hand side, and then Apply will yield the results coming from the RHS. Apply, being a nested loop, can be seen as a warning that a better plan was not found.
Query
MATCH (p:Person)-[:FRIENDS_WITH]->(f) WITH p, count(f) AS fs WHERE fs > 0 OPTIONAL MATCH (p)-[:WORKS_IN*1..2]->(city) RETURN p, city
Query Plan
+---------------------------+----------------+------+---------+----------------------------------+--------------------------+
| Operator | Estimated Rows | Rows | DB Hits | Identifiers | Other |
+---------------------------+----------------+------+---------+----------------------------------+--------------------------+
| +ProduceResults | 1 | 2 | 0 | city, p | p, city |
| | +----------------+------+---------+----------------------------------+--------------------------+
| +Apply | 1 | 2 | 0 | anon[92], anon[126], city, fs, p | |
| |\ +----------------+------+---------+----------------------------------+--------------------------+
| | +Apply | 1 | 2 | 0 | anon[92], anon[126], city, fs, p | |
| | |\ +----------------+------+---------+----------------------------------+--------------------------+
| | | +Optional | 1 | 2 | 0 | anon[126], city, p | |
| | | | +----------------+------+---------+----------------------------------+--------------------------+
| | | +VarLengthExpand(All) | 1 | 2 | 6 | anon[126], city, p | (p)-[:WORKS_IN*]->(city) |
| | | | +----------------+------+---------+----------------------------------+--------------------------+
| | | +Argument | 1 | 2 | 0 | p | |
| | | +----------------+------+---------+----------------------------------+--------------------------+
| | +Filter | 1 | 2 | 0 | anon[92], fs, p | anon[92] |
| | | +----------------+------+---------+----------------------------------+--------------------------+
| | +Argument | 1 | 2 | 0 | anon[92], fs, p | |
| | +----------------+------+---------+----------------------------------+--------------------------+
| +Projection | 1 | 2 | 0 | anon[92], fs, p | p; fs; fs > { AUTOINT0} |
| | +----------------+------+---------+----------------------------------+--------------------------+
| +EagerAggregation | 1 | 2 | 0 | fs, p | p |
| | +----------------+------+---------+----------------------------------+--------------------------+
| +Expand(All) | 2 | 2 | 16 | anon[17], f, p | (p)-[:FRIENDS_WITH]->(f) |
| | +----------------+------+---------+----------------------------------+--------------------------+
| +NodeByLabelScan | 14 | 14 | 15 | p | :Person |
+---------------------------+----------------+------+---------+----------------------------------+--------------------------+
Total database accesses: 37
Anti Semi Apply
Tests for the absence of a pattern predicate.
A pattern predicate that is prepended by NOT is solved with AntiSemiApply.
Query
MATCH (me:Person { name: "me" }),(other:Person) WHERE NOT (me)-[:FRIENDS_WITH]->(other) RETURN other
Query Plan
+--------------------+----------------+------+---------+---------------------+-------------------------------+ | Operator | Estimated Rows | Rows | DB Hits | Identifiers | Other | +--------------------+----------------+------+---------+---------------------+-------------------------------+ | +ProduceResults | 4 | 13 | 0 | other | other | | | +----------------+------+---------+---------------------+-------------------------------+ | +AntiSemiApply | 4 | 13 | 0 | me, other | | | |\ +----------------+------+---------+---------------------+-------------------------------+ | | +Expand(Into) | 0 | 0 | 50 | anon[73], me, other | (me)-[:FRIENDS_WITH]->(other) | | | | +----------------+------+---------+---------------------+-------------------------------+ | | +Argument | 14 | 14 | 0 | me, other | | | | +----------------+------+---------+---------------------+-------------------------------+ | +CartesianProduct | 14 | 14 | 0 | me, other | | | |\ +----------------+------+---------+---------------------+-------------------------------+ | | +NodeByLabelScan | 14 | 14 | 15 | other | :Person | | | +----------------+------+---------+---------------------+-------------------------------+ | +NodeIndexSeek | 1 | 1 | 2 | me | :Person(name) | +--------------------+----------------+------+---------+---------------------+-------------------------------+ Total database accesses: 67
Let Anti Semi Apply
Tests for the absence of a pattern predicate.
When a query contains multiple pattern predicates 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.
The following query will find all the people who don’t have anyfriend or who work somewhere. The LetSemiApply operator
will be used to check for the absence of the FRIENDS_WITH relationship from each person.
Query
MATCH (other:Person) WHERE NOT ((other)-[:FRIENDS_WITH]->()) OR (other)-[:WORKS_IN]->() RETURN other
Query Plan
+--------------------+----------------+------+---------+---------------------------+-----------------------------+ | Operator | Estimated Rows | Rows | DB Hits | Identifiers | Other | +--------------------+----------------+------+---------+---------------------------+-----------------------------+ | +ProduceResults | 11 | 14 | 0 | other | other | | | +----------------+------+---------+---------------------------+-----------------------------+ | +SelectOrSemiApply | 11 | 14 | 0 | anon[42], other | anon[42] | | |\ +----------------+------+---------+---------------------------+-----------------------------+ | | +Expand(All) | 15 | 0 | 2 | anon[82], anon[96], other | (other)-[:WORKS_IN]->() | | | | +----------------+------+---------+---------------------------+-----------------------------+ | | +Argument | 14 | 2 | 0 | other | | | | +----------------+------+---------+---------------------------+-----------------------------+ | +LetAntiSemiApply | 14 | 14 | 0 | anon[42], other | | | |\ +----------------+------+---------+---------------------------+-----------------------------+ | | +Expand(All) | 2 | 0 | 14 | anon[50], anon[68], other | (other)-[:FRIENDS_WITH]->() | | | | +----------------+------+---------+---------------------------+-----------------------------+ | | +Argument | 14 | 14 | 0 | other | | | | +----------------+------+---------+---------------------------+-----------------------------+ | +NodeByLabelScan | 14 | 14 | 15 | other | :Person | +--------------------+----------------+------+---------+---------------------------+-----------------------------+ Total database accesses: 31
Let Semi Apply
Tests for the existence of a pattern predicate.
When a query contains multiple pattern predicates 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 a another operator.
The following query will find all the people who have a friend or who work somewhere. The LetSemiApply operator
will be used to check for the existence of the FRIENDS_WITH relationship from each person.
Query
MATCH (other:Person) WHERE (other)-[:FRIENDS_WITH]->() OR (other)-[:WORKS_IN]->() RETURN other
Query Plan
+--------------------+----------------+------+---------+---------------------------+-----------------------------+ | Operator | Estimated Rows | Rows | DB Hits | Identifiers | Other | +--------------------+----------------+------+---------+---------------------------+-----------------------------+ | +ProduceResults | 13 | 14 | 0 | other | other | | | +----------------+------+---------+---------------------------+-----------------------------+ | +SelectOrSemiApply | 13 | 14 | 0 | anon[38], other | anon[38] | | |\ +----------------+------+---------+---------------------------+-----------------------------+ | | +Expand(All) | 15 | 0 | 12 | anon[77], anon[91], other | (other)-[:WORKS_IN]->() | | | | +----------------+------+---------+---------------------------+-----------------------------+ | | +Argument | 14 | 12 | 0 | other | | | | +----------------+------+---------+---------------------------+-----------------------------+ | +LetSemiApply | 14 | 14 | 0 | anon[38], other | | | |\ +----------------+------+---------+---------------------------+-----------------------------+ | | +Expand(All) | 2 | 0 | 14 | anon[46], anon[64], other | (other)-[:FRIENDS_WITH]->() | | | | +----------------+------+---------+---------------------------+-----------------------------+ | | +Argument | 14 | 14 | 0 | other | | | | +----------------+------+---------+---------------------------+-----------------------------+ | +NodeByLabelScan | 14 | 14 | 15 | other | :Person | +--------------------+----------------+------+---------+---------------------------+-----------------------------+ Total database accesses: 41
Select Or Anti Semi Apply
Tests for the absence of a pattern predicate and evaluates a predicate.
Query
MATCH (other:Person) WHERE other.age > 25 OR NOT (other)-[:FRIENDS_WITH]->() RETURN other
Query Plan
+------------------------+----------------+------+---------+---------------------------+-----------------------------+
| Operator | Estimated Rows | Rows | DB Hits | Identifiers | Other |
+------------------------+----------------+------+---------+---------------------------+-----------------------------+
| +ProduceResults | 4 | 12 | 0 | other | other |
| | +----------------+------+---------+---------------------------+-----------------------------+
| +SelectOrAntiSemiApply | 4 | 12 | 28 | other | other.age > { AUTOINT0} |
| |\ +----------------+------+---------+---------------------------+-----------------------------+
| | +Expand(All) | 2 | 0 | 14 | anon[68], anon[86], other | (other)-[:FRIENDS_WITH]->() |
| | | +----------------+------+---------+---------------------------+-----------------------------+
| | +Argument | 14 | 14 | 0 | other | |
| | +----------------+------+---------+---------------------------+-----------------------------+
| +NodeByLabelScan | 14 | 14 | 15 | other | :Person |
+------------------------+----------------+------+---------+---------------------------+-----------------------------+
Total database accesses: 57
Select Or Semi Apply
Tests for the existence of a pattern predicate and evaluates a predicate.
This operator allows for the mixing of normal predicates and pattern predicates that check for the existing of a pattern.
First the normal expression predicate is evaluated, and only if it returns FALSE the costly pattern predicate evaluation is performed.
Query
MATCH (other:Person) WHERE other.age > 25 OR (other)-[:FRIENDS_WITH]->() RETURN other
Query Plan
+--------------------+----------------+------+---------+---------------------------+-----------------------------+
| Operator | Estimated Rows | Rows | DB Hits | Identifiers | Other |
+--------------------+----------------+------+---------+---------------------------+-----------------------------+
| +ProduceResults | 11 | 2 | 0 | other | other |
| | +----------------+------+---------+---------------------------+-----------------------------+
| +SelectOrSemiApply | 11 | 2 | 28 | other | other.age > { AUTOINT0} |
| |\ +----------------+------+---------+---------------------------+-----------------------------+
| | +Expand(All) | 2 | 0 | 14 | anon[64], anon[82], other | (other)-[:FRIENDS_WITH]->() |
| | | +----------------+------+---------+---------------------------+-----------------------------+
| | +Argument | 14 | 14 | 0 | other | |
| | +----------------+------+---------+---------------------------+-----------------------------+
| +NodeByLabelScan | 14 | 14 | 15 | other | :Person |
+--------------------+----------------+------+---------+---------------------------+-----------------------------+
Total database accesses: 57
Semi Apply
Tests for the existence of a pattern predicate.
SemiApply takes a row from it’s child operator and feeds it to the Argument operator on the right hand side of SemiApply.
If the right hand side operator tree yields at least one row, the row from the left hand side is yielded by the SemiApply operator.
This makes SemiApply a filtering operator, used mostly for pattern predicates in queries.
Query
MATCH (other:Person) WHERE (other)-[:FRIENDS_WITH]->() RETURN other
Query Plan
+------------------+----------------+------+---------+---------------------------+-----------------------------+ | Operator | Estimated Rows | Rows | DB Hits | Identifiers | Other | +------------------+----------------+------+---------+---------------------------+-----------------------------+ | +ProduceResults | 11 | 2 | 0 | other | other | | | +----------------+------+---------+---------------------------+-----------------------------+ | +SemiApply | 11 | 2 | 0 | other | | | |\ +----------------+------+---------+---------------------------+-----------------------------+ | | +Expand(All) | 2 | 0 | 14 | anon[46], anon[64], other | (other)-[:FRIENDS_WITH]->() | | | | +----------------+------+---------+---------------------------+-----------------------------+ | | +Argument | 14 | 14 | 0 | other | | | | +----------------+------+---------+---------------------------+-----------------------------+ | +NodeByLabelScan | 14 | 14 | 15 | other | :Person | +------------------+----------------+------+---------+---------------------------+-----------------------------+ Total database accesses: 29
Triadic
Triadic is used to solve triangular queries, such as the very common "find my friend-of-friends that are not already my friend". It does so by putting all the "friends" in a set, and use that set to check if the friend-of-friends are already connected to me.
Query
MATCH (me:Person)-[:FRIENDS_WITH]-()-[:FRIENDS_WITH]-(other) WHERE NOT (me)-[:FRIENDS_WITH]-(other) RETURN other
Query Plan
+-------------------+----------------+------+---------+-----------------------------------------+----------------------------+ | Operator | Estimated Rows | Rows | DB Hits | Identifiers | Other | +-------------------+----------------+------+---------+-----------------------------------------+----------------------------+ | +ProduceResults | 0 | 2 | 0 | other | other | | | +----------------+------+---------+-----------------------------------------+----------------------------+ | +TriadicSelection | 0 | 2 | 0 | anon[18], anon[35], anon[37], me, other | me, anon[35], other | | |\ +----------------+------+---------+-----------------------------------------+----------------------------+ | | +Filter | 0 | 2 | 0 | anon[18], anon[35], anon[37], me, other | NOT(anon[18] == anon[37]) | | | | +----------------+------+---------+-----------------------------------------+----------------------------+ | | +Expand(All) | 0 | 6 | 10 | anon[18], anon[35], anon[37], me, other | ()-[:FRIENDS_WITH]-(other) | | | | +----------------+------+---------+-----------------------------------------+----------------------------+ | | +Argument | 4 | 4 | 0 | anon[18], anon[35], me | | | | +----------------+------+---------+-----------------------------------------+----------------------------+ | +Expand(All) | 4 | 4 | 18 | anon[18], anon[35], me | (me)-[:FRIENDS_WITH]-() | | | +----------------+------+---------+-----------------------------------------+----------------------------+ | +NodeByLabelScan | 14 | 14 | 15 | me | :Person | +-------------------+----------------+------+---------+-----------------------------------------+----------------------------+ Total database accesses: 43