Graph Data Modeling

Reducing db-hits in a slow Cypher query

You have a query that takes seconds when it should take milliseconds, and a PROFILE shows a db-hits count in the millions. A db-hit is one unit of storage-engine work — reading a node, a relationship, or a property — so a query reporting 4.8 million db-hits is literally touching the store almost five million times to return a handful of rows. This page walks through one concrete slow query and drives its db-hits count from roughly 4.8 million down to under a hundred, using four moves in sequence: anchor the query on an index-backed start node, create the index that anchor needs, parameterize the inline literal so the plan caches, and bound the expansion so a supernode cannot blow up the row count. Each move is verified by re-running PROFILE. This task is part of Query Plan Analysis with EXPLAIN & PROFILE, which teaches the operator vocabulary this walk-through assumes.

Prerequisites

The starting query and its profile

Here is the offending query. It finds the high-value orders placed by one customer, identified by email, with the email spelled out as an inline string literal:

cypher
// SLOW — no index on Customer.email, an inline literal, and an unbounded
// expansion. PROFILE reports ~4,800,000 db-hits to return ~30 rows.
PROFILE
MATCH (c:Customer {email: 'ada@example.com'})-[:PLACED]->(o:Order)
WHERE o.total > 100
RETURN o.order_id, o.total
ORDER BY o.total DESC;

Reading its plan bottom-up reveals three separate problems stacked on top of one another:

  • The leaf is a NodeByLabelScan over every Customer node, because there is no index on email. On a database with 2 million customers, that leaf alone reads roughly 2 million nodes plus a property each to test the predicate.
  • Above it, an Expand(All) walks PLACED out of whichever customers survived — and because the scan produced the anchor, the planner cannot exploit selectivity, so the expansion fans out widely.
  • The inline literal 'ada@example.com' means the planner compiles a fresh plan per distinct email, thrashing the plan cache and preventing reuse across calls.

The total lands near 4.8 million db-hits. The goal is to make the leaf a NodeIndexSeek touching a single node, so every operator above it inherits a tiny row count.

Core implementation: four moves, each re-profiled

Apply the fixes in order. The sequence matters — anchoring is meaningless without the index that backs it, and parameterization only helps once the seek exists.

cypher
// MOVE 1 — Create the missing index so an equality predicate on email
// can seek instead of scan. On a unique identity, prefer a constraint,
// whose backing index the planner uses identically (NodeUniqueIndexSeek).
CREATE CONSTRAINT customer_email_unique IF NOT EXISTS
FOR (c:Customer) REQUIRE c.email IS UNIQUE;

// Wait for the backing index to come ONLINE before profiling again —
// a POPULATING index is invisible to the planner and the query still scans.
CALL db.awaitIndexes(300);
cypher
// MOVE 2 + 3 — Anchor on the now-index-backed start node and PARAMETERIZE
// the email. The literal becomes $email, so ONE cached plan serves every
// customer instead of a fresh compile per distinct value.
// MOVE 4 — the expansion is naturally bounded now: because the anchor is a
// single seeked node, Expand(All) walks only that customer's PLACED edges,
// not a fanned-out set. Pushing o.total into the RETURN keeps Filter cheap.
PROFILE
MATCH (c:Customer {email: $email})-[:PLACED]->(o:Order)
WHERE o.total > 100
RETURN o.order_id, o.total
ORDER BY o.total DESC;

With the constraint online and the parameter in place, the same PROFILE now reports:

  • The leaf is a NodeUniqueIndexSeek touching 2 db-hits and emitting exactly 1 row — the single customer.
  • The Expand(All) now walks only that one customer’s PLACED relationships (say 40 orders), a few dozen db-hits.
  • The Filter on o.total > 100 runs over those 40 rows, not millions, and keeps roughly 30.

Total: well under 100 db-hits. The 50,000-fold reduction came entirely from turning the leaf scan into a seek; parameterization then made the improvement reusable across every distinct email instead of a one-off.

The same query before and after: label scan versus unique-index seek Two stacked plans. Left, before: ProduceResults over Filter over Expand(All) over a NodeByLabelScan leaf drawn in amber, totalling about 4,800,000 db-hits. A central teal arrow labelled add index and parameterize points right. Right, after: ProduceResults over Filter over Expand(All) over a NodeUniqueIndexSeek leaf drawn in teal, totalling under 100 db-hits. The before leaf reads every Customer node; the after leaf touches a single node. Before — NodeByLabelScan ProduceResults Filter Expand(All) NodeByLabelScan ≈ 4,800,000 db-hits reads every Customer add index + parameterize seek replaces scan After — NodeUniqueIndexSeek ProduceResults Filter Expand(All) NodeUniqueIndexSeek < 100 db-hits touches one node
The whole reduction comes from the leaf: a scan reads the store, a seek reads one node.

Validation & verification

Prove the reduction rather than assuming it. Three checks confirm each move landed.

First, confirm the backing index is ONLINE, because a seek is impossible until it is:

cypher
SHOW INDEXES YIELD name, type, state, labelsOrTypes, properties
WHERE 'Customer' IN labelsOrTypes AND 'email' IN properties
RETURN name, type, state;   // expect one row, state = 'ONLINE'

Second, re-run the parameterized PROFILE and read the leaf operator: it must say NodeUniqueIndexSeek (or NodeIndexSeek for a plain index), not NodeByLabelScan, and its db-hits must be in the single digits. The leaf is the whole story — if it is still a scan, the index is not online or the predicate is not index-eligible.

Third, confirm the plan is now cached and reused rather than recompiled per call. A parameterized query should produce a stable plan across different $email values:

cypher
// Inspect plan-cache reuse. A parameterized query should NOT recompile
// per distinct value; a rising compilation count means a literal leaked in.
SHOW SETTINGS YIELD name, value
WHERE name = 'server.db.query_cache_size';

Edge cases & gotchas

1. The index exists but the query still scans. If PROFILE shows a NodeByLabelScan despite the index, the predicate is usually not index-eligible — a function wrapping the property (WHERE toLower(c.email) = $email) disqualifies the index. Match the stored form directly, or index a normalized property:

cypher
// WRONG — the function hides the property from the index, forcing a scan
MATCH (c:Customer) WHERE toLower(c.email) = $email
// RIGHT — store and match a normalized key so the seek is available
MATCH (c:Customer {email_normalized: $email})

2. A supernode defeats the seek’s benefit. If the anchored customer happens to have millions of PLACED edges, the Expand(All) above the seek dominates the db-hits even though the leaf is cheap. Bound or filter the expansion with a relationship-property index — this is the property graph anti-pattern of a dense node, not a plan-reading failure.

3. Parameter type mismatch silently scans. Passing an integer where the stored property is a string (or a native datetime compared to a string literal) can quietly disqualify the index. Confirm the parameter type matches the stored property type, and cast at ingestion so the two always agree.

Related pages