Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 3 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 16:44:38.
› →|
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 8F35727E · write · last 3d

Query
MATCH (ctx:Study)<-[:PLAN_CONTEXT]-(ep:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event) 
WHERE ctx.tildaid IN $contextIds 
WITH DISTINCT e 
OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(statusScope:EventScope) 
WITH e, statusScope 
WHERE (statusScope IS NULL AND true) OR statusScope.status in $scopeStatuses 
WITH DISTINCT e 
WHERE e.isTemplate = $isTemplate 
WITH DISTINCT e 
ORDER BY e.createdat DESC 
SKIP $
skip 
LIMIT $
limit 
OPTIONAL MATCH (e)<-[:EVENTS]-(ep:EventPlan)-[:PLAN_CONTEXT]->(study) 
WITH e, study 
CALL { 
WITH e, study // Get details for each scope in the event 
OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(scope:EventScope) 
WHERE (scope IS NULL AND e.status = $status) OR scope.status in $scopeStatuses 
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 } 
CALL { // Collect summary for all deliverable in the event 
WITH e 
OPTIONAL MATCH (e)-[:REQUIRES_RESOLUTION_OF]->(eventDel:Deliverable) 
WITH collect(eventDel) as deliverables, [d IN collect(distinct eventDel) 
WHERE d.warning IS NOT NULL AND d.warning <> ? | ??\? + d.warning] as deliverableWarnings 
RETURN { trialDeliverables: SIZE([d in deliverables 
WHERE d.applyToTrial = true]), countryDeliverables: SIZE([d in deliverables 
WHERE d.applyToCountries = true]), siteDeliverables: SIZE([d in deliverables 
WHERE d.applyToSites = true]) } as deliverableCounts, deliverableWarnings } 
CALL { // Fetch createdBy 
WITH e 
OPTIONAL MATCH (e)-[:ALL_CHANGELOG]->(cl:ChangeLog{operation:?}) 
WITH cl 
ORDER BY cl.createdat ASC 
limit ? 
OPTIONAL MATCH (u:User{tildaid:cl.changedBy}) 
return {id: u.tildaid, name: u.name, profilePicURL: u.profilePicURL} as createdBy } // Aggregate all scopes and all deliverables in the event 
WITH e, study, createdBy, deliverableCounts, deliverableWarnings, collect(scope) as eventScopes, { total: sum(scopeStats.total), pending: sum(scopeStats.pending), pendingForDelete: sum(scopeStats.pendingForDelete), accepted: sum(scopeStats.accepted), rejected: sum(scopeStats.rejected), incomplete: sum(scopeStats.incomplete), potentialMatchFound: sum(scopeStats.potentialMatchFound), matchExpected: sum(scopeStats.matchExpected), matched: sum(scopeStats.matched), finalized: sum(scopeStats.finalized), completion: {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 eventProgress 
WITH e, study, createdBy, deliverableCounts, deliverableWarnings, eventProgress, eventScopes, { totalCountries: SIZE([s IN eventScopes 
WHERE s.scopeType = ?]), totalSites: SIZE([s IN eventScopes 
WHERE s.scopeType = ?]), countries: [s IN eventScopes 
WHERE s.scopeType = ? | {name: s.scopeId, id: s.scopeId}], sites: [s IN eventScopes 
WHERE s.scopeType = ? | {name: s.siteName, id: s.scopeId, siteNumber: s.siteNumber}] } as scopeSummary 
CALL { 
WITH e 
OPTIONAL MATCH (woc:WorkOrderConfig)-[:WORK_ORDER_CONFIG_TO_EVENT]->(e) 
RETURN woc.tildaid as workOrderConfigId 
LIMIT ? } 
RETURN e{.*, createdBy: createdBy, warnings: deliverableWarnings, scopeSummary: scopeSummary, progress: eventProgress, workOrderConfigId: workOrderConfigId, contextId: COALESCE(study.tildaid, ?), contextType: CASE WHEN study IS NULL THEN ? ELSE head(labels(study)) END, contextName: COALESCE(study.name, ?), aiState: CASE WHEN SIZE(eventScopes) = ? THEN ? WHEN SIZE([s IN eventScopes 
WHERE s.aiState <> ? AND s.aiState IS NOT NULL]) = ? THEN ? ELSE ? END, trialDeliverables: deliverableCounts.trialDeliverables, countryDeliverables: deliverableCounts.countryDeliverables, siteDeliverables: deliverableCounts.siteDeliverables} as result

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

index Index the per-scope Site lookup on tildaid (skip if a constraint already exists)
The issueInside the per-event CALL subquery, `OPTIONAL MATCH (site:Site {tildaid: scope.scopeId})` runs once for every EventScope row the page returns, and it doesn't come off an expansion. If there's no index or uniqueness constraint on Site.tildaid, every one of those rows does a NodeByLabelScan over all Site nodes and filters on tildaid. With up to $limit events × N scopes per event, that repeated scan is the most likely reason for the 950 ms p95 against a 264 ms average.
The fixGive Site.tildaid an index, so the planner can do a NodeIndexSeek (or NodeUniqueIndexSeek) for each scope row. tildaid looks like a global identifier, so a uniqueness constraint (which also creates the index) is the better choice. If the data has duplicates, use a plain range index instead: CREATE INDEX site_tildaid IF NOT EXISTS FOR (s:Site) ON (s.tildaid). Run SHOW CONSTRAINTS / SHOW INDEXES first. If one already covers Site.tildaid, there's nothing to do here.
Add this index
CREATE CONSTRAINT site_tildaid_unique IF NOT EXISTS FOR (s:Site) REQUIRE s.tildaid IS UNIQUE
Expected: If the plan currently scans Site, the per-scope cost should drop from O(|Site|) to about O(1). That could take p95 down substantially, roughly in line with how many scopes are on a page. The shape only ran twice in 7 days (528 ms in total), so the total time saved is small. The real gain is lower latency for the user who loads the page. · Evidence: PROFILE the statement with real parameters. Before the change, look for NodeByLabelScan(Site) + Filter(tildaid) under the Apply. After it, look for NodeIndexSeek/NodeUniqueIndexSeek on Site(tildaid) with far fewer db hits. Then compare p95 over the next run window.
index Anchor the entry point with a Study.tildaid lookup
The issueThe first clause, `MATCH (ctx:Study)<-[:PLAN_CONTEXT]-(ep:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event) WHERE ctx.tildaid IN $contextIds`, starts from Study nodes filtered by tildaid IN a list. Without an index on Study.tildaid, the planner has to scan every Study (or every EventPlan) to find the starting nodes before it expands to events.
The fixAdd an index (preferably a uniqueness constraint) on Study.tildaid. Then the IN list becomes a multi-value NodeIndexSeek and the expansion starts from only the requested studies. Check SHOW CONSTRAINTS first in case one already exists.
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: The entry point should become a seek over |$contextIds| nodes instead of a full label scan. That's probably a modest win per call, and only if no index exists today. · Evidence: EXPLAIN/PROFILE should show NodeIndexSeek on Study(tildaid) as the leaf of the first MATCH instead of NodeByLabelScan(Study) or a scan starting from EventPlan.
index Index EventPlan.orgId as an alternative anchor or join filter
The issue`(ep:EventPlan {orgId: $orgId})` filters on orgId, which isn't the node key. If the planner decides to start from EventPlan (for example when $contextIds is large or Study has no index), it has to scan every EventPlan and filter on orgId. orgId is also a tenant filter that probably appears in other docsvc queries.
The fixAdd a range index on EventPlan.orgId. Then the planner can seek EventPlans for the tenant and hash-join them with the Study seek, or use whichever side is more selective. Only orgId is indexed because it's the only EventPlan property this statement filters on.
Add this index
CREATE INDEX eventplan_orgid IF NOT EXISTS FOR (ep:EventPlan) ON (ep.orgId)
Expected: This gives the planner a cheap anchor on the EventPlan side. I'd expect a small to moderate improvement in the first MATCH, depending on how many EventPlans each org has. Other queries that filter EventPlan by orgId may also benefit. · Evidence: PROFILE should show NodeIndexSeek on EventPlan(orgId), or a NodeHashJoin between the Study and EventPlan seeks, with lower db hits on the first MATCH. Also run SHOW INDEXES to confirm the index is ONLINE and see its readCount go up.

Status: proposed · all suggestions

No rollups for this shape in the window.