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"
2014-12-11 16:56 GMT-03:00 Michael Hunger <[email protected]>
:
> 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.
>
> On Thu, Dec 11, 2014 at 8:47 PM, Jeferson dos Anjos <
> [email protected]> wrote:
>
>> 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]>
>>> 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.
>>
>
> --
> 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
> [email protected].
> 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)
--
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.
