+1 (415) 649-9454
GraphGuru

Tuning Slow Cypher: How to Read a PROFILE Plan (and Fix What It Shows)

Almost every "Neo4j is slow" ticket we pick up as consultants turns out to be one query, one missing index, or one accidental cross product. The graph is fine. The plan is not. Neo4j will happily tell you exactly what went wrong — the information is in the query plan, and reading it is a skill you can learn in an afternoon.

This tutorial walks through that skill end to end: how to get a plan, which numbers actually matter, which operators should make you nervous, and the concrete fixes for each. Everything below runs on a stock Neo4j 5.x or 2025.x/2026.x instance (Community, Enterprise, or Aura); if you need a scratch database, the Docker setup from our earlier tutorial takes about two minutes.

A dataset small enough to reason about

We will use a tiny order graph so that plan numbers stay readable. Paste this into Neo4j Browser or cypher-shell:

UNWIND range(1, 20000) AS i
CREATE (c:Customer {customerId: i, email: 'user' + i + '@example.com', country: CASE i % 5 WHEN 0 THEN 'DE' WHEN 1 THEN 'US' WHEN 2 THEN 'UK' WHEN 3 THEN 'FR' ELSE 'NL' END})
WITH c, i
UNWIND range(1, 5) AS j
CREATE (c)-[:PLACED {at: datetime() - duration({days: j})}]->(:Order {orderId: i * 10 + j, total: toFloat(j) * 19.99, status: CASE j % 3 WHEN 0 THEN 'SHIPPED' WHEN 1 THEN 'PENDING' ELSE 'CANCELLED' END});

That is 20,000 customers and 100,000 orders, with no indexes yet — deliberately, because the first plan should look bad.

EXPLAIN vs. PROFILE

Two prefixes, two very different tools:

  • EXPLAIN compiles the query and shows the plan the planner intends to use, with estimated rows. Nothing executes. Safe on writes, safe on production, instant.
  • PROFILE actually executes the query and annotates every operator with what really happened: rows, db hits, page cache activity, memory. Costs a real execution — and a profiled write query really writes. Wrap writes in a transaction you roll back, or profile them against a copy.

Rule of thumb: EXPLAIN to check shape, PROFILE to check cost. Estimated rows come from database statistics and can be wildly wrong on skewed data; actual rows never lie.

The four numbers that matter

Every operator box in a profiled plan carries the same handful of metrics. In practice you read four of them:

  1. Rows — how many rows the operator produced. Read the plan bottom-up and watch where the row count explodes. That explosion is almost always the bug.
  2. DB Hits — units of storage-engine work: reading a node, a relationship, a property, an index entry. Total db hits is the single best proxy for "how much work did this query do". A query returning 10 rows with 4,000,000 db hits is doing something silly.
  3. Page Cache Hits / Misses — misses mean the data was not in memory and had to come off disk. High miss ratios point at a cold cache or an undersized page cache rather than a bad plan.
  4. Memory (Bytes) — peak memory for buffering operators such as Sort, EagerAggregation, NodeHashJoin and Eager. This is where Memory pool exhausted errors come from.

In Browser, click a plan box to expand these; in cypher-shell they are printed in the ASCII plan. Also note the summary lines above the plan: total db hits and total time.

Step 1 — profile a naive query

PROFILE
MATCH (c:Customer {email: 'user17345@example.com'})-[:PLACED]->(o:Order)
WHERE o.status = 'SHIPPED'
RETURN o.orderId, o.total
ORDER BY o.total DESC;

With no indexes, the bottom of that plan is a NodeByLabelScan over all 20,000 Customer nodes, followed by a Filter on email. Roughly 40,000+ db hits to find one customer whose orders you then expand. The pattern to recognise:

+-----------------------+----------------+------+---------+
| Operator              | Estimated Rows | Rows | DB Hits |
+-----------------------+----------------+------+---------+
| +ProduceResults       |              2 |    2 |       0 |
| +Sort                 |              2 |    2 |       0 |
| +Filter               |              2 |    2 |   30000 |
| +Expand(All)          |              5 |    5 |       6 |
| +Filter               |              1 |    1 |   20000 |
| +NodeByLabelScan      |          20000 |20000 |   20001 |
+-----------------------+----------------+------+---------+

