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.