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
On Thu, Dec 11, 2014 at 3:27 AM, Jeferson dos Anjos <[email protected]>
wrote:
>
> 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.
>
> --
> 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 [email protected].
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
