Cypher performance optimization when Node doesn't have indexing


(Abhi) #1

I asked the same question on Slack and they suggested me to move the discussion on Neo4j Community.

How to optimize below Cypher?

MATCH  (port:PhysicalPort) WHERE NOT (1 IN port.latest)  WITH  port   
MATCH   (device:Device) WHERE NOT (1 IN device.latest)  WITH  port , device   
MATCH   (building:Location) WHERE building.archetypeId=1008 AND NOT (1 IN building.latest)  WITH  port , device , building   
MATCH   (building) - [:HAS*0..2] -> (childLoc:Location) -[:HAS] -> (rack:Rack) -[:HAS] -> (rackPos:RackPosition) -[:HAS] -> (device)  WITH  port , building , device   
MATCH  (device) - [:HAS*1..8] -> (port)    WITH  DISTINCT  port   

RETURN port.drniId

I don't have indexing on :Location(archetypeId) and 'latest' (all Nodes) .! I have attached the execution plan.

There was a suggestion as well on the Slack.


If you have multiple ports and devices that match the latest condition you’ll be running the statements after multiple times so it may be quicker to match the building first

MATCH   (building:Location) - [:HAS*0..2] -> (childLoc:Location) -[:HAS] -> (rack:Rack) -[:HAS] -> (rackPos:RackPosition) -[:HAS] -> (device:Device)  -[:HAS*1..8]->(port:PhysicalPort)
WHERE  building.archetypeId=1008 AND  NOT (1 IN device.latest) AND NOT (1 IN port.latest) 
RETURN port.drniId

But this makes the scenario much worse and my Neo4j browser hangs.

Additional Details:

Neo4j Database Version : 3.4.0
Neo4j Desktop: 1.1.12
Neo4j Database Edition: Enterprise

Any help would be greatly appreciated.

(Michael Hunger) #2

I'm not sure what (1 IN port.latest) means but I would add a label for the :Latest and then use WHERE not port:Latest.

And why not just add the :Location(archetypeId).

Why mess around when an index makes sense and would do.

(Abhi) #3

latest is a property in all Nodes, which is of type array. Currently, it has values [ 1, 2 ] or [ 2, 3, 4] and I want to filter those records where this array doesn't have value 1.

(Michael Hunger) #4

Still turn it into a label or labels.