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 15:03:54.
› →|
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.

Optimizer suggestions 8

What a model proposes for test26's busiest slow Cypher shapes — an index, a rewrite, batching or caching — each with the issue it found, the fix, and the concrete change. Shapes under 10 ms are ignored. Suggests only; a human applies and marks it. ?Daily, over the busiest and slowest shapes in the last 7 days, when an API key is configured. The model sees the normalised statement, its execution count, p95 and total time, and the labels and properties it touches. It is told never to claim a result — every suggestion names what to measure. Last run never · model claude-opus-5-5.

docsvc · 77AB3B8B — 3 runs · p95 4.55 s · 4 s total · 3d ago · proposed open shape & trend →

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.

docsvc · 7134D109 — 2 runs · p95 1.95 s · 4 s total · 3d ago · proposed open shape & trend →

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.

export-svc · 00000000 — 13 runs · p95 3.05 s · 3 s total · 3d ago · proposed open shape & trend →

index Range index on WorkPlanContext.createdat for the cutoff branch
The issueThe second UNION branch, `MATCH (ctx:WorkPlanContext) WHERE ctx.createdat < $cutoff`, is a range predicate. Without a property index Neo4j has to scan every WorkPlanContext node and filter on createdat. The cost grows with the total number of contexts, not with the number that have expired. That fits the large gap between the average (265 ms) and p95 (3,050 ms): runs that find few expired rows still pay for the full scan.
The fixCreate a RANGE index on createdat so the planner can use NodeIndexSeekByRange and read only the nodes older than $cutoff. With an index, the LIMIT can also stop the seek early instead of stopping a scan early.
Add this index
CREATE INDEX workplancontext_createdat IF NOT EXISTS FOR (ctx:WorkPlanContext) ON (ctx.createdat)
Expected: The createdat branch should change from a full label scan to an index range seek. If expired contexts are a small share of the total, this branch's time could fall by an order of magnitude. That is hedged: it depends on how many nodes are expired and how many are stored in total. · Evidence: Run EXPLAIN or PROFILE before and after creating the index. Before, the branch should show NodeByLabelScan followed by Filter on ctx.createdat. After, it should show NodeIndexSeekByRange on workplancontext_createdat. Also check the stored createdat values have one consistent type (all datetime or all epoch numbers) that matches $cutoff, or the range seek will skip rows. Then compare p95 for shape 00000000 over the next 7 days.
index Index on WorkPlanContext.delpending for the pending-delete branch
The issueThe first UNION branch, `MATCH (ctx:WorkPlanContext) WHERE ctx.delpending = true`, is an equality filter on a property that has no lookup. It forces a second full scan of the WorkPlanContext label, even though rows flagged for deletion are probably a small subset.
The fixAdd a RANGE index on delpending. Equality seeks on boolean values work with range indexes in Neo4j 5. Then the pending-delete branch reads only the flagged nodes. Together with the createdat index, neither branch needs to scan the whole label.
Add this index
CREATE INDEX workplancontext_delpending IF NOT EXISTS FOR (ctx:WorkPlanContext) ON (ctx.delpending)
Expected: The first branch should change from a label scan to NodeIndexSeek, so its cost should track the number of flagged nodes rather than the total number of contexts. This helps most when only a few contexts have delpending = true. · Evidence: PROFILE should show NodeIndexSeek on workplancontext_delpending instead of NodeByLabelScan followed by Filter in branch 1. Also compare the db hits for that branch before and after.
rewrite Apply LIMIT inside each UNION branch and drop the redundant DISTINCT
The issue`UNION` already removes duplicates, so the outer `WITH DISTINCT ctx` does the same work a second time. The single LIMIT is applied only after both branches are unioned and deduplicated. DETACH DELETE in the same statement can also bring in Eager operators, which may cause more rows from both branches to be read than $limit before any deletion happens.
The fixPut `LIMIT $limit` inside each branch so neither branch can return more than $limit rows. Keep UNION for deduplication and keep an outer LIMIT so the total stays capped at $limit. Remove DISTINCT because UNION already handles it. The result is the same: at most $limit distinct contexts deleted per call.
Proposed rewrite — current query in red −, proposed in green +
- // First part for StudySiteUser scope match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) with distinct scopeUser match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDefinition{type:?}) match (sitePd)-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]->(w:WorkflowV2)<-[:TRIGGER_TO_WORKFLOW]-(t:Trigger{scope:?}) match (sitePd)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (sitePd)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) match (scopeUser)-[:STUDYSITEACTOR_TO_ACTOR]->(actor:Actor{delpending:false})<-[:STUDY_TO_ACTOR]-(st) where not exists((scopeUser)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) return distinct scopeUser.tildaid as scopeTildaid, t.scope as workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, scopeUser.roles as roles UNION // Second part for StudySite scope match (t:Trigger{scope:?})-[:TRIGGER_TO_WORKFLOW]->(w:WorkflowV2) match (w)<-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]-(scopeSite:DslPlanDefinition{type:?}) where not exists((scopeSite)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) with distinct t.scope as workflowScope, scopeSite match (scopeSite)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (scopeSite)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) return distinct scopeSite.tildaid as scopeTildaid, workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, null as roles
+ CALL { MATCH (ctx:WorkPlanContext) WHERE ctx.delpending = true RETURN ctx LIMIT $limit UNION MATCH (ctx:WorkPlanContext) WHERE ctx.createdat < $cutoff RETURN ctx LIMIT $limit } WITH ctx LIMIT $limit DETACH DELETE ctx RETURN count(ctx) AS deleted
Expected: Each branch's input is capped at $limit rows before the union and the delete. This should stop the occasional run from materialising a large backlog. The gain is modest if the plan already ends each branch early, and larger if PROFILE shows Eager or rows above $limit flowing out of the branches. · Evidence: Compare PROFILE output for the old and new statements on the same data. Check the row counts coming out of each UNION branch, check whether an Eager operator appears before DetachDelete, and confirm that `deleted` returns the same value for the same $limit and $cutoff.

