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.