Xray
1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 1 hour▾
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 22:51:13.
› →|
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 31360F19 · read · last 1h

Query
MATCH (t:Training) 
WHERE t.tildaid IN $trainingIds AND t.scopeId = $studyId AND t.delpending = false 
OPTIONAL MATCH (t)-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{state:$assignedState, deltimestamp:?}) 
WHERE $viewerOrgId = ? OR (ta.ownerOrgId = $viewerOrgId OR $viewerOrgId IN ta.deliveredOrgIds) 
WITH t, ta, [(ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity{deltimestamp:?}) 
WHERE NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY]->() } | [pa.createdat, pa.status, pa.dueDate]] AS attempts 
WITH t, collect(ta.userId) AS userIds, collect(reduce(latest = [], x IN attempts | CASE WHEN size(latest) = ? OR x[?] > latest[?] THEN x ELSE latest END)) AS taAttempts 
WITH collect({id:t.tildaid, trainees:size(userIds), siteUserIds:CASE WHEN (coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType) THEN [] ELSE userIds END, completed:size([a IN taAttempts 
WHERE size(a) > ? AND a[?] IN $completedStatuses]), overdue:size([a IN taAttempts 
WHERE size(a) > ? AND NOT a[?] IN $completedStatuses AND a[?] > ? AND a[?] < $nowMillis])}) AS page 
CALL (page) { 
UNWIND page AS row 
UNWIND row.siteUserIds AS uid 
WITH DISTINCT uid 
MATCH (u:User{tildaid:uid}) 
OPTIONAL MATCH (cs:Site{delpending:false})-[:PLAN_DEFINITION_TO_SITE]-(cpd:DslPlanDefinition{delpending:false, studyId:$studyId})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(cssa:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_ACTOR]->(ca:Actor{scopeId:$studyId, state:$activeActorState, delpending:false})-[:FOR_USER]->(u:User) 
WHERE ca.orgId = cs.orgId AND NOT EXISTS { (cs)-[:SITE_TO_ORGANIZATION]->(siteOrg:Organization) 
WHERE siteOrg.deltimestamp <> ? } AND ($viewerSiteOrgId = ? OR cs.orgId = $viewerSiteOrgId) 
WITH uid, collect(DISTINCT cs.tildaid) AS siteIds 
RETURN apoc.map.fromPairs(collect([uid, siteIds])) AS sitesByUser } 
UNWIND page AS row 
RETURN row.id, row.trainees, row.completed, row.overdue, size(apoc.coll.toSet(apoc.coll.flatten([uid IN row.siteUserIds | coalesce(sitesByUser[uid], [])])))

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

