Neo4j very slowly

[Jeferson dos Anjos]

I'm trying to improve the speed of query below. She is returning the data in 9 seconds. If I remove the shortestPath, the time drops to 1.5 seconds.

Does anyone know what might be wrong with my query or how to optimize shartestPath?

It's a single query:

MATCH (currentUser:Packer {UUID:'19443'})-[:I_Follow*0..1]->followers-[rf:Has_Backpack|Has_Contribution*0..1]->(e)
Match (e)-[rp:Has_Pocket|Has_Document*0..]->d
Match d-[rn:Say_Thanks|I_Follow|I_Favorite_Follow|I_Favorite*0..1]->a
with distinct currentUser,followers, a, last(rf + rp  + rn) as l  
Optional match shortestPath(currentUser-[:Has_Group|Has_Shared_To_Collaboration|Hub_Shared|Has_Shared|Has_Backpack|Has_Pocket|Has_Document]->a)
with  followers, a, count(a) as num,l<br><br>
OPTIONAL MATCH a-[:Hub_Comments]->()-[rf:Has_Comment]->comments   
WITH  followers, a, l, collect(comments)[0..3] as coll,count(comments) as totalComments,num

MATCH parent-[l]->a where (num > 0 or a.Permission <> 'Private') with followers, a, parent, l, coll, totalComments  order by l.Datecreate desc skip 0 limit 10
Match (owner:Packer {Username:a.Createdby})<br>
return followers, a, parent, l, coll, totalComments, owner

Using the profile have this data:
Operator | Rows | DbHits | Identifiers

Extract (0) | 3731 | 7462 |

PatternMatcher (0) | 3731 | 8386 | parent, a, l |

Filter | 3735 | 7470 | | (a> {} AUTOINT3 OR NOT (Property (a, Permission (10)) == {AUTOSTRING4})) |

Total Accesses database: 23386
Version: 2.1.6 nodes: 175,563 properties: 468 402 relationships: 155,284 relationship types: 38 database disk: 780 MB usage: 2 MB
Thanks for any help.

[Michael Hunger]

There seems to be output missing in your profiler, can you check and repost? It also seems to have lost formatting, perhaps put it in a github gist?

I think you run into cross products and combinatorial explosions.

You HAVE to get the cardinalities DOWN in the middle by using distinct or aggregating, with collect(distinct)

So that you execute the MINIMIUM number of matches in the next step.

Could you run the following statements?

MATCH (currentUser:Packer {UUID:'19443'})-[:I_Follow*0..1]->followers-[rf:Has_Backpack|Has_Contribution*0..1]->(e)

return count(*), count(distinct followers), count(distinct e);

MATCH (currentUser:Packer {UUID:'19443'})-[:I_Follow*0..1]->followers-[rf:Has_Backpack|Has_Contribution*0..1]->(e)

Match (e)-[rp:Has_Pocket|Has_Document*0..]->d

return count(*), count(distinct followers), count(distinct e),count(distinct d);

MATCH (currentUser:Packer {UUID:'19443'})-[:I_Follow*0..1]->followers-[rf:Has_Backpack|Has_Contribution*0..1]->(e)

Match (e)-[rp:Has_Pocket|Has_Document*0..]->d

Match d-[rn:Say_Thanks|I_Follow|I_Favorite_Follow|I_Favorite*0..1]->a

return count(*), count(distinct followers), count(distinct e),count(distinct d), count(distinct a);

MATCH (currentUser:Packer {UUID:'19443'})-[:I_Follow*0..1]->followers-[rf:Has_Backpack|Has_Contribution*0..1]->(e)

Match (e)-[rp:Has_Pocket|Has_Document*0..]->d

Match d-[rn:Say_Thanks|I_Follow|I_Favorite_Follow|I_Favorite*0..1]->a

with distinct currentUser,followers, a, last(rf + rp  + rn) as l  

return count(*), count(distinct followers), count(distinct a),count(distinct l);

MATCH (currentUser:Packer {UUID:'19443'})-[:I_Follow*0..1]->followers-[rf:Has_Backpack|Has_Contribution*0..1]->(e)

Match (e)-[rp:Has_Pocket|Has_Document*0..]->d

Match d-[rn:Say_Thanks|I_Follow|I_Favorite_Follow|I_Favorite*0..1]->a

with distinct currentUser,followers, a, last(rf + rp  + rn) as l  

Optional match shortestPath(currentUser-[:Has_Group|Has_Shared_To_Collaboration|Hub_Shared|Has_Shared|Has_Backpack|Has_Pocket|Has_Document]->a)

return count(*), count(distinct a);

MATCH (currentUser:Packer {UUID:'19443'})-[:I_Follow*0..1]->followers-[rf:Has_Backpack|Has_Contribution*0..1]->(e)

Match (e)-[rp:Has_Pocket|Has_Document*0..]->d

Match d-[rn:Say_Thanks|I_Follow|I_Favorite_Follow|I_Favorite*0..1]->a

with distinct currentUser,followers, a, last(rf + rp  + rn) as l  

