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???/') as folderPath, folderConstraints
RETURN {id: fold.tildaid, name: fold.name, number: fold.number, folderType: fold.folderType, folderScope: fold.scope, folderScopeId: fold.scopeId, path: folderPath, mandatoryFields: [], optionalFields: [], subArtifactId: COALESCE(subArtifact.tildaid, ?), subArtifactName: COALESCE(subArtifact.name, ?), constraints: CASE WHEN folderConstraints IS NOT NULL THEN folderConstraints{.*} ELSE null END} 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 {
with doc, docObj
CALL {
with doc, docObj
with doc, docObj
where doc.primaryVersionId = ? or doc.primaryVersionId is null
optional match (doc)-[:UPLOADED_BY]->(uploader:User)
return {tildaid: doc.tildaid, fileName: doc.fileName, originalFileName: doc.originalFileName, contentType: doc.contentType, date: doc.date, dateType: doc.dateType, versionName: doc.versionName, rejectReason: doc.rejectReason, language: doc.language, languages: doc.languages, needsAttention: doc.needsAttention, needsAttentionReasons: doc.needsAttentionReasons, trackActiveDuplicates: doc.trackActiveDuplicates, versionNumber: doc.versionNumber, majorVersion: doc.majorVersion, minorVersion: doc.minorVersion, generation: toString(doc.generation), cksum: doc.cksum, size: doc.size, status: doc.status, stateTag: doc.stateTag, pages: doc.pages, uploadMethod: doc.uploadMethod, changeLog: [], createdAt: doc.createdat, renderedCompletedAt: doc.renderedCompletedAt, renderedFieldsRevision: doc.renderedFieldsRevision, renderedSignersRevision: doc.renderedSignersRevision, uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} as version
union
with doc
optional match (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion)
where $versionsSeesPreRedaction0 or coalesce(dv.preRedaction, false) = false or dv.tildaid = doc.primaryVersionId
optional match (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv)
optional match (dv)-[:UPLOADED_BY]->(uploader:User)
optional match (dv)-[:PROMOTED_FROM]->(psrc:Document)
return case when dv is null then null else {tildaid: dv.tildaid, fileName: dv.fileName, originalFileName: dv.originalFileName, contentType: dv.contentType, date: dv.date, dateType: doc.dateType, versionName: dv.versionName, rejectReason: dv.rejectReason, language: dv.language, languages: dv.languages, needsAttention: dv.needsAttention, needsAttentionReasons: dv.needsAttentionReasons, trackActiveDuplicates: dv.trackActiveDuplicates, versionNumber: dv.versionNumber, majorVersion: dv.majorVersion, minorVersion: dv.minorVersion, documentSourceId: ds.tildaid, promotedFromDocumentId: psrc.tildaid, generation: toString(dv.generation), linearizedPdfUrl: dv.linearizedPdfUrl, linearizedPdfStatus: dv.linearizedPdfStatus, needsProcessedPdf: dv.needsProcessedPdf, langDetectionStatus: dv.langDetectionStatus, translatedPdfUrl: dv.translatedPdfUrl, translatedPdfStatus: dv.translatedPdfStatus, cksum: dv.cksum, size: dv.size, status: dv.status, stateTag: dv.stateTag, pages: dv.pages, uploadMethod: dv.uploadMethod, changeLog: [], url: dv.url, createdAt: dv.createdat, renderedCompletedAt: dv.renderedCompletedAt, renderedFieldsRevision: dv.renderedFieldsRevision, renderedSignersRevision: dv.renderedSignersRevision, preRedaction: coalesce(dv.preRedaction, false), uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} end as version // TODO: [Major/Minor Versioning] Once client supports minor version UX, update ordering to: // }
with doc, version
order by version.majorVersion, version.minorVersion // This will display versions as ?.?, ?.?, ?.? instead of flat ?, ?, ? }
with doc, version
order by version.versionNumber
where $versionsVersionId0 IS NULL OR version.tildaid = $versionsVersionId0
return collect(version) as versions }
CALL {
return null as annotationSpec, null as 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 {
WITH doc
MATCH (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion{tildaid: coalesce($docAttrVersionId0, doc.primaryVersionId)})
MATCH (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv)
OPTIONAL MATCH (ds)-[:HAS_ATTRIBUTE]->(da:DocAttribute)
OPTIONAL MATCH (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition)
RETURN collect(DISTINCT da{.*, name: COALESCE(dad.name, ?), description: COALESCE(dad.description, ?), documentSourceId: ds.tildaid}) 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 {
WITH docObj
OPTIONAL MATCH (docObj)<-[:INCLUDES]-(currentFolder:Folder{folderType:?})
WHERE currentFolder.scope IN [?, ?]
OPTIONAL MATCH (docObj)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder)
OPTIONAL MATCH (docObj)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo)
WITH CASE WHEN stagingInfo IS NOT NULL THEN {artifactId: stagingInfo.folderId, subArtifactId: stagingInfo.subArtifactId, stagingInfoId: stagingInfo.tildaid} WHEN currentFolder.level = ? THEN {artifactId: currentFolder.tildaid, subArtifactId: COALESCE(currentSubArtifact.tildaid, ?), stagingInfoId: ?} ELSE {artifactId: ?, subArtifactId: ?, stagingInfoId: ?} END as tmfInfo
WITH {folderId: tmfInfo.artifactId, subArtifactId: tmfInfo.subArtifactId, stagingInfoId: tmfInfo.stagingInfoId} as tmfCategorization
RETURN CASE WHEN tmfCategorization.folderId=? THEN null ELSE tmfCategorization END 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, 2d ago; suggests only, a human applies
index Confirm a unique lookup on Document.tildaid for the IN-list anchor
The issueEverything in this query starts from `MATCH (doc:Document) WHERE doc.tildaid in $lists.documentIds0`. If no index or uniqueness constraint covers Document.tildaid, Neo4j scans every Document node (NodeByLabelScan + Filter) before any of the per-document subqueries run. That is the most likely fixed cost, and it grows with the size of the Document label, not with the size of the list.
The fixMake sure a uniqueness constraint exists on Document.tildaid. It also creates the backing range index, so the IN list becomes one seek per id. tildaid looks like the application-wide id, so it is probably already constrained. Check with SHOW CONSTRAINTS before running the DDL; with IF NOT EXISTS the DDL does nothing if a constraint with this name exists. If tildaid could ever be duplicated, use a plain CREATE INDEX on that property instead.
Add this index
CREATE CONSTRAINT document_tildaid_unique IF NOT EXISTS FOR (d:Document) REQUIRE d.tildaid IS UNIQUE
Expected: Only helps if the constraint is missing. In that case the anchor step should drop from a full label scan to a few index seeks, which could account for a real part of the ~400 ms average. If the constraint already exists, nothing changes. · Evidence: Run SHOW CONSTRAINTS WHERE 'Document' IN labelsOrTypes. Run EXPLAIN on the statement and check whether the first operator is NodeUniqueIndexSeek/NodeIndexSeek on Document(tildaid) or NodeByLabelScan. Compare PROFILE db hits on that operator before and after.
index Confirm a lookup on Study.tildaid used inside the per-document CALL
The issueInside the CALL subquery, `OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId` runs once per document row. The value comes from a variable (doc.studyId), not a literal. Without an index on Study.tildaid, every document repeats a Study label scan. The same goes for the `Organization{tildaid:$policyOrgId0}` and `User{tildaid:...}` anchors.
The fixMake sure Study.tildaid, and likewise Organization.tildaid and User.tildaid, are backed by uniqueness constraints so each lookup is a seek. Most likely they already exist; this is a check, not an assumed gap.
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: If a constraint is missing, the per-document cost of resolving the study goes from a label scan to a single seek. The total saving scales with the number of documents in $lists.documentIds0, which may partly explain why p95 (1850 ms) is so far above the average (402 ms). · Evidence: Run SHOW CONSTRAINTS for Study, Organization and User. In PROFILE, look for NodeByLabelScan on Study/Organization/User under the Apply branches, and check the rows × db hits on those operators.
none Low volume; the remaining cost is the folder-ancestor traversal, which needs a PROFILE first
The issueThe statement ran 3 times in 7 days, for 1.2 s total, so the overall payoff is small. Once the anchors are seeks, the main cost is probably structural. `OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder)` is followed by `collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder)`. This builds one row per ancestor path for each document, and the paths are multiplied again by the optional version, source and uploader matches in the versions UNION branch. The variable-length expansion depends on folder depth, which fits the gap between the 402 ms average and the 1850 ms p95. No index fixes this: it is a traversal, not a property lookup.
The fixThe statement text is cut off, so I can't give a complete, checked rewrite. If PROFILE shows the INCLUDES varlength expand or the collect after it as the hot operator, the candidate change is: (a) match the ancestor chain once as a path, `OPTIONAL MATCH p=(doc1)<-[:INCLUDES*1..N]-(:Folder)`, and take `nodes(p)`, instead of matching docFolder and then its ancestors separately; or (b) collect folders per document before the version/uploader OPTIONAL MATCHes, so the expansion is not repeated per row. Wait for a PROFILE before changing anything.
Expected: Unknown until profiled. Any gain would show up as lower p95 for documents in deep folder trees. Given only 3 runs a week, total time saved will stay small. · Evidence: Get a PROFILE of a slow run (the ~1850 ms case). Check rows and db hits on VarLengthExpand(INCLUDES) and the EagerAggregation that follows, and see how many documents and how deep a folder tree were involved.
Status: proposed · all suggestions
No rollups for this shape in the window.