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

Please refer to Section 5.2, “Indexes for search performance” for instructions on how to create and maintain the indexes themselves.

6.3.1. A simple example

In the example below, the query will use a Person(firstname) index, if it exists.

Query. 

MATCH (person:Person { firstname: 'Andy' })
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| Operator        | Details                                                  | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Order                |
+-----------------+----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +ProduceResults | person                                                   |              1 |    1 |       0 |               0 |                 0 |               0.0000 | person.firstname ASC |
| |               +----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +NodeIndexSeek  | person:Person(firstname) WHERE firstname = $autostring_0 |              1 |    1 |       2 |               0 |                 0 |               0.0000 | person.firstname ASC |
+-----------------+----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+

Total database accesses: 2, total allocated memory: 0

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

Query. 

MATCH (person:Person)
WHERE person.firstname = 'Andy'
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| Operator        | Details                                                  | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Order                |
+-----------------+----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +ProduceResults | person                                                   |              1 |    1 |       0 |               0 |                 0 |               0.0000 | person.firstname ASC |
| |               +----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +NodeIndexSeek  | person:Person(firstname) WHERE firstname = $autostring_0 |              1 |    1 |       2 |               0 |                 0 |               0.0000 | person.firstname ASC |
+-----------------+----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+

Total database accesses: 2, total allocated memory: 0

6.3.3. 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:

Query. 

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.

Result. 

+-------------------------------------------------------------------------------------------+
| n                                                                                         |
+-------------------------------------------------------------------------------------------+
| Node[0]{country:"UK",highScore:54321,firstname:"John",surname:"Smith",name:"john",age:35} |
+-------------------------------------------------------------------------------------------+
1 row

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

Query. 

MATCH (person:Person)
WHERE person.firstname > 'B'
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------------+----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| Operator              | Details                                                  | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Order                |
+-----------------------+----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +ProduceResults       | person                                                   |              1 |    1 |       0 |               0 |                 0 |               0.0000 | person.firstname ASC |
| |                     +----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +NodeIndexSeekByRange | person:Person(firstname) WHERE firstname > $autostring_0 |              1 |    1 |       2 |               0 |                 0 |               0.0000 | person.firstname ASC |
+-----------------------+----------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+

Total database accesses: 2, total allocated memory: 0

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

Query. 

MATCH (person:Person)
WHERE person.firstname > 'B' AND person.highScore > 10000
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+--------------------------------------------+
| Operator        | Details                                                                                              | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Order                                      |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+--------------------------------------------+
| +ProduceResults | person                                                                                               |              0 |    1 |       0 |               0 |                 0 |               0.0000 | person.firstname ASC, person.highScore ASC |
| |               +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+--------------------------------------------+
| +Filter         | cache[person.highScore] > $autoint_1                                                                 |              0 |    1 |       0 |               0 |                 0 |               0.0000 | person.firstname ASC, person.highScore ASC |
| |               +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+--------------------------------------------+
| +NodeIndexSeek  | person:Person(firstname, highScore) WHERE firstname > $autostring_0 AND exists(highScore), cache[per |              0 |    1 |       2 |               0 |                 0 |               0.0000 | person.firstname ASC, person.highScore ASC |
|                 | son.highScore]                                                                                       |                |      |         |                 |                   |                      |                                            |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+--------------------------------------------+

Total database accesses: 2, total allocated memory: 0

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

Query. 

MATCH (person:Person)
WHERE 10000 < person.highScore < 20000
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------------+----------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| Operator              | Details                                                                          | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Order                |
+-----------------------+----------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +ProduceResults       | person                                                                           |              1 |    1 |       0 |               0 |                 0 |               0.0000 | person.highScore ASC |
| |                     +----------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +NodeIndexSeekByRange | person:Person(highScore) WHERE highScore > $autoint_1 AND highScore < $autoint_0 |              1 |    1 |       2 |               0 |                 0 |               0.0000 | person.highScore ASC |
+-----------------------+----------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+

Total database accesses: 2, total allocated memory: 0

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

Query. 

MATCH (person:Person)
WHERE 10000 < person.highScore < 20000 AND EXISTS (person.name)
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator        | Details                                                                                              | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults | person                                                                                               |              1 |    1 |       0 |               0 |                 0 |               0.0000 |
| |               +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexSeek  | person:Person(highScore, name) WHERE highScore > $autoint_1 AND highScore < $autoint_0 AND exists(na |              1 |    1 |       2 |               0 |                 0 |               0.0000 |
|                 | me)                                                                                                  |                |      |         |                 |                   |                      |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 2, total allocated memory: 0

6.3.8. List membership check using IN (single-property index)

The IN predicate on person.firstname in the following query will use the single-property index Person(firstname) if it exists.

Query. 

MATCH (person:Person)
WHERE person.firstname IN ['Andy', 'John']
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+---------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator        | Details                                                 | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------+---------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults | person                                                  |             24 |    2 |       0 |               0 |                 0 |               0.0000 |
| |               +---------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexSeek  | person:Person(firstname) WHERE firstname IN $autolist_0 |             24 |    2 |       4 |               0 |                 0 |               0.0000 |
+-----------------+---------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 4, total allocated memory: 0

