Xray
1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 1 hour▾
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:40: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 F00D0864 · read · last 1h

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.