My graph is for representing Backlogs, Stories, Sprints, Tasks Users, and Resources. A Backlog can have Stories, which can have tasks, which can be associated with Sprints and Users. I would like to return a nested structure of Backlog -> Stories ->Task, and also show the Sprint & Users associated with Tasks.
For a small Backlog with 11 Stories and 40 tasks, I'm getting 1,972 db hits taking 1208ms.
Seems quite sluggish, and i think the issue is with the nested COLLECT aggregations. I'm hoping someone might have some suggestions to improve performance.
My query is below along with the profiled execution plan
MATCH (backlog:Backlog{id:'2957822f34504862ae51e8f69980a15f'})
OPTIONAL MATCH (backlog)-[:BACKLOG_HAS_STORY]->(story)
OPTIONAL MATCH (story)-[:STORY_HAS_TASK]->(task
OPTIONAL MATCH (task)-[:TASK_HAS_RESOURCE]->(resource)
OPTIONAL MATCH (sprint)-[:SPRINT_HAS_TASK]->(task)
OPTIONAL MATCH (owner)-[:ACCOUNT_OWNS_STORY]->(story)
WITH backlog,story,COLLECT(resource{.*}) as resources, task, sprint,owner
WITH backlog,story,COLLECT(task{.*,resources:resources,sprint:sprint{.*}}) as tasks,owner
WITH backlog, COLLECT(story{.*,owner:owner{.id,.firstName,.lastName},tasks: tasks}) as stories
RETURN backlog{.*,stories:stories}