Planner hints and the USING keyword

A planner hint is used to influence the decisions of the planner when building an execution plan for a query. Planner hints are specified in a query with 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.

1. 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.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+-----------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| 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  | BTREE INDEX cc:Country(formed) WHERE formed = $autoint_3 |              1 |    1 |       2 |            112 |                    6/1 |     0.754 | Fused in Pipeline 0 |
+-----------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 10, total allocated memory: 200

2. 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. An index hint is supplied after an applicable MATCH clause. Available index hints are:

Hint Fulfilled by plans

USING [BTREE | TEXT] INDEX variable:Label(property)

NodeIndexScan, NodeIndexSeek

USING [BTREE | TEXT] INDEX SEEK variable:Label(property)

NodeIndexSeek

USING [BTREE | TEXT] INDEX variable:RELATIONSHIP_TYPE(property)

DirectedRelationshipIndexScan, UndirectedRelationshipIndexScan, DirectedRelationshipIndexSeek, UndirectedRelationshipIndexSeek

USING [BTREE | TEXT] INDEX SEEK variable:RELATIONSHIP_TYPE(property)

DirectedRelationshipIndexSeek, UndirectedRelationshipIndexSeek

When specifying an index type for a hint, e.g. BTREE or TEXT, the hint can only be fulfilled when an index of the specified type is available. When no index type is specified, the hint can be fulfilled by any index types.

Using a hint must never change the result of a query. Therefore, a hint with a specified index type is only fulfillable when the planner knows that using an index of the specified type does not change the results. Please refer to The use of indexes for more details.

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.

2.1. 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.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+-----------------+-----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| 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  | BTREE INDEX p:Pioneer(born) WHERE born = $autoint_2 |              2 |    2 |       3 |            112 |                    4/1 |     0.605 | Fused in Pipeline 0 |
+-----------------+-----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 11, total allocated memory: 200

2.2. Query using a node text index hint

The following query can be tuned to pick a text index.

Query
MATCH (c:Country)
USING TEXT INDEX c:Country(name)
WHERE c.name = 'Country7'
RETURN *
Query plan
Compiler CYPHER 4.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator        | Details                                               | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | c                                                     |              1 |    1 |       0 |                |                        |           | Fused in Pipeline 0 |
| |               +-------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +NodeIndexSeek  | TEXT INDEX c:Country(name) WHERE name = $autostring_0 |              1 |    1 |       2 |            112 |                    2/0 |    34.180 | Fused in Pipeline 0 |
+-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 2, total allocated memory: 176

2.3. 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.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+--------------------------------+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| 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 | BTREE INDEX (p)-[i:INVENTED_BY(year)]->(sc) WHERE year = $autoint_1 |              2 |    2 |       5 |            112 |                    5/1 |     0.518 | Fused in Pipeline 0 |
+--------------------------------+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 9, total allocated memory: 200

2.4. Query using a relationship text index hint

The following query can be tuned to pick a text index.

Query
MATCH ()-[i:INVENTED_BY]->()
USING TEXT INDEX i:INVENTED_BY(location)
WHERE i.location = 'Location7'
RETURN *
Query plan
Compiler CYPHER 4.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+--------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator                       | Details                                                                                | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+--------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults                | i                                                                                      |              1 |    1 |       0 |                |                        |           | Fused in Pipeline 0 |
| |                              +----------------------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +DirectedRelationshipIndexSeek | TEXT INDEX (anon_0)-[i:INVENTED_BY(location)]->(anon_1) WHERE location = $autostring_0 |              1 |    1 |       3 |            112 |                    3/0 |     1.042 | Fused in Pipeline 0 |
+--------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 3, total allocated memory: 176

2.5. 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.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| 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 | BTREE INDEX s:Scientist(born) WHERE born = $autoint_0    |              1 |    0 |       0 |            112 |                    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   | BTREE INDEX cc:Country(formed) WHERE formed = $autoint_3 |              1 |    1 |       2 |            112 |                    7/0 |     0.541 | Fused in Pipeline 0 |
+------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 10, total allocated memory: 752

2.6. Query using multiple index hints with a disjunction

Supplying multiple index hints can also be useful if the query contains a disjunction (OR) in the WHERE clause. This makes sure that all hinted indexes are used and the results are joined together with a Union and a Distinct afterwards.

Query
MATCH (country:Country)
USING INDEX country:Country(name)
USING INDEX country:Country(formed)
WHERE country.formed = 500 OR country.name STARTS WITH "A"
RETURN *
Query plan
Compiler CYPHER 4.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+-----------------------+------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator              | Details                                                                | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Other               |
+-----------------------+------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults       | country                                                                |              1 |    1 |       0 |                |                        |           | Fused in Pipeline 2 |
| |                     +------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Distinct             | country                                                                |              1 |    1 |       0 |            224 |                        |           | Fused in Pipeline 2 |
| |                     +------------------------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| +Union                |                                                                        |              2 |    1 |       0 |           1128 |                    1/0 |     0.394 | Fused in Pipeline 2 |
| |\                    +------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +NodeIndexSeek      | BTREE INDEX country:Country(formed) WHERE formed = $autoint_0          |              1 |    1 |       2 |            112 |                    1/0 |     0.137 | In Pipeline 1       |
| |                     +------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeIndexSeekByRange | BTREE INDEX country:Country(name) WHERE name STARTS WITH $autostring_1 |              1 |    0 |       1 |            112 |                    0/1 |     0.244 | In Pipeline 0       |
+-----------------------+------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 3, total allocated memory: 1208

