Docs
View as markdown

Tune a query

Read a query plan with EXPLAIN and PROFILE, act on the planner's notifications, and confirm that an index changed the plan, on the shared example graph.

cyphercli

Before you start: Your first graph in the CLI

This guide shows you how to read the plan The tree of operators the planner chooses to run a statement; EXPLAIN shows it without running, PROFILE runs and adds rows and time per operator. Read more in the glossary relate chooses for a statement, act on the notification Advice the planner attaches to a plan, such as a missing index, with a GQLSTATUS code, a severity and a remedy; derived only under EXPLAIN or PROFILE. Read more in the glossary the planner attaches to it, and prove with a second plan that the fix worked. EXPLAIN plans a statement without running it; PROFILE runs it and adds what each step did. The example is a lookup by name on the shared graph, planned once without an index A structure the planner uses to find nodes or relationships by a property without scanning a label: range, text, point, vector or full-text. Read more in the glossary and once with one.

Prerequisites

  • The example graph from your first graph. The first step creates it in the cells and in a terminal.
  • For the terminal steps, relate on your PATH (install). The cells run in your browser without it.

Load the example graph

The cells on this page run in page order on one in-memory store A relate database: a directory, a single .rl file, or :memory:, opened with --database. Read more in the glossary. This cell writes the shared graph:

Cypher
CREATE (ada:Person {name: 'Ada', role: 'engineer'}),
       (grace:Person {name: 'Grace', role: 'engineer'}),
       (noor:Person {name: 'Noor', role: 'maintainer'}),
       (relate:Project {name: 'relate', language: 'Rust'}),
       (tck:Project {name: 'opencypher-tck', language: 'Gherkin'}),
       (ada)-[:WORKS_ON {since: 2024}]->(relate),
       (grace)-[:WORKS_ON {since: 2023}]->(relate),
       (grace)-[:WORKS_ON {since: 2022}]->(tck),
       (noor)-[:MAINTAINS]->(relate),
       (ada)-[:KNOWS]->(grace)
Recorded result
(no rows) · nodes created: 5, relationships created: 5, properties set: 13, labels added: 5

To follow in a terminal, write the same graph to a new single-file store, team.rl. This command writes:

relate --database ./team.rl "CREATE (ada:Person {name: 'Ada', role: 'engineer'}),
       (grace:Person {name: 'Grace', role: 'engineer'}),
       (noor:Person {name: 'Noor', role: 'maintainer'}),
       (relate:Project {name: 'relate', language: 'Rust'}),
       (tck:Project {name: 'opencypher-tck', language: 'Gherkin'}),
       (ada)-[:WORKS_ON {since: 2024}]->(relate),
       (grace)-[:WORKS_ON {since: 2023}]->(relate),
       (grace)-[:WORKS_ON {since: 2022}]->(tck),
       (noor)-[:MAINTAINS]->(relate),
       (ada)-[:KNOWS]->(grace)"

Read the plan with EXPLAIN

Prefix the statement with EXPLAIN. Nothing runs: writes, procedures and LOAD CSV are only planned, so EXPLAIN is safe on any statement.

Cypher
EXPLAIN MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project)
RETURN proj.name AS project
Recorded result
(no rows)

The cell returns no rows. Its JSON view holds the envelope One compact JSON object per result set, printed by --mode envelope, that keeps the column list even when there are no rows. Read more in the glossary: plan is the operator tree and notifications is the planner's advice. In a terminal, --mode table prints the same plan as a table and the notifications as text:

relate --database ./team.rl --read-only --mode table "EXPLAIN MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project) RETURN proj.name AS project"
Query: MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project) RETURN proj.name AS project
Plan:
+------------------+----+----------------------------------------+
| Operator         | Id | Details                                |
+------------------+----+----------------------------------------+
| + ProduceResults | 0  | 1 item(s)                              |
+------------------+----+----------------------------------------+
| | + Expand       | 1  | (proj:Project)<-[:WORKS_ON]-(p:Person) |
+------------------+----+----------------------------------------+
Notifications:
Id 1: INFORMATION 03C91 Relate.Notification.Statement.MissingIndex: the equality predicate on name filters :Person without a range index on (:Person {name}) Remedy: CREATE INDEX relate_Person__name FOR (n:Person) ON (n.name)

Read the table from the bottom up. Each row is an operator; | + nests it under the row before it, and the root, ProduceResults, returns the rows. Details names what the operator works on: here Expand starts from every Project node, walks WORKS_ON backward, and keeps the person named Ada. Nothing narrows the start, so the work grows with the number of nodes that carry the label.

Act on the notification

Each notification line reads <severity> <GQLSTATUS> <code>: <description> Remedy: <remedy>, with Id <n>: in front when it concerns one operator. In the envelope, the same entry has the keys code, gql_status, severity, category, title, description and remedy, plus operator_id. The remedy for a missing index is the statement to run, with the index name derived from the label and the property:

