Hey,
We are currently moving from v4 in community to v5 in aura.
One of the queries that was working perfectly well on v4 is not performing well on v5 in aura. We've tried a lot of hints and changes to the query but the query plan doesn't improve so I was looking if someone could know if there were any breaking changes between the two versions.
The query is
MATCH (:category {id:$categoryId})<-[:is_child_of*]-(i:item) RETURN i.id as id
the plan on v4 was
the plan on v5 changed to
Planner COST
Runtime PIPELINED
Runtime version 5.9
Batch size 1024
+------------------------+----+----------------------------------------------+--------------------+---------------------+
| Operator | Id | Details | Estimated Rows | Pipeline |
+------------------------+----+----------------------------------------------+--------------------+---------------------+
| +ProduceResults | 0 | id | 101298052256372768 | |
| | +----+----------------------------------------------+--------------------+ |
| +Projection | 1 | cache[i.id] AS id | 101298052256372768 | |
| | +----+----------------------------------------------+--------------------+ |
| +VarLengthExpand(Into) | 2 | (c)<-[r:is_child_of*]-(i) | 101298052256372768 | Fused in Pipeline 3 |
| | +----+----------------------------------------------+--------------------+---------------------+
| +CartesianProduct | 3 | | 1512878 | In Pipeline 2 |
| |\ +----+----------------------------------------------+--------------------+---------------------+
| | +CacheProperties | 4 | cache[i.id] | 1512878 | |
| | | +----+----------------------------------------------+--------------------+ |
| | +NodeByLabelScan | 5 | i:item | 1512878 | Fused in Pipeline 1 |
| | +----+----------------------------------------------+--------------------+---------------------+
| +NodeUniqueIndexSeek | 6 | UNIQUE c:category(id) WHERE id = $categoryId | 1 | In Pipeline 0 |
+------------------------+----+----------------------------------------------+--------------------+---------------------+
Total database accesses: ?
The difference from v4 I could find and can't understand in v5 is the usage of the cartesian product operator with the categories and items and in v4 the search starts in the category and then the relationships are transversed in the opposite direction.
What hint can I use for this query to generate a query plan just like in v4 in v5?