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:15:02.
› →|
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 0C68BC89 · last 1h

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

index Index User.tildaid for the per-row assignee lookup
The issue`optional match (user:User{tildaid:q.assignTo})` runs once for every row coming out of the nested UNION subqueries and the Task join. That can be thousands of rows. The value comes from another node (q.assignTo), so without a lookup index on User.tildaid every row needs a NodeByLabelScan of User plus a filter. That is a full label scan repeated once per row.
The fixAdd a lookup index on User.tildaid so the planner can use NodeIndexSeek for each row. If tildaid is meant to be unique per user, a uniqueness constraint (CREATE CONSTRAINT user_tildaid IF NOT EXISTS FOR (u:User) REQUIRE u.tildaid IS UNIQUE) gives the same seek and also enforces the rule. Skip this if such an index or constraint already exists.
Add this index
CREATE INDEX user_tildaid IF NOT EXISTS FOR (u:User) ON (u.tildaid)
Expected: If no index exists today, the User part of the plan should go from a scan per row to a seek per row. That could remove a large share of the ~3.9 s, depending on the User count and the row count. This statement ran only once in 7 days, so the total saving is small unless the same lookup appears in other shapes. · Evidence: Run SHOW INDEXES WHERE 'User' IN labelsOrTypes to check whether an index exists. Then run PROFILE before and after: the operator for `user` should change from NodeByLabelScan + Filter to NodeIndexSeek, and db hits on that branch should drop.
rewrite Turn the Task join into an existence check and deduplicate before the OPTIONAL MATCHes
The issue`MATCH (task:Task)-[:WITH_CONTEXT]-(q)` binds `task`, but `task` is never used. Its only effect is to multiply each q row by the number of Tasks linked to it. The rows also still carry pd, pa and doc, which create duplicates of (s, q, pv, cp). Both OPTIONAL MATCHes (User, Visit) then run on every duplicate row, only for collect(DISTINCT ...) to throw the duplicates away at the end.
The fixCollapse the rows to WITH DISTINCT s, q, pv, cp, the only variables the RETURN uses. Apply the q.state filter there, and replace the Task MATCH with an EXISTS subquery. EXISTS stops at the first Task it finds and does not add rows. Every returned collect is DISTINCT and depends only on s, q, pv, cp, user and v, so the result stays the same.
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 
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 } 
WITH DISTINCT s, q, pv, cp 
WHERE q.state <> ? AND EXISTS { 
MATCH (:Task)-[:WITH_CONTEXT]-(q) } 
optional match (user:User{tildaid:q.assignTo}) 
optional match (pv)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) 
return collect(distinct s.name) as siteNames, collect(distinct q.who) as whos, collect(distinct user{.tildaid, .name}) as assignToUsers, collect(distinct q.context) as contexts, collect(distinct v.title) as visitTitles, collect(distinct cp.subjectid) as subjectIds
Expected: Fewer rows should reach the two OPTIONAL MATCHes and the aggregation. The drop is roughly the average number of Tasks per Query multiplied by the pa/doc fan-out. That could cut the tail of the plan noticeably, but the size of the gain depends on the data. · Evidence: PROFILE both versions and compare the row counts going into the OptionalExpand/Apply for `user` and `v`, plus total db hits. Also check that all six returned collections are identical on the same $dashOrgId/$dashStudyId.
index Make sure the Study anchor lookup is indexed
The issueThe whole query starts from `MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId})`. If neither Organization.tildaid nor Study.tildaid has an index, the planner has to label-scan one side before it can expand. Every later branch is driven from these two nodes, so a slow start affects the whole query.
The fixAdd a lookup index (or a uniqueness constraint, if tildaid is a key) on Study.tildaid. Do the same for Organization.tildaid if SHOW INDEXES shows it is missing. The planner can then seek both endpoints and join them with an Expand(Into).
Add this index
CREATE INDEX study_tildaid IF NOT EXISTS FOR (s:Study) ON (s.tildaid)
Expected: This only matters if the index is missing. If so, the scan at the start becomes a single seek, saving roughly the cost of one scan over the Study or Organization label. That is probably small next to the fan-out further down. If the index already exists, this is a no-op. · Evidence: Run SHOW INDEXES and filter for the Study and Organization labels. Then check that the leaf operators in the EXPLAIN plan are NodeIndexSeek (or NodeUniqueIndexSeek), not NodeByLabelScan.

Status: proposed · all suggestions

No rollups for this shape in the window.