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 23:29:00.
› →|
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 19371E20 · write · last 14d

Query
MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) 
WHERE u.status<>? and a.state<>? 
MATCH (a)-[:APPOINTED_AS]->(r:Role) 
WHERE r.name <> ? 
MATCH (filterSite:Site{orgId:a.orgId}) 
WHERE filterSite.tildaid in $filterSiteIds AND EXISTS { 
MATCH (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(spd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(filterSite), (spd)-[:PLAN_DEFINITION_TO_STUDY]-(o) } 
optional match (co:ClinOp{deltimestamp:?, orgId:$orgId})-[:FOR_USER]-(u) 
with distinct o,a,r,u,scope,co 
where ( toString(u.status) = $actorFilter0 ) 
with o,a,u,scope,r,co 
order by r.displayName 
with o,a,u,scope,co,collect(distinct case when r.listed = true then r.displayName end) as displayRoles 
with o,a,u,scope,displayRoles,co 
order by a.createdat, a.tildaid 
limit $qpLimit 
RETURN apoc.
create.vNode([?], { id: a.tildaid, role: a.roles, displayRoles: CASE WHEN size(displayRoles) = ? THEN [?] ELSE displayRoles END, state: a.state, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, userEmail: case when coalesce(co.actorEmail,?)<>? then co.actorEmail else u.email end, userId: u.tildaid, userName: case when coalesce(co.actorName,?)<>? then co.actorName else coalesce(u.name, u.email) end, userContact: case when coalesce(co.actorPhone,?)<>? then co.actorPhone else coalesce(u.contact,?) end, lastActivity: u.lastActivity, profilePicURL: u.profilePicURL, userTitle: u.title, createdat: scope.createdat, isBlinded: a.isBlinded , capabilities: scope.capabilities, applyToStudyRoleFlag: scope.applyToStudyRoleFlag }) as node

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

rewrite Fold the Site match into EXISTS and apply the status filter in the first WHERE
The issue`MATCH (filterSite:Site{orgId:a.orgId}) WHERE filterSite.tildaid in $filterSiteIds AND EXISTS {...}` makes filterSite a row variable, and it is never returned. Each (a, r) row is multiplied by every matching site. The costly EXISTS path traversal (StudySiteActor → DslPlanDefinition → Site/Study) then runs once per (a, r, site) combination, and `WITH DISTINCT` has to collapse the copies again later. The filter `toString(u.status) = $actorFilter0` also runs only after the Role expansion, the per-site EXISTS and the ClinOp OPTIONAL MATCH, so all that work is done for users who get thrown away.
The fixTurn the Site lookup into a pure existence check on `a`, so it runs once per actor and does not multiply rows. Move the u.status equality into the first WHERE so rejected users are dropped before the Role expansion and the subquery. The output stays the same: filterSite was only used as a filter and was removed by DISTINCT, and the status predicate only reads u, which is bound at the start.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? MATCH (filterSite:Site{orgId:a.orgId}) WHERE filterSite.tildaid in $filterSiteIds AND EXISTS { MATCH (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(spd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(filterSite), (spd)-[:PLAN_DEFINITION_TO_STUDY]-(o) } optional match (co:ClinOp{deltimestamp:?, orgId:$orgId})-[:FOR_USER]-(u) with distinct o,a,r,u,scope,co where ( toString(u.status) = $actorFilter0 ) with o,a,u,scope,r,co order by r.displayName with o,a,u,scope,co,collect(distinct case when r.listed = true then r.displayName end) as displayRoles with o,a,u,scope,displayRoles,co order by a.createdat, a.tildaid limit $qpLimit RETURN apoc.create.vNode([?], { id: a.tildaid, role: a.roles, displayRoles: CASE WHEN size(displayRoles) = ? THEN [?] ELSE displayRoles END, state: a.state, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, userEmail: case when coalesce(co.actorEmail,?)<>? then co.actorEmail else u.email end, userId: u.tildaid, userName: case when coalesce(co.actorName,?)<>? then co.actorName else coalesce(u.name, u.email) end, userContact: case when coalesce(co.actorPhone,?)<>? then co.actorPhone else coalesce(u.contact,?) end, lastActivity: u.lastActivity, profilePicURL: u.profilePicURL, userTitle: u.title, createdat: scope.createdat, isBlinded: a.isBlinded , capabilities: scope.capabilities, applyToStudyRoleFlag: scope.applyToStudyRoleFlag }) as node
+ MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? AND ( toString(u.status) = $actorFilter0 ) AND EXISTS { MATCH (filterSite:Site) WHERE filterSite.tildaid IN $filterSiteIds AND filterSite.orgId = a.orgId MATCH (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(spd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(filterSite), (spd)-[:PLAN_DEFINITION_TO_STUDY]-(o) } MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? optional match (co:ClinOp{deltimestamp:?, orgId:$orgId})-[:FOR_USER]-(u) with distinct o,a,r,u,scope,co with o,a,u,scope,r,co order by r.displayName with o,a,u,scope,co,collect(distinct case when r.listed = true then r.displayName end) as displayRoles with o,a,u,scope,displayRoles,co order by a.createdat, a.tildaid limit $qpLimit RETURN apoc.create.vNode([?], { id: a.tildaid, role: a.roles, displayRoles: CASE WHEN size(displayRoles) = ? THEN [?] ELSE displayRoles END, state: a.state, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, userEmail: case when coalesce(co.actorEmail,?)<>? then co.actorEmail else u.email end, userId: u.tildaid, userName: case when coalesce(co.actorName,?)<>? then co.actorName else coalesce(u.name, u.email) end, userContact: case when coalesce(co.actorPhone,?)<>? then co.actorPhone else coalesce(u.contact,?) end, lastActivity: u.lastActivity, profilePicURL: u.profilePicURL, userTitle: u.title, createdat: scope.createdat, isBlinded: a.isBlinded , capabilities: scope.capabilities, applyToStudyRoleFlag: scope.applyToStudyRoleFlag }) as node
Expected: Fewer rows going into the Role expansion, EXISTS and OPTIONAL MATCH, and the subquery runs once per actor instead of once per actor × role × site. This could plausibly cut the ~660 ms by a large fraction for studies with many actors and several roles per actor. However, the statement ran only once in 7 days, so the total time saved is small. This is mainly a latency fix for the one screen that uses it. · Evidence: Run PROFILE on both versions with the same $scopeId, $filterSiteIds and $actorFilter0. Compare total db hits and the row counts going into the SemiApply/Apply for the EXISTS. Confirm both return the same result set on a few real studies.
index Index Site.tildaid so the $filterSiteIds lookup is a seek
The issue`filterSite.tildaid IN $filterSiteIds` (plus `orgId = a.orgId`) is the only way into :Site. If no index or constraint covers Site.tildaid, the planner falls back to a NodeByLabelScan on :Site with a property filter. That scan repeats for every actor row, or gets turned into a large hash join.
The fixCheck SHOW INDEXES / SHOW CONSTRAINTS first. If Site.tildaid is not already covered (for example by a uniqueness constraint), add a range index so the IN-list becomes a NodeIndexSeek returning only the few listed sites. The orgId equality is then checked on those few nodes.
Add this index
CREATE INDEX site_tildaid IF NOT EXISTS FOR (s:Site) ON (s.tildaid)
Expected: If a Site label scan is happening now, the Site access drops from O(#Sites) per probe to O(|$filterSiteIds|), probably tens of ms or more on large site sets. If a constraint already covers tildaid, there is no change and this can be skipped. · Evidence: EXPLAIN before and after: the Site operator should change from NodeByLabelScan + Filter to NodeIndexSeek(site_tildaid). Re-measure the statement's p95 once it has run a few times.

Status: proposed · all suggestions

No rollups for this shape in the window.