docsvc · 5E89E916 — 9 runs · p95 1.55 s · 2 s total · 3d ago · proposed open shape & trend →

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.

docsvc · 559B87B9 — 6 runs · p95 1.70 s · 2 s total · 3d ago · proposed open shape & trend →

index Index the Event anchor lookup on tildaid (+ orgId)
The issueThe whole query starts from `MATCH (e:Event {tildaid: $eventId, orgId: $orgId})`. Nothing shows an index on Event.tildaid or Event.orgId. Without one, the planner uses NodeByLabelScan(:Event) and checks both properties on every Event node. It may also decide to start from the other end of the pattern (Deliverable / EventScope / Document) and join back to e, which costs even more. The anchor sets the cost of everything after it, so a scan here could explain the 1.7 s p95 against the 253 ms average.
The fixGive the anchor a seekable lookup. If tildaid is a globally unique id, which the naming suggests, a uniqueness constraint is best: it creates a backing range index, and orgId then becomes a cheap filter on the single row it returns. If tildaid is NOT unique across orgs, use a composite range index instead: CREATE INDEX event_tildaid_org IF NOT EXISTS FOR (e:Event) ON (e.tildaid, e.orgId).
Add this index
CREATE CONSTRAINT event_tildaid_unique IF NOT EXISTS FOR (e:Event) REQUIRE e.tildaid IS UNIQUE
Expected: Finding the anchor should go from scanning the whole Event label to seeking one node. That probably cuts the slow outlier runs most, though how much depends on how many Event nodes exist. With only 6 runs a week the total time saved is small, but the worst-case response time should fall. · Evidence: Run SHOW INDEXES / SHOW CONSTRAINTS to confirm nothing covers Event.tildaid yet. Check uniqueness with MATCH (e:Event) WITH e.tildaid AS t, count(*) AS c WHERE c > 1 RETURN count(*). Run PROFILE before and after: the Event leaf should change from NodeByLabelScan + Filter to NodeUniqueIndexSeek (or NodeIndexSeek), and total db hits should drop. Then compare p95 over the next week.
index Index Site.tildaid for the per-row site lookup
The issue`OPTIONAL MATCH (site:Site {tildaid: es.scopeId})` runs once for every row that reaches it: one row per placeholder/scope after the earlier aggregations. The value comes from es, so this is planned as an Apply / Optional with its own lookup on the right-hand side. Without an index on Site.tildaid, every row does its own NodeByLabelScan(:Site) plus a Filter. The cost is roughly (rows) × (Site count).
The fixAdd an index on Site.tildaid so each row does an index seek. If Site ids are unique, use a constraint instead: CREATE CONSTRAINT site_tildaid_unique IF NOT EXISTS FOR (s:Site) REQUIRE s.tildaid IS UNIQUE.
Add this index
CREATE INDEX site_tildaid IF NOT EXISTS FOR (s:Site) ON (s.tildaid)
Expected: The site lookup should go from a label scan per row to a seek per row. The more placeholders an event has, the bigger the gain, so the large events behind the p95 should benefit most. · Evidence: In PROFILE, look at the operator under the Optional/Apply for `site`. Before the change it should show NodeByLabelScan(:Site) with rows ≈ placeholders × Site count; after, NodeIndexSeek(:Site(tildaid)) with about 1 row per input row. Also compare db hits before and after for an event with many placeholders.
rewrite Look up study once per event, not once per placeholder row
The issue`OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study)` depends only on e, but it is placed after the per-placeholder aggregations. So it re-expands from e for every ph/es row. If an event has several EventPlans or contexts, the rows multiply (ph × study × pd) before `head(collect(...))` reduces them again. `study` also has no label, so PLAN_CONTEXT is expanded to every kind of target node. Earlier in the query, the acceptedDoc × matchDoc OPTIONAL MATCHes are likewise multiplied per row before `collect(DISTINCT ...)`, and each multiplied row rebuilds the docAttributes pattern comprehensions.
The fixMove the study lookup to straight after the anchor and collect it once: `MATCH (e:Event {tildaid: $eventId, orgId: $orgId}) OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study:<StudyLabel>) WITH e, collect(DISTINCT study) AS studies`. Then carry `studies` through each WITH, and in the site step match `(site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {type: ?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WHERE study IN studies`. Also give `study` its real label so the planner can filter while it expands. Separately, wrap the matchDoc collection in a CALL { WITH ph, acceptedDoc ... } subquery so the docAttributes comprehensions are built once per matching document, not once per row of the acceptedDoc × matchDoc product. I can't give the full rewritten text: the statement in the input is cut off mid-expression (`acceptedDoc.primaryV...`), so these edits need to be applied to the source query by hand.
Expected: Fewer intermediate rows when an event has many placeholders or more than one plan context, which should mainly help the slow outlier calls. The size of the gain depends on the data and could be small if each event has a single EventPlan. · Evidence: PROFILE the slowest eventId: compare the row counts going into the `head(collect(site.name))` aggregation, and the db hits on the EVENTS/PLAN_CONTEXT Expand operators, before and after. Confirm the returned JSON is identical for a sample of events.

