Index hints for the Cypher planner
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. |
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 three types of planner hints:
Query
PROFILE
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
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +-----------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +-----------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | c, cc, i, p, s, sc | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | s.born = $autoint_0 AND s:Scientist | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[anon_0:RESEARCHED]-(s) | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | i.year = $autoint_1 AND sc:Science | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[i:INVENTED_BY]->(sc) | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | p.born = $autoint_2 AND p:Pioneer | 0 | 0 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)<-[anon_1:LIVES_IN]-(p) | 1 | 1 | 3 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 1 | 1 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (cc)<-[anon_2:PART_OF]-(c) | 1 | 1 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +NodeIndexSeek | RANGE INDEX cc:Country(formed) WHERE formed = $autoint_3 | 1 | 1 | 2 | 120 | 6/1 | 0.506 | Fused in Pipeline 0 | +-----------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 11, total allocated memory: 208
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 |
|---|---|
|
|
|
|
|
|
|
|
When specifying an index type for a hint, e.g. RANGE, TEXT, or POINT, 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.
Query using a node index hint
The query above can be tuned to pick a different index as the starting point.
Query
PROFILE
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
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +-----------------+-----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +-----------------+-----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | c, cc, i, p, s, sc | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | cc.formed = $autoint_3 AND cc:Country | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)-[anon_2:PART_OF]->(cc) | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[anon_1:LIVES_IN]->(c) | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | s.born = $autoint_0 AND s:Scientist | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[anon_0:RESEARCHED]-(s) | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | i.year = $autoint_1 AND sc:Science | 0 | 0 | 2 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[i:INVENTED_BY]->(sc) | 2 | 2 | 6 | | | | | | | +-----------------------------------------------------+----------------+------+---------+----------------+ | | | | +NodeIndexSeek | RANGE INDEX p:Pioneer(born) WHERE born = $autoint_2 | 2 | 2 | 3 | 120 | 4/1 | 0.491 | Fused in Pipeline 0 | +-----------------+-----------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 11, total allocated memory: 208
Query using a node text index hint
The following query can be tuned to pick a text index.
Query
PROFILE
MATCH (c:Country)
USING TEXT INDEX c:Country(name)
WHERE c.name = 'Country7'
RETURN *Query plan
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | c | 1 | 1 | 0 | | | | | | | +-------------------------------------------------------+----------------+------+---------+----------------+ | | | | +NodeIndexSeek | TEXT INDEX c:Country(name) WHERE name = $autostring_0 | 1 | 1 | 2 | 120 | 2/0 | 0.949 | Fused in Pipeline 0 | +-----------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 2, total allocated memory: 184
Query using a relationship index hint
The query above can be tuned to pick a relationship index as the starting point.
Query
PROFILE
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
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +--------------------------------+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +--------------------------------+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | c, cc, i, p, s, sc | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | cc.formed = $autoint_3 AND cc:Country | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)-[anon_2:PART_OF]->(cc) | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[anon_1:LIVES_IN]->(c) | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | s.born = $autoint_0 AND s:Scientist | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[anon_0:RESEARCHED]-(s) | 0 | 0 | 0 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | p.born = $autoint_2 AND sc:Science AND p:Pioneer | 0 | 0 | 4 | | | | | | | +---------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +DirectedRelationshipIndexSeek | RANGE INDEX (p)-[i:INVENTED_BY(year)]->(sc) WHERE year = $autoint_1 | 2 | 2 | 3 | 120 | 5/1 | 0.461 | Fused in Pipeline 0 | +--------------------------------+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 7, total allocated memory: 208
Query using a relationship text index hint
The following query can be tuned to pick a text index.
Query
PROFILE
MATCH ()-[i:INVENTED_BY]->()
USING TEXT INDEX i:INVENTED_BY(location)
WHERE i.location = 'Location7'
RETURN *Query plan
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +--------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +--------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | i | 1 | 1 | 0 | | | | | | | +----------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +DirectedRelationshipIndexSeek | TEXT INDEX (anon_0)-[i:INVENTED_BY(location)]->(anon_1) WHERE location = $autostring_0 | 1 | 1 | 2 | 120 | 3/0 | 1.079 | Fused in Pipeline 0 | +--------------------------------+----------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 2, total allocated memory: 184
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
PROFILE
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
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | c, cc, i, p, s, sc | 0 | 0 | 0 | | 0/0 | 0.000 | | | | +----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+ | | +NodeHashJoin | sc | 0 | 0 | 0 | 432 | | | In Pipeline 2 | | |\ +----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | | +Expand(All) | (s)-[anon_0:RESEARCHED]->(sc) | 1 | 0 | 0 | | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +NodeIndexSeek | RANGE INDEX s:Scientist(born) WHERE born = $autoint_0 | 1 | 0 | 0 | 120 | 0/0 | 0.000 | Fused in Pipeline 1 | | | +----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +Filter | i.year = $autoint_1 AND sc:Science | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[i:INVENTED_BY]->(sc) | 0 | 0 | 0 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | p.born = $autoint_2 AND p:Pioneer | 0 | 0 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)<-[anon_1:LIVES_IN]-(p) | 1 | 1 | 3 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 1 | 1 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (cc)<-[anon_2:PART_OF]-(c) | 1 | 1 | 2 | | | | | | | +----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +NodeIndexSeek | RANGE INDEX cc:Country(formed) WHERE formed = $autoint_3 | 1 | 1 | 2 | 120 | 7/0 | 0.494 | Fused in Pipeline 0 | +------------------+----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 11, total allocated memory: 768
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
PROFILE
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
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +-----------------------+------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +-----------------------+------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | country | 1 | 1 | 0 | | | | | | | +------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Distinct | country | 1 | 1 | 0 | 224 | | | | | | +------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Union | | 2 | 1 | 0 | 80 | 1/0 | 0.213 | Fused in Pipeline 2 | | |\ +------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | | +NodeIndexSeek | RANGE INDEX country:Country(formed) WHERE formed = $autoint_0 | 1 | 1 | 2 | 120 | 1/0 | 0.101 | In Pipeline 1 | | | +------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +NodeIndexSeekByRange | RANGE INDEX country:Country(name) WHERE name STARTS WITH $autostring_1 | 1 | 0 | 1 | 120 | 0/1 | 0.307 | In Pipeline 0 | +-----------------------+------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 3, total allocated memory: 320
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.
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
PROFILE
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
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +------------------+-----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +------------------+-----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | c, cc, i, p, s, sc | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | cc.formed = $autoint_3 AND cc:Country | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)-[anon_2:PART_OF]->(cc) | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[anon_1:LIVES_IN]->(c) | 0 | 0 | 0 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | i.year = $autoint_1 AND p.born = $autoint_2 AND p:Pioneer | 0 | 0 | 1 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[i:INVENTED_BY]-(p) | 1 | 1 | 3 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | sc:Science | 1 | 1 | 2 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (s)-[anon_0:RESEARCHED]->(sc) | 1 | 1 | 2 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | s.born = $autoint_0 | 1 | 1 | 200 | | | | | | | +-----------------------------------------------------------+----------------+------+---------+----------------+ | | | | +NodeByLabelScan | s:Scientist | 100 | 100 | 101 | 120 | 11/0 | 0.512 | Fused in Pipeline 0 | +------------------+-----------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 309, total allocated memory: 216
Hinting a relationship type scan
Query
PROFILE
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
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +-------------------------------+--------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +-------------------------------+--------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | c, cc, i, p, s, sc | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | cc.formed = $autoint_3 AND cc:Country | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (c)-[anon_2:PART_OF]->(cc) | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | c:City | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (p)-[anon_1:LIVES_IN]->(c) | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | s.born = $autoint_0 AND s:Scientist | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[anon_0:RESEARCHED]-(s) | 0 | 0 | 0 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | i.year = $autoint_1 AND p.born = $autoint_2 AND sc:Science AND p:Pioneer | 0 | 0 | 204 | | | | | | | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +DirectedRelationshipTypeScan | (p)-[i:INVENTED_BY]->(sc) | 100 | 100 | 101 | 120 | 9/0 | 0.910 | Fused in Pipeline 0 | +-------------------------------+--------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 305, total allocated memory: 208
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 UnionNodeByLabelsScan.
Query
PROFILE
MATCH (person)
USING SCAN person:Pioneer
USING SCAN person:Scientist
WHERE person:Pioneer OR person:Scientist
RETURN *Query plan
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +------------------------+--------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +------------------------+--------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | person | 180 | 200 | 0 | | | | | | | +--------------------------+----------------+------+---------+----------------+ | | | | +UnionNodeByLabelsScan | person:Pioneer|Scientist | 180 | 200 | 202 | 120 | 6/0 | 1.740 | Fused in Pipeline 0 | +------------------------+--------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 202, total allocated memory: 184
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.
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
PROFILE
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
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +------------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +------------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | c, cc, i, p, s, sc | 0 | 0 | 0 | | 0/0 | 0.000 | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+ | | +NodeHashJoin | p | 0 | 0 | 0 | 432 | | | In Pipeline 2 | | |\ +------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | | +Filter | cache[p.born] = $autoint_2 | 1 | 0 | 0 | | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +Expand(All) | (c)<-[anon_1:LIVES_IN]-(p) | 1 | 0 | 0 | | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +Filter | c:City | 1 | 0 | 0 | | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +Expand(All) | (cc)<-[anon_2:PART_OF]-(c) | 1 | 0 | 0 | | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +NodeIndexSeek | RANGE INDEX cc:Country(formed) WHERE formed = $autoint_3 | 1 | 0 | 0 | 120 | 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 | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (sc)<-[i:INVENTED_BY]-(p) | 1 | 1 | 3 | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Filter | sc:Science | 1 | 1 | 2 | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | (s)-[anon_0:RESEARCHED]->(sc) | 1 | 1 | 2 | | | | | | | +------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +NodeIndexSeek | RANGE INDEX s:Scientist(born) WHERE born = $autoint_0 | 1 | 1 | 2 | 120 | 6/1 | 0.515 | Fused in Pipeline 0 | +------------------+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 10, total allocated memory: 768
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
PROFILE
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
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +----------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +----------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | s, sc | 1 | 1 | 0 | | | | | | | +-------------------------------------------------------+----------------+------+---------+----------------+ | | | | +OptionalExpand(All) | (s)-[anon_0:RESEARCHED]->(sc) WHERE sc:Science | 1 | 1 | 4 | | | | | | | +-------------------------------------------------------+----------------+------+---------+----------------+ | | | | +NodeIndexSeek | RANGE INDEX s:Scientist(born) WHERE born = $autoint_0 | 1 | 1 | 2 | 120 | 6/0 | 0.560 | Fused in Pipeline 0 | +----------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 6, total allocated memory: 184
Query
PROFILE
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
Planner COST Runtime PIPELINED Runtime version 5.25 Batch size 128 +------------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +------------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | s, sc | 1 | 1 | 0 | | 2/0 | 0.213 | | | | +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+ | | +NodeLeftOuterHashJoin | s | 1 | 1 | 0 | 3112 | | 0.650 | In Pipeline 2 | | |\ +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | | +Expand(All) | (sc)<-[anon_0:RESEARCHED]-(s) | 100 | 100 | 300 | | | | | | | | +-------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +NodeByLabelScan | sc:Science | 100 | 100 | 101 | 120 | 4/0 | 0.786 | Fused in Pipeline 1 | | | +-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +NodeIndexSeek | RANGE INDEX s:Scientist(born) WHERE born = $autoint_0 | 1 | 1 | 2 | 120 | 1/0 | 0.214 | In Pipeline 0 | +------------------------+-------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 403, total allocated memory: 3192