Hi,
I am currently working on a database and i created some queries. The database is currently still quite small and should become even larger in the future, but the performance of an important query is really bad. To be able to use it productively, this statement must be executed much faster. I updated in this week to Neo4j 4.0.
It would be great if someone could give me some advice on what could be improved.
This is the structure of my database:
(:Category {Id: "CTG-1"}) -[:SPECIALISED_IN]-> (:Category) | A category like Car has a sub-category like mini-van
(:ProductReference) -[:DEFINED_CATEGORY_IN]-> (:Category) | A product is assigned to a category
(:ProductReference) -[:DEFINED_FEATURE_IN {FeatureId: "FLD-2-1", Value: ["avs-2", "avs-3"]}]-> (:Feature {Id: "FLD-2-1"}) | A product has a reference to a feature like Color and the relationship between this nodes has the selected value. So avs-2 could be blue.
I have the following amounts:
88x Categories
152x SPECIALISED_IN
74x Feature
7671x ProductReference
399771x DEFINED_FEATURE_IN
7785x DEFINED_CATEGORY_IN
My statement is:
CALL {MATCH (lowerCategory:Category) <-[:SPECIALISED_IN*0..]- (givenCategory:Category) MATCH (feature:Feature) <-[def:DEFINED_FEATURE_IN]- (product:ProductReference) -[definedCategory:DEFINED_CATEGORY_IN*0..1]-> (lowerCategory) MATCH (:Feature {Id: "FLD-2-3"}) <-[featureRef0:DEFINED_FEATURE_IN]- (product) MATCH (:Feature {Id: "FLD-6-1"}) <-[featureRef1:DEFINED_FEATURE_IN]- (product) WHERE givenCategory.Id IN ["CTG-1"] AND NOT feature.Id IN ["FLD-2-3","FLD-6-1"] AND ANY(val IN featureRef0.Value WHERE val = "avs-5" OR val = "avs-311") AND ANY(val IN featureRef1.Value WHERE val = "avs-278" OR val = "avs-279") RETURN def UNION MATCH (lowerCategory:Category) <-[:SPECIALISED_IN*0..]- (givenCategory:Category) MATCH (feature:Feature) <-[def:DEFINED_FEATURE_IN]- (product:ProductReference) -[definedCategory:DEFINED_CATEGORY_IN*0..1]-> (lowerCategory) MATCH (:Feature {Id: "FLD-6-1"}) <-[featureRef1:DEFINED_FEATURE_IN]- (product) WHERE givenCategory.Id IN ["CTG-1"] AND feature.Id = "FLD-2-3" AND ANY(val IN featureRef1.Value WHERE val = "avs-278" OR val = "avs-279") RETURN def UNION MATCH (lowerCategory:Category) <-[:SPECIALISED_IN*0..]- (givenCategory:Category) MATCH (feature:Feature) <-[def:DEFINED_FEATURE_IN]- (product:ProductReference) -[definedCategory:DEFINED_CATEGORY_IN*0..1]-> (lowerCategory) MATCH (:Feature {Id: "FLD-2-3"}) <-[featureRef0:DEFINED_FEATURE_IN]- (product) WHERE givenCategory.Id IN ["CTG-1"] AND feature.Id = "FLD-6-1" AND ANY(val IN featureRef0.Value WHERE val = "avs-5" OR val = "avs-311") RETURN def } WITH COLLECT(def.Value) as ValuesX, def.FeatureId as FeatureId UNWIND ValuesX AS ValuesY UNWIND ValuesY AS ValuesZ RETURN FeatureId AS FeatureId, Min(ValuesZ) AS MinValueString, Max(ValuesZ) AS MaxValueString, collect(distinct ValuesZ) AS Values
The profiling for this query is
The expected result sould look like this:
I you need further information, feel free to ask.
Thank you.