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 {
RETURN {} AS studyById, [] AS studyIdsInScope }
CALL {
with org, doc
WITH org, doc as doc0
match (org)<-[:DOC_TO_ORG]-(doc0:Document)
RETURN doc0 as doc1, doc0 as docObj, null as docShare, true as owned, null as location
union
with org, doc
WITH org, doc as doc0
match (org)<-[:DOC_SHARED_TO_ORG]-(docObj:DocumentShare)-[:REFERS_TO]->(doc0:Document)
RETURN doc0 as doc1, docObj, docObj as docShare, false as owned, null as location
union
with org, doc
WITH org, doc as doc0
match (:Organization {alias:?})-[:HAS_SYSTEM_TEMPLATE]->(doc0:Document)
where not (doc0)-[:DOC_TO_ORG]->(org)
RETURN doc0 as doc1, doc0 as docObj, null as docShare, false as owned, null as location }
CALL {
with org, doc1
OPTIONAL MATCH (study:Study{tildaid:doc1.studyId})
OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$pickOrgId0})-[:FOR_USER]->(:User{tildaid:$pickUserId0})
RETURN study, studyActor, [] as policies, [] as userSiteIds, [] as userCountryIds, ? as defaultAccessLevel }
CALL {
RETURN ? AS docAccessLevel }
CALL {
WITH doc1
RETURN EXISTS { (doc1)-[:HAS]-(:DocumentAnnotation)-[:ANNOTATION_SIGNER]-(:DocumentSigner{userId:$signerUserId0}) } AS isSigner }
WITH total, vector, doc1 as doc, docObj, owned, study, org, isSigner, CASE WHEN $isNonMember0 AND docAccessLevel = ? THEN ? 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 {
with doc
OPTIONAL MATCH (doc)<-[:INCLUDES]-(b:Binder{binderType:?})
return apoc.text.join(collect (b.tag), ?) as tags }
CALL {
WITH doc, docObj
OPTIONAL MATCH (fold:Folder{delpending:false, folderType:?})-[:INCLUDES]->(docObj)
WHERE (fold.scope=? OR fold.scope=?)
OPTIONAL MATCH path = (collection:Folder{level:?})-[:INCLUDES*?..?]->(fold)
RETURN path, fold, collection, CASE WHEN COALESCE(doc.collection, ?) <> ? THEN [COALESCE(doc.collection, ?)] ELSE [] END as stateTags1 }
CALL {
WITH docObj, path, fold, collection
OPTIONAL MATCH (fold)-[:HAS]->(foldFc:FolderConstraints)
OPTIONAL MATCH (fold)-[:INCLUDES]->(subArtifact:SubArtifact)<-[:FILED_UNDER_SUBARTIFACT]-(docObj)
OPTIONAL MATCH (subArtifact)-[:HAS]->(subFc:FolderConstraints)
WITH collection, fold, subArtifact, [n IN nodes(path) | CASE WHEN n.number=? THEN n.name ELSE apoc.text.lpad(toString(COALESCE(n.number, ?)), ?, ?)+ ?+n.name END] AS nodeIds, CASE WHEN subFc IS NOT NULL THEN subFc ELSE foldFc END as folderConstraints
WITH collection, fold, subArtifact, apoc.text.join(nodeIds, ?) 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, 3d ago; suggests only, a human applies
index Make sure the Document.tildaid anchor lookup uses an index (uniqueness constraint)
The issueEverything in this query starts from `MATCH (doc:Document) WHERE doc.tildaid IN $lists.documentIds0`. Without a constraint or index on Document.tildaid, this becomes a NodeByLabelScan over every Document followed by a Filter. Every later subquery then runs once for each document it finds, so a slow start makes the whole query slow. The gap between the 220 ms average and the 1550 ms p95 fits a full scan whose cost grows with the Document count or the size of the list.
The fixtildaid looks like the application's node key, so add a uniqueness constraint. It also creates a range index, so the IN-list becomes a NodeUniqueIndexSeek with one seek per id. If a constraint or index on Document.tildaid already exists (SHOW CONSTRAINTS / SHOW INDEXES), skip this and look at the other two suggestions.
Add this index
CREATE CONSTRAINT document_tildaid_unique IF NOT EXISTS FOR (d:Document) REQUIRE d.tildaid IS UNIQUE
Expected: If there is currently no index, the anchor step should drop from scanning every Document to a handful of index seeks. That would likely remove most of the p95 tail. If the index already exists, nothing changes. Total weekly time is only about 2 s over 9 runs, so the gain is mostly in single-request latency, not cluster load. · Evidence: Run EXPLAIN/PROFILE on the statement and check that the first operator is NodeUniqueIndexSeek(Document.tildaid) rather than NodeByLabelScan + Filter. Then compare p95 for shape 5E89E916 before and after.
index Index Organization.alias for the system-template branch of the UNION
The issueThe third UNION branch, `match (:Organization {alias:?})-[:HAS_SYSTEM_TEMPLATE]->(doc0:Document)`, runs once for each unwound document. alias is not the Organization key, so the planner either scans every Organization looking for the alias, or starts from doc0 and expands every incoming HAS_SYSTEM_TEMPLATE relationship and then filters on alias. Either way, this lookup is repeated for every document in the list.
The fixAdd a range index on Organization.alias so the template-owner org is found by an index seek and then joined to the bound doc0 with an expand-into. If an alias identifies a single org, use a uniqueness constraint instead of the plain index.
Add this index
CREATE INDEX organization_alias IF NOT EXISTS FOR (o:Organization) ON (o.alias)
Expected: This should cut the cost of the third UNION branch for each document from a label scan or wide expand to a single seek plus one relationship check. That could be a noticeable share of the 220 ms average when the document list is long. This is not verified. · Evidence: In PROFILE, the third UNION branch should show NodeIndexSeek(Organization.alias) instead of NodeByLabelScan(Organization) or a Filter on alias. Also compare db hits for that branch before and after.
index Index Study.tildaid for the per-document study lookup
The issue`OPTIONAL MATCH (study:Study{tildaid:doc1.studyId})` runs inside a CALL once for each document row. Unlike the other lookups here, it starts from a property value rather than an already-bound node, so if Study.tildaid has no index, every document triggers a scan of all Study nodes. Cost then grows as (number of documents × number of Studies), which also fits the high p95.
The fixAdd a uniqueness constraint on Study.tildaid, since tildaid appears to be the application ID across labels. The lookup then becomes a NodeUniqueIndexSeek for each row. The STUDY_TO_ACTOR → Actor → User check that follows can then expand from a single study node.
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: If there is no index today, the study lookup should go from one label scan per document to one seek per document. That would be roughly linear savings in the number of Studies for each row. No change if the constraint already exists. · Evidence: Run SHOW CONSTRAINTS to see whether one already covers Study.tildaid. Then PROFILE should show NodeUniqueIndexSeek(Study.tildaid) under the Apply for that CALL, and the p95 for the shape should be compared after deploy.
Status: proposed · all suggestions
No rollups for this shape in the window.