Cypher
CREATE INDEX relate_Person__name FOR (n:Person) ON (n.name)
Recorded result
(no rows) · indexes added: 1

The same remedy in the terminal writes the index to team.rl:

relate --database ./team.rl "CREATE INDEX relate_Person__name FOR (n:Person) ON (n.name)"

The planner emits these performance notifications. The descriptions are quoted from runs on relate's engine; the parts that name your labels, properties, and counts vary.

GQLSTATUSTitleFires onExample descriptionRemedy
03C91Missing indexAn equality predicate on a label's property with no range index on that pairthe equality predicate on name filters :Person without a range index on (:Person {name})CREATE INDEX relate_Person__name FOR (n:Person) ON (n.name)
03C90All nodes scanA label-less node pattern while the store has a property indexthe scan matches every node; consider the property index (:Person(name))Name the label the scan intends (`MATCH (n:<Label>)`); on the index's label the scan becomes an index-backed lookup.
03N90Cartesian productPatterns in one MATCH that share no variablethe MATCH patterns share no join anchorGive the patterns a join anchor: reuse a node variable across them, or add a pattern that relates their endpoints; otherwise run them as separate queries.
03N91Unbounded variable-length patternA variable-length pattern with no upper boundthe variable-length pattern (p:Person)-[:KNOWS*1..]->(q) has no upper boundBound the expansion (`[:R*1..5]`) where arbitrary length is not required, or add a selective endpoint/label predicate and a `LIMIT`.
03C92Eager aggregationAn aggregation whose input is estimated at 100,000 rows or morethe aggregation buffers an estimated 100000 input rows, at or above the 100000-row eager thresholdReduce the rows reaching the aggregation with a selective label/property predicate or a `LIMIT` before it; profile again to confirm the buffered input shrank.
03C93Estimation missPROFILE only: an operator whose estimate is 10 times its actual rows or moreestimated rows 10x or more above the actual rowsRework the operator's input so the estimate can see the filter (add the selective predicate at the scan, or reorder the join); the estimate is a planner assumption, not a runtime fact.

In --mode table, the three plan warnings (03C90, 03N90 and 03C93) print in the plan's Warnings column and a Warnings: line instead of the Notifications: block; the envelope carries all of them. A plain run without EXPLAIN or PROFILE derives no notifications. The planner also reports a label, relationship type or property key the store does not have (01N50 to 01N52), a CALL subquery without a scope clause (01N00) and a deprecated function or procedure (01N01); the errors reference lists every notification with its code and severity, at a stable anchor.

Compare the plans

Plan the same statement again:

Cypher
EXPLAIN MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project)
RETURN proj.name AS project
Recorded result
(no rows)
 
relate --database ./team.rl --read-only --mode table "EXPLAIN MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project) RETURN proj.name AS project"
Query: MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project) RETURN proj.name AS project
Plan:
+-------------------+----+---------------------------------------------------------------------------------------+----------------+
| Operator          | Id | Details                                                                               | Estimated Rows |
+-------------------+----+---------------------------------------------------------------------------------------+----------------+
| + ProduceResults  | 0  | 1 item(s)                                                                             | -              |
+-------------------+----+---------------------------------------------------------------------------------------+----------------+
| | + NodeIndexSeek | 1  | RANGE INDEX p:Person(name) WHERE name = 'Ada', (p:Person)-[:WORKS_ON]->(proj:Project) | 1              |
+-------------------+----+---------------------------------------------------------------------------------------+----------------+

Expand became NodeIndexSeek: the walk now starts from the one node the index finds, and the plan estimates one row. The Notifications: block is gone, and the envelope's notifications is an empty list.

The plan viewer holds both recorded plans and what changed between them. In a browser it draws each plan as a tree. Compare with the index creates relate_Person__name on this page's database, plans the statement without it and with it, and shows the two side by side; pressed again, it drops the index it created (an index the create-index cell made stays). PROFILE runs the statement for each plan and adds the rows, time and database hits of every operator.

The plan recorded for cell explain-before, as relate --mode table prints it:

Query: MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project) RETURN proj.name AS project
Plan:
+------------------+----+----------------------------------------+
| Operator         | Id | Details                                |
+------------------+----+----------------------------------------+
| + ProduceResults | 0  | 1 item(s)                              |
+------------------+----+----------------------------------------+
| | + Expand       | 1  | (proj:Project)<-[:WORKS_ON]-(p:Person) |
+------------------+----+----------------------------------------+
Notifications:
Id 1: INFORMATION 03C91 Relate.Notification.Statement.MissingIndex: the equality predicate on name filters :Person without a range index on (:Person {name}) Remedy: CREATE INDEX relate_Person__name FOR (n:Person) ON (n.name)

The plan recorded for cell explain-after, later on the page:

