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 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.
