Cypher Query Tuning Anti-Patterns

A well-designed schema does not guarantee a fast query. The same graph that answers one MATCH in three database hits can force another into a multi-million-hit scan, and the difference lives entirely in how the Cypher is written: where the traversal is anchored, how patterns are joined, whether paths are bounded, and whether the text is parameterized so the planner can reuse a cached plan. This reference catalogues the recurring query-level mistakes that turn a healthy Neo4j 5.x deployment into a latency incident, and pairs each with the PROFILE signature that exposes it and the mechanical rewrite that fixes it. These are defects in the query text, not the model — the structural, storage-level counterparts live in property graph anti-patterns, and the two references are meant to be read together: fix the shape of the graph there, fix the shape of the query here.

Prerequisite Concepts

Every rewrite below is justified by what it does to the execution plan, so you must be able to read one before you can trust the fix.

• Reading a plan. You cannot diagnose an anti-pattern you cannot see. Start from query plan analysis with EXPLAIN and PROFILE, which explains operators, estimated versus actual rows, and the db-hits column every symptom in this page refers to.
• The optimization discipline as a whole. These anti-patterns are one facet of the broader Cypher query optimization and index lifecycle reference — indexing, constraints, and plan caching are the other levers, and a query fix often depends on one of them being in place.
• A usable index to anchor on. Half of these rewrites replace a scan with an index seek, which presumes the right index exists. If the anchor property is unindexed, resolve that first with index selection and composite indexing.
• A stable schema underneath. A query tuned against a moving model is wasted work. The label and relationship vocabulary the planner routes on is defined in Neo4j Graph Schema Design & Architecture.

Conceptual Model: Fan-Out vs Anchored Path

Almost every slow-query anti-pattern reduces to one geometry: the query produces a wide intermediate result the planner must carry through later operators, instead of starting narrow and staying narrow. A missing anchor makes the leaf operator emit the whole label; a disconnected pattern multiplies two row sets; an unbounded path fans out across the connected component. The cure is always the same shape — begin at a single index-backed node and expand along a bounded, connected path so cardinality stays flat.

Design Rules: Symptom to Rewrite

Each row is a self-contained fix. The middle column is the operator or signal you will see in PROFILE; the right column is the change that removes it.

Two rules cut across the whole table. First, push selectivity to the leaf: the operator that reads from the store should read the fewest rows, which means the most selective, index-backed predicate belongs on the anchor. Second, keep intermediate cardinality below the result cardinality wherever possible — if a mid-plan operator processes far more rows than the query returns, that operator is where your latency is, and a predicate or a bound has been placed too late.

Step-by-Step Implementation

Step 1 — Anchor the leaf on an index instead of scanning

The single most common tuning defect is a query with no index-backed starting point. Without one, the planner has nowhere to begin but the whole label, so it emits an AllNodesScan or NodeByLabelScan and filters afterward.

// ANTI-PATTERN: email is unindexed, so the leaf scans every :Customer.
// PROFILE shows NodeByLabelScan -> Filter with db-hits ≈ total Customer count.
PROFILE
MATCH (c:Customer)
WHERE c.email = $email
RETURN c.customer_id;

The fix is a supporting index; once it is ONLINE, the same query plans as a seek that touches only the matching nodes.

// Create the anchor. A uniqueness constraint would also work and would
// additionally guarantee identity — either provisions a backing RANGE index.
CREATE INDEX customer_email IF NOT EXISTS
FOR (c:Customer) ON (c.email);

// Same query, now leaf = NodeIndexSeek, db-hits collapses to a handful.
PROFILE
MATCH (c:Customer {email: $email})
RETURN c.customer_id;

Deciding which index makes the difference between a seek and a scan — range, composite, point, or full-text — is covered in composite index vs full-text index selection. The reduction in db-hits this produces is walked through end to end in reducing db-hits in a slow Cypher query.

Step 2 — Connect disconnected patterns to kill cartesian products

When two MATCH clauses (or two comma-separated patterns) share no variable, the planner has no join key and must pair every row of one with every row of the other. The result set is the product of the two cardinalities.

// ANTI-PATTERN: c and p are never connected, so the planner emits a
// CartesianProduct of |Customer| x |Product| rows before filtering.
PROFILE
MATCH (c:Customer {customer_id: $cid})
MATCH (p:Product {sku: $sku})
CREATE (c)-[:VIEWED]->(p);