docsvc · 481203DA — 2 runs · p95 950 ms · 1 s total · 3d ago · proposed open shape & trend →

index Index Site.tildaid for the per-scope site lookup
The issue`OPTIONAL MATCH (site:Site {tildaid: scope.scopeId})` runs once for every EventScope row, inside the per-event CALL. The value comes from a variable, so if Site.tildaid has no index or uniqueness constraint, the planner has to do a NodeByLabelScan on Site plus a filter for each scope. That cost grows as scopes × sites, and it is the most likely reason a 2-call read takes 700–950 ms.
The fixRun SHOW INDEXES / SHOW CONSTRAINTS first. If Site.tildaid is not already covered, add a lookup index. A uniqueness constraint is better if tildaid is a real key. Either one turns each per-scope lookup into a NodeIndexSeek.
Add this index
CREATE INDEX site_tildaid IF NOT EXISTS FOR (s:Site) ON (s.tildaid)
Expected: If the scan is happening now, each per-scope lookup should go from scanning the whole Site label to a single seek. p95 could plausibly drop by a large fraction, maybe from about 950 ms into the low hundreds of ms. If an index already exists, nothing changes. The statement only ran twice in 7 days, so the total saving is about 1 s per week. This matters mainly if the call is on a user-facing page. · Evidence: Check SHOW INDEXES for Site(tildaid). Then run PROFILE on the statement before and after: the Site lookup should change from NodeByLabelScan + Filter to NodeIndexSeek, with far fewer db hits on that operator. Compare p95 on the next few runs.
index Index EventPlan.orgId to anchor the leading MATCH
The issueThe leading `MATCH (study:Study)<-[:PLAN_CONTEXT]-(:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event)` filters EventPlan by orgId. If no index covers orgId, the planner can only anchor on Study. If Study.tildaid is not indexed either, it falls back to a label scan of EventPlan or Study before the expansion to all Events.
The fixAdd a lookup index on EventPlan.orgId. The planner can then seek the org's plans directly, or choose between that and the Study.tildaid seek based on selectivity. This pattern is typical of multi-tenant schemas, so other docsvc queries filtering on orgId may benefit too.
Add this index
CREATE INDEX eventplan_orgid IF NOT EXISTS FOR (p:EventPlan) ON (p.orgId)
Expected: The leading MATCH should stop starting from a label scan. The gain depends on how many EventPlans there are in total versus per org. It is probably modest compared with the per-scope work, so this is a secondary win. · Evidence: Check SHOW INDEXES for EventPlan(orgId). In the PROFILE output, the first operator should be a NodeIndexSeek on EventPlan or Study, not NodeByLabelScan. Compare the db hits on the leading operators before and after.
index Make sure Study.tildaid IN $studyIds can seek
The issue`WHERE study.tildaid in $studyIds` is the most selective predicate in the query, since it is an explicit list of ids. It can only drive the plan if there is an index or constraint on Study.tildaid. Without one, Study is filtered after the expansion, so every Event in the org's plans gets expanded, and the per-event CALL subquery runs on rows that are then thrown away.
The fixIf tildaid is the Study key and has no uniqueness constraint, add one, or at least a range index. The IN-list then becomes a NodeIndexSeek over just the requested ids, and the plan expands from those studies through PLAN_CONTEXT.
Add this index
CREATE INDEX study_tildaid IF NOT EXISTS FOR (s:Study) ON (s.tildaid)
Expected: Fewer Event rows go into the expensive per-event CALL whenever the org has many studies but only a few are requested. Expect no change if a constraint already exists. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES for Study(tildaid). PROFILE should show NodeIndexSeek(study) with rows equal to size($studyIds), and fewer rows going into the Apply/CALL operator.

