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.
