Hi,
we are trying to optimize a query but the time explodes (~20 seconds) when having around 40K nodes in the database, but it should be way faster.
First, I will describe a simplified description of our schema. We have the following nodes:
- Usergroup
- Feature
- Asset
- Section
We also have the following relationships:
- A Feature has only one Section (IS_IN_SECTION)
- A Feature has one or more Asset (CONTAINS_ASSET)
- An asset may be restricted for a Usergroup (HAS_RESTRICTED_ASSET)
- A Feature may be restricted for a Usergroup (HAS_RESTRICTED_FEATURE)
- A Section, and therefore, all the Feature of that Section, may be restricted for a Usergroup (HAS_RESTRICTED_SECTION)
- A Usergroup may have a parent Usergroup (HAS_PARENT_GROUP) and it should fulfill its restrictions and those of its parents
The goal is, given a Usergroup, to list the top 20 assets ordered by date, that don't have any restrictions with the Usergroup.
The current query is similar to:
(1)
MATCH path=(:UserGroup {uid: $usergroup_uid})-[:HAS_PARENT_GROUP*0..]->(root:UserGroup)
WHERE NOT (root)-[:HAS_PARENT_GROUP]->(:UserGroup)
WITH nodes(path) AS usergroups
UNWIND usergroups AS ug
(2)
MATCH (node:Asset)
WHERE AND NOT (node)<-[:CONTAINS_ASSET]-(:Feature)-[:IS_IN_SECTION]->(:Section)<-[:HAS_RESTRICTED_SECTION {restriction_type: "view"}]-(ug)
AND NOT (node)<-[:HAS_RESTRICTED_ASSET {restriction_type: "view"}]-(ug)
AND NOT (node)<-[:CONTAINS_ASSET]-(:Feature)<-[:HAS_RESTRICTED_FEATURE {restriction_type: "view"}]-(ug)
RETURN DISTINCT node
ORDER BY node.date DESC
SKIP 0
LIMIT 20
We have a few more types of restrictions but here we have the main idea.
Some observations we have made are:
- If we execute the query part (1) adding
return ugafter unwind, this query is solved in 1ms - If we change the query part (1) to
MATCH (ug:Usergroup {uid: $usergroup_uid})ignoring the parent groups, the query is solved in around 800ms. And if we add back the original part (1) it is solved in 8 seconds even if the Usergroup has no parents.
Currently, our database is small compared to the expected number of nodes (~6 millions), and the number of restrictions will grow, and we need to optimize this kind of queries.
For that, we have these questions:
- The
NOT <restrictions>(ex:NOT (node)<-[:HAS_RESTRICTED_ASSET {restriction_type: "view"}]-(ug)) conditions is correct in this kind of situation or are there other approachs to get the job done more efficiently? - Do we need any type of index?
- Is the structure of the schema right, or are there any inefficiencies?
- How can we rewrite the part (1) of the query or what do you thinks is causing the overhead with it?
The database version is Neo4j 3.5.X
Captures of the query profile are:
Thanks in advance
1 ACCEPTED SOLUTION
Thanks for the responses @bennu.neo and @glilienfield.
You are right, we've just tested this, and it's making the union of the lists. 
We've considered this approach but we may have built the query incorrectly because all the attempts to face it in this way were even slower.
Restricted directly from a usergroup to an asset through the HAS_RESTRICTED_ASSET relationship there could be tipically less than 1000, but indirectly trhough the other relationships HAS_RESTRICTED_FEATURE, HAS_RESTRICTED_SECTION we could have 100K or more.
Yes we have APOC installed. So any suggestion with this approach will be valid for us
Nice!
So let's play together a little bit. I'll not consider the property condition HAS_RESTRICTED_ASSET for a while so we can first get an idea of the timing like this.
MATCH (u:UserGroup {uid: $usergroup_uid})
WITH u
CALL apoc.path.subgraphNodes(u, {
relationshipFilter: "HAS_PARENT_GROUP>"
}) yield node
WITH collect(node) AS usergroups
CALL apoc.path.subgraphNodes(usergroups, {
relationshipFilter: "HAS_RESTRICTED_ASSET>|CONTAINS_ASSET>|HAS_RESTRICTED_FEATURE>|HAS_RESTRICTED_SECTION|<IS_IN_SECTION",
labelFilter: "/Asset"
}) yield node
with collect(id(node)) as bl
MATCH (node:Asset)
WHERE id(node) not in bl
RETURN node
ORDER BY node.date DESC
LIMIT 20
Let see if this runs 
Hi @ainsausti !
Keep in mind that my query should be equivalent to yours without the [:HAS_RESTRICTED_ASSET {restriction_type: "view"}] condition and just [:HAS_RESTRICTED_ASSET ]. Keep that on mind when comparing results. Then if they are they same, we can talk about ways to introduce that condition without loosing our performance.
Oh @glilienfield , don't abandon your child (query)
Hi again!
There is no reason why the direction is not set in some of the relationship filters, I've added them.
The apoc query takes 32 ms. So, I think, it's not the problematic part of the query.
You understood correctly the intention of the query. Nonetheless, this is the base query, some filters are added dynamically to this one. For example, to find the assets with a description equal to "Test description", the condition "AND node.description = 'Test description' " will be added.
We have a "uid" property with a unique index. I've added a composite index on Asset.uid and Asset.logically_deleted and chnged the query. But still there is no improvement. I think the index are not applied in the execution.
The profile with the index is:
I think the index was created succesfully:
NODES 2022, Neo4j Online Education Summit
All the sessions of the conference are now available online
