Can you run it with PROFILE instead of EXPLAIN ? So that the actual costs are visible not just the estimated ones?
Please also share your current indexes/constraints (run "schema") Michael On Fri, Jul 22, 2016 at 1:09 AM, Matias Burak <[email protected]> wrote: > Hi all, > > Hi, I need some help with a slow query, tried on neo4j 2.5 and 3.0 > > > MATCH (n:DataModel:CI__DataModel) WHERE ( n.name='host' ) > > OPTIONAL MATCH(n)-[:PARENT]->(parentNode) > > OPTIONAL MATCH(n)-[:DISPLAYATTRIBUTE]->(displayAttributeNode) > > OPTIONAL MATCH(n)-[:CREATEDBY]->(createdByNode) > > OPTIONAL MATCH(n)-[:APPLICATIONACCESS]->(applicationAccessNode) > > OPTIONAL MATCH(n)-[:UPDATEDBY]->(updatedByNode) > > OPTIONAL MATCH(n)-[:APPLICATION]->(applicationNode) RETURN n as data > > , collect(DISTINCT applicationNode.__id__) as applicationIds, > collect(DISTINCT applicationAccessNode.__id__) as applicationAccessIds, > collect(DISTINCT createdByNode.__id__) as createdByIds, collect(DISTINCT > displayAttributeNode.__id__) as displayAttributeIds, collect(DISTINCT > parentNode.__id__) as parentIds, collect(DISTINCT updatedByNode.__id__) as > updatedByIds > > > It’s taking about 1 sec > > i cannot change much the query… if i remove the APPLICATIONACCESS and > UPDATEDBY relationships from that query, it goes very fast > > > > I see that it’s doing an AllNodeScan for them: > > > Compiler CYPHER 3.0 > > > Planner COST > > > Runtime INTERPRETED > > > > +----------------------+----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | Operator | Estimated Rows | Variables > | Other > > | > > > +----------------------+----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +ProduceResults | 862472 | applicationAccessIds, > applicationIds, createdByIds, data, displayAttributeIds, parentIds, ... > | data, applicationIds, applicationAccessIds, createdByIds, > displayAttributeIds, parentIds, updatedByIds | > > | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +EagerAggregation | 862472 | applicationAccessIds, > applicationIds, createdByIds, displayAttributeIds, parentIds, ... > | data > | > > | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +Projection | 743858355653 | data -- anon[120], anon[183], > anon[232], anon[297], anon[346], anon[77], applicationAccessNode, ... | > updatedByNode; createdByNode; n; displayAttributeNode; > applicationAccessNode; applicationNode; parentNode | > > | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +NodeOuterHashJoin | 743858355653 | anon[120], anon[183], anon[232], > anon[297], anon[77], applicationAccessNode, createdByNode, ... | > > | > > | |\ > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | | +Expand(All) | 101 | anon[346], applicationNode -- n > | > (n)-[:APPLICATION]->(applicationNode) > | > > | | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | | +AllNodesScan | 446872 | n > | > > | > > | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +NodeOuterHashJoin | 426134864 | anon[120], anon[183], anon[232], > anon[77], applicationAccessNode, createdByNode, ... | > > | > > | |\ > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | | +Expand(All) | 2390 | anon[297], n -- updatedByNode > | > (updatedByNode)<-[:UPDATEDBY]-(n) > | > > | | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | | +AllNodesScan | 446872 | updatedByNode > | > > | > > | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +OptionalExpand(All) | 771541 | anon[232], applicationAccessNode > -- anon[120], anon[183], anon[77], createdByNode, ... | > (n)-[:APPLICATIONACCESS]->(applicationAccessNode) > | > > | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +OptionalExpand(All) | 442 | anon[183], createdByNode -- > anon[120], anon[77], displayAttributeNode, n, parentNode | > (n)-[:CREATEDBY]->(createdByNode) > | > > | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +OptionalExpand(All) | 65 | anon[120], displayAttributeNode > -- anon[77], n, parentNode | > (n)-[:DISPLAYATTRIBUTE]->(displayAttributeNode) > | > > | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +OptionalExpand(All) | 0 | anon[77], parentNode -- n > | > (n)-[:PARENT]->(parentNode) > | > > | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +Filter | 0 | n > | > n.name == { AUTOSTRING0} AND n:CI__DataModel > | > > | | > > +----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > | +NodeByLabelScan | 16 | n > | > :DataModel > | > > > +----------------------+----------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+ > > > Total database accesses: ? | > > | > > > > Perhaps some indexes missing? > > I think they should all be indexed by __id__ > > > Thanks, > > Matias. > > -- > 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.
