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 neo4j+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.