Query Plan Analysis with EXPLAIN & PROFILE

A slow Cypher query is never a mystery once you can read its execution plan. The Neo4j 5.x cost-based planner compiles every statement into a tree of physical operators, and both EXPLAIN and PROFILE expose that tree along with the estimates or measurements the planner used to choose it. The difference between a query that seeks a single node by index and one that scans forty million is almost always visible in the leaf operator of that tree — and the fix is almost always mechanical once you have identified it. This reference teaches the operator vocabulary, the two cost signals (db-hits and rows) that separate a healthy plan from a pathological one, and a repeatable procedure for profiling a slow query end to end. It is the diagnostic skill every other tuning technique in the Cypher query optimization and index lifecycle reference depends on.

Prerequisite Concepts

Plan reading is a diagnostic layer over decisions made elsewhere. Before working through the operators below, be comfortable with:

• The optimization workflow as a whole. A plan tells you what the planner chose; the parent Cypher query optimization and index lifecycle reference frames why — index lifecycle, parameterization, and the anti-patterns that produce bad plans in the first place.
• What indexes the planner can actually use. An operator like NodeIndexSeek only appears when a suitable index exists and the predicate is index-eligible. Understand index selection and composite indexing first, because most “bad plan” incidents are really “missing or wrong index” incidents.
• Constraint-backed identity. Uniqueness constraints provision a backing index, so a lookup on a constrained key plans as a NodeUniqueIndexSeek. Provisioning these correctly is covered across the schema-design reference at Neo4j graph schema design and architecture.
• The property-graph shapes that defeat the planner. Supernodes, unbounded paths, and dynamic labels are documented property graph anti-patterns; each has a signature in the plan you will learn to recognize here.

Conceptual Model: The Operator Tree

An execution plan is a tree. The leaves are the operators that first pull data out of the store — an index seek, a label scan, or a full-store scan. Each parent operator consumes the rows its children produce, transforms them, and passes the result up. The root is always ProduceResults, the operator that streams the final rows back to the client. Data therefore flows from the bottom of the tree to the top, which is why you read a plan bottom-up: the leaf tells you how the query is anchored, and everything above it is the cost of turning that anchor into an answer.

EXPLAIN compiles and returns this tree without running the query. Every operator shows the planner’s estimated rows — cardinality guesses derived from stored statistics — but no measured work. It is free, side-effect-free, and the right tool for a quick “will this hit an index?” check before you run anything expensive. PROFILE runs the query and augments the same tree with actual rows and db-hits per operator: the ground truth of what execution cost. Use EXPLAIN to inspect a plan cheaply; use PROFILE when you need real numbers to find where the work actually went.

The diagram below shows a four-operator plan for a customer-to-order lookup, annotated with the db-hits and rows each operator reported under PROFILE. Read it from the leaf upward.

Design Rules: Operator → Meaning → Fix

The fastest way to triage a plan is to scan its leaves and its highest-db-hits operators against a fixed lookup table. The matrix below is the decision reference for the operators you will actually encounter. The rule of thumb: a healthy plan is anchored by a seek, and expensive operators sit low in the tree over few rows, not high in the tree over many.

Two operators deserve special vigilance because they scale with data volume rather than result size. An AllNodesScan or NodeByLabelScan at a leaf means the query has no index-backed entry point and its cost grows with the store. A CartesianProduct means two independent scans are being multiplied, so the row count explodes combinatorially. Both are structural: no amount of hardware fixes them, only a better plan does.

Step-by-Step Implementation: Profiling a Slow Query

Step 1 — Reproduce with EXPLAIN first

Start with EXPLAIN so you inspect the plan without paying to run a query you suspect is slow. Look only at the leaf operators and the estimated rows.

// Compile the plan and inspect it WITHOUT executing. Free and side-effect-free.
EXPLAIN
MATCH (c:Customer {email: $email})-[:PLACED]->(o:Order)
WHERE o.total > 100
RETURN o.order_id, o.total
ORDER BY o.total DESC;

If the leaf under Customer is already a NodeByLabelScan rather than a NodeIndexSeek, you have found the problem before running anything: there is no index on Customer.email. Fix that class of issue first — a scan at the anchor poisons every operator above it.

Step 2 — Measure the real cost with PROFILE

When the plan shape looks reasonable but latency is still high, run PROFILE to attach measured db-hits and actual rows to each operator. A db-hit is one unit of storage-engine work: reading a node, a relationship, or a property. It is the currency of query cost.

// Execute AND measure. Every operator now reports db-hits and actual rows.
PROFILE
MATCH (c:Customer {email: $email})-[:PLACED]->(o:Order)
WHERE o.total > 100
RETURN o.order_id, o.total
ORDER BY o.total DESC;

Read the profiled tree from the leaf up and find the operator with the largest db-hits. That single operator is where the time went — everything else is noise until you have addressed it.

Step 3 — Compare estimated rows against actual rows

Open the operator with the biggest gap between its estimated rows (what EXPLAIN predicted) and its actual rows (what PROFILE measured). A large divergence means the planner chose its plan from statistics that no longer describe the data — typically after a bulk load added millions of nodes without the planner re-sampling.

