Inflated Transfer Counts with OPTIONAL MATCH
A payment-risk team runs Neo4j 5.26 on a three-node cluster. The graph has 3.2 million :Account nodes, 900,000 :Device nodes, 14 million :TRANSFERRED_TO relationships with amount and ts, and 2.1 million :USES relationships. An analyst query should return the 50 accounts that sent money to the most distinct accounts, but only when the sender and at least one recipient share a device. The current query starts with MATCH (a:Account)-[t:TRANSFERRED_TO]->(b:Account), then OPTIONAL MATCH (a)-[:USES]->(d:Device)<-[:USES]-(c:Account), applies WHERE c <> a AND c <> b, and returns a.id, count(DISTINCT b), count(t). For an account with three devices and two co-device recipients, the reported transfer count is about six times the true count; accounts with no device disappear from the result. The query takes 38 seconds, and PROFILE shows a large expand into followed by a filter and aggregation.
1. Diagnose both the inflated count and the missing device-less accounts. Rewrite the query so each account is returned once with correct counts.
Read the worked answer
Two separate issues. First, the OPTIONAL MATCH can produce multiple rows per transferred-to relationship: one row for every combination of sender device and recipient who uses that device. count(t) therefore counts the same relationship repeatedly; count(DISTINCT t) is required. Second, predicates in WHERE after OPTIONAL MATCH are evaluated on the optional rows. For a sender with no device, c is null, so c <> a evaluates to null and the row is filtered out; the optional match becomes an inner join. Put the device condition inside an EXISTS subquery, or filter with c IS NOT NULL only if device-less accounts are intentionally excluded. A correct shape is:
MATCH (a:Account)-[t:TRANSFERRED_TO]->(b:Account)
WHERE EXISTS {
MATCH (a)-[:USES]->(d:Device)<-[:USES]-(c:Account)
WHERE c <> a AND c <> b
}
WITH a, count(DISTINCT b) AS targets, count(DISTINCT t) AS transfers
RETURN a.id, targets, transfers
ORDER BY transfers DESC
LIMIT 50
EXISTS is a predicate and does not multiply rows. Confirm on a small subgraph by comparing counts with and without the device condition.
2. The rewritten query is correct but still slow. What would you inspect in the plan, and what query-shape change would you test first?
Read the worked answer
Use PROFILE, not just EXPLAIN, to see estimated vs actual rows and db hits. Look for the expand from :Account into :TRANSFERRED_TO and for the EXISTS subquery being evaluated for every account. If most accounts have a device, starting from all 3.2M accounts is expensive. Test a shape that starts from the rarer side: match accounts that use a device first, then their transfers, and use an EXISTS subquery for the co-device recipient. For example, MATCH (a:Account)-[:USES]->(:Device) reduces candidates if only a fraction of accounts have devices. Compare db hits and rows at the aggregation. Also ensure count(DISTINCT t) is not forcing a large dedup if t is unique per path. If the plan still expands all 14M transfers, consider a relationship property index or a precomputed risk score, but only after measuring. Verify with a parameterized query on one known account.