Xray
1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 1 hour▾
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 17:16:52.
› →|
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 61D9334F · last 1h

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

rewrite Apply the site-name and query-state/externalFlag filters early instead of at the end
The issueThe filter `WHERE q.state = $mqState AND q.externalFlag = true AND s.name IN $siteNames` sits on the `MATCH (task:Task)-[:WITH_CONTEXT]-(q)` that comes after both large CALL subqueries. So every (pd, site) row from the site UNION expands Patient→CarePlan→SOA. Every CarePlan then expands into four ParticipantActivity UNION branches, including the variable-length `[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]` path, plus the Query lookups through ParticipantQuestion/ParticipantTable and the Visit→Binder→Document→Binder chain. Rows for sites not in $siteNames, and Queries in the wrong state or with externalFlag=false, are built and then thrown away. That wasted traversal is most of the cost of a 3.7 s run.
The fix`s.name IN $siteNames` only depends on `site`, so it can go straight after `WITH pd, site AS s`. Sites outside the list are then dropped before any patient or care-plan expansion. The `q.state` and `q.externalFlag` checks can go inside each UNION branch that returns q, so non-matching Queries are discarded before they multiply against Task, ChangeLog and Inbox rows. Removing rows earlier gives the same result, because the original WHERE only ever removed rows. While doing this, check the hidden upper bound on `*?..` in the ParticipantActivity branch. If it is large or missing, give it an explicit small maximum depth, because an unbounded expansion over two relationship types is the riskiest part of this plan.
Proposed rewrite
MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId}) 
call (org,st) { 
match (org)-[:STUDY_TO_ORG]-(st)-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) 
where (org.orgType=? or org.orgType=?) and pd.delpending=false 
return pd, site 
union 
match (org)-[:IS_AN_ORG]-(:Smo)-[:SITE_TO_SMO]-(site:Site)-[:PLAN_DEFINITION_TO_SITE]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(st) 
where pd.delpending=false 
return pd,site } 
WITH pd, site AS s 
WHERE s.name IN $siteNames 
MATCH (p:Patient)-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd) 
call { 
with cp 
call { 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) 
return pa, pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(cp) 
return pa, null as pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) 
return pa, pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]-(cp) 
return pa, null as pv } 
call { 
with pa 
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) 
where q.state = $mqState and q.externalFlag = true 
return q 
union 
with pa 
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(:ParticipantTable)-[:PARTICIPANT_TABLE]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) 
where (q.delpending is null or q.delpending = false) and q.state = $mqState and q.externalFlag = true 
return q } 
return q, pa, pv, null as doc 
union 
with cp, p 
call { 
with cp, p 
match (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2)-[:BINDER_FOR]-(vb:Binder)-[:INCLUDES]-(doc:Document)-[:INCLUDES]-(pb:Binder)-[:BINDER_FOR]-(p) 
MATCH (q:Query)-[:HAS_QUERY]-(doc) 
where q.state = $mqState and q.externalFlag = true 
return doc, pv, q } 
return q, null as pa, pv, doc } 
MATCH (task:Task)-[:WITH_CONTEXT]-(q) 
optional match (pa)-[:BELONGS_TO_ACTIVITY]-(act:Activity|ActivityV2) 
optional match (pv)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) 
match (p)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi)
WITH task,q,s,act,v,cp,phi,null as study,null as studyActor 
OPTIONAL MATCH (q)-[:ALL_CHANGELOG]-(cl:ChangeLog) 
optional match (task)-[:BELONGS_TO]->(i:Inbox)-[:OWNED_BY]->(u:User)
WITH task,q,s,act,v,cp.subjectid as subjectid, phi,study,studyActor,collect(distinct u.name) as assignedToUsers, cl 
ORDER BY cl.createdat ASC 
WITH task,q,s,act,v,phi,subjectid,assignedToUsers,study,studyActor, COLLECT(cl) AS changeLogs,count(case when cl.type=? or (cl.type=? and cl.reason <> ?) then ? end) as comments, duration.inDays(datetime({epochMillis: q.createdat}), datetime()).days AS durationDays 
WITH task,q,s,act,v,phi,subjectid,assignedToUsers,study,studyActor, changeLogs, comments, durationDays, reduce(d = ?, cl IN changeLogs | CASE WHEN cl.type = ? AND cl.newValue = ? THEN cl.createdat ELSE d END) AS resolvedAt 
WITH {task: task, q: q, s: s, act: act, v: v, phi: phi, subjectid: subjectid, assignedToUsers: assignedToUsers, study: study, studyActor: studyActor, changeLogs: changeLogs, comments: comments, durationDays: durationDays} as entry 
WITH entry 
WITH entry.q.tildaid as queryId, collect(entry) as entriesPerQuery 
WITH count(queryId) as countOfN, collect(head(entriesPerQuery)) as rows 
UNWIND rows as entry 
WITH countOfN, entry 
RETURN distinct entry.q, entry.s, entry.study, entry.studyActor, entry.changeLogs, entry.durationDays, countOfN, apoc.
create.vNode([?], {taskId: entry.task.tildaid, subject: entry.phi.firstname + ? + entry.phi.lastname, subjectid: entry.subjectid, visitTitle: entry.v.title, activityTitle: entry.act.title, type: entry.q.type, comments: entry.comments, assignedTo: entry.assignedToUsers, blindedAccess: entry.act.blindedAccess}) as dashboardInfo
Expected: This could plausibly cut runtime by a large fraction, in proportion to how many sites and Query states are excluded. If $siteNames usually covers most sites and most Queries already match the state, the gain will be small. Since the query ran only once in 7 days, the total time saved is modest, but this one dashboard call should feel noticeably faster. · Evidence: PROFILE both versions with the same parameters. Compare db hits and rows on the CarePlan/ScheduleOfActivities expand step and inside the ParticipantActivity/Query subqueries; the Filter on s.name should now sit right after the site Union. Check that both versions return the same rows and the same countOfN, then compare elapsed time over the next few runs.
index Make sure the Study and Organization tildaid anchors have unique lookups
The issueThe whole statement starts from `MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId})`. Without a uniqueness constraint or index on tildaid, the planner has to scan all Organization or Study nodes to find the anchor before any traversal starts. The property list for this shape does not show whether tildaid is already indexed.
The fixRun SHOW CONSTRAINTS / SHOW INDEXES. If Study.tildaid or Organization.tildaid does not have a uniqueness constraint (tildaid looks like an application key), add one. The constraint creates a backing range index, so the anchor becomes a NodeUniqueIndexSeek. Only one of the two is strictly needed for the planner to seek and then expand, but having both is cheap. The second DDL is: CREATE CONSTRAINT org_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE.
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: If these keys are currently unindexed, the anchor step changes from a label scan to a constant-time seek. That saves time in proportion to how many Study and Organization nodes exist, probably a few to tens of milliseconds. If the constraints already exist, this changes nothing and should be skipped. · Evidence: Run EXPLAIN on the statement. The leftmost operator should be NodeUniqueIndexSeek on Study(tildaid) or Organization(tildaid), not NodeByLabelScan. Confirm the current state with SHOW CONSTRAINTS before creating anything.

Status: proposed · all suggestions

No rollups for this shape in the window.