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 20:16:24.
› →|
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 7178EFFA · last 14d

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.