Planner hints and the USING keyword

Forcing planner behavior is an advanced feature, and should be used with caution by experienced developers and/or database administrators only, as it may cause queries to perform poorly.

Introduction

When executing a query, Neo4j needs to decide where in the query graph to start matching. This is done by looking at the MATCH clause and the WHERE conditions and using that information to find useful indexes, or other starting points.

However, the selected index might not always be the best choice. Sometimes multiple indexes are possible candidates, and the query planner picks the suboptimal one from a performance point of view. Moreover, in some circumstances (albeit rarely) it is better not to use an index at all.

Neo4j can be forced to use a specific starting point through the USING keyword. This is called giving a planner hint. There are four types of planner hints: index hints, scan hints, join hints, and the PERIODIC COMMIT query hint.

Query
MATCH (s:Scientist {born: 1850})-[:RESEARCHED]->(sc:Science)<-[i:INVENTED_BY {year: 560}]-(p:Pioneer {born: 525})-[:LIVES_IN]->(c:City)-[:PART_OF]->(cc:Country {formed: 411}) RETURN *

The query above will be used in some of the examples on this page. Without any hints, one index and no join is used.

Query plan
Compiler CYPHER 4.3

Planner COST

Runtime PIPELINED

Runtime version 4.3

+-----------------+----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                      | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+-----------------+----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | c, cc, i, p, s, sc                           |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter         | s.born = $autoint_0 AND s:Scientist          |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)    | (sc)<-[anon_0:RESEARCHED]-(s)                |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter         | i.year = $autoint_1 AND sc:Science           |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)    | (p)-[i:INVENTED_BY]->(sc)                    |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter         | p.born = $autoint_2 AND p:Pioneer            |              0 |    0 |       2 |                |                        |           | Fused in Pipeline 0 |
| |               +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)    | (c)<-[anon_1:LIVES_IN]-(p)                   |              1 |    1 |       3 |                |                        |           | Fused in Pipeline 0 |
| |               +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter         | c:City                                       |              1 |    1 |       1 |                |                        |           | Fused in Pipeline 0 |
| |               +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)    | (cc)<-[anon_2:PART_OF]-(c)                   |              1 |    1 |       2 |                |                        |           | Fused in Pipeline 0 |
| |               +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +NodeIndexSeek  | cc:Country(formed) WHERE formed = $autoint_3 |              1 |    1 |       2 |             72 |                    6/1 |     0.718 | Fused in Pipeline 0 |
+-----------------+----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 10, total allocated memory: 160

Index hints

Index hints are used to specify which index, the planner should use as a starting point. This can be beneficial in cases where the index statistics are not accurate for the specific values that the query at hand is known to use, which would result in the planner picking a non-optimal index. To supply an index hint, use USING INDEX variable:Label(property) or USING INDEX SEEK variable:Label(property) after the applicable MATCH clause for node indexes, and USING INDEX variable:RELATIONSHIP_TYPE(property) or USING INDEX SEEK variable:RELATIONSHIP_TYPE(property) for relationship indexes.

USING INDEX can be fulfilled by any of the following plans: NodeIndexScan, DirectedRelationshipIndexScan, UndirectedRelationshipIndexScan, NodeIndexSeek, DirectedRelationshipIndexSeek, UndirectedRelationshipIndexSeek. USING INDEX SEEK can only be fulfilled by NodeIndexSeek, DirectedRelationshipIndexSeek or UndirectedRelationshipIndexSeek.

It is possible to supply several index hints, but keep in mind that several starting points will require the use of a potentially expensive join later in the query plan.

Query using a node index hint

The query above can be tuned to pick a different index as the starting point.

Query
MATCH (s:Scientist {born: 1850})-[:RESEARCHED]->(sc:Science)<-[i:INVENTED_BY {year: 560}]-(p:Pioneer {born: 525})-[:LIVES_IN]->(c:City)-[:PART_OF]->(cc:Country {formed: 411})
USING INDEX p:Pioneer(born)
RETURN *
Query plan
Compiler CYPHER 4.3

Planner COST

Runtime PIPELINED

