Thanks Michael. That first query helped a lot.
The second one is not working. I think it's because you are not defining
"rel" anywhere.
El domingo, 24 de julio de 2016, 16:14:08 (UTC-3), Michael Hunger escribió:
>
> 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] <javascript:>> 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]
>> <javascript:>> 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] <javascript:>.
>>> 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.