// Inspect the planner's stored graph counts. If these are far from reality,
// the planner is costing plans against a stale picture of the data.
CALL db.stats.retrieve('GRAPH COUNTS');

Stale statistics cause the planner to, for example, expect a Filter to keep 5 rows when it actually keeps 500,000, so it picks a plan that is optimal for the wrong distribution. After a large load, force a fresh sample of the affected index so the planner re-plans against current cardinality.

// Re-sample one index so the planner's selectivity estimate reflects the
// post-load distribution. Run after a bulk import, then re-PROFILE.
CALL db.resampleIndex('Customer(email)');

Step 4 — Apply the fix and re-PROFILE to confirm

Every plan fix must be verified the same way it was diagnosed: re-run PROFILE and confirm the leaf changed from a scan to a seek and the total db-hits dropped. Never assume a fix worked — measure it. The two most common transformations are turning a NodeByLabelScan into a NodeIndexSeek by creating an index, and collapsing a CartesianProduct by connecting the patterns. The detailed walk-through of driving a db-hits count down is in reducing db-hits in a slow Cypher query.

Constraint & Validation Layer

A plan is only trustworthy if the schema it was compiled against is stable and its statistics are current. Two validation gates keep plan reading honest.

First, confirm the index the seek depends on actually exists and is ONLINE. An index in the POPULATING state is invisible to the planner, so a query that should seek will scan until population completes.

// A seek requires an ONLINE index. A POPULATING or FAILED index is ignored,
// silently downgrading the plan to a scan.
SHOW INDEXES YIELD name, type, state, labelsOrTypes, properties
WHERE state <> 'ONLINE';

Second, treat a persistent estimated-versus-actual divergence as a data-quality signal, not a one-off. If re-sampling does not close the gap, the predicate itself may be non-selective — an equality filter on a low-cardinality property (a boolean, a status enum) legitimately matches a large fraction of the store, and the correct fix is a better anchor or a composite index, not a resample. Enforcing the identity keys that make seeks possible is a schema discipline detailed in Neo4j graph schema design and architecture.

Performance & Scale Considerations

Reading plans at scale is about knowing which numbers matter as the store grows.

• db-hits, not wall-clock, is the portable signal. Wall-clock latency varies with page-cache warmth, concurrent load, and hardware; db-hits is deterministic for a given plan and data shape. Tune against db-hits and the latency follows.
• Rows multiply upward. A leaf that emits one extra order of magnitude of rows forces every operator above it to do ten times the work. This is why fixing the leaf is always the first move: it is the base of the multiplication.
• Selectivity governs seek-versus-scan. For a highly selective predicate (a unique email), a seek touching a handful of db-hits wins overwhelmingly. For a predicate matching most of the store, a scan can genuinely be cheaper — the planner knows this, which is why a “missing index” is sometimes an intentional plan, not a bug.
• Eager and sorts are memory, not just db-hits. An Eager or an ORDER BY without an index-provided order buffers rows in heap. On a bulk write this can dominate; db-hits alone will not reveal it, so watch actual-row counts on those operators too.
• Statistics drift with write volume. Neo4j re-samples indexes automatically as they change, but a single massive load can outrun the auto-sample threshold, leaving the planner stale until the next resample. Make a manual db.resampleIndex the last step of any bulk migration.

Known Pitfalls

1. Reading the plan top-down. Symptom: you fixate on ProduceResults or a high Filter and miss the real cost. Root cause: execution flows leaf-to-root, so the top of the tree is the last thing to run and rarely the culprit. Fix: always start at the leaves and follow db-hits upward.

2. Trusting EXPLAIN’s row counts as measurements. Symptom: a plan “looks fine” in EXPLAIN but is slow in production. Root cause: EXPLAIN shows estimates from statistics, not measured work; a stale estimate can hide an operator that actually processes millions of rows. Fix: use PROFILE for any real diagnosis, and compare estimated against actual rows.

3. Fixing the wrong operator. Symptom: you add an index and latency barely moves. Root cause: the index addressed an operator that was already cheap while the dominant db-hits sat elsewhere — commonly an Expand(All) over a dense node. Fix: rank operators by db-hits first, fix the largest, then re-PROFILE before touching anything else.

4. Ignoring a stale-statistics divergence. Symptom: the planner picks a scan even though a perfect index exists. Root cause: after a bulk load the planner’s cardinality estimate is wrong, so it mis-costs the seek. Fix: run db.resampleIndex (or db.stats inspection) after large loads so the planner re-plans against real counts.

Related

• Cypher Query Optimization & Index Lifecycle — the parent reference this diagnostic skill anchors.
• Index Selection & Composite Indexing — choosing the index that turns a leaf scan into a seek.
• Constraint Lifecycle Management — provisioning the constraints whose backing indexes power unique-index seeks.
• Cypher Query Tuning Anti-Patterns — the query shapes that force cartesian products and unbounded expansions.
• Query Parameterization & Plan Caching — keeping the plan you diagnosed in the cache instead of re-planning it.
• Reducing db-hits in a Slow Cypher Query — a full before/after PROFILE walk-through.
• Interpreting the Eager Operator in Cypher PROFILE — why Eager appears and how to remove it.
• Property Graph Anti-Patterns — the schema shapes whose fingerprints show up as expensive plan operators.