It just misses the rel before the colon
>> MATCH (n:DataModel:CI__DataModel) WHERE n.name={name}
>> OPTIONAL
>> MATCH(n)-[rel: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
Von meinem iPhone gesendet
> Am 25.07.2016 um 23:37 schrieb Matias Burak <[email protected]>:
>
> 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]> 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.
--
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.