virtual-assistant · 54CFB8E7 — 4 runs · p95 900 ms · 1 s total · 3d ago · proposed open shape & trend →

index Index WorkOrder.work_order_id so the thread lookup can start from the work order instead of the org
The issueThe query starts with `MATCH (:Organization {tildaid: $org_id})<-[:WORK_ORDER_TO_ORGANIZATION]-(w:WorkOrder)` and then applies `(w.tildaid = $work_order_id OR w.work_order_id = $work_order_id)` as a filter. The OR across two properties can only use index seeks (a union of two seeks) if both properties are indexed. If `work_order_id` has no index, the planner has to fan out from the org to every one of its WorkOrders and test each one, even though the predicate identifies one thread. That fan-out matches the 900 ms p95 against a 248 ms average: big orgs pay much more.
The fixAdd a range index on WorkOrder.work_order_id. Also make sure WorkOrder.tildaid has a uniqueness constraint or index (it is probably the node key). With both in place, the planner can seek on each side of the OR, de-duplicate the results, and check the single ORGANIZATION relationship from there. It no longer has to expand the whole org.
Add this index
CREATE INDEX workorder_work_order_id IF NOT EXISTS FOR (w:WorkOrder) ON (w.work_order_id)
Expected: With both indexes, rows into the first filter should drop from the org's WorkOrder count to about 1. That could take the p95 from hundreds of ms to tens of ms for large orgs. The total saving is small, though, because this shape ran only 4 times in 7 days. · Evidence: Run `SHOW INDEXES` to see whether WorkOrder(tildaid) and WorkOrder(work_order_id) are covered. Then PROFILE the query before and after the change: look for NodeIndexSeek on WorkOrder (two seeks under a Union/Distinct) in place of an Expand(All) from Organization, with rows/db hits at the top of the plan near 1. Then compare p95 over the next week.
rewrite Drop the redundant EXISTS and read the activities once instead of four times
The issueThe same filtered set `(w)-[:WORK_ORDER_TO_ACTIVITY]->(:WorkOrderActivity)` with the same five predicates is read four times: once in the `EXISTS { ... }` block, once in the aggregating MATCH, once in the `CALL (w)` block for the latest email (`le`), and once in the `CALL (w)` block for `from_addresses`, which also sorts. The EXISTS adds nothing, because the MATCH right after it already drops any w with no matching activity. Each of the two CALL blocks reads the thread again and sorts it, so a long email thread is scanned and sorted several times.
The fixRemove the EXISTS block. Match the activities once, sort them by (coalesce(timestamp, createdat), tildaid) ascending, and collect them in that order while computing count/max/min/sum in the same aggregation. After pagination, the latest email is `acts[-1]`. Ascending order with the last element gives the same result as the original DESC LIMIT 1, including how nulls sort. `from_addresses` is a list comprehension over the already-ordered list. The tail of the statement (study/site OPTIONAL MATCHes and the last_snippet expression on `le`) is copied exactly as given; that part of the original is garbled, so check that it still refers to `le`.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (:Organization {tildaid: $org_id})<-[:WORK_ORDER_TO_ORGANIZATION]-(w:WorkOrder) WHERE w.delpending = false AND (w.tildaid = $work_order_id OR w.work_order_id = $work_order_id) AND (w.study_id IN $accessible_study_ids OR coalesce(w.study_id, ?) = ?) AND EXISTS { MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(m:WorkOrderActivity) WHERE m.activity_type IN $email_types AND (m.activity_type <> ? OR coalesce(m.status, ?) IN [?, ?]) AND (m.activity_type <> ? OR coalesce(m.status, ?) IN [?]) AND coalesce(m.ignoreType, ?) <> ? } MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(a:WorkOrderActivity) WHERE a.activity_type IN $email_types AND (a.activity_type <> ? OR coalesce(a.status, ?) IN [?, ?]) AND (a.activity_type <> ? OR coalesce(a.status, ?) IN [?]) AND coalesce(a.ignoreType, ?) <> ? WITH w, count(a) AS email_count, max(coalesce(a.timestamp, a.createdat)) AS last_email_at_ms, min(coalesce(a.timestamp, a.createdat)) AS first_email_at_ms, sum(CASE WHEN a.activity_type = ? AND a.status = ? THEN ? ELSE ? END) AS draft_count WITH w, email_count, last_email_at_ms, first_email_at_ms, draft_count ORDER BY last_email_at_ms DESC, w.tildaid DESC SKIP $skip LIMIT $limit // The newest email in the thread — what the list pane previews. Its body is // truncated here, not in Python: that is the point of a summaries-only page. CALL (w) { MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(le:WorkOrderActivity) WHERE le.activity_type IN $email_types AND (le.activity_type <> ? OR coalesce(le.status, ?) IN [?, ?]) AND (le.activity_type <> ? OR coalesce(le.status, ?) IN [?]) AND coalesce(le.ignoreType, ?) <> ? RETURN le ORDER BY coalesce(le.timestamp, le.createdat) DESC, le.tildaid DESC LIMIT ? } // Every From address in the thread, in chronological order. Deduped in Python, where // preserving first-seen order is one call; Cypher has no ordered set without APOC. CALL (w) { MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(pa:WorkOrderActivity) WHERE pa.activity_type IN $email_types AND (pa.activity_type <> ? OR coalesce(pa.status, ?) IN [?, ?]) AND (pa.activity_type <> ? OR coalesce(pa.status, ?) IN [?]) AND coalesce(pa.ignoreType, ?) <> ? WITH pa ORDER BY coalesce(pa.timestamp, pa.createdat) ASC, pa.tildaid ASC RETURN collect(CASE WHEN pa.activity_type = ? THEN coalesce(pa.sender, ?) ELSE coalesce(pa.from_ai_teammate_email, ?) END) AS from_addresses } OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(site:Site) RETURN w, email_count, last_email_at_ms, first_email_at_ms, draft_count, from_addresses, study.name AS study_name, site.name AS site_name, // The site NUMBER lives on the study?U_REPLY???U_REPLY???[]?[]??null??']), ?, $snippet_chars) AS last_snippet ORDER BY last_email_at_ms DESC, w.tildaid DESC
+ MATCH (:Organization {tildaid: $org_id})<-[:WORK_ORDER_TO_ORGANIZATION]-(w:WorkOrder) WHERE w.delpending = false AND (w.tildaid = $work_order_id OR w.work_order_id = $work_order_id) AND (w.study_id IN $accessible_study_ids OR coalesce(w.study_id, ?) = ?) MATCH (w)-[:WORK_ORDER_TO_ACTIVITY]->(a:WorkOrderActivity) WHERE a.activity_type IN $email_types AND (a.activity_type <> ? OR coalesce(a.status, ?) IN [?, ?]) AND (a.activity_type <> ? OR coalesce(a.status, ?) IN [?]) AND coalesce(a.ignoreType, ?) <> ? WITH w, a ORDER BY coalesce(a.timestamp, a.createdat) ASC, a.tildaid ASC WITH w, collect(a) AS acts, count(a) AS email_count, max(coalesce(a.timestamp, a.createdat)) AS last_email_at_ms, min(coalesce(a.timestamp, a.createdat)) AS first_email_at_ms, sum(CASE WHEN a.activity_type = ? AND a.status = ? THEN ? ELSE ? END) AS draft_count ORDER BY last_email_at_ms DESC, w.tildaid DESC SKIP $skip LIMIT $limit WITH w, email_count, last_email_at_ms, first_email_at_ms, draft_count, acts[-1] AS le, [pa IN acts | CASE WHEN pa.activity_type = ? THEN coalesce(pa.sender, ?) ELSE coalesce(pa.from_ai_teammate_email, ?) END] AS from_addresses OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(site:Site) RETURN w, email_count, last_email_at_ms, first_email_at_ms, draft_count, from_addresses, study.name AS study_name, site.name AS site_name, // The site NUMBER lives on the study?U_REPLY???U_REPLY???[]?[]??null??']), ?, $snippet_chars) AS last_snippet ORDER BY last_email_at_ms DESC, w.tildaid DESC
Expected: Activity expansions per work order should fall from 4 to 1, with one sort instead of two. Db hits on the activity side should drop by roughly 3–4×, which matters most for long threads. · Evidence: PROFILE the old and new statements on the same long thread. Compare total db hits and the number of Expand(All) operators on WORK_ORDER_TO_ACTIVITY (4 before, 1 after). Diff the returned rows to confirm email_count, draft_count, the last_snippet source and the from_addresses order are identical.
index Make sure WorkOrder.tildaid is backed by a uniqueness constraint
The issueThe other half of `w.tildaid = $work_order_id OR w.work_order_id = $work_order_id` also needs an index. Otherwise the OR cannot become an index union and the plan goes back to expanding from Organization, even after work_order_id is indexed. tildaid looks like the WorkOrder key, so it should be unique.
The fixIf `SHOW CONSTRAINTS` does not list a uniqueness constraint on WorkOrder.tildaid, create one. It provides the backing index for the seek and also guarantees one node per key.
Add this index
CREATE CONSTRAINT workorder_tildaid_unique IF NOT EXISTS FOR (w:WorkOrder) REQUIRE w.tildaid IS UNIQUE
Expected: Together with the work_order_id index, the WorkOrder lookup becomes two seeks returning at most a couple of rows. If the constraint already exists, nothing changes. · Evidence: Check `SHOW CONSTRAINTS` before creating it. In PROFILE, look for NodeUniqueIndexSeek or NodeIndexSeek on WorkOrder(tildaid) next to the work_order_id seek.

