The use of indexes
This section describes the query plans when indexes are used in various scenarios.
The task of tuning calls for different indexes depending on what the queries look like. Therefore, it is important to have a fundamental understanding of how the indexes operate. This section describes the query plans that result from different index scenarios.
Node indexes and relationship indexes operate in the same way. Therefore, node and relationship indexes are used interchangeably in this section.
Please refer to Indexes for search performance for instructions on how to create and maintain the indexes themselves.
Index types and predicate compatibility
There are different types of indexes available in Neo4j but they are not all compatible with the same property predicates.
Indexes are commonly used for MATCH
and OPTIONAL MATCH
clauses that combine a label predicate with a property predicate.
Therefore, it is important to know what kind of predicates that can be solved by the different indexes.
The different indexes are:
-
BTREE
-
TEXT
BTREE indexes
BTREE
indexes support all types of predicates:
Predicate | Syntax |
---|---|
equality check |
|
list membership check |
|
existence check |
|
range search |
|
prefix search |
|
suffix search |
|
substring search |
|
TEXT indexes
TEXT
indexes only work for predicates operating on strings.
That means that TEXT
indexes are only used when it is known that the predicate evaluates to null
for all non-string values.
Predicates that only operate on strings are always solvable by a TEXT
index:
-
STARTS WITH
-
ENDS WITH
-
CONTAINS
However, other predicates are only used when it is known that the property is compared to a string:
-
n.prop = "string"
-
n.prop IN ["a", "b", "c"]
-
n.prop > "string"
This means that a TEXT
index is not able to solve e.g. a.prop = b.prop
.
In summary, TEXT
indexes support the following predicates:
Predicate | Syntax |
---|---|
equality check |
|
list membership check |
|
range search |
|
prefix search |
|
suffix search |
|
substring search |
|
Ensuring text index use
In order for the planner to use text indexes, it must be able to confirm that the properties included in the predicate are STRING
values.
This is not possible when accessing property values within nodes or relationships, or values within a MAP
, since Cypher® does not store the type information of these values.
To ensure text indexes are used in these cases, the toString
function should be used.
WITH {name: 'John'} AS varName
MERGE (:Person {name:varName.name})
WITH {name: 'John'} AS varName
MERGE (:Person {name: toString(varName.name)})
Index preference
When multiple indexes are available and able to solve a predicate, there is an order defined that decides which index to use. It is defined as such:
-
TEXT
indexes are preferred overBTREE
indexes forCONTAINS
andENDS WITH
. -
BTREE
indexes are preferred overTEXT
indexes in all other cases.
Examples:
Relationship BTREE index
In this example, a KNOWS(since)
relationship BTREE
index is available.
MATCH (person)-[relationship:KNOWS {since: 1992}]->(friend)
RETURN person, friend
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 | person, friend | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +-------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +DirectedRelationshipIndexSeek | BTREE INDEX (person)-[relationship:KNOWS(since)]->(friend) WHERE since = $autoint_0 | 1 | 1 | 3 | 112 | 2/1 | 1.404 | Fused in Pipeline 0 |
+--------------------------------+-------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 3, total allocated memory: 176
Node TEXT index
In the example below, a Person(surname)
node TEXT
index is available.
MATCH (person:Person {surname: 'Smith'})
RETURN person
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 | person | 2 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +-----------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexSeek | TEXT INDEX person:Person(surname) WHERE surname = $autostring_0 | 2 | 1 | 2 | 112 | 2/0 | 6.367 | Fused in Pipeline 0 |
+-----------------+-----------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 2, total allocated memory: 176
Relationship TEXT index
In this example, a KNOWS(lastMetLocation)
relationship TEXT
index is available.
MATCH (person)-[relationship:KNOWS {metIn: 'Malmo'} ]->(friend)
RETURN person, friend
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 | person, friend | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +---------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +DirectedRelationshipIndexSeek | TEXT INDEX (person)-[relationship:KNOWS(metIn)]->(friend) WHERE metIn = $autostring_0 | 1 | 1 | 3 | 112 | 2/0 | 17.095 | Fused in Pipeline 0 |
+--------------------------------+---------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 3, total allocated memory: 176
Multiple available index types
In the example below, both a Person(middlename)
node TEXT
index and a Person(middlename)
node BTREE
index are available.
The TEXT
node index is chosen.
MATCH (person:Person {middlename: 'Ron'})
RETURN person
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 | person | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexSeek | BTREE INDEX person:Person(middlename) WHERE middlename = $autostring_0 | 1 | 1 | 2 | 112 | 2/1 | 0.392 | Fused in Pipeline 0 |
+-----------------+------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 2, total allocated memory: 176
Equality check using WHERE
(single-property index)
A query containing equality comparisons of a single indexed property in the WHERE
clause is backed automatically by the index.
It is also possible for a query with multiple OR
predicates to use multiple indexes, if indexes exist on the properties.
For example, if indexes exist on both :Label(p1)
and :Label(p2)
, MATCH (n:Label) WHERE n.p1 = 1 OR n.p2 = 2 RETURN n
will use both indexes.
MATCH (person:Person)
WHERE person.firstname = 'Andy'
RETURN person
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 | person | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +----------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexSeek | BTREE INDEX person:Person(firstname) WHERE firstname = $autostring_0 | 1 | 1 | 2 | 112 | 2/1 | 1.208 | Fused in Pipeline 0 |
+-----------------+----------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 2, total allocated memory: 176
Equality check using WHERE
(composite index)
A query containing equality comparisons for all the properties of a composite index will automatically be backed by the same index. However, the query does not need to have equality on all properties. It can have ranges and existence predicates as well. But in these cases rewrites might happen depending on which properties have which predicates, see composite index limitations.
The following query will use the composite index defined earlier:
MATCH (n:Person)
WHERE n.age = 35 AND n.country = 'UK'
RETURN n
However, the query MATCH (n:Person) WHERE n.age = 35 RETURN n
will not be backed by the composite index, as the query does not contain a predicate on the country
property.
It will only be backed by an index on the Person
label and age
property defined thus: :Person(age)
; i.e. a single-property index.
+------------------------------------------------------------------------------------------------------------+
| n |
+------------------------------------------------------------------------------------------------------------+
| Node[0]{country:"UK",firstname:"John",highScore:54321,surname:"Smith",name:"john",middlename:"Ron",age:35} |
+------------------------------------------------------------------------------------------------------------+
1 row
Range comparisons using WHERE
(single-property index)
Single-property indexes are also automatically used for inequality (range) comparisons of an indexed property in the WHERE
clause.
MATCH (friend)<-[r:KNOWS]-(person)
WHERE r.since < 2011
RETURN friend, person
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 | friend, person | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +--------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +DirectedRelationshipIndexSeekByRange | BTREE INDEX (person)-[r:KNOWS(since)]->(friend) WHERE since < $autoint_0 | 1 | 1 | 3 | 112 | 2/1 | 1.767 | Fused in Pipeline 0 |
+---------------------------------------+--------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 3, total allocated memory: 176
Range comparisons using WHERE
(composite index)
Composite indexes are also automatically used for inequality (range) comparisons of indexed properties in the WHERE
clause.
Equality or list membership check predicates may precede the range predicate.
However, predicates after the range predicate may be rewritten as an existence check predicate and a filter as described in composite index limitations.
MATCH ()-[r:KNOWS]-()
WHERE r.since < 2011 AND r.lastMet > 2019
RETURN r.since
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 | `r.since` | 2 | 2 | 0 | | | | Fused in Pipeline 0 |
| | +-----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +Projection | cache[r.since] AS `r.since` | 2 | 2 | 0 | | | | Fused in Pipeline 0 |
| | +-----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +Filter | cache[r.lastMet] > $autoint_1 | 2 | 2 | 0 | | | | Fused in Pipeline 0 |
| | +-----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +UndirectedRelationshipIndexSeek | BTREE INDEX (anon_0)-[r:KNOWS(since, lastMet)]-(anon_1) WHERE since < $autoint_0 AND lastMet IS NOT | 2 | 2 | 3 | 112 | 1/1 | 1.181 | Fused in Pipeline 0 |
| | NULL, cache[r.since], cache[r.lastMet] | | | | | | | |
+----------------------------------+-----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 3, total allocated memory: 176
Multiple range comparisons using WHERE
(single-property index)
When the WHERE
clause contains multiple inequality (range) comparisons for the same property, these can be combined in a single index range seek.
MATCH (person:Person)
WHERE 10000 < person.highScore < 20000
RETURN person
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 | person | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +----------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexSeekByRange | BTREE INDEX person:Person(highScore) WHERE highScore > $autoint_0 AND highScore < $autoint_1 | 1 | 1 | 2 | 112 | 2/1 | 0.812 | Fused in Pipeline 0 |
+-----------------------+----------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 2, total allocated memory: 176
Multiple range comparisons using WHERE
(composite index)
When the WHERE
clause contains multiple inequality (range) comparisons for the same property, these can be combined in a single index range seek.
That single range seek created in the following query will then use the composite index Person(highScore, name)
if it exists.
MATCH (person:Person)
WHERE 10000 < person.highScore < 20000 AND person.name IS NOT NULL
RETURN person
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 | person | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexSeek | BTREE INDEX person:Person(highScore, name) WHERE highScore > $autoint_0 AND highScore < $autoint_1 A | 1 | 1 | 2 | 112 | 2/1 | 3.233 | Fused in Pipeline 0 |
| | ND name IS NOT NULL | | | | | | | |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 2, total allocated memory: 176
List membership check using IN
(single-property index)
The IN
predicate on r.lastMetIn
in the following query will use the single-property index KNOWS(lastMetIn)
if it exists.
MATCH (person)-[r:KNOWS]->(friend)
WHERE r.lastMetIn IN ['Malmo', 'Stockholm']
RETURN person, friend
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 | person, friend | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +DirectedRelationshipIndexSeek | BTREE INDEX (person)-[r:KNOWS(lastMetIn)]->(friend) WHERE lastMetIn IN $autolist_0 | 1 | 1 | 4 | 112 | 3/1 | 0.537 | Fused in Pipeline 0 |
+--------------------------------+------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 4, total allocated memory: 176
List membership check using IN
(composite index)
The IN
predicates on r.since
and r.lastMet
in the following query will use the composite index KNOWS(since, lastMet)
if it exists.
MATCH (person)-[r:KNOWS]->(friend)
WHERE r.since IN [1992, 2017] AND r.lastMet IN [2002, 2021]
RETURN person, friend
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 | person, friend | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +DirectedRelationshipIndexSeek | BTREE INDEX (person)-[r:KNOWS(since, lastMet)]->(friend) WHERE since IN $autolist_0 AND lastMet IN $ | 1 | 1 | 6 | 112 | 5/1 | 4.788 | Fused in Pipeline 0 |
| | autolist_1 | | | | | | | |
+--------------------------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 6, total allocated memory: 176
Prefix search using STARTS WITH
(single-property index)
The STARTS WITH
predicate on person.firstname
in the following query will use the Person(firstname)
index, if it exists.
MATCH (person:Person)
WHERE person.firstname STARTS WITH 'And'
RETURN person
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 | person | 2 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +--------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexSeekByRange | BTREE INDEX person:Person(firstname) WHERE firstname STARTS WITH $autostring_0 | 2 | 1 | 2 | 112 | 3/0 | 0.630 | Fused in Pipeline 0 |
+-----------------------+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 2, total allocated memory: 176
Prefix search using STARTS WITH
(composite index)
The STARTS WITH
predicate on person.firstname
in the following query will use the Person(firstname,surname)
index, if it exists.
Any (non-existence check) predicate on person.surname
will be rewritten as existence check with a filter.
However, if the predicate on person.firstname
is a equality check then a STARTS WITH
on person.surname
would also use the index (without rewrites).
More information about how the rewriting works can be found in composite index limitations.
MATCH (person:Person)
WHERE person.firstname STARTS WITH 'And' AND person.surname IS NOT NULL
RETURN person
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 | person | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +-----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexSeek | BTREE INDEX person:Person(firstname, surname) WHERE firstname STARTS WITH $autostring_0 AND surname | 1 | 1 | 2 | 112 | 3/0 | 0.544 | Fused in Pipeline 0 |
| | IS NOT NULL | | | | | | | |
+-----------------+-----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 2, total allocated memory: 176
Suffix search using ENDS WITH
(single-property index)
The ENDS WITH
predicate on r.metIn
in the following query uses the KNOWS(metIn)
index, if it exists.
All values stored in the KNOWS(metIn)
index are searched, and entries ending with 'mo'
are returned.
This means that although the search is not optimized to the extent of queries using =
, IN
, >
, <
or STARTS WITH
, it is still faster than not using an index in the first place.
MATCH (person)-[r:KNOWS]->(friend)
WHERE r.metIn ENDS WITH 'mo'
RETURN person, friend
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 | person, friend | 0 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +-------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +DirectedRelationshipIndexEndsWithScan | BTREE INDEX (person)-[r:KNOWS(metIn)]->(friend) WHERE metIn ENDS WITH $autostring_0 | 0 | 1 | 3 | 112 | 2/1 | 0.409 | Fused in Pipeline 0 |
+----------------------------------------+-------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 3, total allocated memory: 176
Suffix search using ENDS WITH
(composite index)
The ENDS WITH
predicate on r.metIn
in the following query uses the KNOWS(metIn,lastMetIn)
index, if it exists.
However, it is rewritten as existence check and a filter due to the index not supporting actual suffix searches for composite indexes, this is still faster than not using an index in the first place.
Any (non-existence check) predicate on KNOWS.lastMetIn
is also rewritten as existence check with a filter.
More information about how the rewriting works can be found in composite index limitations.
MATCH (person)-[r:KNOWS]->(friend)
WHERE r.metIn ENDS WITH 'mo' AND r.lastMetIn IS NOT NULL
RETURN person, friend
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 | person, friend | 0 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +-----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +Filter | cache[r.metIn] ENDS WITH $autostring_0 | 0 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +-----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +DirectedRelationshipIndexScan | BTREE INDEX (person)-[r:KNOWS(metIn, lastMetIn)]->(friend) WHERE metIn IS NOT NULL AND lastMetIn IS | 1 | 1 | 3 | 112 | 2/1 | 0.407 | Fused in Pipeline 0 |
| | NOT NULL, cache[r.metIn] | | | | | | | |
+--------------------------------+-----------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 3, total allocated memory: 176
Substring search using CONTAINS
(single-property index)
The CONTAINS
predicate on person.firstname
in the following query will use the Person(firstname)
index, if it exists.
All values stored in the Person(firstname)
index will be searched, and entries containing 'h'
will be returned.
This means that although the search will not be optimized to the extent of queries using =
, IN
, >
, <
or STARTS WITH
, it is still faster than not using an index in the first place.
Composite indexes are currently not able to support CONTAINS
.
MATCH (person:Person)
WHERE person.firstname CONTAINS 'h'
RETURN person
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 | person | 2 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +-----------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexContainsScan | BTREE INDEX person:Person(firstname) WHERE firstname CONTAINS $autostring_0 | 2 | 1 | 2 | 112 | 3/0 | 1.355 | Fused in Pipeline 0 |
+------------------------+-----------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 2, total allocated memory: 176
Substring search using CONTAINS
(composite index)
The CONTAINS
predicate on person.country
in the following query will use the Person(country,age)
index, if it exists.
However, it will be rewritten as existence check and a filter due to the index not supporting actual suffix searches for composite indexes, this is still faster than not using an index in the first place.
Any (non-existence check) predicate on person.age
will also be rewritten as existence check with a filter.
More information about how the rewriting works can be found in composite index limitations.
MATCH (person:Person)
WHERE person.country CONTAINS '300' AND person.age IS NOT NULL
RETURN person
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 | person | 2 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +Filter | cache[person.country] CONTAINS $autostring_0 | 2 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexScan | BTREE INDEX person:Person(country, age) WHERE country IS NOT NULL AND age IS NOT NULL, cache[person. | 303 | 303 | 304 | 112 | 5/0 | 2.171 | Fused in Pipeline 0 |
| | country] | | | | | | | |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 304, total allocated memory: 176
Existence check using IS NOT NULL
(single-property index)
The r.since IS NOT NULL
predicate in the following query uses the KNOWS(since)
index, if it exists.
MATCH (person)-[r:KNOWS]->(friend)
WHERE r.since IS NOT NULL
RETURN person, friend
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 | person, friend | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +-------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +DirectedRelationshipIndexScan | BTREE INDEX (person)-[r:KNOWS(since)]->(friend) WHERE since IS NOT NULL | 1 | 1 | 3 | 112 | 2/1 | 4.300 | Fused in Pipeline 0 |
+--------------------------------+-------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 3, total allocated memory: 176
Existence check using IS NOT NULL
(composite index)
The p.firstname IS NOT NULL
and p.surname IS NOT NULL
predicates in the following query will use the Person(firstname,surname)
index, if it exists.
Any (non-existence check) predicate on person.surname
will be rewritten as existence check with a filter.
MATCH (p:Person)
WHERE p.firstname IS NOT NULL AND p.surname IS NOT NULL
RETURN p
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 | p | 1 | 2 | 0 | | | | Fused in Pipeline 0 |
| | +----------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexScan | BTREE INDEX p:Person(firstname, surname) WHERE firstname IS NOT NULL AND surname IS NOT NULL | 1 | 2 | 3 | 112 | 2/1 | 2.915 | Fused in Pipeline 0 |
+-----------------+----------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 3, total allocated memory: 176
Spatial distance searches (single-property index)
If a property with point values is indexed, the index is used for spatial distance searches as well as for range queries.
MATCH ()-[r:KNOWS]->()
WHERE point.distance(r.lastMetPoint, point({x: 1, y: 2})) < 2
RETURN r.lastMetPoint
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 | `r.lastMetPoint` | 13 | 9 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +Projection | cache[r.lastMetPoint] AS `r.lastMetPoint` | 13 | 9 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +Filter | point.distance(cache[r.lastMetPoint], point({x: $autoint_0, y: $autoint_1})) < $autoint_2 | 13 | 9 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +DirectedRelationshipIndexSeekByRange | BTREE INDEX (anon_0)-[r:KNOWS(lastMetPoint)]->(anon_1) WHERE point.distance(lastMetPoint, point($aut | 13 | 9 | 19 | 112 | 5/3 | 1.596 | Fused in Pipeline 0 |
| | oint_0, $autoint_1)) < $autoint_2, cache[r.lastMetPoint] | | | | | | | |
+---------------------------------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 19, total allocated memory: 176
Spatial distance searches (composite index)
If a property with point values is indexed, the index is used for spatial distance searches as well as for range queries.
Any following (non-existence check) predicates (here on property p.name
for index :Person(place,name)
) will be rewritten as existence check with a filter.
MATCH (p:Person)
WHERE point.distance(p.place, point({x: 1, y: 2})) < 2 AND p.name IS NOT NULL
RETURN p.place
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 | `p.place` | 0 | 9 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +Projection | cache[p.place] AS `p.place` | 0 | 9 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +Filter | point.distance(cache[p.place], point({x: $autoint_0, y: $autoint_1})) < $autoint_2 | 0 | 9 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexSeek | BTREE INDEX p:Person(place, name) WHERE point.distance(place, point($autoint_0, $autoint_1)) < $auto | 0 | 9 | 10 | 112 | 6/0 | 1.370 | Fused in Pipeline 0 |
| | int_2 AND name IS NOT NULL, cache[p.place] | | | | | | | |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 10, total allocated memory: 176
Spatial bounding box searches (single-property index)
The ability to do index seeks on bounded ranges works even with the 2D and 3D spatial Point
types.
MATCH (person:Person)
WHERE point.withinBBox(person.location, point({x: 1.2, y: 5.4}), point({x: 1.3, y: 5.5}))
RETURN person.firstname
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 | `person.firstname` | 0 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +Projection | person.firstname AS `person.firstname` | 0 | 1 | 2 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexSeekByRange | BTREE INDEX person:Person(location) WHERE point.withinBBox(location, point($autodouble_0, $autodoubl | 0 | 1 | 2 | 112 | 6/0 | 16.182 | Fused in Pipeline 0 |
| | e_1), point($autodouble_2, $autodouble_3)) | | | | | | | |
+-----------------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 4, total allocated memory: 176
Spatial bounding box searches (composite index)
The ability to do index seeks on bounded ranges works even with the 2D and 3D spatial Point
types.
Any following (non-existence check) predicates (here on property p.firstname
for index :Person(place,firstname)
) will be rewritten as existence check with a filter.
For index :Person(firstname,place)
, if the predicate on firstname
is equality or list membership then the bounded range is handled as a range itself.
If the predicate on firstname
is anything else then the bounded range is rewritten to existence and filter.
MATCH (person:Person)
WHERE
point.withinBBox(person.place, point({x: 1.2, y: 5.4}), point({x: 1.3, y: 5.5}))
AND person.firstname IS NOT NULL
RETURN person
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 | person | 1 | 1 | 0 | | | | Fused in Pipeline 0 |
| | +------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | +---------------------+
| +NodeIndexSeek | BTREE INDEX person:Person(place, firstname) WHERE point.withinBBox(place, point($autodouble_0, $auto | 1 | 1 | 2 | 112 | 6/0 | 1.065 | Fused in Pipeline 0 |
| | double_1), point($autodouble_2, $autodouble_3)) AND firstname IS NOT NULL | | | | | | | |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
Total database accesses: 2, total allocated memory: 176