Runtime version 4.3

+-----------------+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                 | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+-----------------+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | c, cc, i, p, s, sc                      |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +-----------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter         | cc.formed = $autoint_3 AND cc:Country   |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +-----------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)    | (c)-[anon_2:PART_OF]->(cc)              |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +-----------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter         | c:City                                  |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +-----------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)    | (p)-[anon_1:LIVES_IN]->(c)              |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +-----------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter         | s.born = $autoint_0 AND s:Scientist     |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +-----------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)    | (sc)<-[anon_0:RESEARCHED]-(s)           |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +-----------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter         | i.year = $autoint_1 AND sc:Science      |              0 |    0 |       2 |                |                        |           | Fused in Pipeline 0 |
| |               +-----------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)    | (p)-[i:INVENTED_BY]->(sc)               |              2 |    2 |       6 |                |                        |           | Fused in Pipeline 0 |
| |               +-----------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +NodeIndexSeek  | p:Pioneer(born) WHERE born = $autoint_2 |              2 |    2 |       3 |             72 |                    4/1 |     0.665 | Fused in Pipeline 0 |
+-----------------+-----------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 11, total allocated memory: 160

Query using a relationship index hint

The query above can be tuned to pick a relationship index as the starting point.

Query
MATCH (s:Scientist {born: 1850})-[:RESEARCHED]->(sc:Science)<-[i:INVENTED_BY {year: 560}]-(p:Pioneer {born: 525})-[:LIVES_IN]->(c:City)-[:PART_OF]->(cc:Country {formed: 411})
USING INDEX i:INVENTED_BY(year)
RETURN *
Query plan
Compiler CYPHER 4.3

Planner COST

Runtime PIPELINED

Runtime version 4.3

+--------------------------------+---------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                       | Details                                                 | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+--------------------------------+---------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                | c, cc, i, p, s, sc                                      |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                              +---------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter                        | cc.formed = $autoint_3 AND cc:Country                   |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                              +---------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)                   | (c)-[anon_2:PART_OF]->(cc)                              |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                              +---------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter                        | c:City                                                  |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                              +---------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)                   | (p)-[anon_1:LIVES_IN]->(c)                              |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                              +---------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter                        | s.born = $autoint_0 AND s:Scientist                     |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                              +---------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)                   | (sc)<-[anon_0:RESEARCHED]-(s)                           |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                              +---------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter                        | p.born = $autoint_2 AND sc:Science AND p:Pioneer        |              0 |    0 |       4 |                |                        |           | Fused in Pipeline 0 |
| |                              +---------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +DirectedRelationshipIndexSeek | (p)-[i:INVENTED_BY(year)]->(sc) WHERE year = $autoint_1 |              2 |    2 |       5 |             72 |                    5/1 |     0.517 | Fused in Pipeline 0 |
+--------------------------------+---------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 9, total allocated memory: 160

Query using multiple index hints

Supplying one index hint changed the starting point of the query, but the plan is still linear, meaning it only has one starting point. If we give the planner yet another index hint, we force it to use two starting points, one at each end of the match. It will then join these two branches using a join operator.

Query
MATCH (s:Scientist {born: 1850})-[:RESEARCHED]->(sc:Science)<-[i:INVENTED_BY {year: 560}]-(p:Pioneer {born: 525})-[:LIVES_IN]->(c:City)-[:PART_OF]->(cc:Country {formed: 411})
USING INDEX s:Scientist(born)
USING INDEX cc:Country(formed)
RETURN *
Query plan
Compiler CYPHER 4.3

Planner COST

Runtime PIPELINED

Runtime version 4.3

