Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 14 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 13:46:31.
› →|
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 FE26D0B8 · service docsvc · read · last 14d

Query
MATCH (doc:Document) 
WHERE doc.tildaid in $lists.documentIds0 
WITH collect(DISTINCT doc) as documents, count(DISTINCT doc) as total 
UNWIND documents as doc 
WITH doc, total, null as vector 
MATCH (org:Organization{tildaid:$policyOrgId0}) 
CALL { 
WITH org, doc 
UNWIND [doc.studyId] AS studyId 
OPTIONAL MATCH (study:Study) 
WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. 
OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) 
WHERE studyActor.state <> ? // Collect sites 
where user has membership in this study 
OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) 
WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries 
where user has membership in this study 
OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) 
WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study 
OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) 
WHERE EXISTS { 
MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { 
MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) 
WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies 
WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st 
WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id 
with no Study // node can never equal a matched study?tildaScale?ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) 
OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) 
OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) 
WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership 
OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) 
WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly' ELSE docAccessLevel END AS docAccessLevel 
CALL {
return null as patientId, null as patientName, null as subjectId}
CALL { 
return null as site, ? as siteNumber, [] as piNames }
CALL { 
return null as visit }
CALL { 
return null as user }
CALL { 
return null as uploadedBy }
CALL { 
return null as docType }
CALL { 
return null as signedByUsers , null as reviewDate } 
WITH distinct total, doc, docObj, vector, owned, study, org, docAccessLevel, isSigner, patientId, patientName, subjectId, site, siteNumber, piNames, visit, user, uploadedBy, docType, signedByUsers, reviewDate 
order by doc.createdat asc , doc.tildaid asc 
skip $skip0 
CALL { 
return null as workOrders, false as woContact, null as latestAIRun, null as findingCount, null as queriesCount, null as confirmedCount, null as resolvedCount }
CALL { 
return null as tags } 
CALL { 
return null as path, null as fold, null as collection, null as stateTags1 } 
CALL { 
return null as primaryFolder } 
CALL { 
return null as listingFolders } 
CALL { 
return null as stateTags2, null as stagingLabels } 
CALL { 
return [] as reviews} 
CALL { 
return null as templateDetails} 
CALL { 
return null as versions } 
CALL { 
with doc 
optional match (doc)-[:HAS]-(ann:DocumentAnnotation) 
optional match (ann)-[:ANNOTATION_SIGNER]-(ds:DocumentSigner) 
optional match (ann)-[:ANNOTATION_FIELD]-(df:DocumentField) 
optional match (ann)-[:ALL_CHANGELOG]->(cl:ChangeLog) 
with doc, ann, ds, df, cl 
order by cl.createdat asc 
with doc, ann, apoc.convert.fromJsonList(ann.signers) as signersOld, collect(distinct ds{.*}) as signers, collect(distinct df{.*}) as fields, collect(distinct cl{.*}) as auditLog 
with doc, ann, auditLog, case when size(signers) <> ? then signers when size(signers) = ? and signersOld is null then [] else signersOld end as signers1, case when fields is null then [] else fields end as fields1 
with doc, ann{.*, signers: signers1, fields: fields1} as annotationSpec, case when doc.template = false then ann{.*, signers: signers1, fields: fields1} else null end as annotationVal 
return annotationSpec, annotationVal } 
CALL { 
return [] as queries } 
CALL { 
WITH docObj, owned 
OPTIONAL MATCH (docObj)<-[:REFERS_TO]-(docShare:DocumentShare)-[:DOC_SHARED_TO_ORG]->(otherOrg:Organization) 
WHERE owned 
CALL { 
WITH docShare, docObj 
OPTIONAL MATCH (docShare)-[signedByRel:SIGNED_BY]->(signedBy:User) 
WHERE (docObj.docKind = ? OR coalesce(signedByRel.majorVersion, docObj.majorVersion, ?) = coalesce(head([(docObj)-[:DOCUMENT_VERSION]->(vv:DocumentVersion{tildaid: $reviewedVersionId0}) | vv.majorVersion]), docObj.majorVersion, ?)) 
WITH COLLECT(DISTINCT({id: signedBy.tildaid, name: signedBy.name, profilePicURL: signedBy.profilePicURL})) as signedByUsers, count(signedBy) as signersCount, max(signedByRel.createdat) as reviewDate 
RETURN CASE WHEN signersCount<>? THEN signedByUsers ELSE [] END as signedByUsers, reviewDate } 
CALL { 
WITH docShare 
OPTIONAL MATCH (docShare)-[:ALL_CHANGELOG]->(c:ChangeLog) 
WHERE c.level IS NULL OR c.level = ? 
OPTIONAL MATCH (e:ESignature{tildaid:c.blob}) 
WITH CASE WHEN NOT c is NULL THEN {changeLog: c{.*}, eSignature: e{.*}} END as clog 
order by c.createdat asc 
RETURN collect(clog) as changeLog } 
WITH CASE WHEN NOT otherOrg is NULL THEN {readerOrg: otherOrg.name, readerOrgId: otherOrg.tildaid, readerOrgAlias: otherOrg.alias, status: docShare.ownerStatus, signedByUsers: signedByUsers, reviewDate: reviewDate, changeLog: changeLog} END as shareEntry 
RETURN collect(shareEntry) as sharedWith } 
CALL { 
WITH docObj 
RETURN CASE WHEN (docObj)-[:REFERS_TO]->(:Document) THEN docObj{.*} ELSE null END as currentShare } 
CALL { 
return null as docAttributes } 
CALL { 
return null as notesCount } 
CALL { 
return null as eventId, null as eventName, null as deliverable, null as eventScope } 
CALL { 
return null as copiedFrom } 
CALL { 
return [] as copiedTo, ? as copiedToCount } 
CALL { 
return null as promotedTo } 
CALL { 
return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } 
CALL { 
return null as tmfCategorization } 
with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo 
WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total 
return document, total

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

