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:08:06.
› →|
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 77AB3B8B · read · last 14d

Query
MATCH (org:Organization{tildaid:$scopeOrgId0}) 
CALL { 
WITH org 
UNWIND $lists.allowedStudies0 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?Basic??ReadWrite?ReadOnly?ReadWrite?ReadWrite?ReadOnly?NoAccess??Inactive? ?Received?Quality Control?PHI Issues???/???/???/') ELSE ? END as childFolderPath 
WITH docObj, ct, child, st, head(collect(childFolderPath)) AS childFolderPath 
WITH child, CASE WHEN ct IS NULL THEN null ELSE { documentId: child.tildaid, studyId: COALESCE(child.studyId, ?), studyName: COALESCE(st.name, ?), folderPath: childFolderPath, kind: ct.kind, stale: ct.sourceVersionId <> docObj.primaryVersionId, version: COALESCE(child.versionName, toString(child.majorVersion)) } END as copiedToEntry 
RETURN collect(copiedToEntry) as copiedTo, count(DISTINCT child) 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 tmfInfo.artifactId as artifactId, tmfInfo.subArtifactId as subArtifactId, tmfInfo.stagingInfoId as stagingInfoId 
OPTIONAL MATCH (artifact:Folder{tildaid:artifactId})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{name:?}) 
OPTIONAL MATCH (artifact)-[:INCLUDES]->(allSubArtifacts:SubArtifact) 
OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:subArtifactId}) 
OPTIONAL MATCH (subArtifact)-[:HAS]->(subArtifactConstraints:FolderConstraints) 
OPTIONAL MATCH (artifact)-[:HAS]->(artifactConstraints:FolderConstraints) 
WITH artifactId as folderId, subArtifactId, stagingInfoId, CASE WHEN zone.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+ ? + zone.name, ?) ELSE ? END as zone, CASE WHEN section.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+ ? + section.name, ?) ELSE ? END as section, CASE WHEN artifact.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+?+apoc.text.lpad(toString(artifact.number), ?, ?)+ ? + artifact.name, ?) ELSE ? END as artifact, CASE WHEN subArtifact.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+?+apoc.text.lpad(toString(artifact.number), ?, ?)+?+apoc.text.lpad(toString(subArtifact.number), ?, ?)+ ? + subArtifact.name, ?) ELSE ? END as subArtifact, COALESCE(subArtifact.name, ?) as subArtifactName, COALESCE(subArtifactConstraints, artifactConstraints) as constraints, NOT(COUNT(allSubArtifacts) > ? AND subArtifactId = ?) as subArtifactValid 
OPTIONAL MATCH (constraints)-[:HAS_ATTRIBUTE_CONSTRAINT]->(dac:DocAttributeConstraint) 
WITH folderId, subArtifactId, stagingInfoId, zone, section, artifact, subArtifact, subArtifactName, subArtifactValid, constraints, COLLECT(DISTINCT CASE WHEN dac IS NOT NULL THEN {id: dac.tildaid, key: dac.key, order: dac.order, isMandatory: dac.isMandatory, description: dac.description} ELSE NULL END) as attributeConstraintsList 
WITH folderId, subArtifactId, stagingInfoId, zone, section, artifact, subArtifact, subArtifactName, subArtifactValid, CASE WHEN constraints IS NOT NULL THEN { id: constraints.tildaid, mandatory: constraints.mandatory, tmfLevel: constraints.tmfLevel, attributeConstraints: [attr IN attributeConstraintsList 
WHERE attr IS NOT NULL | attr] } ELSE null END as constraints 
WITH {folderId: folderId, subArtifactId: subArtifactId, stagingInfoId: stagingInfoId, zone: zone, section: section, artifact: artifact, subArtifact: subArtifact, subArtifactName: subArtifactName, subArtifactValid: subArtifactValid, path: apoc.text.join([x IN [zone, section, artifact] 
WHERE x <> ? | x], ?), constraints: constraints} 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 Study.tildaid has a uniqueness constraint so each UNWIND lookup is a seek
The issueInside the per-org CALL, `UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId` runs one lookup per allowed study id. If Study.tildaid has no index, every id costs a full NodeByLabelScan over :Study. Every later OPTIONAL MATCH (actor, sites, countries, policies) starts from this node, so a slow anchor here makes the whole fan-out slower. The labels/properties list doesn't show whether Study.tildaid is already indexed.
The fixRun SHOW CONSTRAINTS / SHOW INDEXES. If Study.tildaid isn't covered, add a uniqueness constraint. tildaid acts as the node key across this schema, so a constraint is the right choice (it gives both a seek and integrity). Check Organization.tildaid ($scopeOrgId0) and User.tildaid ($ctxUserId0) the same way, because they anchor the outer MATCH and the EXISTS subqueries.
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: If tildaid isn't indexed today, each study lookup goes from a label scan to a unique seek, which could cut a large share of the ~1.2 s average when the allowed-studies list is long. If it's already indexed, nothing changes, and you can rule this out. · Evidence: PROFILE with a realistic $lists.allowedStudies0: look for NodeUniqueIndexSeek(study:Study) instead of NodeByLabelScan/Filter, with db hits on that operator falling to about 1–2 per id.
index Index Folder.tildaid for the artifact lookup in the TMF-path subquery
The issueIn the docObj subquery, `OPTIONAL MATCH (artifact:Folder{tildaid:artifactId})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{name:?})` uses a key value computed at runtime (artifactId comes from tmfInfo). Without an index on Folder.tildaid, the planner has to scan :Folder or start from `collection:Folder{name:?}` and expand 3 hops down INCLUDES. This runs once per document row, so the cost grows with the number of documents returned.
The fixAdd a uniqueness constraint on Folder.tildaid so the planner can seek the artifact directly and expand upward 3 hops (each Folder has 1 parent, so that's cheap). Do not add an index on Folder.name: it could push the planner to anchor on the collection, which is the wrong direction.
Add this index
CREATE CONSTRAINT folder_tildaid_unique IF NOT EXISTS FOR (f:Folder) REQUIRE f.tildaid IS UNIQUE
Expected: The per-document folder-path resolution should become a seek plus a few single-parent expansions instead of a scan or a downward fan-out. The gain grows with the documents per call and could be significant on the 4.5 s outlier, but this is unverified. · Evidence: In the PROFILE, the Apply branch for this subquery should show NodeUniqueIndexSeek(artifact:Folder) with rows ≈ the document count, and no NodeByLabelScan(:Folder).
caching Check whether the 4.5 s p95 is planning/cold-cache rather than execution
The issueThe query ran only 3 times in 7 days: the average is 1255 ms but p95 is 4550 ms, which suggests 1 slow run and 2 fast ones. The statement is very large: many nested CALL subqueries, more than a dozen OPTIONAL MATCHes, EXISTS subqueries and apoc calls. Plans for statements like this are expensive to compile, and at this low frequency the cached plan is probably evicted or never warm, so the outlier may be planning time plus page-cache misses rather than graph work.
The fixIn query.log (with time logging enabled), compare planning time to total time for the slow run. If planning dominates, keep the statement text byte-identical and fully parameterised: the '?' slots such as status, folderType, scope and the Folder name should stay constant literals or become $params so one plan is reused. Consider warming the plan at deploy time by running an EXPLAIN of the statement. If page-cache faults dominate instead, it's a memory sizing issue, not a query issue.
Expected: If compile time is the cause, the p95 should move toward the average (~1 s) or below. If it isn't, this rules compile time out and points back to the index items. Total weekly cost is only ~3.8 s, so treat this as low priority unless the endpoint is user-facing. · Evidence: For the 4.5 s execution, query.log planning= vs total time; for the fast runs, PROFILE page cache hits/faults.

Status: proposed · all suggestions

No rollups for this shape in the window.