Query: MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project) RETURN proj.name AS project
Plan:
+-------------------+----+---------------------------------------------------------------------------------------+----------------+
| Operator          | Id | Details                                                                               | Estimated Rows |
+-------------------+----+---------------------------------------------------------------------------------------+----------------+
| + ProduceResults  | 0  | 1 item(s)                                                                             | -              |
+-------------------+----+---------------------------------------------------------------------------------------+----------------+
| | + NodeIndexSeek | 1  | RANGE INDEX p:Person(name) WHERE name = 'Ada', (p:Person)-[:WORKS_ON]->(proj:Project) | 1              |
+-------------------+----+---------------------------------------------------------------------------------------+----------------+

What changed between explain-before and explain-after:

Measureexplain-beforeexplain-afterChange
Operators, root firstProduceResults, ExpandProduceResults, NodeIndexSeekchanged
Estimated rows where the plan starts-1-
Notifications03C91none1 fewer

Notifications on explain-before, each with its remedy:

  • 03C91 Missing index on operator 1: the equality predicate on name filters :Person without a range index on (:Person {name}). Remedy: CREATE INDEX relate_Person__name FOR (n:Person) ON (n.name)

Notifications on explain-after: none.

In a browser, the viewer's comparison runs CREATE INDEX relate_Person__name FOR (n:Person) ON (n.name) on the page's in-memory database only.

Live: the plan is drawn as a tree, and PROFILE runs the statement on the page's database in your browser and adds each operator's rows, time and database hits as bars; the index button creates the index on the page's database, plans the statement with and without it side by side with the changes, and drops it again; a PROFILE downloads as a speedscope file.

Measure with PROFILE

PROFILE runs the statement and returns its rows, then adds the measured columns to the plan. This command opens the store read-only:

relate --database ./team.rl --read-only --mode table "PROFILE MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project) RETURN proj.name AS project"

The Time (ms) and Total (ms) columns differ on every run:

+----------+
| project  |
+----------+
| "relate" |
+----------+
Query: MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project) RETURN proj.name AS project
Plan:
+-------------------+----+---------------------------------------------------------------------------------------------------------------------+----------------+------+-----------+------------+---------+-------+--------+----------------+--------------------------+------------+
| Operator          | Id | Details                                                                                                             | Estimated Rows | Rows | Time (ms) | Total (ms) | Db Hits | Nodes | Degree | Memory (Bytes) | Expression Built (Bytes) | Est/Actual |
+-------------------+----+---------------------------------------------------------------------------------------------------------------------+----------------+------+-----------+------------+---------+-------+--------+----------------+--------------------------+------------+
| + ProduceResults  | 0  | 1 item(s)                                                                                                           | -              | 1    | 0.005     | 0.060      | 0       |       |        | 45             | 0                        | -          |
+-------------------+----+---------------------------------------------------------------------------------------------------------------------+----------------+------+-----------+------------+---------+-------+--------+----------------+--------------------------+------------+
| | + NodeIndexSeek | 1  | RANGE INDEX p:Person(name) WHERE name = 'Ada', (p:Person)-[:WORKS_ON]->(proj:Project)  [start #0, forward, index-0] | 1              | 1    | 0.055     | 0.055      | 2       | 1     | 1      | 45             | 0                        | 1.0x       |
+-------------------+----+---------------------------------------------------------------------------------------------------------------------+----------------+------+-----------+------------+---------+-------+--------+----------------+--------------------------+------------+

The same PROFILE before the index, measured on the same store (the plan viewer's PROFILE measures both in your browser):

PlanOperatorRowsDb HitsNodes
Before the indexExpand152
After the indexNodeIndexSeek121

Est/Actual compares the estimate with the rows the operator produced; ! after the ratio marks an estimate 10 times too high or more, the same rule as the 03C93 notification.

Verify

The tuned statement returns the same rows as before the index:

Cypher
MATCH (p:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project)
RETURN proj.name AS project
Recorded result
project
"relate"

1 row

The index is listed and online:

relate --database ./team.rl --read-only --mode table "SHOW INDEXES YIELD name, type, labelsOrTypes, properties, state RETURN name, type, labelsOrTypes, properties, state"
+-----------------------+---------+---------------+------------+----------+
| name                  | type    | labelsOrTypes | properties | state    |
+-----------------------+---------+---------------+------------+----------+
| "relate_Person__name" | "RANGE" | ["Person"]    | ["name"]   | "ONLINE" |
+-----------------------+---------+---------------+------------+----------+

If it goes wrong

SymptomCauseRemedy
semantic error: IndexAlreadyExists (exit code 6)An index with that name exists.Add IF NOT EXISTS after the name, or check SHOW INDEXES first.
The plan still shows Expand or NodeByLabelScan after the indexThe index is on another label or property than the predicate, or the predicate is not an equality or range comparison on the indexed property.Compare SHOW INDEXES with the 03C91 description, which names the label and property it wants.
A 01N50, 01N51 or 01N52 warning, and the statement returns no rowsA label, type or property key is misspelled.Correct the name the description quotes.
03N90 in the envelope, but no Notifications: block in the terminalPlan warnings print in the table's Warnings column.Read the Warnings: line under the plan, or use --mode envelope.