Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 14 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 17:44:00.
› →|
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 B5BE080F · last 14d

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

rewrite Apply the site-name filter right after the plan-definition subquery
The issue`s.name IN $siteNames` sits in the WHERE of `MATCH (task:Task)-[:WITH_CONTEXT]-(q)`. That is far down the query. Before it runs, every (pd, site) pair from the first CALL is expanded through Patient→CarePlan→ScheduleOfActivities. It then goes through four UNION branches over ParticipantActivity (one is a variable-length `[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]` traversal), the nested Query UNIONs, and the Visit→Binder→Document→Binder UNION branch. Rows for sites the caller never asked for get built and then thrown away. `s` only depends on the first CALL, so the filter can be applied as soon as `s` is bound.
The fixAdd the predicate to the `WITH pd, site AS s` that follows the first CALL: `WITH pd, site AS s WHERE s.name IN $siteNames`. Remove it from the later Task WHERE. This gives the same results because `s` never changes after that point. The Patient/CarePlan expansion and all nested subqueries then run only for the requested sites. Nothing else in the statement changes.
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) 
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 
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) 
return doc, pv, q } 
return q, null as pa, pv, doc } 
MATCH (task:Task)-[:WITH_CONTEXT]-(q) 
WHERE q.state = $mqState AND q.externalFlag = true 
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)
OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(study:Study{tildaid:$dashStudyId})-[:STUDY_TO_ACTOR]->(studyActor:Actor)-[:FOR_USER]->(requestingUser:User{tildaid:$dashRequestingUserId}) 
WHERE studyActor.state<>? 
WITH task, q, s, act, v, cp, phi, study, head(collect(studyActor)) 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 
order by entry.q.createdat desc 
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 
limit $pgLimit
Expected: How much this helps depends on what fraction of the study's sites are in $siteNames. If dashboards usually ask for a few sites out of many, db hits and elapsed time should drop by roughly that ratio. If $siteNames usually covers every site, there is little gain. The statement ran only once in 7 days (3.8 s), so the absolute saving is small. Treat this as a cheap, low-risk change, not an urgent one. · Evidence: Run PROFILE before and after with the same parameters. Compare the row count leaving the first `WITH pd, site AS s` and entering the Patient/CarePlan Expand, and compare total db hits. Check that both versions return identical result sets. After deploying, compare p95 against the current 4850 ms.
index Make sure the anchor lookups on tildaid use an index or unique constraint
The issueThe statement starts with `MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId})`. It also matches `(study:Study{tildaid:$dashStudyId})` and `(requestingUser:User{tildaid:$dashRequestingUserId})` inside the OPTIONAL MATCH. Without a tildaid lookup on Organization or Study, the planner has to start from a NodeByLabelScan plus a Filter instead of a single NodeUniqueIndexSeek, and everything after it fans out from that start point.
The fixRun SHOW CONSTRAINTS and SHOW INDEXES and check for tildaid on Organization, Study and User. If any is missing, add a uniqueness constraint, which also creates a backing range index. The Organization DDL is below. Use the same form for Study (`study_tildaid`) and User (`user_tildaid`). If tildaid can be duplicated, use `CREATE INDEX ... ON (n.tildaid)` instead. If the constraints already exist, skip this suggestion.
Add this index
CREATE CONSTRAINT organization_tildaid IF NOT EXISTS FOR (n:Organization) REQUIRE n.tildaid IS UNIQUE
Expected: If an index is missing, the anchor goes from a label scan to an index seek. That saves milliseconds to tens of milliseconds per call, depending on how many Organization or Study nodes there are. Most of the 3.8 s is probably spent in the downstream fan-out, so this is a supporting fix, not the main one. If the constraints already exist, nothing changes. · Evidence: Look at the EXPLAIN plan for the leaf operators that bind `org` and `st`. Expect NodeUniqueIndexSeek (or NodeIndexSeek) on tildaid, not NodeByLabelScan followed by Filter. Also confirm with SHOW CONSTRAINTS.

Status: proposed · all suggestions

No rollups for this shape in the window.