All your optional matches, what cardinality do they have? at most 1 or more?
If it is > 1 you should reduce the cardinality in between, e.g.
MATCH (n:DataModel:CI__DataModel) WHERE ( n.name='host' )
OPTIONAL MATCH(n)-[:PARENT]->(parentNode)
WITH n, collect(DISTINCT parentNode.__id__) as parentIds
...
otherwise each subsequent (optional) match has to be executed for all those
multiplied rows.
I don't think you need a distinct then if you do the aggregation directly
after each match.
Something else that might help is to rewrite the query to:
MATCH (n:DataModel:CI__DataModel) WHERE n.name={name}
OPTIONAL
MATCH(n)-[:PARENT|:DISPLAYATTRIBUTE|:CREATEDBY|:APPLICATIONACCESS|:UPDATEDBY|:APPLICATION]->(related)
WITH n, type(rel) as type, collect(related.__id__) as ids
RETURN n as data, collect({type:type, ids:ids}) as relatedIds
you should also use a parameter for 'host' -> {name}
On Sun, Jul 24, 2016 at 9:06 PM, Michael Hunger <
[email protected]> wrote:
> 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.