+------------------+----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Details                                      | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+------------------+----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  | c, cc, i, p, s, sc                           |              0 |    0 |       0 |                |                    0/0 |     0.000 | In Pipeline 2       |
| |                +----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeHashJoin    | sc                                           |              0 |    0 |       0 |            432 |                        |           | In Pipeline 2       |
| |\               +----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Expand(All)   | (s)-[anon_0:RESEARCHED]->(sc)                |              1 |    0 |       0 |                |                        |           | Fused in Pipeline 1 |
| | |              +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| | +NodeIndexSeek | s:Scientist(born) WHERE born = $autoint_0    |              1 |    0 |       0 |             72 |                    0/0 |     0.000 | Fused in Pipeline 1 |
| |                +----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Filter          | i.year = $autoint_1 AND sc:Science           |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)     | (p)-[i:INVENTED_BY]->(sc)                    |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter          | p.born = $autoint_2 AND p:Pioneer            |              0 |    0 |       2 |                |                        |           | Fused in Pipeline 0 |
| |                +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)     | (c)<-[anon_1:LIVES_IN]-(p)                   |              1 |    1 |       3 |                |                        |           | Fused in Pipeline 0 |
| |                +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter          | c:City                                       |              1 |    1 |       1 |                |                        |           | Fused in Pipeline 0 |
| |                +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)     | (cc)<-[anon_2:PART_OF]-(c)                   |              1 |    1 |       2 |                |                        |           | Fused in Pipeline 0 |
| |                +----------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +NodeIndexSeek   | cc:Country(formed) WHERE formed = $autoint_3 |              1 |    1 |       2 |             72 |                    7/0 |     0.553 | Fused in Pipeline 0 |
+------------------+----------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 10, total allocated memory: 672

Scan hints

If your query matches large parts of an index, it might be faster to scan the label or relationship type and filter out rows that do not match. To do this, you can use USING SCAN variable:Label after the applicable MATCH clause for node indexes, and USING SCAN variable:RELATIONSHIP_TYPE for relationship indexes. This will force Cypher® to not use an index that could have been used, and instead do a label scan/relationship type scan. You can use the same hint to enforce a starting point where no index is applicable.

Hinting a label scan

Query
MATCH (s:Scientist {born: 1850})-[:RESEARCHED]->(sc:Science)<-[i:INVENTED_BY {year: 560}]-(p:Pioneer {born: 525})-[:LIVES_IN]->(c:City)-[:PART_OF]->(cc:Country {formed: 411})
USING SCAN s:Scientist
RETURN *
Query plan
Compiler CYPHER 4.3

Planner COST

Runtime PIPELINED

Runtime version 4.3

+------------------+-----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Details                                                   | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+------------------+-----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  | c, cc, i, p, s, sc                                        |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                +-----------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter          | cc.formed = $autoint_3 AND cc:Country                     |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                +-----------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)     | (c)-[anon_2:PART_OF]->(cc)                                |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                +-----------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter          | c:City                                                    |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                +-----------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)     | (p)-[anon_1:LIVES_IN]->(c)                                |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                +-----------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter          | i.year = $autoint_1 AND p.born = $autoint_2 AND p:Pioneer |              0 |    0 |       1 |                |                        |           | Fused in Pipeline 0 |
| |                +-----------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)     | (sc)<-[i:INVENTED_BY]-(p)                                 |              1 |    1 |       3 |                |                        |           | Fused in Pipeline 0 |
| |                +-----------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter          | sc:Science                                                |              1 |    1 |       1 |                |                        |           | Fused in Pipeline 0 |
| |                +-----------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)     | (s)-[anon_0:RESEARCHED]->(sc)                             |              1 |    1 |       2 |                |                        |           | Fused in Pipeline 0 |
| |                +-----------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter          | s.born = $autoint_0                                       |              1 |    1 |     200 |                |                        |           | Fused in Pipeline 0 |
| |                +-----------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +NodeByLabelScan | s:Scientist                                               |            100 |  100 |     101 |             72 |                   10/0 |     0.988 | Fused in Pipeline 0 |
+------------------+-----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 308, total allocated memory: 168

Hinting a relationship type scan

Query
MATCH (s:Scientist {born: 1850})-[:RESEARCHED]->(sc:Science)<-[i:INVENTED_BY {year: 560}]-(p:Pioneer {born: 525})-[:LIVES_IN]->(c:City)-[:PART_OF]->(cc:Country {formed: 411})
USING SCAN i:INVENTED_BY
RETURN *
Query plan
Compiler CYPHER 4.3