Anchoring both endpoints on indexed keys keeps each branch to one row, so even a cartesian join is cheap here — but when the branches are genuinely large, connect them through a shared traversal or pipeline the first result into the second with WITH. The full detection-and-removal procedure, including the correlated-subquery form, is in eliminating cartesian products in Cypher queries.

Step 3 — Bound every variable-length traversal

An unbounded * traversal is licensed to walk the entire reachable subgraph. On a densely connected component it visits orders of magnitude more relationships than the answer needs.

// ANTI-PATTERN: no hop bound — VarLengthExpand can traverse the whole
// connected component before LIMIT ever applies.
PROFILE
MATCH path = (a:Account {account_id: $aid})-[:TRANSFERRED_TO*]->(b:Account)
RETURN b.account_id LIMIT 20;

// REWRITE: anchor on the indexed key, cap the depth, and the expand
// stops at four hops regardless of how large the component is.
PROFILE
MATCH path = (a:Account {account_id: $aid})-[:TRANSFERRED_TO*1..4]->(b:Account)
RETURN b.account_id, length(path) AS hops LIMIT 20;

Choosing the right upper bound, and when to switch to shortestPath, is the subject of bounding variable-length path traversals.

Step 4 — Contain OPTIONAL MATCH sprawl

Each OPTIONAL MATCH preserves rows even when it matches nothing, and stacking several of them lets their partial results multiply. Aggregate each optional branch behind its own WITH so the row count is collapsed before the next branch expands it.

// ANTI-PATTERN: three optional expansions multiply into a wide row set,
// then DISTINCT tries to repair the damage at the end.
PROFILE
MATCH (c:Customer {customer_id: $cid})
OPTIONAL MATCH (c)-[:PLACED]->(o:Order)
OPTIONAL MATCH (c)-[:VIEWED]->(p:Product)
OPTIONAL MATCH (c)-[:LOCATED_IN]->(r:Region)
RETURN c, count(DISTINCT o), count(DISTINCT p), collect(DISTINCT r.name);

// REWRITE: aggregate each branch to a scalar/list behind its own WITH so
// cardinality is flattened between optional expansions.
PROFILE
MATCH (c:Customer {customer_id: $cid})
OPTIONAL MATCH (c)-[:PLACED]->(o:Order)
WITH c, count(o) AS orders
OPTIONAL MATCH (c)-[:VIEWED]->(p:Product)
WITH c, orders, count(p) AS views
OPTIONAL MATCH (c)-[:LOCATED_IN]->(r:Region)
RETURN c.customer_id, orders, views, collect(r.name) AS regions;

Step 5 — Break the read/write Eager barrier

When a query reads a pattern and then writes in a way that could change what the read would have seen, the planner inserts an Eager operator that fully materializes the read before the write starts — a hidden pipeline stall. Interpreting when this barrier is protective versus wasteful is detailed in interpreting the Eager operator in Cypher PROFILE.

// ANTI-PATTERN: the CREATE could add rows the MATCH would re-read, so the
// planner forces an Eager that buffers every matched row first.
PROFILE
MATCH (c:Customer)
CREATE (:AuditEntry {customer_id: c.customer_id, at: datetime()});

// REWRITE: aggregate the read to a parameter list, then write in a second
// statement over UNWIND — no read/write dependency, no Eager.
MATCH (c:Customer) RETURN c.customer_id AS id;    // statement 1: collect ids
// statement 2 (separate call), driver passes ids back as $rows
UNWIND $rows AS id
CREATE (:AuditEntry {customer_id: id, at: datetime()});

Step 6 — Aggregate late, project narrow, parameterize always

Three smaller disciplines close out the pattern. Filter and limit before a collect() so aggregation runs over the final row set, not the raw expansion. Return only the properties you need rather than whole nodes, so mid-plan projections stay small. And never inline literals that vary between calls — a parameter lets the planner reuse one cached plan instead of compiling a fresh one per distinct string, the concern covered in avoiding plan cache thrash from inline literals.

// ANTI-PATTERN: collects every order for every customer, THEN filters —
// the giant list is built and discarded.
MATCH (c:Customer)-[:PLACED]->(o:Order)
WITH c, collect(o) AS orders
WHERE size(orders) > 10
RETURN c.customer_id, size(orders);

