Optimizer suggestions — proposals from claude-opus-5-5, 1d ago; suggests only, a human applies
rewrite Apply the site filter inside the UNION branches and drop the inner ORDER BY
The issueThe only site restriction, `toString(site.tildaid) = $cpFilter0`, sits in the WHERE of the second CALL subquery. Both UNION branches therefore return every (pd, site) pair for every study of the org, through Study→DslPlanDefinition→Site and Org→Smo→Site→DslPlanDefinition. Each of those pairs then runs the long User→Actor→StudySiteActor→pd→SOA→CarePlan→Patient→Phi expansion and the CarePlan filters before most rows are dropped for the wrong site. The work grows with the number of sites in the org, even though only one site is wanted, which fits the gap between the 255 ms average and the 1650 ms p95. Separately, `order by cp.createdat desc` inside the second subquery sorts rows that the outer query re-sorts with the same key before collecting, so that inner sort is wasted.
The fixMove `toString(site.tildaid) = $cpFilter0` into the WHERE of both UNION branches, so pd/site pairs for other sites are dropped before the per-row subquery runs, and remove it from the inner subquery. Also remove the inner `order by cp.createdat desc`, since the outer `order by cp.createdat desc` before the collect sets the final order. The results stay the same because `site` is passed into the second subquery unchanged and the filter only uses `site`.
Proposed rewrite
match (org:Organization{tildaid: $orgId})-[:STUDY_TO_ORG]- (st:Study)
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 and toString(site.tildaid) = $cpFilter0
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 and toString(site.tildaid) = $cpFilter0
return pd,site }
call (st,pd,site,org) {
match (u:User {tildaid: $userId})<-[:FOR_USER]- (a:Actor{orgId:$orgId})-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor{delpending:false}) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd)-[:SOA_TO_PLAN_DEFINITION]- (soa:ScheduleOfActivities)-[:EXECUTE_SOA]-(cp:CarePlan{isDeleted:false})-[:RESEARCH_SUBJECT]- (p:Patient)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi)
where a.state <> ? and case when org.orgType=? or $edcEnabledFilter then (cp.subjectid <> ?) else (cp.subjectid <> ? or not a.roles contains ?) end and coalesce(cp.mode, ?) <> ?
with phi,soa,a,case when (ssa)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]-(:StudySiteDuty{delpending:false})-[:DUTY_TO_STUDYSITEDUTY]-(:Duty) then true else false end as hasDuties,cp
with phi,cp,soa,a,hasDuties,null as nv
call(cp){
optional match (cp)-[:ALL_CHANGELOG]-(clog:ChangeLog)
where clog.type in [?,?,?]
with clog
order by clog.createdat desc
return collect(clog) as cpLogs }
return phi,cp,cpLogs,nv,hasDuties,soa, a.roles as roles }
with st,phi,hasDuties,roles,cp,cpLogs,site,pd,nv,soa
order by cp.createdat desc
with st,phi,hasDuties,roles,collect({cp:cp,nv:nv,site:site,siteNumber:pd.siteNumber,soav:soa.siteVersion,cpLogs:cpLogs}) as cpList
with st,phi,cpList,head(cpList) as ongoingCP,hasDuties,roles,size(cpList)-? as rescreenCount
with st,phi,cpList,ongoingCP,hasDuties,roles,rescreenCount
order by ongoingCP.cp.createdat desc
with collect({st:st,phi:phi,ongoingCP:case when false then cpList else ongoingCP end,roles:roles,rescreenCount:rescreenCount,hasDuties:hasDuties}) as patient
with patient,size(patient) as pCount
unwind patient as p
with pCount,p
limit $qpLimit
return pCount,p
Expected: The second subquery should run once per (study, matching site) instead of once per (study, any site). For orgs with many sites this could cut db hits and p95 by roughly the site fan-out (several times or more). For single-site orgs, little change. · Evidence: Run PROFILE before and after with the same p95-causing $orgId/$userId/$cpFilter0. Compare rows out of the UNION Apply and total db hits, and check the result sets are identical. Then watch p95 for the shape after deploy.
index Index Site.tildaid and compare it without toString()
The issueEven after moving it, `toString(site.tildaid) = $cpFilter0` wraps the property in a function. That rules out any index seek on Site, so the planner can only reach the site by expanding from Organization/Study and filtering each candidate. With the site id known up front, the plan could instead start from both ends: Organization by tildaid and Site by tildaid.
The fixIf Site.tildaid is stored as a string, which is likely given the toString/param comparison, change the predicate to `site.tildaid = $cpFilter0` and add a lookup index (or a uniqueness constraint if tildaid is a key). The planner can then seek the single Site and join it to the org/study side, or prune with a cheap hash join.
Add this index
CREATE INDEX site_tildaid IF NOT EXISTS FOR (s:Site) ON (s.tildaid)
Expected: With the bare-property predicate, the plan may switch to a NodeIndexSeek on Site, cutting the pd/site expansion to a handful of rows. This is a modest extra gain on top of the rewrite, and it depends on whether the planner picks the seek. · Evidence: First confirm the property type with `MATCH (s:Site) RETURN DISTINCT valueType(s.tildaid)`, since dropping toString is only safe if the values are strings. Then check that EXPLAIN shows NodeIndexSeek on site_tildaid and that PROFILE db hits drop.
index Make sure the User and Actor anchors are indexed
The issueIn the second subquery, `(u:User {tildaid: $userId})<-[:FOR_USER]-(a:Actor{orgId:$orgId})` is a good anchor because it is fixed per call. Without an index on User.tildaid the planner may instead expand from the bound `pd` through SOA→CarePlan→Patient for every row, which is far more expensive. The same can happen if it picks Actor and has no lookup on Actor.orgId.
The fixMake sure User.tildaid has a uniqueness constraint or index so the subquery can seek the user and expand to its few Actors before going to StudySiteActor→pd. Optionally add an Actor(orgId) index so the planner can filter actors cheaply.
Add this index
CREATE CONSTRAINT user_tildaid_unique IF NOT EXISTS FOR (u:User) REQUIRE u.tildaid IS UNIQUE
Expected: If no index exists today, the subquery would anchor on a single User node instead of scanning or expanding broadly. That could remove a large part of the per-row cost. If the constraint already exists, nothing changes. · Evidence: Run SHOW INDEXES and SHOW CONSTRAINTS to see whether User.tildaid and Actor.orgId are already covered. Check PROFILE for NodeByLabelScan on User/Actor, or for the subquery anchoring on pd/cp instead of User.
Status: proposed · all suggestions
No rollups for this shape in the window.