multiple OPTIONAL MATCH clauses in cypher 2

Question

I have a problem selecting data from a graph with 2 optional paths. Assume nodes A, B, C where A has optional relations to B and C.

If I query

match (a:A) where a.xx = XX optional match (a:A)-->(b:B) return ...

or

match (a:A) where a.xx = XX optional match (a:A)-->(c:C) return ...

everything works as expected.

If I combine the 2:

match (a:A) where a.xx = XX 
optional match (a:A)-->(b:B) 
optional match (a:A)-->(c:C) 
return ...

then I only get (after a long querying time) an unknown error.

The return selects attributes from a,b,c and uses a limit to restrict the amount of data returned. Is it not possible to have more than one optional match?

Update: When I change the query to

match (a1:A) where a.xx = XX 
optional match (a2:A)-->(b:B) where a2.uid = a1.uid
optional match (a3:A)-->(c:C) where a3.uid = a1.uid
return ...

with uid being an unique indexed id then the query returns the required result. But it runs very slowly (~60 seconds if uid is an index and ~40s if uid has a unique constraint) The dataset is not something I would call huge: a 6500 , b 86 and c 90000 entries.

Answer 1

given you have already matched A once, why are you rematching it?

MATCH (a1:A) WHERE a.xx = XX 
OPTIONAL MATCH (a2:A)-->(b:B) WHERE a2.uid = a1.uid
OPTIONAL MATCH (a3:A)-->(c:C) WHERE a3.uid = a1.uid

Could be better expressed:

MATCH (a:A) WHERE a.xx = XX 
OPTIONAL MATCH (a)-->(b:B)
OPTIONAL MATCH (a)-->(c:C)

That looks dangerously close to your initial example, but there is no reason for it to perform badly. It would perform better if you could put a type on the relationship matches:

MATCH (a:A) WHERE a.xx = XX 
OPTIONAL MATCH (a)-[:REL]->(b:B)
OPTIONAL MATCH (a)-[:REL]->(c:C)

Please note that you do not need to relabel the already bound nodes when carrying them through into the OPTIONAL MATCH clauses.

You should have an index on whichever property you are using for the initial match:

CREATE INDEX ON :A(xx)

You can try this in the console to see it working:

MATCH (n:Crew)
OPTIONAL MATCH (n)-[:KNOWS]-m
OPTIONAL MATCH (n)-[:LOVES]-t
RETURN n AS Neo,COLLECT(m) AS knows, COLLECT(t) AS loves

Answer 2

the problem is that the two optional matches create a cross product, so you'd want to get the cardinality down befor the second one, i.e.

create index on :A(xx);

MATCH (a:A) WHERE a.xx = XX 
OPTIONAL MATCH (a)-->(b:B) 
WITH a, collect(b) as b_nodes
OPTIONAL MATCH (a)-->(c:C) 
RETURN a, b_nodes, collect(c) as c_nodes`