Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 7 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 19:12:48.
› →|
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 481203DA · read · last 7d

Query
MATCH (study:Study)<-[:PLAN_CONTEXT]-(:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event) 
WHERE coalesce(e.isTemplate, false) = false AND study.tildaid in $studyIds 
WITH DISTINCT e, study 
CALL { 
WITH e, study // Get details for each scope in the event 
OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(scope:EventScope) 
WHERE scope.status IN $scopeStatuses AND ($scopeType = ? OR scope.scopeType = $scopeType) 
OPTIONAL MATCH (site:Site {tildaid: scope.scopeId}) 
OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition{type:?})-[:PLAN_DEFINITION_TO_STUDY]->(study) 
WITH scope, CASE WHEN scope.scopeType = ? THEN site.name ELSE ? END as siteName, CASE WHEN scope.scopeType = ? THEN COALESCE(pd.siteNumber, ?) ELSE ? END as siteNumber 
OPTIONAL MATCH (scope)-[:APPLICABLE_TO]->(scopeDeliverable:Deliverable) 
CALL { // Get details of each deliverable applicable to the scope 
WITH scope, scopeDeliverable 
OPTIONAL MATCH (scopeDeliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document)-[:SCOPED_TO]->(scope) 
CALL { // Get 
match status for each placeholder 
WITH ph, tracksRel, scope 
OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) 
OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) 
WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid 
WITH ph, tracksRel, scope, acceptedDoc, [m IN collect(matchDoc) 
WHERE m IS NOT NULL] as matchDocs 
WITH ph, tracksRel, scope, acceptedDoc, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchDocs) > ? AND all(m IN matchDocs 
WHERE m.collection = ?) THEN ? WHEN SIZE(matchDocs) > ? THEN ? ELSE ? END as matchStatus 
RETURN CASE WHEN ph IS NULL THEN null ELSE {tildaid: ph.tildaid, fileName: ph.fileName, status: matchStatus, placeholderState: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR scope.status = ?} END as deliverablePlaceholder } 
WITH collect(DISTINCT deliverablePlaceholder) as deliverablePlaceholders 
WITH [d IN deliverablePlaceholders 
WHERE d IS NOT NULL] as allPlaceholders 
RETURN allPlaceholders as deliverablePlaceholders, { total: size(allPlaceholders), pending: size([pp IN allPlaceholders 
WHERE pp.placeholderState IN [?, ?]]), pendingForDelete: size([pp IN allPlaceholders 
WHERE pp.placeholderState = ?]), accepted: size([pp IN allPlaceholders 
WHERE pp.placeholderState = ?]), rejected: size([pp IN allPlaceholders 
WHERE pp.placeholderState = ?]), incomplete: size([pp IN allPlaceholders 
WHERE pp.status = ?]), potentialMatchFound: size([pp IN allPlaceholders 
WHERE pp.status = ?]), matchExpected: size([pp IN allPlaceholders 
WHERE pp.status = ?]), matched: size([pp IN allPlaceholders 
WHERE pp.status = ?]), finalized: size([pp IN allPlaceholders 
WHERE pp.status = ?]), completion: {required: size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]), completed: size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]), aiSuggested: size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), inStaging: size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), noMatchFound: size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), notConfirmed: size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND pp.placeholderState = ? AND NOT pp.status = ?]), removed: size([pp IN allPlaceholders 
WHERE (pp.placeholderState = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) / size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]))) END, isComplete: size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) > ? AND size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) = size([pp IN allPlaceholders 
WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)])} } as deliverableStats } // Aggregate all deliverables in the scope 
WITH scope, siteName, siteNumber, deliverableStats 
WITH distinct scope, siteName, siteNumber, { total: sum(deliverableStats.total), pending: sum(deliverableStats.pending), pendingForDelete: sum(deliverableStats.pendingForDelete), accepted: sum(deliverableStats.accepted), rejected: sum(deliverableStats.rejected), incomplete: sum(deliverableStats.incomplete), potentialMatchFound: sum(deliverableStats.potentialMatchFound), matchExpected: sum(deliverableStats.matchExpected), matched: sum(deliverableStats.matched), finalized: sum(deliverableStats.finalized), completion: {required: sum(deliverableStats.completion.required), completed: sum(deliverableStats.completion.completed), aiSuggested: sum(deliverableStats.completion.aiSuggested), inStaging: sum(deliverableStats.completion.inStaging), noMatchFound: sum(deliverableStats.completion.noMatchFound), notConfirmed: sum(deliverableStats.completion.notConfirmed), removed: sum(deliverableStats.completion.removed), percentComplete: CASE WHEN sum(deliverableStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(deliverableStats.completion.completed) / sum(deliverableStats.completion.required))) END, isComplete: sum(deliverableStats.completion.required) > ? AND sum(deliverableStats.completion.completed) = sum(deliverableStats.completion.required)} } as scopeStats 
RETURN distinct scope{.*, siteName: siteName, siteNumber: siteNumber} as scope, scopeStats } 
WITH study, e, scope, scopeStats 
WHERE scope IS NOT NULL OR ($keepScopeless AND ($status = ? OR e.status = $status) AND NOT EXISTS { 
MATCH (e)<-[:PART_OF_EVENT]-(:EventScope) }) 
WITH study, e, scope.scopeId AS siteId, ? AS eventCount, coalesce(head(collect(scope.siteName)), ?) AS siteName, coalesce(head(collect(scope.siteNumber)), ?) AS siteNumber, {required: sum(scopeStats.completion.required), completed: sum(scopeStats.completion.completed), aiSuggested: sum(scopeStats.completion.aiSuggested), inStaging: sum(scopeStats.completion.inStaging), noMatchFound: sum(scopeStats.completion.noMatchFound), notConfirmed: sum(scopeStats.completion.notConfirmed), removed: sum(scopeStats.completion.removed), percentComplete: CASE WHEN sum(scopeStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(scopeStats.completion.completed) / sum(scopeStats.completion.required))) END, isComplete: sum(scopeStats.completion.required) > ? AND sum(scopeStats.completion.completed) = sum(scopeStats.completion.required)} AS completion 
WITH {studyId: study.tildaid, studyName: coalesce(study.name, ?), completion: completion, eventId: e.tildaid, eventName: e.name, eventStatus: coalesce(e.status, ?), siteId: siteId, siteName: siteName, siteNumber: siteNumber, eventCount: eventCount} AS row 
WHERE ($minPercent IS NULL OR (row.completion.percentComplete IS NOT NULL AND row.completion.percentComplete >= $minPercent)) AND ($maxPercent IS NULL OR (row.completion.percentComplete IS NOT NULL AND row.completion.percentComplete <= $maxPercent)) AND ($isComplete IS NULL OR row.completion.isComplete = $isComplete) 
RETURN collect(row) AS rows, {required: sum(row.completion.required), completed: sum(row.completion.completed), aiSuggested: sum(row.completion.aiSuggested), inStaging: sum(row.completion.inStaging), noMatchFound: sum(row.completion.noMatchFound), notConfirmed: sum(row.completion.notConfirmed), removed: sum(row.completion.removed), percentComplete: CASE WHEN sum(row.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(row.completion.completed) / sum(row.completion.required))) END, isComplete: sum(row.completion.required) > ? AND sum(row.completion.completed) = sum(row.completion.required)} AS completion

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