// REWRITE: count with a predicate pushed down; no list is ever materialized.
MATCH (c:Customer)-[:PLACED]->(o:Order)
WITH c, count(o) AS order_count
WHERE order_count > 10
RETURN c.customer_id, order_count;

Constraint & Validation Layer

Query tuning and schema DDL meet at the index. A rewrite that assumes an index seek is only correct if the index exists and is ONLINE; issued against a still-building index, the same query silently falls back to the scan you were trying to eliminate. Gate any deploy that ships a tuned query on index readiness.

// Confirm the anchor index the rewrite depends on is live before shipping.
SHOW INDEXES YIELD name, state, type
WHERE name = 'customer_email'
RETURN state;   // block release until this returns 'ONLINE'

Two validation habits keep rewrites honest. First, capture a PROFILE of the query before and after the change and compare the total db-hits, not the wall-clock time — wall-clock is noisy under cache warmth, db-hits is the deterministic work measure. Second, assert the leaf operator: a tuned lookup should show NodeIndexSeek (or NodeUniqueIndexSeek when a constraint backs it), never NodeByLabelScan with a trailing Filter. The lifecycle rules for the constraints these seeks rely on are governed in constraint lifecycle management.

Performance & Scale Considerations

The cost of every anti-pattern above scales with data, not with the query text, which is why they hide in development and surface in production.

• Cardinality is the currency. A cartesian product or unbounded path costs nothing on a hundred-node test graph and dominates the plan on a hundred-million-node one, because both grow multiplicatively with the branches they touch. Always validate a rewrite against production-scale statistics, not a fixture.
• Selectivity decides the anchor. The planner starts from the most selective index-backed predicate it can find. If two properties are indexed, an equality filter on the higher-cardinality one yields the narrower leaf — the same reasoning that governs ordering properties in a composite index correctly.
• Intermediate width, not result width, sets memory. A query that returns 10 rows but builds a 2-million-row intermediate spends memory and db-hits on the intermediate. Pushing predicates and limits earlier is the lever, and it matters most on the largest label.
• Plan reuse compounds. A parameterized query compiles once and runs from cache thousands of times; a literal-laden one recompiles on every distinct value, adding planning latency to every call and evicting other plans from the cache. At scale, cache-hit rate is a throughput metric.

Known Pitfalls

1. Over-indexing to “fix” every scan. Symptom: write throughput drops after adding indexes to chase seeks. Root cause: every index adds write-time maintenance, and an index the planner never chooses is pure overhead. Fix: index the properties your anchors actually filter on, confirm with PROFILE that the planner uses each one, and drop indexes that no plan selects.

2. Bounding a path too tightly. Symptom: a traversal is fast but silently returns incomplete results after adding *1..2. Root cause: the hop cap was set below the real path length to make the query fast, trading correctness for latency. Fix: set the bound from the domain’s true maximum depth, and if that depth is genuinely large, switch to shortestPath or an anchored breadth-limited expansion rather than truncating.

3. Rewriting away an Eager that was protecting you. Symptom: after splitting a read/write query to remove Eager, results become non-deterministic or a row is processed twice. Root cause: the Eager was enforcing a correct read-before-write ordering, not wasting effort. Fix: only remove it by genuinely decoupling the read and write (aggregate to a parameter list, then write in a separate statement), never by forcing the planner past a dependency it inserted for correctness.

4. Parameterizing structure, not just values. Symptom: plan cache still thrashes despite using parameters. Root cause: labels, relationship types, or property keys were templated into the query string, so each variant is still a distinct query. Fix: parameterize only values; keep labels and relationship types as an allow-listed, fixed vocabulary in the query text, exactly as the property graph anti-patterns guidance on dynamic labels requires.

Related

• Query Plan Analysis with EXPLAIN and PROFILE — the plan-reading skill every diagnosis here depends on.
• Index Selection and Composite Indexing — provisioning the anchors that turn scans into seeks.
• Query Parameterization and Plan Caching — reusing compiled plans instead of recompiling per literal.
• Eliminating Cartesian Products in Cypher Queries — detecting and connecting disconnected patterns in full.
• Bounding Variable-Length Path Traversals — capping and anchoring * expansions.
• Property Graph Anti-Patterns — the schema-level counterpart to these query-level defects.