Reading EXPLAIN and PROFILE Plans for Spatial Queries

A spatial query that returns forty rows can still read forty million. The gap between what a routing query returns and what it touches is invisible in the result set and glaring in the operator tree — and if you cannot read that tree, every tuning decision is a guess. The specific trap for spatial workloads is a proximity or path query whose widest operator sits three levels below the row you actually wanted: a NodeByLabelScan that fed a CartesianProduct, an Expand(All) that fanned out before the distance Filter clipped it. This page is a field guide to reading those trees — which operators matter for spatial and routing queries, what DbHits, Rows, and estimated rows each tell you, and how to locate the one operator that is doing all the work. Once you can name the widest operator you can decide whether it needs a reshaped predicate or, as a last resort, forcing index seeks with Cypher planner hints.

Prerequisites & Versions

pip install "neo4j>=5.18" "pytest>=8.0" "pytest-asyncio>=0.23"

EXPLAIN compiles the query and returns the plan with estimated rows but never runs it — cheap, safe on production, and correct for asserting plan shape in CI. PROFILE executes the query and annotates every operator with actual Rows and DbHits. Use EXPLAIN to catch a regression before it ships; use PROFILE to explain one that already did.

Implementation

The driver exposes the plan as a nested dict on the result summary: summary.plan for EXPLAIN, summary.profile for PROFILE. Each node carries an operatorType, a children list, and — for a profiled plan — dbHits, rows, and an args map holding EstimatedRows. The code below profiles a proximity-plus-expansion query, walks the tree, and reports the widest operator by actual rows. The pytest case then asserts the base operator is a seek, so a refactor that reintroduces a scan fails in CI.

import asyncio
from neo4j import AsyncGraphDatabase

# Nearest transit stops that link onward to a station, within a radius.
QUERY = """
MATCH (s:TransitStop)
WHERE point.distance(s.location,
      point({srid: 4326, latitude: $lat, longitude: $lon})) <= $radius
MATCH (s)-[:LINKS]->(station:Station)
RETURN station.name AS name, count(*) AS links
ORDER BY links DESC
"""


def flatten(plan: dict) -> list[dict]:
    """Depth-first list of operator nodes from a driver plan/profile tree."""
    out: list[dict] = [plan]
    for child in plan.get("children", []):
        out.extend(flatten(child))
    return out


def widest_operator(profile: dict) -> dict:
    """The operator whose actual row count is largest — the row-explosion point."""
    return max(flatten(profile), key=lambda n: n.get("rows", 0))


async def main() -> None:
    driver = AsyncGraphDatabase.driver(
        "neo4j://localhost:7687",
        auth=("neo4j", "password"),
        max_connection_pool_size=20,
        connection_acquisition_timeout=5.0,
    )
    params = {"lat": 37.7749, "lon": -122.4194, "radius": 3000.0}
    try:
        async with driver.session(database="neo4j") as session:
            result = await session.run(f"PROFILE {QUERY}", **params)
            _ = [row async for row in result]      # drain so counters fill
            summary = await result.consume()
            profile = summary.profile
            for op in flatten(profile):
                print(f"{op['operatorType']:<28} "
                      f"rows={op.get('rows'):>10} "
                      f"dbHits={op.get('dbHits'):>10} "
                      f"est={op.get('args', {}).get('EstimatedRows', '?')}")
            hot = widest_operator(profile)
            print(f"\nwidest operator: {hot['operatorType']} @ {hot.get('rows')} rows")
    finally:
        await driver.close()


if __name__ == "__main__":
    asyncio.run(main())

import pytest
from neo4j import AsyncGraphDatabase


@pytest.mark.asyncio
async def test_plan_enters_on_a_seek():
    driver = AsyncGraphDatabase.driver("bolt://localhost:7687", auth=("neo4j", "test"))
    query = """
    MATCH (s:TransitStop)
    WHERE point.distance(s.location,
          point({srid: 4326, latitude: 37.7749, longitude: -122.4194})) <= 3000.0
    RETURN s.id
    """
    async with driver.session(database="neo4j") as session:
        result = await session.run(f"EXPLAIN {query}")
        summary = await result.consume()          # no rows for EXPLAIN
        ops = {n["operatorType"] for n in flatten(summary.plan)}
    await driver.close()
    assert any("IndexSeek" in op for op in ops), f"expected a seek, got {ops}"
    assert "NodeByLabelScan" not in ops, "predicate regressed to a full label scan"

