Query-Level Optimization: Anchors, Selectivity, Predicate Placement, Subqueries, Aggregation, and Result Shaping

Learning outcomes

1. AtlasMart problem: a small response can hide a huge intermediate pipeline

An endpoint returns only ten products, yet the query first expands every recent order, every contained product, every category, groups thousands of rows, sorts them, and finally applies LIMIT 10. Cypher is a row/pattern pipeline: the final row count does not reveal the amount of intermediate work.

Selectivity is how strongly a predicate narrows candidate entities. An anchor is the part of the pattern the planner can use to begin from a constrained/selective set. Cypher is declarative, so writing a variable first in text does not force execution order. Use indexes/constraints and inspect the plan rather than “reordering MATCH” by superstition.

2. Bad anchor vs usable anchor: change semantics as little as possible

The first query applies a function to a property and asks for a product by case-folded name. That expression may prevent a normal range-index seek on the raw property and can widen work. The repaired query uses the stable constrained product key.

// Diagnostic only: inspect locally with PROFILE.
PROFILE
MATCH (p:CH26Product)<-[:CONTAINS]-(o:CH26Order)
WHERE toLower(p.name)=toLower($name)
RETURN o.orderId LIMIT 20;

PROFILE
MATCH (p:CH26Product {productId:$productId})<-[:CONTAINS]-(o:CH26Order)
RETURN o.orderId LIMIT 20;

Do not infer a speedup from the text alone. Confirm the second query starts from an index-backed selective access path, then compare actual rows/DB hits and repeated latency. If the application truly requires case-insensitive name search, model/index the requirement deliberately (for example normalized property or text/full-text index where semantics fit) instead of hiding it inside an arbitrary function.

3. Predicate placement: reduce rows without changing meaning

Predicates can be pushed/optimized by the planner, but scoping still matters. The dangerous cases are semantic: predicates attached to OPTIONAL MATCH, variables introduced by previous clauses, or filtering after an expansion/aggregation that could have been bounded earlier.

// Baseline shape: inspect intermediate Rows in PROFILE.
PROFILE
MATCH (c:CH26Customer)-[:PLACED]->(o:CH26Order)-[:CONTAINS]->(p:CH26Product)
WHERE o.createdDay >= $cutoff AND o.status='COMPLETE'
RETURN p.productId, count(*) AS times
ORDER BY times DESC LIMIT 20;

// When the business question really is "top products among the 200 most recent qualifying orders",
// make that bounded scope explicit with a subquery.
PROFILE
CALL {
  MATCH (o:CH26Order)
  WHERE o.createdDay >= $cutoff AND o.status='COMPLETE'
  WITH o ORDER BY o.createdDay DESC, o.orderId DESC LIMIT 200
  RETURN o
}
MATCH (o)-[:CONTAINS]->(p:CH26Product)
RETURN p.productId, count(*) AS times
ORDER BY times DESC LIMIT 20;

4. Aggregation, subqueries, and accidental cardinality multiplication

Every additional independent pattern can multiply rows. If AtlasMart independently matches a customer’s viewed products and placed orders in the same row scope, it can form combinations before aggregation. Separate independent aggregates into subqueries or aggregate one side before expanding the other.

// Multiplication-prone: views × orders for one customer.
PROFILE
MATCH (c:CH26Customer {customerId:$cid})-[:VIEWED_CH26]->(p:CH26Product),
      (c)-[:PLACED]->(o:CH26Order)
RETURN count(DISTINCT p) AS productsViewed,
       count(DISTINCT o) AS orders;

// Same business outputs, independent bounded pipelines.
PROFILE
MATCH (c:CH26Customer {customerId:$cid})
CALL (c) {
  MATCH (c)-[:VIEWED_CH26]->(p:CH26Product)
  RETURN count(DISTINCT p) AS productsViewed
}
CALL (c) {
  MATCH (c)-[:PLACED]->(o:CH26Order)
  RETURN count(DISTINCT o) AS orders
}
RETURN productsViewed, orders;

5. Result shaping belongs to query performance

Returning entire nodes/paths asks Neo4j to read more properties, serialize more data, send more bytes, and asks the driver/application to materialize more objects. When the API needs three scalars, project three scalars. A small driver fetch_size can bound batches for lazy results, but it cannot make an intrinsically blocking server operator such as a large sort stream before its required work is complete.

MATCH (c:CH26Customer {customerId:$cid})-[:PLACED]->(o:CH26Order)-[:CONTAINS]->(p:CH26Product)
RETURN o.orderId AS orderId,
       o.status AS status,
       collect({productId:p.productId, quantity:1})[0..5] AS products
ORDER BY orderId DESC
LIMIT 20;

6. Deliberately wrong approach: optimize by rearranging MATCH text

A practitioner moves (p:Product) to the left side of the pattern and reports the query is now “anchored.” Cypher’s cost planner can choose execution order independently of textual pattern order. The rewrite may have changed nothing.

Repair: make a selective predicate/index available, run EXPLAIN to inspect the intended access path, run PROFILE in a controlled environment to collect actual Rows/DB Hits/memory, and measure end-to-end latency with the same parameters/result size. Keep plan operator names/runtime/version in the evidence because they can evolve.

Production judgment

Query-level tuning should reduce work while preserving correctness under real parameter distributions. A plan optimized for a rare selective key may behave differently for a broad category. Parameterize queries for plan cache reuse and injection safety; maintain statistics/indexes; bound variable-length traversals; treat large sorts/aggregations as memory consumers; keep transaction timeouts/retries/idempotency in the application contract; and separate server execution from driver pool/network/client costs. Lesson 4 asks whether the remaining bottleneck is genuinely infrastructure rather than query shape.

Summary and next step

Query-Level Optimization: Anchors, Selectivity, Predicate Placement, Subqueries, Aggregation, and Result Shaping is useful only when its assumptions and observed evidence stay attached to the decision. The examples above establish a reproducible mechanism and boundary; they do not turn one lab result into a universal production rule.

Next, continue to Infrastructure Tuning: Heap, Page Cache, Transaction Memory, CPU, Storage, Containers, and OS Limits. Carry forward the verified assumptions, fixture state, version/edition boundaries, and measurements from this lesson instead of treating the next topic as an isolated recipe.

Authoritative references

• Neo4j current versions — Current database release and LTS baseline.
• Neo4j Operations Manual — Performance — Current performance topics and operational tuning surface.
• Memory configuration — Heap, page cache, transaction/native memory, OS headroom, and memory recommendation guidance.
• Disks, RAM and other tips — Page-cache warmup, storage and RAM behavior.
• Configuration settings — Authoritative current setting names and edition/dynamic boundaries.
• Docker configuration — Container configuration mapping and production configuration guidance.
• Cypher execution plans — EXPLAIN/PROFILE semantics and runtime evidence.
• Cypher operators in detail — Current operators, Rows, DB Hits, memory and plan behavior.
• Indexes for search performance — Current range/text/point/token index behavior and syntax.
• Cypher query tuning — Planner, statistics and query-tuning concepts.
• Neo4j Python driver performance — Driver-side result streaming, database selection and performance guidance.
• Neo4j Python driver API — Connection pool, timeout, retry and fetch-size configuration.
• Neo4j logging — Current debug/query/security/GC logging surfaces and edition boundaries.
• Neo4j metrics — Enterprise metrics surfaces and operational evidence.
• Transaction management — Transaction lifecycle and operational behavior.
• Java requirements — Current supported Java/runtime and platform requirements.
• Neo4j status codes — Classify transient/client/database failures rather than collapsing them into latency.