rewrite Stop the annotation subquery from multiplying signers × fields × changelogs
The issueIn the per-document `CALL { with doc optional match (doc)-[:HAS]-(ann:DocumentAnnotation) optional match (ann)-[:ANNOTATION_SIGNER]-(ds) optional match (ann)-[:ANNOTATION_FIELD]-(df) optional match (ann)-[:ALL_CHANGELOG]->(cl) ... }`, the three OPTIONAL MATCHes hang off `ann` independently, so each annotation produces |ds| × |df| × |cl| rows. That product is then sorted (`order by cl.createdat`) and reduced again with `collect(distinct ...)`. An annotation with 5 signers, 30 fields and 40 changelog entries makes 6,000 rows, where 75 would do. That fits the gap between the 540 ms average and the 1,900 ms p95: the slow call probably hit a document with many annotations or a long audit log.
The fixReplace the three OPTIONAL MATCHes with one pattern comprehension per relationship type, keeping a single row per annotation. Inside the CALL block: CALL { WITH doc OPTIONAL MATCH (doc)-[:HAS]-(ann:DocumentAnnotation) WITH doc, ann, apoc.convert.fromJsonList(ann.signers) AS signersOld, [(ann)-[:ANNOTATION_SIGNER]-(ds:DocumentSigner) | ds{.*}] AS signers, [(ann)-[:ANNOTATION_FIELD]-(df:DocumentField) | df{.*}] AS fields, apoc.coll.sortMaps([(ann)-[:ALL_CHANGELOG]->(cl:ChangeLog) | cl{.*}], '^createdat') AS auditLog WITH doc, ann, auditLog, CASE WHEN size(signers) <> ? THEN signers WHEN size(signers) = ? AND signersOld IS NULL THEN [] ELSE signersOld END AS signers1, coalesce(fields, []) AS fields1 RETURN ann{.*, signers: signers1, fields: fields1} AS annotationSpec, CASE WHEN doc.template = false THEN ann{.*, signers: signers1, fields: fields1} ELSE NULL END AS annotationVal } Two things differ from the original. The original sorts the auditLog by cl.createdat before collecting it, and sortMaps keeps that order. Wrap the lists in apoc.coll.toSet if duplicate maps were relied on being removed. I couldn't give the full rewritten statement because the shape is truncated (it cuts off in the DocumentShare subquery). Apply this change to the CALL block in the source query.
Expected: Rows per annotation drop from a product to roughly 1, and the in-memory sort and DISTINCT over the product go away. The gain should be largest on documents with many annotations or long changelogs (the p95 case). It may be small on the average call. This is unverified. · Evidence: Run PROFILE on the slowest documentIds list before and after the change. Compare the row counts going into the Sort/EagerAggregation operators of this subquery and the total db hits. Also check that the annotation output is the same, including auditLog order.
index Confirm Document.tildaid has a unique constraint backing the IN lookup
The issueThe entry clause `MATCH (doc:Document) WHERE doc.tildaid in $lists.documentIds0` is the only seed for the whole query. Without a range or unique index on Document.tildaid, it turns into a NodeByLabelScan over every Document, and the rest of the expensive pipeline then runs on top of it. tildaid looks like the node key, so a constraint may already exist. The listed properties don't show it, so it needs checking. Organization.tildaid, Study.tildaid and User.tildaid are used the same way in `{tildaid:$policyOrgId0}`, `study.tildaid = studyId` and `{tildaid:$ctxUserId0}`.
The fixRun SHOW CONSTRAINTS / SHOW INDEXES. If Document.tildaid isn't covered, add the uniqueness constraint (it also enforces the id semantics). Then the IN list plans as a NodeUniqueIndexSeek, with one seek per id. Do the same check for Organization, Study and User tildaid.
Add this index
CREATE CONSTRAINT document_tildaid_unique IF NOT EXISTS FOR (d:Document) REQUIRE d.tildaid IS UNIQUE
Expected: If the index is missing, the seed step goes from O(all Documents) to O(|documentIds0|), which could account for a large share of the 1.9 s p95. If the constraint already exists, nothing changes and this item can be closed. · Evidence: Check the output of SHOW CONSTRAINTS. The EXPLAIN plan's first operator should be NodeUniqueIndexSeek(Document, tildaid) rather than NodeByLabelScan, and the same should hold for the Organization, Study and User lookups.
none Low volume: tune only if the p95 call matters to a user
The issueThis shape ran just 2 times in 7 days, for about 1.1 s of total DB time. One of the two runs probably caused the 1,900 ms p95, likely from a cold page cache or a large documentIds0 list. The policy and folder-hierarchy subquery (`OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder)`, the policy EXISTS checks) runs for every document before `order by ... skip $skip0`. With no visible LIMIT, the skip doesn't cut that work. Its traversals are anchored on bound nodes, though, so no further index would help.
The fixBeyond the annotation rewrite and the constraint check, the cluster-wide gain from this statement is negligible: at best about 1 s per week. Only restructure the access-policy section, for example by computing the paging window before evaluating the folder hierarchy, if a user-facing screen actually waits on this call.
Expected: No measurable change in cluster-wide load is expected from further work on this shape. · Evidence: Look at the execution count and p95 trend over a longer window. Only revisit this if volume grows or the p95 call is tied to a user complaint.

Status: proposed · all suggestions

p95 latency over time
95th-percentile elapsedTimeMs per bucket
0ms500ms1s1.5s2s00:0000:00 1.9s p95
p50 latency over time
Median elapsedTimeMs per bucket
0ms2ms4ms6ms8ms10ms00:0000:00 10ms p50
Executions / bucket
How often this shape ran
01200:0000:00 2

p50/p95 are recovered from the per-bucket latency histogram. A rising p95 with a flat p50 means the tail is getting slower (lock contention, GC, a cold cache) even when the typical case is fine.