Hello, I am trying to understand that why following query takes long time to return results:
PROFILE
with ['frace_paris', 'spain_barcelona', 'spain_madrid'] as cities,
'turkey_istanbul' as startCity
MATCH p = (l1:Location {name :'turkey_istanbul', day: '2024/03/18'})-[:DEPARTURE]->(f1:Flight)-[:ARRIVAL]->(l2:Location)-[:NEXT*3..3]->(l3:Location)
-[:DEPARTURE]->(f2:Flight)-[:ARRIVAL]->(l4:Location)-[:NEXT*3..3]->(l5:Location)
-[:DEPARTURE]->(f3:Flight)-[:ARRIVAL]->(l6:Location {name : 'turkey_istanbul'})
where
l2.name in cities and l4.name in cities and l3.name in cities and l5.name in cities
return f1.price + f2.price + f3.price as totalPrice order by totalPrice limit 10
The profile that you attached shows that the query engine started with l1, ploughed through short of 30 million intermediate results and spent a lot of time on the filters that were only possible to evaluate after reaching the end of the pattern at l6.
So, in essence: Your query contains a long pattern which is very generic on the nodes in the middle.
But it is only the end nodes of the pattern that could be obtained from these indexes.
There are now generally two variants how we could approach this pattern:
Using a JOIN
In this scenario, we start from both sides, produce all combinations and see whether we meet on some middle node.
This is the approach that relational databases usually choose and if "all combinations" sounds like a very large number, that is exactly the point and will probably not perform very well.
Using Expands
In this scenario, we start from one side and only explore the relationships that are bound to the node(s) that we have encountered so far, to check in the end whether we reached the right node.
This scenario only uses the index on one side but tends to have far lower intermediate results to process.
Conclusion
Neo4j's execution engine considers both alternatives and chooses the one which it deems more performant. "Quickly" is relative to all other alternatives to answer your query.
With large patterns, indexes only provide a quick starting point but we still need to explore all relationships. What you can do, however, is to provide more information about the nodes and relationships within the pattern (are there any restrictions on the properties), so that we could potentially discard some of the intermediate results.
