I removed shortestPath and changed the logic of my query. Now is returning
in less than 2 seconds.
Thanks
Em quinta-feira, 11 de dezembro de 2014 04h58min01s UTC-3, Michael Hunger
escreveu:
>
> 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]
> <javascript:>> 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] <javascript:>.
> > 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.
