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.