Planner COST

Runtime PIPELINED

Runtime version 4.3

+-------------------------------+--------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                      | Details                                                                  | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+-------------------------------+--------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults               | c, cc, i, p, s, sc                                                       |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                             +--------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter                       | cc.formed = $autoint_3 AND cc:Country                                    |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                             +--------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)                  | (c)-[anon_2:PART_OF]->(cc)                                               |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                             +--------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter                       | c:City                                                                   |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                             +--------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)                  | (p)-[anon_1:LIVES_IN]->(c)                                               |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                             +--------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter                       | s.born = $autoint_0 AND s:Scientist                                      |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                             +--------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)                  | (sc)<-[anon_0:RESEARCHED]-(s)                                            |              0 |    0 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                             +--------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter                       | i.year = $autoint_1 AND p.born = $autoint_2 AND sc:Science AND p:Pioneer |              0 |    0 |     204 |                |                        |           | Fused in Pipeline 0 |
| |                             +--------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +DirectedRelationshipTypeScan | (p)-[i:INVENTED_BY]->(sc)                                                |            100 |  100 |     201 |             72 |                   10/0 |     1.417 | Fused in Pipeline 0 |
+-------------------------------+--------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 405, total allocated memory: 160

Join hints

Join hints are the most advanced type of hints, and are not used to find starting points for the query execution plan, but to enforce that joins are made at specified points. This implies that there has to be more than one starting point (leaf) in the plan, in order for the query to be able to join the two branches ascending from these leaves. Due to this nature, joins, and subsequently join hints, will force the planner to look for additional starting points, and in the case where there are no more good ones, potentially pick a very bad starting point. This will negatively affect query performance. In other cases, the hint might force the planner to pick a seemingly bad starting point, which in reality proves to be a very good one.

Hinting a join on a single node

In the example above using multiple index hints, we saw that the planner chose to do a join, but not on the p node. By supplying a join hint in addition to the index hints, we can enforce the join to happen on the p node.

Query
MATCH (s:Scientist {born: 1850})-[:RESEARCHED]->(sc:Science)<-[i:INVENTED_BY {year: 560}]-(p:Pioneer {born: 525})-[:LIVES_IN]->(c:City)-[:PART_OF]->(cc:Country {formed: 411})
USING INDEX s:Scientist(born)
USING INDEX cc:Country(formed)
USING JOIN ON p
RETURN *
Query plan
Compiler CYPHER 4.3

Planner COST

Runtime PIPELINED

Runtime version 4.3

+------------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator         | Details                                                          | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+------------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults  | c, cc, i, p, s, sc                                               |              0 |    0 |       0 |                |                    0/0 |     0.000 | In Pipeline 2       |
| |                +------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeHashJoin    | p                                                                |              0 |    0 |       0 |            432 |                        |           | In Pipeline 2       |
| |\               +------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Filter        | cache[p.born] = $autoint_2                                       |              1 |    0 |       0 |                |                        |           | Fused in Pipeline 1 |
| | |              +------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| | +Expand(All)   | (c)<-[anon_1:LIVES_IN]-(p)                                       |              1 |    0 |       0 |                |                        |           | Fused in Pipeline 1 |
| | |              +------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| | +Filter        | c:City                                                           |              1 |    0 |       0 |                |                        |           | Fused in Pipeline 1 |
| | |              +------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| | +Expand(All)   | (cc)<-[anon_2:PART_OF]-(c)                                       |              1 |    0 |       0 |                |                        |           | Fused in Pipeline 1 |
| | |              +------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| | +NodeIndexSeek | cc:Country(formed) WHERE formed = $autoint_3                     |              1 |    0 |       0 |             72 |                    0/0 |     0.000 | Fused in Pipeline 1 |
| |                +------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +Filter          | i.year = $autoint_1 AND cache[p.born] = $autoint_2 AND p:Pioneer |              0 |    0 |       1 |                |                        |           | Fused in Pipeline 0 |
| |                +------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)     | (sc)<-[i:INVENTED_BY]-(p)                                        |              1 |    1 |       3 |                |                        |           | Fused in Pipeline 0 |
| |                +------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Filter          | sc:Science                                                       |              1 |    1 |       1 |                |                        |           | Fused in Pipeline 0 |
| |                +------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Expand(All)     | (s)-[anon_0:RESEARCHED]->(sc)                                    |              1 |    1 |       2 |                |                        |           | Fused in Pipeline 0 |
| |                +------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +NodeIndexSeek   | s:Scientist(born) WHERE born = $autoint_0                        |              1 |    1 |       2 |             72 |                    6/1 |     0.753 | Fused in Pipeline 0 |
+------------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 9, total allocated memory: 672

