Nice glad that helped! /peter
(snt)-[:frm]->(phn) On Feb 14, 2014 10:18 PM, "Tze-John Tang" <[email protected]> wrote: > Peter, > > thanks for the comments. Adding direction sped it up immensely. I was > originally attempting to pull all paths between two nodes, which is why I > had them non-directional, and I was using a wildcard earlier. > > -tj > > On Wednesday, February 12, 2014 8:04:15 AM UTC-6, Peter Neubauer wrote: >> >> Hi there, >> I think the problem here is that you have to evaluate a lot of paths >> that are possible in the MATCH clause in order to find the few right >> ones, filtered in the WHERE clause. This of course could be optimized >> by the query planner, and we are working on it, but for now you could >> try to "force" sequential, early, pruning by piping things through >> WITH clauses. >> >> Also, you did not specify direction or relationship type in your >> second query for (c:Container)--(h:Host), forcing Neo4j to evaluate a >> lot more paths potentially, which might be part of your increased >> db_hits. >> >> match (co:Component)<-[:INSTANCE_OF]-(ci:ComponentInstance)-[: >> INSTALLED_ON]-(c:Container)--(h:Host) >> where h.name =~ '(?i)uq00028p' >> return co,ci,c,h >> >> would become >> >> >> match (c:Container)-[:LOCATED_ON]->(h:Host) >> where h.name =~ '(?i)uq00028p' >> WITH h, c >> match (ci:ComponentInstance)<-[:INSTALLED_ON]-c >> WITH ci, h, c >> match (co:Component)<-[:INSTANCE_OF]-(ci) >> return co,ci,c,h >> >> HTH >> >> /peter >> >> G: neubauer.peter >> S: peter.neubauer >> P: +46 704 106975 >> L: http://www.linkedin.com/in/neubauer >> T: @peterneubauer >> >> Neo4j 2.0.0 - >> (graphs)-[:FOR]->(everyone) >> Kids LAN creative party in Malmö - Kidscraft ICE >> >> >> On Fri, Feb 7, 2014 at 4:38 PM, Tze-John Tang <[email protected]> >> wrote: >> > I am doing a query on a graph where the structure is the following, >> where >> > there are multiple paths between :Application and :ComponentInstance: >> > >> > (:Application) >> > <-[:COMPONENT_OF]-(:Component)<-[:INSTANCE_OF]-(:ComponentInstance)-[: >> INSTALLED_ON]->(:Container)-[:LOCATED_ON]->(:Host) >> > (:Application)<-[:INSTANCE_OF]-(:ApplicationInstance)<-[: >> COMPONENT_OF]-(:ComponentInstance) >> > >> > If I execute the following query, is it fast, within 221ms. >> > >> > match (ci:ComponentInstance)-[:INSTALLED_ON]-(c:Container)--(h:Host) >> > where h.name =~ '(?i)uq00028p' >> > return ci,c,h >> > >> > If I return one more node above :ComponentInstance, to return >> :Component, or >> > if I even go higher and try to find out which :Application is >> associated >> > with a given :Host with the following query, it takes > 6 seconds. >> > >> > match >> > (co:Component)<-[:INSTANCE_OF]-(ci:ComponentInstance)-[: >> INSTALLED_ON]-(c:Container)--(h:Host) >> > where h.name =~ '(?i)uq00028p' >> > return co,ci,c,h >> > >> > Both return the same number of rows. Can someone give some advice on >> how to >> > speed it up? I have the two profiles here, but I don't know how to read >> > them. The db hits are 22798 versus 1005601. >> > >> > ============================== >> > TRACE 1 >> > ============================== >> > >> > neo4j-sh (?)$ profile match >> > (ci:ComponentInstance)-[:INSTALLED_ON]-(c:Container)--(h:Host) >> >> where h.name =~ '(?i)uq00028p' >> >> return ci, c, h; >> > ==> >> > +----------------------------------------------------------- >> ------------------------------------------------------------ >> ----------------------------------------------+ >> > ==> | ci | c >> > | h | >> > ==> >> > +----------------------------------------------------------- >> ------------------------------------------------------------ >> ----------------------------------------------+ >> > ==> | Node[37529]{id:6678.0} | >> > Node[37531]{name:"AMS : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[41561]{id:4623.0} | >> > Node[41562]{name:"SPOTFIRE : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42903]{id:8417.0} | >> > Node[41886]{name:"ADME : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[41885]{id:2458.0} | >> > Node[41886]{name:"ADME : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42141]{id:6250.0} | >> > Node[42142]{name:"Unknown"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42167]{id:12782.0} | >> > Node[42168]{name:"D360_OWNER : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42170]{id:12781.0} | >> > Node[42171]{name:"PCSDW : DISLCNP1"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42199]{id:10702.0} | >> > Node[42200]{name:"PROTEIN : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42302]{id:5000.0,description:"Static Data Storage"} | >> > Node[42303]{name:"ABT_SST_DICT : STRLCVP1"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42305]{id:5010.0,description:"SDMS Data Storage"} | >> > Node[42306]{name:"ABT_SST_SDMS : STRLCVP1"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42308]{id:4983.0,description:"Data Storage"} | >> > Node[42309]{name:"ABT_SST : STRLCVP1"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42565]{id:3435.0} | >> > Node[42566]{name:"MARS : DISLCNP1"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42748]{id:8987.0} | >> > Node[42749]{name:"CRYSTALWEB : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42766]{id:10284.0} | >> > Node[42767]{name:"DIGSIG : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43044]{id:8914.0} | >> > Node[43045]{name:"PLASMA : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43291]{id:8414.0} | >> > Node[43292]{name:"STUDYLOG : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43407]{id:9141.0} | >> > Node[43408]{name:"CRYSTALWEB_USER : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43418]{id:9311.0} | >> > Node[43419]{name:"CCLIP : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43443]{id:9505.0} | >> > Node[43444]{name:"QTISA : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43515]{id:13359.0} | >> > Node[43516]{name:"MOLBIO : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[44286]{id:14214.0} | >> > Node[44287]{name:"SA1_CRIT_DB : DISLCNP2.PPG"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> >> > +----------------------------------------------------------- >> ------------------------------------------------------------ >> ----------------------------------------------+ >> > ==> 21 rows >> > ==> >> > ==> ColumnFilter(symKeys=[" UNNAMED58", "ci", " UNNAMED28", "c", >> "h"], >> > returnItemNames=["ci", "c", "h"], _rows=21, _db_hits=0) >> > ==> Filter(pred="hasLabel(ci:ComponentInstance(4))", _rows=21, >> _db_hits=0) >> > ==> PatternMatch(g="(c)-[' UNNAMED28']-(ci)", _rows=21, _db_hits=0) >> > ==> >> > Filter(pred="(LiteralRegularExpression(Property(h,name(0)),Literal((?i)uq00028p)) >> >> > AND hasLabel(h:Host(6)))", _rows=20, _db_hits=20) >> > ==> TraversalMatcher(trail="(c)-[ UNNAMED58 WHERE >> > (LiteralRegularExpression(Property(NodeIdentifier(), >> name(0)),Literal((?i)uq00028p)) >> > AND hasLabel(NodeIdentifier():Host(6))) AND true]-(h)", _rows=20, >> > _db_hits=22798) >> > >> > ============================== >> > TRACE 2 >> > ============================== >> > neo4j-sh (?)$ profile match >> > (co:Component)<-[:INSTANCE_OF]-(ci:ComponentInstance)-[: >> INSTALLED_ON]-(c:Container)--(h:Host) >> >> where h.name =~ '(?i)uq00028p' >> >> return co,ci,c, h; >> > ==> >> > +----------------------------------------------------------- >> ------------------------------------------------------------ >> ------------------------------------------------------------ >> ----------------------------------------------------------------------------+ >> >> > ==> | co >> > | ci | c >> > | h | >> > ==> >> > +----------------------------------------------------------- >> ------------------------------------------------------------ >> ------------------------------------------------------------ >> ----------------------------------------------------------------------------+ >> >> > ==> | Node[37527]{name:"Database",id:2427.0,type:"Database Server >> > Component"} | Node[37529]{id:6678.0} >> > | Node[37531]{name:"AMS : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[41560]{name:"spotfire - DB server",id:1667.0,type:"Database >> > Server Component"} | Node[41561]{id:4623.0} >> > | Node[41562]{name:"SPOTFIRE : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[41884]{name:"Digsig DB",id:890.0,type:"Database Server >> > Component"} | Node[41885]{id:2458.0} >> > | Node[41886]{name:"ADME : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42140]{name:"Database",id:2244.0,type:"Database Server >> > Component"} | Node[42141]{id:6250.0} >> > | Node[42142]{name:"Unknown"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42165]{name:"D360 Database",id:4868.0,type:"Database Server >> > Component"} | Node[42167]{id:12782.0} >> > | Node[42168]{name:"D360_OWNER : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42169]{name:"Data Warehouse Database",id:4867.0,type:"Database >> >> > Server Component"} | Node[42170]{id:12781.0} >> > | Node[42171]{name:"PCSDW : DISLCNP1"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42198]{name:"Database Server",id:4177.0,type:"Database >> Server >> > Component"} | Node[42199]{id:10702.0} >> > | Node[42200]{name:"PROTEIN : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42300]{name:"Database Server 2",id:1800.0,type:"Database >> Server >> > Component"} | Node[42302]{id:5000.0,description:"Static Data >> > Storage"} | Node[42303]{name:"ABT_SST_DICT : STRLCVP1"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42304]{name:"Database Server 3",id:1801.0,type:"Database >> Server >> > Component"} | Node[42305]{id:5010.0,description:"SDMS Data >> Storage"} >> > | Node[42306]{name:"ABT_SST_SDMS : STRLCVP1"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42307]{name:"Database Server",id:1794.0,type:"Database >> Server >> > Component"} | Node[42308]{id:4983.0,description:"Data >> Storage"} >> > | Node[42309]{name:"ABT_SST : STRLCVP1"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42564]{name:"MARS DB",id:1245.0,type:"Database Server >> Component"} >> > | Node[42565]{id:3435.0} | >> > Node[42566]{name:"MARS : DISLCNP1"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42747]{name:"Database",id:3353.0,type:"Database Server >> > Component"} | Node[42748]{id:8987.0} >> > | Node[42749]{name:"CRYSTALWEB : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42765]{name:"Database",id:3934.0,type:"Database Server >> > Component"} | Node[42766]{id:10284.0} >> > | Node[42767]{name:"DIGSIG : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[42902]{name:"HT-ADME Database",id:3109.0,type:"Database >> Server >> > Component"} | Node[42903]{id:8417.0} >> > | Node[41886]{name:"ADME : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43043]{name:"Operational Data Store",id:3328.0,type:"Database >> >> > Server Component"} | Node[43044]{id:8914.0} >> > | Node[43045]{name:"PLASMA : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43289]{name:"Studylog - DB",id:3108.0,type:"Database Server >> > Component"} | Node[43291]{id:8414.0} >> > | Node[43292]{name:"STUDYLOG : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43406]{name:"Oracle Database",id:3438.0,type:"Database >> Server >> > Component"} | Node[43407]{id:9141.0} >> > | Node[43408]{name:"CRYSTALWEB_USER : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43416]{name:"Operational Data Store",id:3504.0,type:"Database >> >> > Server Component"} | Node[43418]{id:9311.0} >> > | Node[43419]{name:"CCLIP : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43442]{name:"Operational Data Store",id:3592.0,type:"Database >> >> > Server Component"} | Node[43443]{id:9505.0} >> > | Node[43444]{name:"QTISA : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[43514]{name:"Database",id:5022.0,type:"Database Server >> > Component"} | Node[43515]{id:13359.0} >> > | Node[43516]{name:"MOLBIO : DISLCNP2"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> | Node[44284]{name:"Citeline oracle database",id:5245.0,type:"Database >> >> > Server Component"} | Node[44286]{id:14214.0} >> > | Node[44287]{name:"SA1_CRIT_DB : DISLCNP2.PPG"} | >> > Node[37530]{name:"UQ00028P",id:13630.0,type:"Physical"} | >> > ==> >> > +----------------------------------------------------------- >> ------------------------------------------------------------ >> ------------------------------------------------------------ >> ----------------------------------------------------------------------------+ >> >> > ==> 21 rows >> > ==> >> > ==> ColumnFilter(symKeys=["co", " UNNAMED59", " UNNAMED20", " >> UNNAMED89", >> > "ci", "c", "h"], returnItemNames=["co", "ci", "c", "h"], _rows=21, >> > _db_hits=0) >> > ==> Filter(pred="hasLabel(co:Component(3))", _rows=21, _db_hits=0) >> > ==> PatternMatch(g="(ci)-[' UNNAMED20']-(co)", _rows=21, _db_hits=0) >> > ==> >> > Filter(pred="(LiteralRegularExpression(Property(h,name(0)),Literal((?i)uq00028p)) >> >> > AND hasLabel(h:Host(6)))", _rows=21, _db_hits=21) >> > ==> TraversalMatcher(trail="(ci)-[ UNNAMED59:INSTALLED_ON WHERE >> > (hasLabel(NodeIdentifier():Container(5)) AND >> > hasLabel(NodeIdentifier():Container(5))) AND true]-(c)-[ UNNAMED89 >> WHERE >> > (LiteralRegularExpression(Property(NodeIdentifier(), >> name(0)),Literal((?i)uq00028p)) >> > AND hasLabel(NodeIdentifier():Host(6))) AND true]-(h)", _rows=21, >> > _db_hits=1005601) >> > >> > Thanks. >> > >> > -- >> > 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/groups/opt_out. >> > -- > 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/groups/opt_out. > -- 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/groups/opt_out.