(Your numbers will differ; the shape is what counts.) Two Filter operators doing tens of thousands of db hits to produce two rows is the signature of a missing index.

Step 2 — index the lookup, not the query

CREATE CONSTRAINT customer_email IF NOT EXISTS
FOR (c:Customer) REQUIRE c.email IS UNIQUE;

CREATE INDEX order_status IF NOT EXISTS
FOR (o:Order) ON (o.status);

CALL db.awaitIndexes(300);

Re-profile. The NodeByLabelScan collapses into a NodeUniqueIndexSeek (or NodeIndexSeek) with a handful of db hits, and the total drops by three or four orders of magnitude. That is the whole game: anchor every query on an index seek, then expand through relationships.

A few index facts worth internalising:

  • A uniqueness constraint creates a backing index. Don't create both.
  • RANGE indexes serve equality, range, STARTS WITH and ordering. TEXT indexes serve CONTAINS and ENDS WITH on strings. POINT indexes serve spatial predicates. FULLTEXT indexes serve scored keyword search and must be called via CALL db.index.fulltext.queryNodes(...). VECTOR indexes serve approximate nearest-neighbour search (see our vector search tutorial).
  • A composite index on (a, b) only helps when the leading property is constrained. ON (o.status, o.total) will not accelerate a query that filters on total alone.
  • Relationship property indexes exist too. If you filter on [:PLACED {at: ...}], index it: CREATE INDEX placed_at FOR ()-[r:PLACED]-() ON (r.at).
  • Check what you already have with SHOW INDEXES — and look at the usage stats. An index nothing ever seeks is write overhead you are paying for nothing.

Step 3 — the operators that mean trouble

Once you can read a plan, most tuning becomes pattern recognition.

AllNodesScan — the planner has no label and no index to work with. Add a label to the pattern, or an index for the predicate. In a query you actually meant to run over everything, batch it instead (see step 6).

NodeByLabelScan at the bottom of an OLTP query — fine for 500 nodes, a problem at 5 million. Index the anchor property.

CartesianProduct — two disconnected patterns in the same MATCH. Rows multiply:

// bad: 20,000 x 100,000 rows of potential
MATCH (c:Customer), (o:Order)
WHERE c.customerId = o.orderId / 10
RETURN c, o;

Fix it by connecting the patterns through a relationship, or by forcing order with WITH:

MATCH (c:Customer {customerId: 1734})
WITH c
MATCH (c)-[:PLACED]->(o:Order)
RETURN c, o;

Eager — the planner has inserted a full materialisation barrier because a later part of the query reads what an earlier part writes (the classic "Eager operator" warning in loading scripts). It kills streaming and inflates memory. Usually caused by mixing MATCH and CREATE/MERGE/SET over the same labels in one query, or by LOAD CSV doing several writes per row. Split the work into separate passes.

Filter immediately after Expand(All) with huge row counts — you expanded a supernode and then threw most of it away. Push the predicate into the pattern, add a relationship property index, or restructure the model (a typed relationship such as :PLACED_SHIPPED is sometimes the right answer for a hot access path).

EagerAggregation / Sort with big memory numbers — aggregate or sort after you have reduced cardinality, not before. A WITH ... LIMIT in the right place can turn 4 GB of buffering into 4 MB.

VarLengthExpand(All) with unbounded depth — [:PLACED*] on a connected graph is a traversal of everything. Bound the depth, or use the shortest-path forms and apoc.path.* / GDS traversals designed for it.

Step 4 — control cardinality on purpose

The planner optimises what you wrote. Two semantically identical queries can have very different plans, and the difference is usually where you narrow the row set. Compare:

// counts orders per country by expanding everything first
PROFILE
MATCH (c:Customer)-[:PLACED]->(o:Order)
WHERE o.status = 'SHIPPED'
RETURN c.country AS country, count(o) AS orders
ORDER BY orders DESC;

versus starting from the indexed, more selective side:

PROFILE
MATCH (o:Order {status: 'SHIPPED'})
MATCH (c:Customer)-[:PLACED]->(o)
RETURN c.country AS country, count(o) AS orders
ORDER BY orders DESC;