6.3.9. List membership check using IN (composite index)

The IN predicates on person.age and person.country in the following query will use the composite index Person(age, country) if it exists.

Query. 

MATCH (person:Person)
WHERE person.age IN [10, 20, 35] AND person.country IN ['Sweden', 'USA', 'UK']
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+---------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator        | Details                                                                         | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------+---------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults | person                                                                          |            451 |    1 |       0 |               0 |                 0 |               0.0000 |
| |               +---------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexSeek  | person:Person(age, country) WHERE age IN $autolist_0 AND country IN $autolist_1 |            451 |    1 |      10 |               0 |                 0 |               0.0000 |
+-----------------+---------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 10, total allocated memory: 0

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

Query. 

MATCH (person:Person)
WHERE person.firstname STARTS WITH 'And'
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------------+--------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| Operator              | Details                                                            | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Order                |
+-----------------------+--------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +ProduceResults       | person                                                             |              2 |    1 |       0 |               0 |                 0 |               0.0000 | person.firstname ASC |
| |                     +--------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +NodeIndexSeekByRange | person:Person(firstname) WHERE firstname STARTS WITH $autostring_0 |              2 |    1 |       2 |               0 |                 0 |               0.0000 | person.firstname ASC |
+-----------------------+--------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+

Total database accesses: 2, total allocated memory: 0

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

Query. 

MATCH (person:Person)
WHERE person.firstname STARTS WITH 'And' AND EXISTS (person.surname)
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+-------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator        | Details                                                                                         | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------+-------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults | person                                                                                          |              1 |    1 |       0 |               0 |                 0 |               0.0000 |
| |               +-------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexSeek  | person:Person(firstname, surname) WHERE firstname STARTS WITH $autostring_0 AND exists(surname) |              1 |    1 |       2 |               0 |                 0 |               0.0000 |
+-----------------+-------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 2, total allocated memory: 0

6.3.12. Suffix search using ENDS WITH (single-property index)

The ENDS WITH 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 ending with 'hn' 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 ENDS WITH.

Query. 

MATCH (person:Person)
WHERE person.firstname ENDS WITH 'hn'
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+------------------------+------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| Operator               | Details                                                          | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Order                |
+------------------------+------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +ProduceResults        | person                                                           |              2 |    1 |       0 |               0 |                 0 |               0.0000 | person.firstname ASC |
| |                      +------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +NodeIndexEndsWithScan | person:Person(firstname) WHERE firstname ENDS WITH $autostring_0 |              2 |    1 |       2 |               0 |                 0 |               0.0000 | person.firstname ASC |
+------------------------+------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+

Total database accesses: 2, total allocated memory: 0

6.3.13. Suffix search using ENDS WITH (composite index)

