Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 3 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 12:50:07.
› →|
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 7134D109 · read · last 3d

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 { 
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, 3d ago; suggests only, a human applies

index Make sure the per-study lookup on Study.tildaid uses an index
The issueInside the CALL subquery, `UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId` runs once for every allowed study id. Without an index or uniqueness constraint on Study(tildaid), each row does a NodeByLabelScan over all :Study nodes. The cost is (number of allowed studies) × (number of Study nodes), and everything after this line (actors, sites, countries, policies) expands from these rows.
The fixAdd a uniqueness constraint on Study.tildaid. It also creates a backing range index, so the lookup becomes a NodeUniqueIndexSeek for each id. If tildaid is not unique for Study, use `CREATE INDEX study_tildaid IF NOT EXISTS FOR (s:Study) ON (s.tildaid)` instead. Check that Organization.tildaid has the same kind of constraint too, because `MATCH (org:Organization{tildaid:$scopeOrgId0})` is the query's entry point.
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: If no index exists today, this should remove a label scan per allowed study id. That could cut a noticeable part of the ~1.8 s average for users with many allowed studies. If a constraint already exists, nothing changes. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES for Study(tildaid) and Organization(tildaid). Then run PROFILE with a realistic $lists.allowedStudies0 and confirm NodeUniqueIndexSeek(Study) has replaced NodeByLabelScan(Study), and check whether db hits drop.
index Index User.tildaid used in the FOR_USER pattern and the policy EXISTS subquery
The issue`(:User{tildaid:$ctxUserId0})` shows up twice for every study row: once in the `studyActor ... -[:FOR_USER]->(:User{...})` OPTIONAL MATCH, and once inside `EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) }`. If User.tildaid has no index, the planner may scan :User to anchor these patterns, or fall back to expanding and filtering on a property it can't look up.
The fixAdd a uniqueness constraint on User.tildaid. The planner can then seek the single user once and use it as the anchor for the FOR_USER / POLICY_FOR_USER expansions (often by hash-joining on that node), instead of filtering properties for every candidate actor or policy.
Add this index
CREATE CONSTRAINT user_tildaid_unique IF NOT EXISTS FOR (u:User) REQUIRE u.tildaid IS UNIQUE
Expected: Probably a modest gain per study row. It matters most when the org has many Actors or DocumentAccessPolicies linked to each study. · Evidence: Run SHOW CONSTRAINTS for User(tildaid). In the PROFILE, look for NodeUniqueIndexSeek(User) instead of a label scan or a per-row Filter on User.tildaid, and compare rows/db hits on the FOR_USER and POLICY_FOR_USER operators.
index Support the status filter on DocumentAccessPolicy
The issue`OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study)` filters on policy.status with no lookup available. Depending on direction, the planner either expands every org policy per study and filters status afterward, or scans :DocumentAccessPolicy. Then it runs two EXISTS subqueries per candidate policy.
The fixCreate a range index on DocumentAccessPolicy(status). This gives the planner the choice to seek active policies directly when that is more selective than expanding from org or study. If the plan still expands from org (all policies, every study), compare it with a version anchored from study via POLICY_TO_STUDY. I can't give that as a full rewrite because the middle of this statement was truncated during normalisation.
Add this index
CREATE INDEX doc_access_policy_status IF NOT EXISTS FOR (p:DocumentAccessPolicy) ON (p.status)
Expected: Uncertain. It helps only if the org has many policies and few are in the requested status. If status is not selective, the planner will rightly ignore the index. · Evidence: Count policies per org by status. PROFILE the query and check rows coming out of the DOC_ACCESS_POLICY_TO_ORG expand before the status Filter. Also keep in mind the volume: 2 runs/week (3.5 s total), so most of the real cost may be in the truncated document-assembly part, which should be profiled too.

Status: proposed · all suggestions

No rollups for this shape in the window.