Why Did One More Hop Make the Query So Expensive?

Botie returns three equipment IDs, but the query takes seconds. It began at every asset, followed broad relationship types and only then filtered to P7. The small final answer does not show how many candidates the database inspected.

We need to look at where the search starts and how intermediate rows grow. An index can narrow the start, while selective patterns and early justified predicates can limit expansion. We should measure the plan instead of judging work from the final row count.

A tiny answer can hide a scan of unrelated relationships.

An equality predicate on a constrained Asset.id can provide a selective starting point. A label narrows the entity category; a property condition narrows the instance. A traversal from a huge unfiltered population asks a different amount of work even if the final WHERE keeps one asset.

The optimiser may reorder compatible operations. Write clear predicates, then inspect the physical plan rather than assuming text order is execution order.

The logical query and the physical access path are connected but different descriptions.

A naive one-hop loop scans every edge for each distinct start. An already built adjacency lookup inspects only outgoing edges for those starts. Comparing these counts makes the access-path idea tangible.

Building and maintaining the adjacency lookup costs work and memory too. Our counter excludes that setup and is not an estimate of database latency, cache behaviour or storage I/O.

State what the measured quantity includes before interpreting the result.

g={"nodes": [{"id": "P7", "kind": "Asset"}, {"id": "S1", "kind": "Asset"}, {"id": "P8", "kind": "Asset"}], "edges": [{"id": "d1", "source": "P7", "target": "S1", "type": "DEPENDS_ON"}, {"id": "d2", "source": "P8", "target": "P7", "type": "DEPENDS_ON"}]}
adj={}
for e in g['edges']:
    adj.setdefault(e['source'],[]).append(e['id'])
print(adj['P7'])

['d1']

EXPLAIN shows a planned execution without executing the query. PROFILE executes it and reports actual operator statistics, so profiling a write can perform that write. Begin with read-only queries against representative test data.

Look for starting scans or seeks, expansion operators, estimated versus actual rows and substantial intermediate multiplication. Compare DB hits and timing carefully; warm caches, parameter values and server load can change results.

A final LIMIT does not necessarily remove upstream expansion, sorting or aggregation work.

EXPLAIN
MATCH (a:Asset {id:$asset})-[:DEPENDS_ON]->(d:Asset)
RETURN DISTINCT d.id AS dependency

A node with many outgoing relationships can produce a large frontier even when the starting lookup is fast. Several hops can combine many routes to the same endpoint. DISTINCT removes repeated output rows but may still require processing many prior matches.

Constrain relationship types, justified properties and path length according to the actual question. Avoid enumerating all paths when you only need existence, reachability or a shortest-path result.

The operation you ask for determines how much route information must be retained.

WITH a, count(i) AS n carries only a and n into the next stage. A variable omitted from that projection is no longer available in the ordinary following scope. WITH DISTINCT can deliberately change cardinality before later matches.

Do not drop the identity needed to connect the next branch correctly. An optimisation that accidentally changes grouping or optional-match semantics is a different query, even if it runs faster.

Preserve meaning first, then measure the equivalent alternatives.

Set timeouts, result-size limits and controlled query templates appropriate to the service. A high-degree tenant or newly imported relationship type can change a previously cheap workload. Monitor latency distributions and failure rates, with enough plan context to investigate regressions.

Performance bounds do not replace tenant access checks. They protect capacity while the authorisation boundary determines which records may be touched.

The workshop compares one-hop candidate checks so its interpretation stays narrow and testable.