Creating a relation with CREATE/MERGE is taking a lot of time

[ExtReMLapin]

Hello,

Probably related to this old issue (but it was for old engine, here it's new cypher engine) : #3148

Query :

UNWIND $batch as row
MATCH (a),(b) WHERE ID(a) = row.source_id and ID(b) = row.target_id 
MERGE (a)-[r:`in`]->(b)RETURN a, b, r

Batch had to be reduced to 1 because it was tooo slow

{"batch": [{"source_id": "#1:0", "target_id": "#4:0", "features": {}, "relation_type": "in"}]}

Takes something like 4 seconds.

DB dump (not backup button because it's broken) :

NEW_ORANO.zip

[ExtReMLapin]

Even simplified query has this issue :

MATCH (a),(b) WHERE ID(a) = "#1:0" and ID(b) = "#4:0" 
MERGE (a)-[r:`in`]->(b)RETURN a, b, r

[lvca]

Are you executing it using "opencypher" as language?

[ExtReMLapin]

Yes

[lvca]

I see the issue. The WHERE clause is applied AFTER the Cartesian product is created by chained MatchNodeSteps. The solution is to push ID() filters down into the MatchNodeStep itself when possible.

[lvca]

row.source_id is a dynamic value from UNWIND iteration so the ID filter cannot be extracted at plan time. This query will still create a Cartesian product, but it's unavoidable for dynamic ID values. For static IDs, use parameters instead:

 MATCH (a),(b) WHERE ID(a) = $sourceId AND ID(b) = $targetId

I was able to speed up the query if you use parameters.

For best performance with ID-based queries:

DO use parameters:

MATCH (a),(b) WHERE ID(a) = $sourceId AND ID(b) = $targetId
MERGE (a)-[r:`in`]->(b)
RETURN a, b, r

DO use literal IDs:

MATCH (a),(b) WHERE ID(a) = "#1:0" AND ID(b) = "#4:0"
MERGE (a)-[r:`in`]->(b)
RETURN a, b, r

AVOID property access in ID filters:

UNWIND $batch as row
MATCH (a),(b) WHERE ID(a) = row.source_id AND ID(b) = row.target_id
MERGE (a)-[r:`in`]->(b)
RETURN a, b, r

Instead, use parameters directly:

// Process batch items one at a time with parameters
MATCH (a),(b) WHERE ID(a) = $sourceId AND ID(b) = $targetId
MERGE (a)-[r:`in`]->(b)
RETURN a, b, r

Or use separate MATCH clauses:

UNWIND $batch as row
MATCH (a) WHERE ID(a) = row.source_id
MATCH (b) WHERE ID(b) = row.target_id
MERGE (a)-[r:`in`]->(b)
RETURN a, b, r

[lvca]

@ExtReMLapin Please let me know how it goes now and if you're able to follow the recommendations above to force the optimization at plan time.