index Index Site.tildaid for the per-scope site lookup
The issue`OPTIONAL MATCH (site:Site {tildaid: scope.scopeId})` runs once for every EventScope row, inside the per-event CALL. The value comes from a variable, so if Site.tildaid has no index or uniqueness constraint, the planner has to do a NodeByLabelScan on Site plus a filter for each scope. That cost grows as scopes × sites, and it is the most likely reason a 2-call read takes 700–950 ms.
The fixRun SHOW INDEXES / SHOW CONSTRAINTS first. If Site.tildaid is not already covered, add a lookup index. A uniqueness constraint is better if tildaid is a real key. Either one turns each per-scope lookup into a NodeIndexSeek.
Add this index
CREATE INDEX site_tildaid IF NOT EXISTS FOR (s:Site) ON (s.tildaid)
Expected: If the scan is happening now, each per-scope lookup should go from scanning the whole Site label to a single seek. p95 could plausibly drop by a large fraction, maybe from about 950 ms into the low hundreds of ms. If an index already exists, nothing changes. The statement only ran twice in 7 days, so the total saving is about 1 s per week. This matters mainly if the call is on a user-facing page. · Evidence: Check SHOW INDEXES for Site(tildaid). Then run PROFILE on the statement before and after: the Site lookup should change from NodeByLabelScan + Filter to NodeIndexSeek, with far fewer db hits on that operator. Compare p95 on the next few runs.
index Index EventPlan.orgId to anchor the leading MATCH
The issueThe leading `MATCH (study:Study)<-[:PLAN_CONTEXT]-(:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event)` filters EventPlan by orgId. If no index covers orgId, the planner can only anchor on Study. If Study.tildaid is not indexed either, it falls back to a label scan of EventPlan or Study before the expansion to all Events.
The fixAdd a lookup index on EventPlan.orgId. The planner can then seek the org's plans directly, or choose between that and the Study.tildaid seek based on selectivity. This pattern is typical of multi-tenant schemas, so other docsvc queries filtering on orgId may benefit too.
Add this index
CREATE INDEX eventplan_orgid IF NOT EXISTS FOR (p:EventPlan) ON (p.orgId)
Expected: The leading MATCH should stop starting from a label scan. The gain depends on how many EventPlans there are in total versus per org. It is probably modest compared with the per-scope work, so this is a secondary win. · Evidence: Check SHOW INDEXES for EventPlan(orgId). In the PROFILE output, the first operator should be a NodeIndexSeek on EventPlan or Study, not NodeByLabelScan. Compare the db hits on the leading operators before and after.
index Make sure Study.tildaid IN $studyIds can seek
The issue`WHERE study.tildaid in $studyIds` is the most selective predicate in the query, since it is an explicit list of ids. It can only drive the plan if there is an index or constraint on Study.tildaid. Without one, Study is filtered after the expansion, so every Event in the org's plans gets expanded, and the per-event CALL subquery runs on rows that are then thrown away.
The fixIf tildaid is the Study key and has no uniqueness constraint, add one, or at least a range index. The IN-list then becomes a NodeIndexSeek over just the requested ids, and the plan expands from those studies through PLAN_CONTEXT.
Add this index
CREATE INDEX study_tildaid IF NOT EXISTS FOR (s:Study) ON (s.tildaid)
Expected: Fewer Event rows go into the expensive per-event CALL whenever the org has many studies but only a few are requested. Expect no change if a constraint already exists. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES for Study(tildaid). PROFILE should show NodeIndexSeek(study) with rows equal to size($studyIds), and fewer rows going into the Apply/CALL operator.

Status: proposed · all suggestions

No rollups for this shape in the window.