Cypher will usually provide a plan that uses all indexes for a disjunction without hints. It may, however, decide to plan a NodeByLabelScan instead, if the predicates appear to be not very selective. In this case, the index hints can be useful.

3. 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.

3.1. 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.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+------------------+-----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| 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 |            112 |                   11/0 |     0.892 | Fused in Pipeline 0 |
+------------------+-----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 308, total allocated memory: 208

3.2. 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.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+-------------------------------+--------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| 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 |            112 |                    9/0 |     1.278 | Fused in Pipeline 0 |
+-------------------------------+--------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 405, total allocated memory: 200

3.3. Query using multiple scan hints with a disjunction

Supplying multiple scan hints can also be useful if the query contains a disjunction (OR) in the WHERE clause. This makes sure that all involved label predicates are solved by a NodeByLabelScan and the results are joined together with a Union and a Distinct afterwards.

Query
MATCH (person)
USING SCAN person:Pioneer
USING SCAN person:Scientist
WHERE person:Pioneer OR person:Scientist
RETURN *
Query plan
Compiler CYPHER 4.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+--------------------+------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------+
| Operator           | Details          | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by | Other         |
+--------------------+------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------+
| +ProduceResults    | person           |            180 |  200 |       0 |                |                    4/0 |     1.497 | person ASC | In Pipeline 2 |
| |                  +------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------+
| +OrderedDistinct   | person           |            180 |  200 |       0 |             32 |                    0/0 |     0.857 | person ASC | In Pipeline 2 |
| |                  +------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------+
| +OrderedUnion      |                  |            200 |  200 |       0 |           1128 |                    0/0 |     0.894 | person ASC | In Pipeline 2 |
| |\                 +------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------+
| | +NodeByLabelScan | person:Scientist |            100 |  100 |     101 |            112 |                    1/0 |     0.257 | person ASC | In Pipeline 1 |
| |                  +------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------+
| +NodeByLabelScan   | person:Pioneer   |            100 |  100 |     101 |            112 |                    1/0 |     0.423 | person ASC | In Pipeline 0 |
+--------------------+------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------+

Total database accesses: 202, total allocated memory: 1320

Cypher will usually provide a plan that uses scans for a disjunction without hints. It may, however, decide to plan an AllNodeScan followed by a Filter instead, if the label predicates appear to be not very selective. In this case, the scan hints can be useful.

4. 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.

4.1. 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.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+------------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| 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 | BTREE INDEX cc:Country(formed) WHERE formed = $autoint_3         |              1 |    0 |       0 |            112 |                    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   | BTREE INDEX s:Scientist(born) WHERE born = $autoint_0            |              1 |    1 |       2 |            112 |                    6/1 |     0.688 | Fused in Pipeline 0 |
+------------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 9, total allocated memory: 752

4.2. 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.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+----------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| 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       | BTREE INDEX s:Scientist(born) WHERE born = $autoint_0 |              1 |    1 |       2 |            112 |                    6/0 |     0.747 | Fused in Pipeline 0 |
+----------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 5, total allocated memory: 176
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.4

Planner COST

Runtime PIPELINED

Runtime version 4.4

+------------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| 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.115 | In Pipeline 2       |
| |                      +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeLeftOuterHashJoin | s                                                     |              1 |    1 |       0 |           4864 |                        |     4.559 | In Pipeline 2       |
| |\                     +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| | +Expand(All)         | (sc)<-[anon_0:RESEARCHED]-(s)                         |            100 |  100 |     300 |                |                        |           | Fused in Pipeline 1 |
| | |                    +-------------------------------------------------------+----------------+------+---------+----------------+                        |           +---------------------+
| | +NodeByLabelScan     | sc:Science                                            |            100 |  100 |     101 |            112 |                    4/0 |     0.895 | Fused in Pipeline 1 |
| |                      +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +NodeIndexSeek         | BTREE INDEX s:Scientist(born) WHERE born = $autoint_0 |              1 |    1 |       2 |            112 |                    1/0 |     0.253 | In Pipeline 0       |
+------------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 403, total allocated memory: 4944

5. [deprecated]#PERIODIC COMMIT query hint

The PERIODIC COMMIT query hint will be removed in the next major release. It is recommended to use CALL { …​ } IN TRANSACTIONS instead.

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 query hint.

Queries with the PERIODIC COMMIT query hint can not be routed by Server-side routing. Such queries must rely on standard client-side routing, done by the Neo4j Driver.