On this dataset the difference is modest; on a real graph with a 1%-selective status value it is often 10x. Profile both. Never assume.

If you are confident the planner is choosing the wrong leaf, you can hint — USING INDEX c:Customer(email), USING SCAN, USING JOIN ON — but treat hints as a last resort and a documented one. They freeze a decision that a future statistics update, or a future Neo4j version, might have made better on its own. Before hinting, refresh statistics on the database and re-profile.

Step 5 — pick the right runtime

Modern Neo4j compiles queries into one of several runtimes, and you can request one per query:

CYPHER runtime=parallel
MATCH (o:Order)
WHERE o.total > 50
RETURN o.status, count(*) AS n;

The parallel runtime spreads a read-only query across multiple worker threads and can dramatically cut wall-clock time for large analytical scans. It is for reads only, it consumes more of the server's cores, and it is not a substitute for an index on a selective lookup. The pipelined runtime is the general-purpose default in Enterprise; slotted and interpreted are fallbacks for queries the others cannot compile. The plan header tells you which runtime actually ran — always confirm rather than assume, because an unsupported feature silently pushes the query back to a slower runtime.

Also note the Cypher language version. From the CalVer releases onward you may be running Cypher 5 or Cypher 25 semantics, and the planner differs between them — see our note on CalVer, Cypher 25, and GQL. When you benchmark, pin the language version explicitly (CYPHER 25 MATCH ...) so you are comparing like with like.

Step 6 — batch the writes

Long-running write queries are their own failure mode: memory pressure, lock contention, and a rollback that undoes an hour of work. Use subquery transactions:

MATCH (o:Order)
WHERE o.status = 'CANCELLED'
CALL (o) {
  SET o.archived = true
} IN TRANSACTIONS OF 10000 ROWS;

Each batch commits independently. The outer MATCH streams, the inner block writes, and Eager never appears. (On older releases the syntax is CALL { WITH o ... } IN TRANSACTIONS OF 10000 ROWS.) For bulk property backfills, this pattern plus an index on the driving predicate is almost always faster than anything clever.

Step 7 — find the slow queries you have not thought to profile

Profiling assumes you already know which query is slow. To find out:

  • SHOW TRANSACTIONS — what is running right now, with elapsed time and the client that submitted it.
  • dbms.setConfig-free config check: the query.log (Enterprise) with db.logs.query.threshold set to something like 500ms gives you a ranked list of offenders over time. This is the single highest-value observability switch in Neo4j.
  • SHOW INDEXES usage counters and SHOW CONSTRAINTS — cheap sanity checks after any deploy.
  • Aura and Neo4j Ops Manager both surface slow-query and resource dashboards; use them, then come back and PROFILE the top ten by total time, not by worst single execution. Ten thousand executions of a 40ms query hurt more than one 3-second report.

Our Neo4j health check checklist covers the surrounding configuration — page cache and heap sizing, index inventory, driver settings — that determines whether a good plan actually runs fast.

A tuning loop you can hand to your team

  1. Capture the slow query with real parameters (never with literals inlined — parameterised queries reuse the plan cache; literal-laden ones do not).
  2. EXPLAIN it. Is the shape sane? Any AllNodesScan, CartesianProduct, Eager?
  3. PROFILE it. Where do rows explode? Where do db hits concentrate?
  4. Apply the smallest fix: index, predicate placement, WITH barrier, batching.
  5. Re-PROFILE and record the before/after db hits in the pull request. Numbers, not adjectives.
  6. Only then consider hints, runtime overrides, or model changes.

Most queries need one pass through that loop. The ones that need more are usually telling you something about the data model rather than the query — a supernode, a property that should be a relationship, or a relationship type that should be split.

Where this gets hard

Plan reading takes you a long way, but some problems are structural: supernodes with millions of relationships, models that force unbounded traversals, multi-tenant graphs that need composite databases, or clusters where the read replicas are the bottleneck rather than the plan. That is the point where a second pair of eyes pays for itself.

If you have a query that resists everything above — or a graph where you suspect the model is the real cost — get in touch. Our senior Neo4j consultants do exactly this work: profile the top queries, fix the model and the indexes, and leave your team with the tuning loop above running on its own.