Hello, I have this query and it refuses to use an index, idk if it's because the "Expand" stage in the pipeline or what exactly, but I can't get it to use an index in this form, especially in the ORDER BY clause, it still gives me a "Sort" stage in the planner, and I'd like to avoid it.
The index is the createdAt property.
PROFILE
MATCH (u:User {user_id: '61c84762da4e457d55656efa'})-[follows:FOLLOWS]->(following:User)-[relatedTo:POSTED|SHARED]->(everything)
WHERE relatedTo.createdAt > datetime("2000-02-12T15:42:10.866+00:00")
RETURN u, relatedTo, everything
ORDER BY relatedTo.createdAt DESC
The only way it does what I want it to do, is if I remove everything prior to the last relation, which obviously defies the point of that query but it was just for testing.
PROFILE
MATCH (following:User)-[relatedTo:POSTED|SHARED]->(everything)
WHERE relatedTo.createdAt > datetime("2000-02-12T15:42:10.866+00:00")
RETURN relatedTo, everything
ORDER BY relatedTo.createdAt DESC
Now it uses the index.
I am not allowed to embed 2 images, so here's the plan but uploaded: Screenshot by Lightshot
Any ideas how to do I get it to use an index in both, the query & the sort?
you can split the query like you did
and use USING JOIN on following
you can also force the index usage on the relationship with USING INDEX relatedTo:POSTED(createdAt)
I think that criterion you're showing is probably also not very selective, so it might not help so much to pull all X million relationships that have that from the index.
Sometimes a better approach is to elevate the time-information to the relationship-type or an additional label on the target node.
explain MATCH (u:User {user_id: '61c84762da4e457d55656efa'})-[follows:FOLLOWS]->(following:User)
match (following)-[relatedTo:POSTED]->(everything)
using index relatedTo:POSTED(createdAt)
WHERE relatedTo.createdAt > datetime("2000-02-12T15:42:10.866+00:00")
RETURN u, relatedTo, everything
ORDER BY relatedTo.createdAt DESC
The issue is, even with that approach, as you can see neo4j still added a "Sort" stage in the pipeline, despite it actually using the order in the index stage. Why does that happen?
My other issue is the fact that "relatedTo" can be so many other relations up to, I think 15. So how do I deal with it that way.
I discovered that the index doesn't get used, EVEN for node indexes when there are multiple matches in the query, or a long extended path matching.
For example:
PROFILE
MATCH (u:User {user_id: '61c84762da4e457d55656efa'}) - [follows:FOLLOWS] -> (following:User)- [r:ORDERED] -> (order:Order)
WHERE order.isAllowedOnFeedActivities = true
RETURN order
The index on order.isAllowedOnFeedActivities doesn't get utilised here at all.
It usually only uses indexes for entry points or when there is an option to close in from two sides.
in general it tries to start at some nodes and then expand and filter because the indexes mostly defeat the purpose of local graph searches because you get thousands of unrelated elements back that you need to filter through.
You can force it to use two or more indexes by forcing two sided pattern matching, using USING JOIN ON x if the x is the point in the path where it would usually explode, then coming from two sides and intersecting will be more efficient.
