Xray
hourly rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 48 hours▾
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 17:00:54.
› →|
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 559B87B9 · read · last 48h

Query
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) 
MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PART_OF_EVENT]->(e) 
OPTIONAL MATCH (wo:WorkOrder)-[woClaim:WO_CLAIMS_PLACEHOLDER]->(ph) 
WHERE woClaim.is_current = true 
WITH e, ph, d, es, tracksRel, head(collect(wo)) AS wo 
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 e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND matchDoc.collection = ? THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) 
WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as matchesExpectedCollected, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND (matchDoc.collection IS NULL OR matchDoc.collection <> ?) THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) 
WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as pendingMatchesCollected 
WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, [m IN matchesExpectedCollected 
WHERE m IS NOT NULL] as matchesExpected, [m IN pendingMatchesCollected 
WHERE m IS NOT NULL] as pendingMatches 
WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchesExpected) > ? AND SIZE(pendingMatches) = ? THEN ? WHEN SIZE(matchesExpected) > ? OR SIZE(pendingMatches) > ? THEN ? ELSE ? END as matchStatus 
OPTIONAL MATCH (ph)-[renewalRel:RENEWAL_OF]->(renewalSrcCandidate:Document) 
WHERE coalesce(renewalRel.primary, false) = true AND coalesce(renewalSrcCandidate.archived, false) = false 
WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, head(collect(renewalSrcCandidate)) AS renewalSrc 
OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study) 
OPTIONAL MATCH (site:Site {tildaid: es.scopeId}) 
OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {type: ?})-[:PLAN_DEFINITION_TO_STUDY]->(study) 
WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, renewalSrc, COALESCE(head(collect(site.name)), ?) AS siteName, COALESCE(head(collect(pd.siteNumber)), ?) AS siteNumber 
ORDER BY CASE es.scopeType WHEN ? THEN ? WHEN ? THEN ? ELSE ? END, es.name, d.name ASC, ph.fileName ASC 
WITH es, collect({ placeholderId: ph.tildaid, placeholderName: ph.fileName, deliverableId: d.tildaid, deliverableName: d.name, eventScopeId: es.tildaid, scopeType: es.scopeType, country: CASE WHEN es.scopeType = ? THEN COALESCE(es.scopeId, ?) ELSE ? END, siteNumber: siteNumber, siteName: siteName, dueDate: es.dueDate, matchStatus: matchStatus, userDecision: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR es.status = ?, origin: COALESCE(tracksRel.origin, ?), feedback: COALESCE(tracksRel.feedback, ?), reason: COALESCE(tracksRel.reason, ?), removalHasAcceptedDocument: COALESCE(tracksRel.removalHasAcceptedDocument, false), workOrder: CASE WHEN wo IS NOT NULL THEN {tildaid: wo.tildaid, work_order_id: wo.work_order_id, status: wo.status} ELSE null END, renewalSource: CASE WHEN renewalSrc IS NOT NULL THEN {documentId: renewalSrc.tildaid, documentName: renewalSrc.fileName} ELSE null END, acceptedMatch: CASE WHEN acceptedDoc IS NOT NULL THEN { documentId: acceptedDoc.tildaid, displayId: acceptedDoc.displayId, contentType: acceptedDoc.contentType, documentName: acceptedDoc.fileName, documentDate: CASE WHEN acceptedDoc.createdat IS NOT NULL THEN datetime({epochMillis: acceptedDoc.createdat}) ELSE null END, isAccepted: true, matchedBy: COALESCE(head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | amr.matchedBy ]), ?), matchedAt: head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | CASE WHEN amr.createdat IS NOT NULL THEN datetime({epochMillis: amr.createdat}) ELSE null END ]), acceptedBy: acceptedRel.acceptedBy, acceptedAt: CASE WHEN acceptedRel.createdat IS NOT NULL THEN datetime({epochMillis: acceptedRel.createdat}) ELSE null END, docAttributes: [ (acceptedDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) 
WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: acceptedDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } ELSE null END, matchesExpected: matchesExpected, pendingMatches: pendingMatches }) as scopeRows 
WITH es, scopeRows, { total: size(scopeRows), pending: size([pp IN scopeRows 
WHERE pp.userDecision IN [?, ?]]), pendingForDelete: size([pp IN scopeRows 
WHERE pp.userDecision = ?]), accepted: size([pp IN scopeRows 
WHERE pp.userDecision = ?]), rejected: size([pp IN scopeRows 
WHERE pp.userDecision = ?]), incomplete: size([pp IN scopeRows 
WHERE pp.matchStatus = ?]), potentialMatchFound: size([pp IN scopeRows 
WHERE pp.matchStatus = ?]), matchExpected: size([pp IN scopeRows 
WHERE pp.matchStatus = ?]), matched: size([pp IN scopeRows 
WHERE pp.matchStatus = ?]), finalized: size([pp IN scopeRows 
WHERE pp.matchStatus = ?]), completion: {required: size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]), completed: size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]), aiSuggested: size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), inStaging: size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), noMatchFound: size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), notConfirmed: size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND pp.userDecision = ? AND NOT pp.matchStatus = ?]), removed: size([pp IN scopeRows 
WHERE (pp.userDecision = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) / size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]))) END, isComplete: size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) > ? AND size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) = size([pp IN scopeRows 
WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)])}, matchedWithNew: size([pp IN scopeRows 
WHERE pp.matchStatus = ? AND (size(pp.pendingMatches) > ? OR size(pp.matchesExpected) > ?)]) } as progress, scopeRows as placeholders 
RETURN es.tildaid as scopeId, es.name as scopeName, es.scopeType as scopeType, size(placeholders) as total, progress, placeholders, COALESCE(scopeRows[?].siteName, ?) as siteName, COALESCE(scopeRows[?].siteNumber, ?) as siteNumber, CASE WHEN es.scopeType = ? THEN COALESCE(es.scopeId, ?) ELSE ? END as country

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

