Tune a Slow Cypher Query by Changing Model, Predicate, Index, or Query Shape—Then Re-Measure

Learning outcomes

The tuning exercise now combines everything: one AtlasMart traversal, one baseline, one controlled change at a time, and an explicit decision about whether the right fix is query shape, predicate placement, index support, model shape—or no change at all.

1. State the workload contract before touching the query

2. Baseline: customer-first traversal

CYPHER 25PROFILEMATCH (c:Customer {customerId:$customerId})-[:PLACED]->(o:Order)-[line:CONTAINS]->(p:Product)WHERE p.categoryCode=$category AND p.price >= $minPriceRETURN DISTINCT p.productId,p.name,p.priceORDER BY p.price DESC,p.productId;

Hypothesis: the unique customer seek is cheap, but the hot customer generates many order and line rows before the product predicate can reject them. Record the actual rows at each expansion and filter. If that hypothesis is wrong, do not proceed as if it were true.

3. Change A: make product selectivity an alternative starting point

The composite product index from Chapter 09/this lab can only help as a starting access path if the query shape allows the planner to bind products through the indexed predicate before they are already reached by traversal. Compare a product-first rewrite, preserving result semantics.

CYPHER 25PROFILEMATCH (p:Product)WHERE p.categoryCode=$category AND p.price >= $minPriceMATCH (p)<-[line:CONTAINS]-(o:Order)<-[:PLACED]-(c:Customer {customerId:$customerId})RETURN DISTINCT p.productId,p.name,p.priceORDER BY p.price DESC,p.productId;

For a highly selective product predicate, the indexed start may reduce candidates. For a broad predicate, traversing backward from many products may be worse than customer-first. Test both dense and typical customers plus more than one price threshold.

4. Change B: add a tempting but possibly useless index

A common mistake is to create an index on every filtered property without checking whether the plan can use it. Create a disposable single-property price index, capture EXPLAIN, and ask whether it changes the chosen access path compared with the existing composite schema.

CYPHER 25CREATE RANGE INDEX ch10_product_price_only IF NOT EXISTSFOR (p:Product) ON (p.price);CALL db.awaitIndex('ch10_product_price_only',300);EXPLAINMATCH (p:Product)WHERE p.categoryCode=$category AND p.price >= $minPriceMATCH (p)<-[:CONTAINS]-(o:Order)<-[:PLACED]-(c:Customer {customerId:$customerId})RETURN DISTINCT p.productId,p.price;DROP INDEX ch10_product_price_only IF EXISTS;

If the planner keeps choosing the composite index or another better path, the added index has not earned its ongoing write/storage/build cost for this workload. Preserve the plan evidence with the schema decision.

5. Change C: model-level mitigation for repeated hot-customer analytics

If the actual requirement repeatedly asks for aggregated history across a high-degree customer, a query rewrite may not be enough. A separate summary/fact structure (for example, customer-category purchase aggregates by time window) can reduce read work but adds write complexity, freshness semantics and reconciliation. This chapter does not silently introduce that model. It requires you to prove the read bottleneck first, define the summary’s consistency contract, then benchmark both read and write consequences.

6. Re-measure with a causal experiment sheet

variant,case,parameterShape,planStart,estimatedRowsAtHotspot,actualRowsAtHotspot,dbHits,peakMemoryBytes,p50Ms,p95Ms,p99Ms,resultRows,correctResult,decisionbaseline,hot,dense,,,,,,,,,,,baseline,typical,sparse,,,,,,,,,,,product-first,hot,dense,,,,,,,,,,,product-first,typical,sparse,,,,,,,,,,,price-index-test,hot,dense,,,,,,,,,,,

Warm up according to a declared protocol, run enough repetitions to report a distribution rather than a single average, and keep correctness checks alongside performance. If the “faster” version returns different products or handles nulls differently, it is not a tuning win.

7. Deliberately wrong: change index, query, runtime and model together

Multiple simultaneous changes destroy causal attribution. If the result improves, you cannot know which change mattered; if it regresses, rollback becomes ambiguous. The repair is a sequence of reversible experiments with one independent variable at a time and a stable workload fixture.

8. Cleanup and Chapter 11 bridge

Keep the fixture if you want to reuse it for transaction/locking experiments, or remove only Chapter 10 objects with the isolated cleanup below. Chapter 11 moves from plan work to transaction behavior: isolation, locks, deadlocks, retries and consistency.

CYPHER 25MATCH (n) WHERE n.labTag='ch10' DETACH DELETE n;DROP INDEX ch10_product_category_price IF EXISTS;DROP INDEX ch10_order_status IF EXISTS;DROP INDEX ch10_product_price_only IF EXISTS;DROP INDEX ch10_customer_segment IF EXISTS;

Summary

Performance engineering is a controlled scientific loop: representative workload → plan/runtime evidence → one hypothesis → one change → re-measure → correctness check → accept or rollback. That discipline is more durable than memorizing “good” operator names or universal index recipes.

Authoritative references

• Current Neo4j versions — Release/LTS snapshot used for this chapter.
• Understanding query plans — EXPLAIN/PROFILE, plan columns, rows, DB hits, memory and plan reading.
• Operators — Current operator families and operator semantics.
• Operators in detail — Current seek/scan, expand, apply/join, eager, sort and aggregation operators.
• Statistics and execution plans — Statistics collection, selectivity, replanning thresholds and manual preparation.
• Query caches — Per-database query caches, cache sizing and current query-size behavior.
• Cypher runtimes — Slotted, pipelined and parallel runtime boundaries.
• Query tuning — Current tuning concepts and replanning controls.
• Advanced query tuning example — Evidence-driven examples of plan improvement and index-backed ordering/aggregation behavior.
• Metrics reference — Operational Cypher replan metrics and database-size signals for later production observability.