execsvc · 19371E20 — 1 runs · p95 975 ms · 1 s total · 3d ago · proposed open shape & trend →

rewrite Fold the Site match into EXISTS and apply the status filter in the first WHERE
The issue`MATCH (filterSite:Site{orgId:a.orgId}) WHERE filterSite.tildaid in $filterSiteIds AND EXISTS {...}` makes filterSite a row variable, and it is never returned. Each (a, r) row is multiplied by every matching site. The costly EXISTS path traversal (StudySiteActor → DslPlanDefinition → Site/Study) then runs once per (a, r, site) combination, and `WITH DISTINCT` has to collapse the copies again later. The filter `toString(u.status) = $actorFilter0` also runs only after the Role expansion, the per-site EXISTS and the ClinOp OPTIONAL MATCH, so all that work is done for users who get thrown away.
The fixTurn the Site lookup into a pure existence check on `a`, so it runs once per actor and does not multiply rows. Move the u.status equality into the first WHERE so rejected users are dropped before the Role expansion and the subquery. The output stays the same: filterSite was only used as a filter and was removed by DISTINCT, and the status predicate only reads u, which is bound at the start.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? MATCH (filterSite:Site{orgId:a.orgId}) WHERE filterSite.tildaid in $filterSiteIds AND EXISTS { MATCH (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(spd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(filterSite), (spd)-[:PLAN_DEFINITION_TO_STUDY]-(o) } optional match (co:ClinOp{deltimestamp:?, orgId:$orgId})-[:FOR_USER]-(u) with distinct o,a,r,u,scope,co where ( toString(u.status) = $actorFilter0 ) with o,a,u,scope,r,co order by r.displayName with o,a,u,scope,co,collect(distinct case when r.listed = true then r.displayName end) as displayRoles with o,a,u,scope,displayRoles,co order by a.createdat, a.tildaid limit $qpLimit RETURN apoc.create.vNode([?], { id: a.tildaid, role: a.roles, displayRoles: CASE WHEN size(displayRoles) = ? THEN [?] ELSE displayRoles END, state: a.state, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, userEmail: case when coalesce(co.actorEmail,?)<>? then co.actorEmail else u.email end, userId: u.tildaid, userName: case when coalesce(co.actorName,?)<>? then co.actorName else coalesce(u.name, u.email) end, userContact: case when coalesce(co.actorPhone,?)<>? then co.actorPhone else coalesce(u.contact,?) end, lastActivity: u.lastActivity, profilePicURL: u.profilePicURL, userTitle: u.title, createdat: scope.createdat, isBlinded: a.isBlinded , capabilities: scope.capabilities, applyToStudyRoleFlag: scope.applyToStudyRoleFlag }) as node
+ MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? AND ( toString(u.status) = $actorFilter0 ) AND EXISTS { MATCH (filterSite:Site) WHERE filterSite.tildaid IN $filterSiteIds AND filterSite.orgId = a.orgId MATCH (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(spd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(filterSite), (spd)-[:PLAN_DEFINITION_TO_STUDY]-(o) } MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? optional match (co:ClinOp{deltimestamp:?, orgId:$orgId})-[:FOR_USER]-(u) with distinct o,a,r,u,scope,co with o,a,u,scope,r,co order by r.displayName with o,a,u,scope,co,collect(distinct case when r.listed = true then r.displayName end) as displayRoles with o,a,u,scope,displayRoles,co order by a.createdat, a.tildaid limit $qpLimit RETURN apoc.create.vNode([?], { id: a.tildaid, role: a.roles, displayRoles: CASE WHEN size(displayRoles) = ? THEN [?] ELSE displayRoles END, state: a.state, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, userEmail: case when coalesce(co.actorEmail,?)<>? then co.actorEmail else u.email end, userId: u.tildaid, userName: case when coalesce(co.actorName,?)<>? then co.actorName else coalesce(u.name, u.email) end, userContact: case when coalesce(co.actorPhone,?)<>? then co.actorPhone else coalesce(u.contact,?) end, lastActivity: u.lastActivity, profilePicURL: u.profilePicURL, userTitle: u.title, createdat: scope.createdat, isBlinded: a.isBlinded , capabilities: scope.capabilities, applyToStudyRoleFlag: scope.applyToStudyRoleFlag }) as node
Expected: Fewer rows going into the Role expansion, EXISTS and OPTIONAL MATCH, and the subquery runs once per actor instead of once per actor × role × site. This could plausibly cut the ~660 ms by a large fraction for studies with many actors and several roles per actor. However, the statement ran only once in 7 days, so the total time saved is small. This is mainly a latency fix for the one screen that uses it. · Evidence: Run PROFILE on both versions with the same $scopeId, $filterSiteIds and $actorFilter0. Compare total db hits and the row counts going into the SemiApply/Apply for the EXISTS. Confirm both return the same result set on a few real studies.
index Index Site.tildaid so the $filterSiteIds lookup is a seek
The issue`filterSite.tildaid IN $filterSiteIds` (plus `orgId = a.orgId`) is the only way into :Site. If no index or constraint covers Site.tildaid, the planner falls back to a NodeByLabelScan on :Site with a property filter. That scan repeats for every actor row, or gets turned into a large hash join.
The fixCheck SHOW INDEXES / SHOW CONSTRAINTS first. If Site.tildaid is not already covered (for example by a uniqueness constraint), add a range index so the IN-list becomes a NodeIndexSeek returning only the few listed sites. The orgId equality is then checked on those few nodes.
Add this index
CREATE INDEX site_tildaid IF NOT EXISTS FOR (s:Site) ON (s.tildaid)
Expected: If a Site label scan is happening now, the Site access drops from O(#Sites) per probe to O(|$filterSiteIds|), probably tens of ms or more on large site sets. If a constraint already covers tildaid, there is no change and this can be skipped. · Evidence: EXPLAIN before and after: the Site operator should change from NodeByLabelScan + Filter to NodeIndexSeek(site_tildaid). Re-measure the statement's p95 once it has run a few times.