I have the following Neo4j Cypher count() query:
MATCH (dg:DecisionGroup)-[:CONTAINS]->(childD:Decision)
WHERE dg.id = 1
MATCH (childD)-[relationshipValueRel4:HAS_VALUE_ON]-(filterCharacteristic4:Characteristic)
WHERE filterCharacteristic4.id = 4
WITH relationshipValueRel4, childD, dg
WHERE (ANY (id IN [5, 25, 106] WHERE id IN relationshipValueRel4.optionIds ))
WITH childD, dg
RETURN count(childD) as total
Right now this query works pretty slow:
Cypher version: CYPHER 3.3, planner: COST, runtime: INTERPRETED. 3380782 total db hits in 2991 ms.
This is PROFILE output:
How to optimize this query performance ?
P.S
The corresponding main query works pretty fast:
PROFILE MATCH (dg:DecisionGroup)-[:CONTAINS]->(childD:Decision)
WHERE dg.id = 1
MATCH (childD)-[relationshipValueRel4:HAS_VALUE_ON]-(filterCharacteristic4:Characteristic)
WHERE filterCharacteristic4.id = 4
WITH relationshipValueRel4, childD, dg
WHERE (ANY (id IN [5, 25, 106]
WHERE id IN relationshipValueRel4.optionIds ))
WITH childD, dg WITH childD , dg
SKIP 0 LIMIT 10
WITH *
MATCH (childD)-[ru:CREATED_BY]->(u:User)
OPTIONAL MATCH (childD)-[rup:UPDATED_BY]->(up:User)
RETURN ru, u, rup, up, childD AS decision,
[ (dg)<-[:DEFINED_BY]-(entity)<-[:COMMENTED_ON]-(comg:CommentGroup)-[:COMMENTED_FOR]->(childD) | {entityId: toInt(entity.id), types: labels(entity), totalComments: toInt(comg.totalComments)} ] AS commentGroups,
[ (dg)<-[:DEFINED_BY]-(c1)<-[vg1:HAS_VOTE_ON]-(childD) | {criterionId: toInt(c1.id), weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes)} ] AS weightedCriteria, [ (dg)<-[:DEFINED_BY]-(ch1:Characteristic)<-[v1:HAS_VALUE_ON]-(childD) WHERE NOT ((ch1)<-[:DEPENDS_ON]-()) | {characteristicId: toInt(ch1.id), optionIds: v1.optionIds, valueIds: v1.valueIds, value: v1.value, available: v1.available, totalHistoryValues: v1.totalHistoryValues, totalFlags: v1.totalFlags, description: v1.description, valueType: ch1.valueType, visualMode: ch1.visualMode} ] AS valuedCharacteristics
Cypher version: CYPHER 3.3, planner: COST, runtime: INTERPRETED. 11725 total db hits in 8 ms
Please help to optimize the count() query performance also.