The ENDS WITH predicate on person.surname in the following query will use the Person(surname,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.

Query. 

MATCH (person:Person)
WHERE person.surname ENDS WITH '300' AND EXISTS (person.age)
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator        | Details                                                                                  | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------+------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults | person                                                                                   |             11 |    1 |       0 |               0 |                 0 |               0.0000 |
| |               +------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +Filter         | cache[person.surname] ENDS WITH $autostring_0                                            |             11 |    1 |       0 |               0 |                 0 |               0.0000 |
| |               +------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexScan  | person:Person(surname, age) WHERE exists(surname) AND exists(age), cache[person.surname] |            106 |  303 |     304 |               0 |                 0 |               0.0000 |
+-----------------+------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 304, total allocated memory: 0

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

Query. 

MATCH (person:Person)
WHERE person.firstname CONTAINS 'h'
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+------------------------+-----------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| Operator               | Details                                                         | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio | Order                |
+------------------------+-----------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +ProduceResults        | person                                                          |              2 |    1 |       0 |               0 |                 0 |               0.0000 | person.firstname ASC |
| |                      +-----------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+
| +NodeIndexContainsScan | person:Person(firstname) WHERE firstname CONTAINS $autostring_0 |              2 |    1 |       2 |               0 |                 0 |               0.0000 | person.firstname ASC |
+------------------------+-----------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+----------------------+

Total database accesses: 2, total allocated memory: 0

6.3.15. Substring search using CONTAINS (composite index)

The CONTAINS predicate on person.surname in the following query will use the Person(surname,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.

Query. 

MATCH (person:Person)
WHERE person.surname CONTAINS '300' AND EXISTS (person.age)
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator        | Details                                                                                  | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------+------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults | person                                                                                   |             11 |    1 |       0 |               0 |                 0 |               0.0000 |
| |               +------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +Filter         | cache[person.surname] CONTAINS $autostring_0                                             |             11 |    1 |       0 |               0 |                 0 |               0.0000 |
| |               +------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexScan  | person:Person(surname, age) WHERE exists(surname) AND exists(age), cache[person.surname] |            106 |  303 |     304 |               0 |                 0 |               0.0000 |
+-----------------+------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 304, total allocated memory: 0

6.3.16. Existence check using exists (single-property index)

The exists(p.firstname) predicate in the following query will use the Person(firstname) index, if it exists.

Query. 

MATCH (p:Person)
WHERE EXISTS (p.firstname)
RETURN p

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+---------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator        | Details                                     | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------+---------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults | p                                           |              2 |    2 |       0 |               0 |                 0 |               0.0000 |
| |               +---------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexScan  | p:Person(firstname) WHERE exists(firstname) |              2 |    2 |       3 |               0 |                 0 |               0.0000 |
+-----------------+---------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 3, total allocated memory: 0

6.3.17. Existence check using exists (composite index)

The exists(p.firstname) and exists(p.surname) predicate 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.

Query. 

MATCH (p:Person)
WHERE EXISTS (p.firstname) AND EXISTS (p.surname)
RETURN p

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+--------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator        | Details                                                                  | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------+--------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults | p                                                                        |              1 |    2 |       0 |               0 |                 0 |               0.0000 |
| |               +--------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexScan  | p:Person(firstname, surname) WHERE exists(firstname) AND exists(surname) |              1 |    2 |       3 |               0 |                 0 |               0.0000 |
+-----------------+--------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 3, total allocated memory: 0

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

Query. 

MATCH (p:Person)
WHERE distance(p.location, point({ x: 1, y: 2 }))< 2
RETURN p.location

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator              | Details                                                                                              | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults       | `p.location`                                                                                         |              0 |    9 |       0 |               0 |                 0 |               0.0000 |
| |                     +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +Projection           | cache[p.location] AS `p.location`                                                                    |              0 |    9 |       0 |               0 |                 0 |               0.0000 |
| |                     +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +Filter               | distance(cache[p.location], point({x: $autoint_0, y: $autoint_1})) < $autoint_2                      |              0 |    9 |       0 |               0 |                 0 |               0.0000 |
| |                     +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexSeekByRange | p:Person(location) WHERE distance(location, point($autoint_0, $autoint_1)) < $autoint_2, cache[p.loc |              0 |    9 |      10 |               0 |                 0 |               0.0000 |
|                       | ation]                                                                                               |                |      |         |                 |                   |                      |
+-----------------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 10, total allocated memory: 0

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

Query. 

MATCH (p:Person)
WHERE distance(p.place, point({ x: 1, y: 2 }))< 2 AND EXISTS (p.name)
RETURN p.place

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator        | Details                                                                                              | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults | `p.place`                                                                                            |             69 |    9 |       0 |               0 |                 0 |               0.0000 |
| |               +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +Projection     | cache[p.place] AS `p.place`                                                                          |             69 |    9 |       0 |               0 |                 0 |               0.0000 |
| |               +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +Filter         | distance(cache[p.place], point({x: $autoint_0, y: $autoint_1})) < $autoint_2                         |             69 |    9 |       0 |               0 |                 0 |               0.0000 |
| |               +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexSeek  | p:Person(place, name) WHERE distance(place, point($autoint_0, $autoint_1)) < $autoint_2 AND exists(n |             69 |    9 |      10 |               0 |                 0 |               0.0000 |
|                 | ame), cache[p.place]                                                                                 |                |      |         |                 |                   |                      |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 10, total allocated memory: 0

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

Query. 

MATCH (person:Person)
WHERE point({ x: 1, y: 5 })< person.location < point({ x: 2, y: 6 })
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator              | Details                                                                                              | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults       | person                                                                                               |              0 |    1 |       0 |               0 |                 0 |               0.0000 |
| |                     +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexSeekByRange | person:Person(location) WHERE location > point({x: $autoint_2, y: $autoint_3}) AND location < point( |              0 |    1 |       2 |               0 |                 0 |               0.0000 |
|                       | {x: $autoint_0, y: $autoint_1})                                                                      |                |      |         |                 |                   |                      |
+-----------------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 2, total allocated memory: 0

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

Query. 

MATCH (person:Person)
WHERE point({ x: 1, y: 5 })< person.place < point({ x: 2, y: 6 }) AND EXISTS (person.firstname)
RETURN person

Query Plan. 

Compiler CYPHER 4.1

Planner COST

Runtime INTERPRETED

Runtime version 4.1

+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| Operator        | Details                                                                                              | Estimated Rows | Rows | DB Hits | Page Cache Hits | Page Cache Misses | Page Cache Hit Ratio |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +ProduceResults | person                                                                                               |              0 |    1 |       0 |               0 |                 0 |               0.0000 |
| |               +------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+
| +NodeIndexSeek  | person:Person(place, firstname) WHERE place > point({x: $autoint_2, y: $autoint_3}) AND place < poin |              0 |    1 |       2 |               0 |                 0 |               0.0000 |
|                 | t({x: $autoint_0, y: $autoint_1}) AND exists(firstname)                                              |                |      |         |                 |                   |                      |
+-----------------+------------------------------------------------------------------------------------------------------+----------------+------+---------+-----------------+-------------------+----------------------+

Total database accesses: 2, total allocated memory: 0