index Index the Event anchor lookup on tildaid (+ orgId)
The issueThe whole query starts from `MATCH (e:Event {tildaid: $eventId, orgId: $orgId})`. Nothing shows an index on Event.tildaid or Event.orgId. Without one, the planner uses NodeByLabelScan(:Event) and checks both properties on every Event node. It may also decide to start from the other end of the pattern (Deliverable / EventScope / Document) and join back to e, which costs even more. The anchor sets the cost of everything after it, so a scan here could explain the 1.7 s p95 against the 253 ms average.
The fixGive the anchor a seekable lookup. If tildaid is a globally unique id, which the naming suggests, a uniqueness constraint is best: it creates a backing range index, and orgId then becomes a cheap filter on the single row it returns. If tildaid is NOT unique across orgs, use a composite range index instead: CREATE INDEX event_tildaid_org IF NOT EXISTS FOR (e:Event) ON (e.tildaid, e.orgId).
Add this index
CREATE CONSTRAINT event_tildaid_unique IF NOT EXISTS FOR (e:Event) REQUIRE e.tildaid IS UNIQUE
Expected: Finding the anchor should go from scanning the whole Event label to seeking one node. That probably cuts the slow outlier runs most, though how much depends on how many Event nodes exist. With only 6 runs a week the total time saved is small, but the worst-case response time should fall. · Evidence: Run SHOW INDEXES / SHOW CONSTRAINTS to confirm nothing covers Event.tildaid yet. Check uniqueness with MATCH (e:Event) WITH e.tildaid AS t, count(*) AS c WHERE c > 1 RETURN count(*). Run PROFILE before and after: the Event leaf should change from NodeByLabelScan + Filter to NodeUniqueIndexSeek (or NodeIndexSeek), and total db hits should drop. Then compare p95 over the next week.
index Index Site.tildaid for the per-row site lookup
The issue`OPTIONAL MATCH (site:Site {tildaid: es.scopeId})` runs once for every row that reaches it: one row per placeholder/scope after the earlier aggregations. The value comes from es, so this is planned as an Apply / Optional with its own lookup on the right-hand side. Without an index on Site.tildaid, every row does its own NodeByLabelScan(:Site) plus a Filter. The cost is roughly (rows) × (Site count).
The fixAdd an index on Site.tildaid so each row does an index seek. If Site ids are unique, use a constraint instead: CREATE CONSTRAINT site_tildaid_unique IF NOT EXISTS FOR (s:Site) REQUIRE s.tildaid IS UNIQUE.
Add this index
CREATE INDEX site_tildaid IF NOT EXISTS FOR (s:Site) ON (s.tildaid)
Expected: The site lookup should go from a label scan per row to a seek per row. The more placeholders an event has, the bigger the gain, so the large events behind the p95 should benefit most. · Evidence: In PROFILE, look at the operator under the Optional/Apply for `site`. Before the change it should show NodeByLabelScan(:Site) with rows ≈ placeholders × Site count; after, NodeIndexSeek(:Site(tildaid)) with about 1 row per input row. Also compare db hits before and after for an event with many placeholders.
rewrite Look up study once per event, not once per placeholder row
The issue`OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study)` depends only on e, but it is placed after the per-placeholder aggregations. So it re-expands from e for every ph/es row. If an event has several EventPlans or contexts, the rows multiply (ph × study × pd) before `head(collect(...))` reduces them again. `study` also has no label, so PLAN_CONTEXT is expanded to every kind of target node. Earlier in the query, the acceptedDoc × matchDoc OPTIONAL MATCHes are likewise multiplied per row before `collect(DISTINCT ...)`, and each multiplied row rebuilds the docAttributes pattern comprehensions.
The fixMove the study lookup to straight after the anchor and collect it once: `MATCH (e:Event {tildaid: $eventId, orgId: $orgId}) OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study:<StudyLabel>) WITH e, collect(DISTINCT study) AS studies`. Then carry `studies` through each WITH, and in the site step match `(site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {type: ?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WHERE study IN studies`. Also give `study` its real label so the planner can filter while it expands. Separately, wrap the matchDoc collection in a CALL { WITH ph, acceptedDoc ... } subquery so the docAttributes comprehensions are built once per matching document, not once per row of the acceptedDoc × matchDoc product. I can't give the full rewritten text: the statement in the input is cut off mid-expression (`acceptedDoc.primaryV...`), so these edits need to be applied to the source query by hand.
Expected: Fewer intermediate rows when an event has many placeholders or more than one plan context, which should mainly help the slow outlier calls. The size of the gain depends on the data and could be small if each event has a single EventPlan. · Evidence: PROFILE the slowest eventId: compare the row counts going into the `head(collect(site.name))` aggregation, and the db hits on the EVENTS/PLAN_CONTEXT Expand operators, before and after. Confirm the returned JSON is identical for a sample of events.

Status: proposed · all suggestions

No rollups for this shape in the window.