How It Works

Read the tree bottom-up. The driver prints the root — usually ProduceResults — first, but execution starts at the leaves and flows upward, so the base operators are where data enters and where a scan-versus-seek decision is made. The operators that decide spatial-query cost are a short list:

• NodeByLabelScan reads every node of a label. As a base operator under a spatial predicate it is almost always the defect: the point index was not seeked.
• PointIndexSeekByRange (and NodeIndexSeekByRange) is the healthy base — the index descended to a bounding region and returned only candidates. This is what you want feeding the rest of the tree.
• Expand(All) walks relationships from each incoming row. Its output rows are the input rows times the average out-degree, so it is the most common row-explosion point in routing queries. Expand(Into) is the cheaper cousin used when both endpoints are already bound.
• Filter applies a predicate to rows already produced. A point.distance() filter sitting above a scan means the distance math ran once per node in the label — the exact cost the index was meant to avoid.
• CartesianProduct multiplies two row streams. Two disconnected MATCH clauses produce it, and it turns two thousand-row inputs into two million rows; on a spatial join it is a silent latency bomb.
• EagerAggregation materialises all input to compute a count, collect, or ORDER BY. It is a memory checkpoint — a wide input here is what fills the heap before the final LIMIT ever runs.

Three numbers annotate each operator, and reading them in the right order is the whole skill. Rows is the actual output count — trace it upward and the operator where it jumps by orders of magnitude is your row explosion. DbHits is the count of storage-engine accesses; it is the truest proxy for work, and an operator can be cheap in rows but expensive in DbHits if it probes properties per row. EstimatedRows is what the planner predicted from statistics — compare it against actual Rows, and a large divergence means the planner is costing blind, which is the root cause behind most mis-plans that a hint has to correct.

Common Failure Patterns

1. Reading top-down and blaming the wrong operator. The driver prints ProduceResults first, so it is tempting to read the tree like a call stack and assume the top matters most. It does not — the top usually shows your final row count, which is small by definition. Reconstruct the tree bottom-up (the flatten helper preserves child order) and follow Rows upward until it jumps. In the diagram above the base seek is fine and the Expand(All) is the culprit; a top-down read would have you tuning the aggregation that returns forty rows.

2. Trusting estimated rows over actual rows. EXPLAIN shows only EstimatedRows, and on a graph with stale statistics those estimates can be off by orders of magnitude — which is precisely why the planner mis-chose. Never conclude a query is fine because its estimated plan looks cheap. Run PROFILE and compare EstimatedRows against actual Rows operator by operator; the operator with the widest divergence is where the planner is costing blind, and it is the first place to refresh statistics or reshape the predicate. This estimate-versus-actual gap is the same signal that decides whether a query needs optimizing its plan or is genuinely as good as it gets.

3. Ignoring DbHits when rows look reasonable. An operator can output few rows while hammering storage — a Filter that reads three properties per row shows modest Rows but DbHits several times higher, and a Projection that calls point.distance() per row is invisible in the row count entirely. When latency is high but no operator shows a row explosion, sort the operators by DbHits instead; the work is hiding in per-row property access, not fan-out.

Performance Notes

There is no formula for reading a plan, only a loop, and it has a fixed budget: capture PROFILE, flatten the tree, sort by Rows to find the fan-out and by DbHits to find the property-access cost, act on the single widest operator, and re-profile. Bound the work by treating the base operator as the gate — if it is a NodeByLabelScan under a spatial predicate, fix that before looking anywhere else, because everything above it inherits its row count. A rough sanity check on any spatial plan: the base operator’s Rows should be near the count of nodes genuinely inside your search region, not the label total; if it equals the label total, the seek never happened. That widest-operator loop is the same discipline formalised in Cypher performance tuning, and when the diagnosis is a correctly-shaped predicate that still mis-plans, it hands off to forcing index seeks with Cypher planner hints.

Related

• Forcing Index Seeks with Cypher Planner Hints — what to do once the tree proves the planner chose wrong.
• Optimizing Cypher Query Plans for Spatial Data — reshape the predicate the plan revealed as unbounded.
• Cypher Performance Tuning — the full capture-diagnose-reprofile loop.
• Spatial Indexing Strategies — the index the base seek should be reading.

This guide is part of Graph Query Planner Optimization, within the Spatial Graph Database Fundamentals for Python reference.