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, 4d 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
p50 latency over time
Median elapsedTimeMs per bucket
Executions / bucket
How often this shape ran
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.