Performance query


(Tim Hanssen) #1

Hi All,

I'm trying to optimize my query but after some changes it still takes about 4 seconds to run.

CYPHER 3.4, planner: COST, runtime: SLOTTED. 3906086 total db hits in 3992 ms.

Data: 964291 interactions

PROFILE
MATCH (startleaf:Hour{Hash: "4581620/2018/01/01/00"})
, (endleaf:Hour{Hash: "4581620/2018/10/24/00"})
, p = shortestPath((startleaf)-[:NEXT*0..]->(endleaf))
UNWIND nodes(p) AS leaf

MATCH (year)-[:CHILD]->(month)-[:CHILD]->(day)-[:CHILD]->(leaf)<-[:INTERACTED_ON]-(interaction)

OPTIONAL MATCH (interaction)<-[pInt:INTERACTED]-(person)

WITH 
year, month, day
count(interaction) AS interactions,
sum(interaction.Value) AS value,
( count(DISTINCT person) + ( count(interaction) - count(pInt) ) ) AS persons

RETURN year, month, day interactions, value, persons

Finding the leafs just takes 60ms with 7db hits.

Any suggestions in how to optimise this?