Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 7 days▾
Ends now. Anything up to this cluster's 30-day retention.
Start and end times
Cancel
Interpreted in UTC (change it at the right of this bar). Earliest available: 2026-10-19 12:53:28.
› →|
updated 1d ago
Sample data. This database was generated by scripts/seed_dev_db.py — every service, deploy, person and figure on this page is synthetic. Names are sample.*@seed.invalid and image tags are prefixed SEED-. Nothing here happened.
← query trends

Query shape trend

plan 80A742DC · last 7d

Optimizer suggestions — proposals from claude-opus-5-5, 2d ago; suggests only, a human applies

index Make sure the Study.tildaid anchor lookup is backed by a uniqueness constraint
The issueThe only entry point is `match (st:Study{tildaid: $studyId})`. Every other node is reached by traversing from `st`. If `Study.tildaid` has no index, this line becomes a NodeByLabelScan over all Study nodes with a property filter. The rest of the query depends on this lookup, so it should be a single-node seek.
The fixCheck `SHOW CONSTRAINTS` / `SHOW INDEXES` for Study(tildaid). If neither exists, create a uniqueness constraint. tildaid is used as an ID, so uniqueness also protects the data, and the constraint creates a backing range index the planner can use for a NodeUniqueIndexSeek. Other labels are reached by traversal, so they don't need indexes for this statement. Note the volume: 2 runs and 1.2 s total in 7 days, so the total gain is small either way.
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: If no index exists today, the anchor goes from a label scan proportional to the number of Study nodes to a single seek. That probably saves a few ms to tens of ms per call. If a constraint already exists, nothing changes. · Evidence: Run EXPLAIN/PROFILE before and after. The first operator should change from NodeByLabelScan(st:Study) + Filter to NodeUniqueIndexSeek(st:Study(tildaid)). Also confirm the constraint creates without errors (it will fail if duplicate tildaid values exist).
rewrite Gather tasks in per-control COLLECT subqueries instead of a long OPTIONAL MATCH chain that multiplies rows
The issueAfter `c` is found, the query chains about 10 OPTIONAL MATCHes that multiply each other's rows: - two unbounded var-length GRAPH_NEXT expansions (`(c)-[:GRAPH_NEXT*?..]-(...)-(tasklist)-[:GRAPH_NEXT*?..]->(taskconfig)`), one of them undirected, which reach every path and not just every node - ptasklist × ptsn × task1 - untyped `(c)-[]-(td:Todo)-[]-(tds:TodoSpecNode)` × ptd × task2 All of these rows reach `COLLECT(task1) + COLLECT(task2)`, so the same task appears many times. The query then UNWINDs, joins documents, and dedups again with DISTINCT. The p95 (1.9 s) is about 3× the average, which fits a fan-out that depends on the data. Also, `optional match (wf)-...-(pw)` is followed by a mandatory `match (pw)-...`, so it is effectively a required MATCH. Writing it as one makes that clear to the planner.
The fixKeep the anchor, workflow, activity and control matching as it is. Make the pw/scope match a plain MATCH. Reduce to one row per (wf, c, trigger, scope, pc) before collecting tasks. Then get task1 and task2 through two COLLECT { ... RETURN DISTINCT } subqueries (Neo4j 5.6+), so each path family is expanded once per control/participant-control and deduplicated right away instead of being multiplied together. The task1 subquery keeps the original's links: tasklist→ptasklist→pc and taskconfig→ptsn→ptasklist. One semantic caveat: the original `sum(case when task.state ...)` and `count(pc)` count duplicate rows, so numTasksCompleted and controlStatus can be inflated today. With deduplicated tasks those numbers change, most likely to the intended values. Confirm this with the owning team.
Proposed rewrite
match (st:Study{tildaid: $studyId})
match (st)-[:PLAN_DEFINITION_TO_STUDY]-(sitePd:DslPlanDefinition{type:?})
match (sitePd)-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]-(wf:WorkflowV2)-[:TRIGGER_TO_WORKFLOW]-(trigger:Trigger)
match (wf)-[r:GRAPH_NEXT*?..]->(ref:GraphNodeRef) where r[-?].path[?]=wf.tildaid and ref.objtype=?
match (ref)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:wf.tildaid}]->(a:ActivityV2)
match (a)-[:GRAPH_NEXT]-(:GraphNodeRef)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:wf.tildaid}]-(c:ControlV2)
match (wf)-[:PARTICIPANTWORKFLOW_TO_WORKFLOW]-(pw:ParticipantWorkflow)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(scope)
where ( (scope:DslPlanDefinition and (st)-[:PLAN_DEFINITION_TO_STUDY]-(scope)) or (scope:StudySiteActor and (st)--(:DslPlanDefinition)--(scope)) )
optional match (a)-[:BELONGS_TO_ACTIVITY]-(pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw)
optional match (c)-[:PCONTROL_TO_CONTROL]-(pc:ParticipantControl)-[:PARTICIPANT_CONTROL]-(pa)
with distinct wf, c, trigger, scope, pc
with wf, c, trigger, scope, pc,
  COLLECT {
    match (c)-[:GRAPH_NEXT*?..]-(:GraphNodeRef)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:wf.tildaid}]-(tasklist:TaskList)-[:GRAPH_NEXT*?..]->(taskconfig:TaskConfig)
    match (tasklist)-[:BELONGS_TO_TASKLIST]-(ptasklist:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(pc)
    match (taskconfig)-[:BELONGS_TO_TASKCONFIG]-(ptsn:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(ptasklist)
    match (ptsn)-[:CREATED_FROM_SPEC]-(task1:Task)
    return distinct task1
  } AS tasks1,
  COLLECT {
    match (c)-[]-(td:Todo)-[]-(:TodoSpecNode)
    match (td)-[]-(:ParticipantTodo)-[:CREATED_FROM_SPEC]-(task2:Task)
    return distinct task2
  } AS tasks2
with wf, c, trigger, scope, pc, tasks1 + tasks2 AS tasks
unwind tasks as task
optional match (task)<-[:CREATED_IN]-(document:Document)
optional match (scope)-[:BELONGS_TO]-(scopeDoc:Document)
return distinct c, count(distinct task) as totalTasks, sum(case when task.state = ? or task.state = ? then ? else ? end) as numTasksCompleted, case when count(pc) = sum(case when pc.status = ? then ? else ? END) then true else false end as controlStatus, trigger.scope, collect(distinct [{name: document.fileName, id: document.tildaid, url: document.url, type:document.docTypeName}, {name: scopeDoc.fileName, id: scopeDoc.tildaid, url: scopeDoc.url, type: scopeDoc.docTypeName}]) as documents
Expected: Intermediate rows (DB hits and rows in PROFILE) should drop a lot. The gain grows with how many GRAPH_NEXT paths and todos each control has. Plausibly a several-fold cut in p95, but this is unproven. · Evidence: PROFILE both versions with the same $studyId, ideally the one behind the 1.9 s p95. Compare total DB hits, rows into the EagerAggregation, and elapsed time. Diff the result sets: `c`, totalTasks and documents should match. numTasksCompleted and controlStatus may differ only where the original double-counted duplicates.

Status: proposed · all suggestions

No rollups for this shape in the window.