index Make sure the Training.tildaid lookup is backed by an index or constraint
The issueThe first clause, `MATCH (t:Training) WHERE t.tildaid IN $trainingIds AND t.scopeId = $studyId AND t.delpending = false`, drives the whole query. If Training.tildaid has no index or uniqueness constraint, Neo4j has to scan every Training node (NodeByLabelScan) and then filter on tildaid, scopeId and delpending, so the cost grows with the size of the Training label and not with the size of $trainingIds. That is one likely reason p95 (455 ms) is more than 3x the average (136 ms).
The fixRun SHOW CONSTRAINTS and SHOW INDEXES first. If tildaid is the unique business key for Training, which the name suggests, a uniqueness constraint gives you a seek index and also protects the data. The DDL is below. If duplicates are possible, use `CREATE INDEX training_tildaid IF NOT EXISTS FOR (t:Training) ON (t.tildaid)` instead. scopeId and delpending can then be cheap filters on the few rows the seek returns. If a constraint already exists, this suggestion gives no gain and can be dropped.
Add this index
CREATE CONSTRAINT training_tildaid_unique IF NOT EXISTS FOR (t:Training) REQUIRE t.tildaid IS UNIQUE
Expected: With the index, the first operator changes from a label scan to a NodeUniqueIndexSeek / NodeIndexSeek over the IN list. That should cut this stage from scaling with the Training count to scaling with |$trainingIds|. The whole statement only ran 3 times in 7 days, so the total saving is small. The main benefit is a lower and steadier p95. · Evidence: An EXPLAIN/PROFILE before and after, showing the Training operator change from NodeByLabelScan + Filter to an index seek, with fewer db hits. A p95 for shape 31360F19 after the change.
index Index User.tildaid for the per-trainee lookup in the subquery
The issueInside the CALL subquery, `MATCH (u:User{tildaid:uid})` runs once for each distinct trainee uid across the whole page, which can be hundreds of users. Without an index or constraint on User.tildaid, each of those lookups is a scan of the User label, so the cost is (distinct uids) × (User count). That is the most likely cause of the 455 ms outlier when a page has many site-scoped trainees.
The fixCheck SHOW CONSTRAINTS for User. If tildaid is unique, which is likely because it is used as an identifier, create the uniqueness constraint below so every uid becomes a single seek. If there is already an index or constraint on User.tildaid, skip this.
Add this index
CREATE CONSTRAINT user_tildaid_unique IF NOT EXISTS FOR (u:User) REQUIRE u.tildaid IS UNIQUE
Expected: Each uid lookup should change from a label scan plus filter to a NodeUniqueIndexSeek. The subquery's cost then scales with the number of uids and not with the size of the User label. The improvement is largest on pages with many siteUserIds. · Evidence: PROFILE showing NodeUniqueIndexSeek on User(tildaid) inside the Apply/subquery branch, and a lower db-hit count for that branch. A p95 comparison on calls with large trainee lists.
rewrite Start the site-membership OPTIONAL MATCH from the bound user
The issueThe subquery's `OPTIONAL MATCH (cs:Site{delpending:false})-[:PLAN_DEFINITION_TO_SITE]-(cpd:DslPlanDefinition{...studyId:$studyId})-...-(ca:Actor{scopeId:$studyId,...})-[:FOR_USER]->(u:User)` is a five-hop pattern with filters spread over Site, DslPlanDefinition and Actor. Because it repeats `(u:User)` and offers several labelled, filtered starting points, the planner may begin from DslPlanDefinition(studyId) or Site, expand to every actor in the study, and only then join on u (a NodeHashJoin or a repeated expand for each uid). It would do much less work by starting at u, which is already bound, and walking FOR_USER to that one user's few Actors. The relationship patterns are also undirected, so each hop is checked in both directions.
The fixPut the expansion in a correlated `CALL (u) { ... }` subquery whose first hop is `(u)<-[:FOR_USER]-(ca:Actor)`. This makes u the argument, so the traversal starts from one user's Actors and fans out only from there. The repeated `:User` label is removed. Directions are added where the model implies them (STUDYSITEACTOR_TO_ACTOR and FOR_USER are already directed in the original). The other hops are left undirected, because the original does not state their direction. If you know it, add arrows to halve the expansion checks. Results and placeholders are unchanged.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (t:Training) WHERE t.tildaid IN $trainingIds AND t.scopeId = $studyId AND t.delpending = false OPTIONAL MATCH (t)-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{state:$assignedState, deltimestamp:?}) WHERE $viewerOrgId = ? OR (ta.ownerOrgId = $viewerOrgId OR $viewerOrgId IN ta.deliveredOrgIds) WITH t, ta, [(ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity{deltimestamp:?}) WHERE NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY]->() } | [pa.createdat, pa.status, pa.dueDate]] AS attempts WITH t, collect(ta.userId) AS userIds, collect(reduce(latest = [], x IN attempts | CASE WHEN size(latest) = ? OR x[?] > latest[?] THEN x ELSE latest END)) AS taAttempts WITH collect({id:t.tildaid, trainees:size(userIds), siteUserIds:CASE WHEN (coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType) THEN [] ELSE userIds END, completed:size([a IN taAttempts WHERE size(a) > ? AND a[?] IN $completedStatuses]), overdue:size([a IN taAttempts WHERE size(a) > ? AND NOT a[?] IN $completedStatuses AND a[?] > ? AND a[?] < $nowMillis])}) AS page CALL (page) { UNWIND page AS row UNWIND row.siteUserIds AS uid WITH DISTINCT uid MATCH (u:User{tildaid:uid}) OPTIONAL MATCH (cs:Site{delpending:false})-[:PLAN_DEFINITION_TO_SITE]-(cpd:DslPlanDefinition{delpending:false, studyId:$studyId})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(cssa:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_ACTOR]->(ca:Actor{scopeId:$studyId, state:$activeActorState, delpending:false})-[:FOR_USER]->(u:User) WHERE ca.orgId = cs.orgId AND NOT EXISTS { (cs)-[:SITE_TO_ORGANIZATION]->(siteOrg:Organization) WHERE siteOrg.deltimestamp <> ? } AND ($viewerSiteOrgId = ? OR cs.orgId = $viewerSiteOrgId) WITH uid, collect(DISTINCT cs.tildaid) AS siteIds RETURN apoc.map.fromPairs(collect([uid, siteIds])) AS sitesByUser } UNWIND page AS row RETURN row.id, row.trainees, row.completed, row.overdue, size(apoc.coll.toSet(apoc.coll.flatten([uid IN row.siteUserIds | coalesce(sitesByUser[uid], [])])))
+ MATCH (t:Training) WHERE t.tildaid IN $trainingIds AND t.scopeId = $studyId AND t.delpending = false OPTIONAL MATCH (t)-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{state:$assignedState, deltimestamp:?}) WHERE $viewerOrgId = ? OR (ta.ownerOrgId = $viewerOrgId OR $viewerOrgId IN ta.deliveredOrgIds) WITH t, ta, [(ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity{deltimestamp:?}) WHERE NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY]->() } | [pa.createdat, pa.status, pa.dueDate]] AS attempts WITH t, collect(ta.userId) AS userIds, collect(reduce(latest = [], x IN attempts | CASE WHEN size(latest) = ? OR x[?] > latest[?] THEN x ELSE latest END)) AS taAttempts WITH collect({id:t.tildaid, trainees:size(userIds), siteUserIds:CASE WHEN (coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType) THEN [] ELSE userIds END, completed:size([a IN taAttempts WHERE size(a) > ? AND a[?] IN $completedStatuses]), overdue:size([a IN taAttempts WHERE size(a) > ? AND NOT a[?] IN $completedStatuses AND a[?] > ? AND a[?] < $nowMillis])}) AS page CALL (page) { UNWIND page AS row UNWIND row.siteUserIds AS uid WITH DISTINCT uid MATCH (u:User{tildaid:uid}) CALL (u) { OPTIONAL MATCH (u)<-[:FOR_USER]-(ca:Actor{scopeId:$studyId, state:$activeActorState, delpending:false})<-[:STUDYSITEACTOR_TO_ACTOR]-(cssa:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(cpd:DslPlanDefinition{delpending:false, studyId:$studyId})-[:PLAN_DEFINITION_TO_SITE]-(cs:Site{delpending:false}) WHERE ca.orgId = cs.orgId AND NOT EXISTS { (cs)-[:SITE_TO_ORGANIZATION]->(siteOrg:Organization) WHERE siteOrg.deltimestamp <> ? } AND ($viewerSiteOrgId = ? OR cs.orgId = $viewerSiteOrgId) RETURN collect(DISTINCT cs.tildaid) AS siteIds } WITH uid, siteIds RETURN apoc.map.fromPairs(collect([uid, siteIds])) AS sitesByUser } UNWIND page AS row RETURN row.id, row.trainees, row.completed, row.overdue, size(apoc.coll.toSet(apoc.coll.flatten([uid IN row.siteUserIds | coalesce(sitesByUser[uid], [])])))
Expected: The subquery's work should become proportional to (uids × actors per user × sites per actor) instead of (actors in the study) joined against the uids. This may matter for large studies. Because the shape runs rarely, the gain is mostly in worst-case latency. It needs to be confirmed with PROFILE, because the planner may already choose the u-anchored plan. · Evidence: A PROFILE of the original showing whether the subquery starts at DslPlanDefinition/Site, or contains a hash join on u. Then a PROFILE of the rewrite showing an Argument(u) → Expand(FOR_USER) start, fewer db hits, and identical result rows on the same parameters.

Status: proposed · all suggestions

No rollups for this shape in the window.