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.