Optional match shortestPath(currentUser-[:Has_Group|Has_Shared_To_Collaboration|Hub_Shared|Has_Shared|Has_Backpack|Has_Pocket|Has_Document]->a)

with  followers, a, count(a) as num,l<br><br>

OPTIONAL MATCH a-[:Hub_Comments]->()-[rf:Has_Comment]->comments   

return count(*),count(distinct a), count(distinct comments);

MATCH (currentUser:Packer {UUID:'19443'})-[:I_Follow*0..1]->followers-[rf:Has_Backpack|Has_Contribution*0..1]->(e)

Match (e)-[rp:Has_Pocket|Has_Document*0..]->d

Match d-[rn:Say_Thanks|I_Follow|I_Favorite_Follow|I_Favorite*0..1]->a

with distinct currentUser,followers, a, last(rf + rp  + rn) as l  

Optional match shortestPath(currentUser-[:Has_Group|Has_Shared_To_Collaboration|Hub_Shared|Has_Shared|Has_Backpack|Has_Pocket|Has_Document]->a)

with  followers, a, count(a) as num,l<br><br>

OPTIONAL MATCH a-[:Hub_Comments]->()-[rf:Has_Comment]->comments   

WITH  followers, a, l, collect(comments)[0..3] as coll,count(comments) as totalComments,num

return count(*),count(distinct a), count(distinct followers),totalComments,num;

MATCH (currentUser:Packer {UUID:'19443'})-[:I_Follow*0..1]->followers-[rf:Has_Backpack|Has_Contribution*0..1]->(e)

Match (e)-[rp:Has_Pocket|Has_Document*0..]->d

Match d-[rn:Say_Thanks|I_Follow|I_Favorite_Follow|I_Favorite*0..1]->a

with distinct currentUser,followers, a, last(rf + rp  + rn) as l  

Optional match shortestPath(currentUser-[:Has_Group|Has_Shared_To_Collaboration|Hub_Shared|Has_Shared|Has_Backpack|Has_Pocket|Has_Document]->a)

with  followers, a, count(a) as num,l<br><br>

OPTIONAL MATCH a-[:Hub_Comments]->()-[rf:Has_Comment]->comments   

WITH  followers, a, l, collect(comments)[0..3] as coll,count(comments) as totalComments,num

MATCH parent-[l]->a 

where (num > 0 or a.Permission <> 'Private') 

with followers, a, parent, l, coll, totalComments  

order by l.Datecreate desc 

skip 0 limit 10

return count(*),count(distinct a), count(distinct parent),count(distinct followers),count(distinct l),num;

Michael

> --
> You received this message because you are subscribed to the Google Groups "Neo4j" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+un...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Jeferson dos Anjos]

Micheal, thanks for the reply. Essentially the output with the following queries were the same. Except for the latter.

Here is the result of my profile:

https://gist.github.com/jefersonmanjos/02cb3972b4f738b42397

ps .: If you can, I can get you access to the database.

Thank you for your help.

--
You received this message because you are subscribed to a topic in the Google Groups "Neo4j" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/neo4j/fMSUX6iApfE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to neo4j+un...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Jeferson M. dos Anjos
CEO do Packdocs

ps.: Mantenha seus arquivos vivos com o Packdocs (www.packdocs.com)

[Jeferson dos Anjos]

I removed shortestPath and changed the logic of my query. Now is returning in less than 2 seconds.

Thanks

[Michael Hunger]

you don't need shortestpath there because it is only one hop anyway.

I'd still look at the cardinalities before the matches and make sure that they are minimal.

Only the nodes of the match should have full cardinality, everything else should be aggregated.

[Jeferson dos Anjos]

Yes Michael, I understand that now.

I understand that now. But not understand what you mean by "Only the nodes of the match shouldnt have full cardinality, everything else shouldnt be aggregated." ?

Also follows the new query if you see something to have better.

Hugs

MATCH (currentUser:Packer {UUID:'19443'})-[ri:I_Follow|Hub_Shared|Has_Group*0..1]->followers

match followers-[rs:Has_Shared_To_Collaboration|Has_Shared*0..1]->s

match s-[rf:Has_Backpack|Has_Contribution*0..1]->(e)

match (e)-[rp:Has_Pocket*0..]->p

match p-[rd:Has_Document*0..1]->d

match d-[rn:Say_Thanks|I_Follow|I_Favorite_Follow|I_Favorite*0..1]->a where a.Createby = currentUser.Username or a.Permission <> 'Private' or type(last(rs)) is not null

with distinct currentUser,followers, a, last(ri + rs + rf + rp + rd + rn) as l,type(last(rs)) as la  order by l.Datecreate desc skip 0 limit 11

OPTIONAL MATCH a-[:Hub_Comments]->()-[:Has_Comment]->comments with  currentUser,followers, a, l,comments order by comments.Datecreated desc

MATCH parent-[l]->a 

Match (owner:Packer {Username:a.Createdby})

return followers, a, parent, l, owner,collect(comments)[0..3] as coll,count(comments) as totalComments"

--
You received this message because you are subscribed to a topic in the Google Groups "Neo4j" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/neo4j/fMSUX6iApfE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to neo4j+un...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--