Hinting a join for an OPTIONAL MATCH

A join hint can also be used to force the planner to pick a NodeLeftOuterHashJoin or NodeRightOuterHashJoin to solve an OPTIONAL MATCH. In most cases, the planner will rather use an OptionalExpand.

Query
MATCH (s:Scientist {born: 1850})
OPTIONAL MATCH (s)-[:RESEARCHED]->(sc:Science)
RETURN *

Without any hint, the planner did not use a join to solve the OPTIONAL MATCH.

Query plan
Compiler CYPHER 4.3

Planner COST

Runtime PIPELINED

Runtime version 4.3

+----------------------+------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator             | Details                                        | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+----------------------+------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults      | s, sc                                          |              1 |    1 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                    +------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +OptionalExpand(All) | (s)-[anon_0:RESEARCHED]->(sc) WHERE sc:Science |              1 |    1 |       3 |                |                        |           | Fused in Pipeline 0 |
| |                    +------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +NodeIndexSeek       | s:Scientist(born) WHERE born = $autoint_0      |              1 |    1 |       2 |             72 |                    6/0 |     0.630 | Fused in Pipeline 0 |
+----------------------+------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 5, total allocated memory: 136
Query
MATCH (s:Scientist {born: 1850})
OPTIONAL MATCH (s)-[:RESEARCHED]->(sc:Science)
USING JOIN ON s
RETURN *

Now the planner uses a join to solve the OPTIONAL MATCH.

Query plan
Compiler CYPHER 4.3

Planner COST

Runtime PIPELINED

Runtime version 4.3

+------------------------+-------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator               | Details                                   | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+------------------------+-------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults        | s, sc                                     |              1 |    1 |       0 |                |                    2/0 |     0.123 | In Pipeline 2       |
| |                      +-------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeLeftOuterHashJoin | s                                         |              1 |    1 |       0 |           3096 |                        |     7.145 | In Pipeline 2       |
| |\                     +-------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Expand(All)         | (sc)<-[anon_0:RESEARCHED]-(s)             |            100 |  100 |     300 |                |                        |           | Fused in Pipeline 1 |
| | |                    +-------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| | +NodeByLabelScan     | sc:Science                                |            100 |  100 |     101 |             72 |                    4/0 |     0.812 | Fused in Pipeline 1 |
| |                      +-------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeIndexSeek         | s:Scientist(born) WHERE born = $autoint_0 |              1 |    1 |       2 |             72 |                    1/0 |     0.926 | In Pipeline 0       |
+------------------------+-------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 403, total allocated memory: 3176

PERIODIC COMMIT query hint

Importing large amounts of data using LOAD CSV with a single Cypher query may fail due to memory constraints. This will manifest itself as an OutOfMemoryError.

For this situation only, Cypher provides the global USING PERIODIC COMMIT query hint for updating queries using LOAD CSV. If required, the limit for the number of rows per commit may be set as follows: USING PERIODIC COMMIT 500.

PERIODIC COMMIT will process the rows until the number of rows reaches a limit. Then the current transaction will be committed and replaced with a newly opened transaction. If no limit is set, a default value will be used.

See Importing large amounts of data in LOAD CSV for examples of USING PERIODIC COMMIT with and without setting the number of rows per commit.

Using PERIODIC COMMIT will prevent running out of memory when importing large amounts of data. However, it will also break transactional isolation and thus it should only be used where needed.

The USE clause can not be used together with the PERIODIC COMMIT clause.