Graph Data Modeling

Eliminating cartesian products in Cypher queries

You have a Cypher query that runs in milliseconds against your test fixture and times out in production, and the PROFILE shows a CartesianProduct operator whose estimated row count is the multiplication of two other numbers. This happens whenever a query matches two patterns that share no bound variable — two separate MATCH clauses, or a comma-separated pattern with no common node — because the planner has no join key and must pair every row from one side with every row from the other. Ten thousand nodes on each side become a hundred million intermediate rows. This page shows you how to recognise the operator, trace it back to the exact clause that caused it, and remove it three ways: by connecting the patterns through a shared variable, by pipelining one result into the next with WITH, or by isolating a genuinely independent branch in a correlated subquery. It is one specific fix from the broader catalogue in Cypher query tuning anti-patterns.

Prerequisites

Why disconnected patterns explode

The planner joins two patterns on a variable they share. When they share none, there is nothing to join on, so it falls back to the only operation that produces every valid combination: a cartesian product, |left| × |right| rows. The two classic sources are a second top-level MATCH that reuses no earlier variable, and a single MATCH with two comma-separated patterns whose nodes are all distinct. In both cases the query reads correctly — it returns the rows you expect — but it builds an enormous intermediate result on the way there, and any WHERE you add afterward filters that already-materialized product rather than preventing it.

This task is part of Cypher query tuning anti-patterns, and the cartesian product is the one anti-pattern whose PROFILE signature is unmistakable: the operator is named literally, and its estimated rows equal the product of its two children.

Core implementation

Start by making the operator visible, then apply the rewrite the shape of the query calls for. The comments below mark exactly what the planner is doing at each step.

cypher
// STEP 1 — SEE IT. Two MATCH clauses, no shared variable between (a) and (b).
// PROFILE puts a CartesianProduct at the join, with rows = |Account| x |Merchant|.
PROFILE
MATCH (a:Account)
MATCH (m:Merchant)
WHERE a.account_id = $aid AND m.region = $region
RETURN a.account_id, m.name;

// STEP 2 — FIX A (CONNECT): if the two patterns are meant to relate, join them
// through a shared traversal. The planner now anchors on a, expands to m, and the
// CartesianProduct disappears — replaced by an index seek plus an Expand.
PROFILE
MATCH (a:Account {account_id: $aid})-[:TRANSACTED_WITH]->(m:Merchant)
WHERE m.region = $region
RETURN a.account_id, m.name;

// STEP 3 — FIX B (PIPELINE): if the branches are independent but you still need
// both, resolve the first to a single row, then carry it forward with WITH so the
// second pattern expands from a 1-row context instead of multiplying.
PROFILE
MATCH (a:Account {account_id: $aid})
WITH a
MATCH (m:Merchant {region: $region})
RETURN a.account_id, collect(m.name) AS merchants;

// STEP 4 — FIX C (CORRELATED SUBQUERY): when a branch is genuinely independent
// and you only need an aggregate from it, isolate it in a CALL subquery so its
// rows never enter the outer cardinality.
PROFILE
MATCH (a:Account {account_id: $aid})
CALL (a) {
  MATCH (m:Merchant {region: $region})
  RETURN count(m) AS merchant_count
}
RETURN a.account_id, merchant_count;

Which fix applies depends on intent. If the two nodes are semantically related, they almost always should be connected by a relationship — FIX A is both faster and more correct, and it is the outcome you want most of the time. If they are independent but small, anchoring each on an index (as in the original query, once both properties are indexed) can make even a cartesian join acceptable, because 1 × 1 is one row. Reach for the pipeline or subquery forms only when one branch must fan out and you need to stop it from multiplying the other.

Cartesian product versus a joined pattern Left: a set of N Account nodes and a set of M Merchant nodes with no connection, joined into an N-by-M grid of intermediate rows labelled a CartesianProduct. Right: one anchored Account node connected by a TRANSACTED_WITH relationship to a filtered set of Merchant nodes, producing only the matching rows. Disconnected — N × M rows Joined — anchored result a1 a2 a3 m1 m2 m3 CartesianProduct grid Account $aid Merchant Merchant TRANSACTED_WITH only matching edges expanded
A shared relationship gives the planner a join key, collapsing the product into an anchored expand.

Validation & verification

The rewrite is correct only if the plan no longer contains the operator and the results are unchanged. Verify both.

First, confirm the CartesianProduct operator is gone and the leaf is now a seek:

cypher
// After the rewrite, the plan should show NodeIndexSeek + Expand(All) and
// NO CartesianProduct anywhere. Compare the total db-hits before and after.
PROFILE
MATCH (a:Account {account_id: $aid})-[:TRANSACTED_WITH]->(m:Merchant)
WHERE m.region = $region
RETURN a.account_id, m.name;

Second, prove the answer did not change. Run both the original and rewritten queries and compare the returned rows — a correct de-cartesianing preserves the result set exactly, only the intermediate cardinality shrinks:

cypher
// Row-count parity check: both forms must return the same count for the same
// parameters. A different count means the rewrite changed the semantics.
MATCH (a:Account {account_id: $aid})-[:TRANSACTED_WITH]->(m:Merchant)
WHERE m.region = $region
RETURN count(*) AS joined_rows;

If the PROFILE still shows a CartesianProduct, the two patterns still share no variable — trace each clause and confirm the connecting variable is actually reused, not just similarly named. Reducing the db-hits this exposes is the same discipline as in reducing db-hits in a slow Cypher query.

Edge cases & gotchas

1. A cartesian product that is actually correct. Sometimes you genuinely want every combination — cross-joining a small set of categories against a small set of regions, for instance. When both branches are anchored to a handful of rows, the product is tiny and the operator is harmless. The fix is not to eliminate it but to ensure each branch is index-anchored so neither side is large:

cypher
// Intentional, safe: both sides are single indexed lookups, so 1 x 1 = 1 row.
MATCH (c:Category {code: $cat})
MATCH (r:Region {code: $region})
RETURN c.name, r.name;

2. The subquery that reintroduces the product. A CALL subquery only isolates cardinality if it returns an aggregate. If it returns raw rows, those rows multiply the outer query just as a comma-separated pattern would:

cypher
// WRONG — subquery returns M rows, so the outer result is again N x M.
MATCH (a:Account {account_id: $aid})
CALL (a) { MATCH (m:Merchant {region: $region}) RETURN m }
RETURN a, m;
// RIGHT — return an aggregate (count/collect) so the branch stays one row.
MATCH (a:Account {account_id: $aid})
CALL (a) { MATCH (m:Merchant {region: $region}) RETURN collect(m.name) AS names }
RETURN a.account_id, names;

3. WITH without narrowing the first branch. Pipelining only helps if the clause before WITH resolves to few rows. If the first MATCH is itself unanchored, WITH merely relocates the explosion downstream. Anchor the first pattern on an indexed key before you pipeline — the same leaf-anchoring rule that governs the whole Cypher query tuning anti-patterns catalogue.

Related pages