Xray
1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 24 hours▾
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 21:08:15.
› →|
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 16

What a model proposes for master'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.

workplansvc · F0A5B6CF — 7k runs · p95 13 ms · 56 s total · 4d ago · proposed open shape & trend →

index Lookup index on WorkPlanMilestoneItem.itemType to anchor the poller
The issueThe only predicate that can start the plan is `item.itemType = ?`. Every other item filter compares one property of the node with another (`checkpointPendingGenId > coalesce(checkpointLastComputedGenId, ?)`) or wraps the property in coalesce(), and an index can't serve either form. Without an index on itemType, the planner most likely runs a NodeByLabelScan over all WorkPlanMilestoneItem nodes (or over WorkplanCfg/WorkPlanMilestone) and then checks every filter on every row. This statement looks like a poller (about 1,000 runs a day), so it repeats that full scan whether or not any work is pending.
The fixCreate a range index on WorkPlanMilestoneItem(itemType) so the plan starts with NodeIndexSeek on the requested item type. The plan then only expands to milestone and cfg for items of that type. The coalesce and property-to-property filters still run per row, but only over the subset with that item type rather than the whole label.
Add this index
CREATE INDEX workplan_milestone_item_itemtype IF NOT EXISTS FOR (i:WorkPlanMilestoneItem) ON (i.itemType)
Expected: How much this helps depends on how selective itemType is. If the requested type is a small share of items, per-call time could fall from about 8 ms to roughly 1–3 ms, which would save most of the about 56 s of total time per week. If one type covers most items, the gain will be small, and suggestion 3 (the hint) or a dedicated 'pending' flag would be needed. · Evidence: Run PROFILE before and after creating the index. Check that the first operator changes from NodeByLabelScan(item) to NodeIndexSeek(item:WorkPlanMilestoneItem(itemType)) and that total db hits drop. Run `MATCH (i:WorkPlanMilestoneItem) RETURN i.itemType, count(*)` to check selectivity. Compare the shape's p95 over the week after the deploy.
index Unique constraint on Organization.tildaid for the OPTIONAL MATCH lookup
The issue`OPTIONAL MATCH (org:Organization {tildaid: cfg.orgid})` runs for each result row. If Organization.tildaid has no index or constraint, each of those lookups scans the Organization label. It also means more than one org can match a cfg, which would duplicate rows.
The fixIf the schema doesn't already have it, add a uniqueness constraint on Organization.tildaid. The constraint creates a backing index, so the optional match becomes a NodeUniqueIndexSeek, and it guarantees at most one org per cfg. If SHOW CONSTRAINTS already lists one, skip this.
Add this index
CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE
Expected: Each org lookup goes from a label scan to an O(1) seek. The overall saving grows with $limit and with how many Organization nodes there are. If a constraint already exists, nothing changes. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES for Organization. Then run PROFILE and check that the operator under the Optional / Apply branch is NodeUniqueIndexSeek, not NodeByLabelScan with a Filter.
rewrite Start from the indexed item, apply LIMIT before the org lookup
The issueBecause the pattern is written starting from cfg, the planner can choose to start the traversal at WorkplanCfg or WorkPlanMilestone and filter the items last. Also, LIMIT comes after the OPTIONAL MATCH, so nothing guarantees the Organization lookup runs only for the rows that are actually returned.
The fixWrite the pattern starting from item, with a `USING INDEX` hint on itemType (this needs the index from suggestion 1). Apply the item-only filters first, then expand to milestone and cfg. Take `LIMIT $limit` in a WITH before the OPTIONAL MATCH, so the org is looked up for at most $limit rows. Results are the same as long as tildaid is unique per org, which suggestion 2 guarantees.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (cfg:WorkplanCfg)-[:WORKPLAN_CFG_TO_MILESTONE]->(milestone:WorkPlanMilestone)-[:WORKPLAN_MILESTONE_TO_MILESTONE_ITEM]->(item:WorkPlanMilestoneItem) WHERE item.itemType = ? AND coalesce(item.cfgItemInvalid, false) = false AND milestone.scoringMode = ? AND item.checkpointPendingGenId > coalesce(item.checkpointLastComputedGenId, ?) AND (coalesce(item.checkpointGenerateRequestGenId, ?) <> item.checkpointPendingGenId OR $nowMs - coalesce(item.checkpointGenerateRequestSentAt, ?) >= ?) AND NOT (coalesce(item.checkpointUnsupportedGenId, ?) = item.checkpointPendingGenId AND coalesce(item.checkpointUnsupportedCount, ?) >= $maxUnsupported) AND coalesce(cfg.state, ?) <> ? OPTIONAL MATCH (org:Organization {tildaid: cfg.orgid}) RETURN item.tildaid AS cfgMilestoneItemId, cfg.tildaid AS cfgId, milestone.tildaid AS milestoneId, coalesce(item.itemConfig, ?) AS itemConfig, item.checkpointPendingGenId AS pendingGenId, coalesce(org.alias, ?) AS orgAlias, coalesce(item.checkpointPendingBy, ?) AS pendingBy LIMIT $limit
+ MATCH (item:WorkPlanMilestoneItem)
+ USING INDEX item:WorkPlanMilestoneItem(itemType)
+ WHERE item.itemType = ?
+ AND coalesce(item.cfgItemInvalid, false) = false
+ AND item.checkpointPendingGenId > coalesce(item.checkpointLastComputedGenId, ?)
+ AND (coalesce(item.checkpointGenerateRequestGenId, ?) <> item.checkpointPendingGenId OR $nowMs - coalesce(item.checkpointGenerateRequestSentAt, ?) >= ?)
+ AND NOT (coalesce(item.checkpointUnsupportedGenId, ?) = item.checkpointPendingGenId AND coalesce(item.checkpointUnsupportedCount, ?) >= $maxUnsupported)
+ MATCH (cfg:WorkplanCfg)-[:WORKPLAN_CFG_TO_MILESTONE]->(milestone:WorkPlanMilestone)-[:WORKPLAN_MILESTONE_TO_MILESTONE_ITEM]->(item)
+ WHERE milestone.scoringMode = ?
+ AND coalesce(cfg.state, ?) <> ?
+ WITH cfg, milestone, item
+ LIMIT $limit
+ OPTIONAL MATCH (org:Organization {tildaid: cfg.orgid})
+ RETURN item.tildaid AS cfgMilestoneItemId, cfg.tildaid AS cfgId, milestone.tildaid AS milestoneId, coalesce(item.itemConfig, ?) AS itemConfig, item.checkpointPendingGenId AS pendingGenId, coalesce(org.alias, ?) AS orgAlias, coalesce(item.checkpointPendingBy, ?) AS pendingBy
Expected: The plan should consistently start at the item index seek, and org lookups should be capped at $limit. If the planner already picks this plan, expect only a small improvement. If it currently starts at cfg or milestone, the improvement should be larger. · Evidence: Compare PROFILE of the old and new statement on the same parameters: same rows returned, first operator is NodeIndexSeek on item, fewer db hits, and the Optional branch runs at most $limit times. Then check p95 after the deploy.

virtual-assistant · 442678D3 — 2 runs · p95 1.90 s · 2 s total · 2d ago · proposed open shape & trend →

index Index WorkplanInstanceMilestoneItem.cfgMilestoneItemId so the workplan lookup stops scanning every instance
The issueThe clause `OPTIONAL MATCH (wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone)-[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem:WorkplanInstanceMilestoneItem) WHERE ... (wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id)` has no bound start node. Unless both sides of the OR can be index-seeked, the planner has to start from a WorkPlanInstance (or WorkplanInstanceMilestoneItem) label scan. It then expands instance→milestone→item and filters, once for every one of the up to $fetch_limit configs. This is probably the largest part of the ~1-1.9 s, and it grows with the size of the workplan graph, not the result size.
The fixAdd a range index on cfgMilestoneItemId. With an index on each OR branch, the Neo4j 5 planner can turn the OR into a Union/Distinct of two NodeIndexSeeks that use the b.wp_object_id argument. It can then walk backwards item→milestone→instance for just the 0–2 matching items, instead of scanning the whole workplan graph. Pair this with the tildaid constraint in the next suggestion. Without both, the OR still falls back to a scan.
Add this index
CREATE INDEX wp_item_cfg_milestone_item_id IF NOT EXISTS FOR (n:WorkplanInstanceMilestoneItem) ON (n.cfgMilestoneItemId)
Expected: If the plan switches to index seeks, the cost of the workplan lookup should go from proportional to all WorkPlanInstance subgraphs to a few db hits per config. I would expect the p95 to drop noticeably, possibly by hundreds of ms, but this depends on how big the workplan graph is. The overall gain is small because it ran only 2 times in 7 days, but each call is user-facing latency. · Evidence: Run PROFILE before and after with a real $org_id. Before, look for NodeByLabelScan on WorkPlanInstance or WorkplanInstanceMilestoneItem under the Apply for this OPTIONAL MATCH. After, look for NodeIndexSeek on WorkplanInstanceMilestoneItem(cfgMilestoneItemId) and (tildaid) under a Union/Distinct, with lower total db hits. Then compare p95 after the deploy.
index Add a uniqueness constraint (or index) on WorkplanInstanceMilestoneItem.tildaid
The issueThe other OR branch, `wpItem.tildaid = b.wp_object_id`, needs its own index. The planner can only replace the OR with index seeks if both properties are indexed. If tildaid on WorkplanInstanceMilestoneItem has no constraint or index, the whole predicate falls back to a label scan plus filter for each config row.
The fixFirst check SHOW CONSTRAINTS / SHOW INDEXES. If tildaid on WorkplanInstanceMilestoneItem is not already covered, create a uniqueness constraint. tildaid looks like the node key across this model, so uniqueness is likely correct and also gives the planner accurate cardinality. If duplicates exist, use a plain range index instead.
Add this index
CREATE CONSTRAINT wp_item_tildaid_unique IF NOT EXISTS FOR (n:WorkplanInstanceMilestoneItem) REQUIRE n.tildaid IS UNIQUE
Expected: Together with the cfgMilestoneItemId index, this should let the OR be planned as two index seeks. On its own it does little for the OR. It may also speed up other per-item lookups by tildaid elsewhere in the service. · Evidence: Run SHOW CONSTRAINTS WHERE 'WorkplanInstanceMilestoneItem' IN labelsOrTypes to see if it is already there. Then PROFILE this statement and confirm NodeUniqueIndexSeek or NodeIndexSeek on tildaid in the workplan branch.
index Make sure Actor.tildaid (and Partner/Site.tildaid) are indexed for the per-child recipient lookups
The issueFor every child WorkOrder row, the statement runs unanchored property lookups: `OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key})`, `OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User)` and `OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId})`. If any of these labels lacks an index or constraint on tildaid, each child row causes a full label scan. Actor in particular may not have one, because it is only reached through this indirect recipient_key join.
The fixRun SHOW CONSTRAINTS and add a uniqueness constraint for any of Actor, Partner or Site that lacks one on tildaid. The DDL below is for Actor; use the same pattern for Partner or Site if they are also missing. With the constraint in place, each lookup becomes a NodeUniqueIndexSeek driven by the w.recipient_key / wES.scopeId argument.
Add this index
CREATE CONSTRAINT actor_tildaid_unique IF NOT EXISTS FOR (n:Actor) REQUIRE n.tildaid IS UNIQUE
Expected: If one of these is currently a label scan, the cost changes from children × label size to children × O(1) seeks, which could be a large share of the p95. If all three are already indexed, there is no change, and the remaining cost is in the workplan branch and the repeated child traversal. · Evidence: In a PROFILE, check whether NodeByLabelScan or NodeIndexScan appears for Actor, Partner or Site under the per-child Apply operators. After the change, confirm NodeUniqueIndexSeek and lower db hits.

execsvc · 7178EFFA — 7 runs · p95 1.65 s · 2 s total · 1d ago · proposed open shape & trend →

rewrite Apply the site filter inside the UNION branches and drop the inner ORDER BY
The issueThe only site restriction, `toString(site.tildaid) = $cpFilter0`, sits in the WHERE of the second CALL subquery. Both UNION branches therefore return every (pd, site) pair for every study of the org, through Study→DslPlanDefinition→Site and Org→Smo→Site→DslPlanDefinition. Each of those pairs then runs the long User→Actor→StudySiteActor→pd→SOA→CarePlan→Patient→Phi expansion and the CarePlan filters before most rows are dropped for the wrong site. The work grows with the number of sites in the org, even though only one site is wanted, which fits the gap between the 255 ms average and the 1650 ms p95. Separately, `order by cp.createdat desc` inside the second subquery sorts rows that the outer query re-sorts with the same key before collecting, so that inner sort is wasted.
The fixMove `toString(site.tildaid) = $cpFilter0` into the WHERE of both UNION branches, so pd/site pairs for other sites are dropped before the per-row subquery runs, and remove it from the inner subquery. Also remove the inner `order by cp.createdat desc`, since the outer `order by cp.createdat desc` before the collect sets the final order. The results stay the same because `site` is passed into the second subquery unchanged and the filter only uses `site`.
Proposed rewrite
match (org:Organization{tildaid: $orgId})-[:STUDY_TO_ORG]- (st:Study) 
call (org,st) { 
match (org)-[:STUDY_TO_ORG]-(st)-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) 
where (org.orgType=? or org.orgType=?) and pd.delpending=false and toString(site.tildaid) = $cpFilter0 
return pd, site 
union 
match (org)-[:IS_AN_ORG]-(:Smo)-[:SITE_TO_SMO]-(site:Site)-[:PLAN_DEFINITION_TO_SITE]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(st) 
where pd.delpending=false and toString(site.tildaid) = $cpFilter0 
return pd,site } 
call (st,pd,site,org) { 
match (u:User {tildaid: $userId})<-[:FOR_USER]- (a:Actor{orgId:$orgId})-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor{delpending:false}) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd)-[:SOA_TO_PLAN_DEFINITION]- (soa:ScheduleOfActivities)-[:EXECUTE_SOA]-(cp:CarePlan{isDeleted:false})-[:RESEARCH_SUBJECT]- (p:Patient)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi) 
where a.state <> ? and case when org.orgType=? or $edcEnabledFilter then (cp.subjectid <> ?) else (cp.subjectid <> ? or not a.roles contains ?) end and coalesce(cp.mode, ?) <> ? 
with phi,soa,a,case when (ssa)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]-(:StudySiteDuty{delpending:false})-[:DUTY_TO_STUDYSITEDUTY]-(:Duty) then true else false end as hasDuties,cp 
with phi,cp,soa,a,hasDuties,null as nv 
call(cp){ 
optional match (cp)-[:ALL_CHANGELOG]-(clog:ChangeLog) 
where clog.type in [?,?,?] 
with clog 
order by clog.createdat desc 
return collect(clog) as cpLogs } 
return phi,cp,cpLogs,nv,hasDuties,soa, a.roles as roles } 
with st,phi,hasDuties,roles,cp,cpLogs,site,pd,nv,soa 
order by cp.createdat desc 
with st,phi,hasDuties,roles,collect({cp:cp,nv:nv,site:site,siteNumber:pd.siteNumber,soav:soa.siteVersion,cpLogs:cpLogs}) as cpList 
with st,phi,cpList,head(cpList) as ongoingCP,hasDuties,roles,size(cpList)-? as rescreenCount 
with st,phi,cpList,ongoingCP,hasDuties,roles,rescreenCount 
order by ongoingCP.cp.createdat desc 
with collect({st:st,phi:phi,ongoingCP:case when false then cpList else ongoingCP end,roles:roles,rescreenCount:rescreenCount,hasDuties:hasDuties}) as patient 
with patient,size(patient) as pCount 
unwind patient as p 
with pCount,p 
limit $qpLimit 
return pCount,p
Expected: The second subquery should run once per (study, matching site) instead of once per (study, any site). For orgs with many sites this could cut db hits and p95 by roughly the site fan-out (several times or more). For single-site orgs, little change. · Evidence: Run PROFILE before and after with the same p95-causing $orgId/$userId/$cpFilter0. Compare rows out of the UNION Apply and total db hits, and check the result sets are identical. Then watch p95 for the shape after deploy.
index Index Site.tildaid and compare it without toString()
The issueEven after moving it, `toString(site.tildaid) = $cpFilter0` wraps the property in a function. That rules out any index seek on Site, so the planner can only reach the site by expanding from Organization/Study and filtering each candidate. With the site id known up front, the plan could instead start from both ends: Organization by tildaid and Site by tildaid.
The fixIf Site.tildaid is stored as a string, which is likely given the toString/param comparison, change the predicate to `site.tildaid = $cpFilter0` and add a lookup index (or a uniqueness constraint if tildaid is a key). The planner can then seek the single Site and join it to the org/study side, or prune with a cheap hash join.
Add this index
CREATE INDEX site_tildaid IF NOT EXISTS FOR (s:Site) ON (s.tildaid)
Expected: With the bare-property predicate, the plan may switch to a NodeIndexSeek on Site, cutting the pd/site expansion to a handful of rows. This is a modest extra gain on top of the rewrite, and it depends on whether the planner picks the seek. · Evidence: First confirm the property type with `MATCH (s:Site) RETURN DISTINCT valueType(s.tildaid)`, since dropping toString is only safe if the values are strings. Then check that EXPLAIN shows NodeIndexSeek on site_tildaid and that PROFILE db hits drop.
index Make sure the User and Actor anchors are indexed
The issueIn the second subquery, `(u:User {tildaid: $userId})<-[:FOR_USER]-(a:Actor{orgId:$orgId})` is a good anchor because it is fixed per call. Without an index on User.tildaid the planner may instead expand from the bound `pd` through SOA→CarePlan→Patient for every row, which is far more expensive. The same can happen if it picks Actor and has no lookup on Actor.orgId.
The fixMake sure User.tildaid has a uniqueness constraint or index so the subquery can seek the user and expand to its few Actors before going to StudySiteActor→pd. Optionally add an Actor(orgId) index so the planner can filter actors cheaply.
Add this index
CREATE CONSTRAINT user_tildaid_unique IF NOT EXISTS FOR (u:User) REQUIRE u.tildaid IS UNIQUE
Expected: If no index exists today, the subquery would anchor on a single User node instead of scanning or expanding broadly. That could remove a large part of the per-row cost. If the constraint already exists, nothing changes. · Evidence: Run SHOW INDEXES and SHOW CONSTRAINTS to see whether User.tildaid and Actor.orgId are already covered. Check PROFILE for NodeByLabelScan on User/Actor, or for the subquery anchoring on pd/cp instead of User.

virtual-assistant · C86158F7 — 1 runs · p95 1.95 s · 2 s total · 1d ago · proposed open shape & trend →

index Index WorkplanInstanceMilestoneItem lookup keys so the workplan OPTIONAL MATCH can seek instead of scan
The issue`OPTIONAL MATCH (wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone)-[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem) WHERE (wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id)` is not tied to `b` by any relationship. It is joined only through an OR across two properties. If either property has no index, the planner cannot run an index seek for each side of the OR. It falls back to a label scan of WorkPlanInstance or WorkplanInstanceMilestoneItem plus expansion for each of up to $fetch_limit config rows (or a Cartesian product joined afterwards). That is likely the largest share of the ~1.7 s.
The fixAdd a lookup index on cfgMilestoneItemId, and make sure tildaid on the same label is also indexed: `CREATE CONSTRAINT wpi_milestone_item_tildaid IF NOT EXISTS FOR (n:WorkplanInstanceMilestoneItem) REQUIRE n.tildaid IS UNIQUE`, or a plain index if values are not unique. Once both sides of the OR are indexed, the Neo4j 5 planner can answer the OR with a union of two NodeIndexSeeks on wpItem, then expand backwards to the milestone and instance. The other option is scanning every workplan instance for every config.
Add this index
CREATE INDEX wpi_milestone_item_cfg_id IF NOT EXISTS FOR (n:WorkplanInstanceMilestoneItem) ON (n.cfgMilestoneItemId)
Expected: For configs where wp_object_id is set, this step should change from work that grows with the total WorkPlanInstance/item count to a few index seeks per config row. That could plausibly remove a large part of the 1.6–1.9 s, but this is unverified. The statement ran only once in 7 days, so the total saving is small unless call volume grows. · Evidence: PROFILE the statement before and after. Before: look for NodeByLabelScan on WorkPlanInstance or WorkplanInstanceMilestoneItem, or a CartesianProduct/ValueHashJoin, with high db hits. After: look for NodeIndexSeek (inside a Union/Distinct) on WorkplanInstanceMilestoneItem(cfgMilestoneItemId) and (tildaid), with db hits dropping in proportion. Also run SHOW INDEXES to check which of the two properties is already covered.
index Unique constraint on Partner.tildaid (and Actor.tildaid) for the per-child recipient_key lookups
The issue`OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key})` and `OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User)` run once per child WorkOrder row, after the child fan-out. Neither is anchored by a relationship. Without an index or constraint on tildaid, each one is a full NodeByLabelScan of Partner or Actor for every child row, so the cost grows as configs × children × label size.
The fixCreate a uniqueness constraint (which comes with a backing range index) on Partner.tildaid. Do the same for Actor: `CREATE CONSTRAINT actor_tildaid IF NOT EXISTS FOR (a:Actor) REQUIRE a.tildaid IS UNIQUE`. If duplicate tildaid values exist, constraint creation will fail. In that case use a plain `CREATE INDEX ... ON (n.tildaid)` instead.
Add this index
CREATE CONSTRAINT partner_tildaid IF NOT EXISTS FOR (p:Partner) REQUIRE p.tildaid IS UNIQUE
Expected: Each per-row lookup should become one NodeUniqueIndexSeek instead of a label scan. With many child WOs per page, that could plausibly cut hundreds of milliseconds, but this is unverified. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES to see whether Partner.tildaid and Actor.tildaid are already covered. In PROFILE, look for NodeByLabelScan(Partner) / NodeByLabelScan(Actor) changing to NodeUniqueIndexSeek, with fewer db hits on those operators.
index Unique constraint on Site.tildaid for the EventScope site resolution (and check Organization.tildaid)
The issue`OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId}) WHERE wES.scopeType = ?` looks up a Site by property for every child row, with no relationship anchor. If Site.tildaid has no index, this is a Site label scan for each row. The query also starts with `MATCH (o:Organization {tildaid: $org_id})`. If Organization.tildaid is not constrained, the first step is a full Organization scan too.
The fixCreate a uniqueness constraint on Site.tildaid so the scopeId lookup becomes a unique index seek. Also check that Organization.tildaid is covered: `CREATE CONSTRAINT organization_tildaid IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE`. Most schemas already have this. Add it only if SHOW CONSTRAINTS shows it is missing.
Add this index
CREATE CONSTRAINT site_tildaid IF NOT EXISTS FOR (s:Site) REQUIRE s.tildaid IS UNIQUE
Expected: If Site.tildaid is unindexed today, the Site lookup should change from a label scan per child row to one seek per row. If it is already indexed, nothing changes. The size of the benefit depends on Site count and how many children are Event-type. · Evidence: Check SHOW CONSTRAINTS for Site and Organization tildaid. In PROFILE, look for NodeByLabelScan(Site) or NodeByLabelScan(Organization) being replaced by NodeUniqueIndexSeek, and for a lower p95 on later executions.

execsvc · B131B5A3 — 3 runs · p95 1.85 s · 1 s total · 3d ago · proposed open shape & trend →

index Index Query.careplanId for the supersede subquery's anchor
The issueThe second CALL subquery starts at `MATCH (q:Query {careplanId: $carePlanId})-[:BELONGS_TO]->(aq:AutoQueryConfig)`. careplanId is not the node key, so unless an index exists the planner has to NodeByLabelScan every :Query node and then filter. Nothing else in the subquery narrows the search earlier. The `NOT q.state IN $closedQueryStates` and `coalesce(q.supersededAt, ?) = ?` filters can't be served by an index (negation and a function call), so careplanId is the only predicate that can limit the scan. That label scan is the most likely cause of the 1850 ms p95 against a 410 ms average.
The fixAdd a range index on :Query(careplanId) so the anchor becomes a NodeIndexSeek that returns only one care plan's queries. state and supersededAt are then filtered on that small set. The statement only ran 3 times in 7 days (1.2 s total), so the overall time saved is small. The benefit is mostly a lower tail latency on the write path and less time holding locks while the CREATEs run.
Add this index
CREATE INDEX query_careplanId IF NOT EXISTS FOR (q:Query) ON (q.careplanId)
Expected: If the plan currently shows a NodeByLabelScan on Query, per-call time should drop roughly in proportion to (all :Query nodes) / (queries for this care plan), likely from hundreds of ms to single-digit ms. If an index already exists, expect no change. · Evidence: Run SHOW INDEXES and check for :Query(careplanId). Then PROFILE the statement and compare db hits on the Query operator: it should go from NodeByLabelScan+Filter to NodeIndexSeek. Check p95 again after the deploy. Because there are only 3 calls a week, judge the result from PROFILE db hits more than from p95.
index Make sure ProtocolVersionV2.tildaid is backed by a uniqueness constraint
The issueThe first clause, `OPTIONAL MATCH (pv:ProtocolVersionV2 {tildaid: $effectiveVersionId})`, drives the whole statement. Both subqueries are correlated on pv. If tildaid has no index, every call starts with a label scan of all protocol versions. Without a uniqueness guarantee, the planner also can't assume this returns at most one row, so the CALL subqueries could run once per duplicate.
The fixtildaid looks like an application-generated UUID key, so make it a uniqueness constraint. That gives the lookup an index and guarantees one row (or none) for the rest of the query. If a constraint already exists, this suggestion doesn't apply.
Add this index
CREATE CONSTRAINT protocolversionv2_tildaid IF NOT EXISTS FOR (p:ProtocolVersionV2) REQUIRE p.tildaid IS UNIQUE
Expected: If no index exists today, the pv lookup becomes a constant-time NodeUniqueIndexSeek, saving a scan on every call. If the constraint is already there, expect no change. · Evidence: Run SHOW CONSTRAINTS for ProtocolVersionV2. The PROFILE should show NodeUniqueIndexSeek(pv) as the first operator. Before creating the constraint, check for duplicates with: MATCH (p:ProtocolVersionV2) WITH p.tildaid AS k, count(*) AS c WHERE c > 1 RETURN k, c.
rewrite Stop early when pv is missing instead of filtering inside the write subquery
The issueThe not-found case isn't handled until the second CALL, in `WHERE pv IS NOT NULL` placed after `MATCH (q:Query {careplanId: ...})-[:BELONGS_TO]->(aq)`. So when the version doesn't exist, the engine still expands every Query → AutoQueryConfig pair for the care plan before throwing them all away. The first CALL also does a pointless OPTIONAL MATCH expansion from a null pv.
The fixCheck `pv IS NOT NULL` in its own WITH ... WHERE immediately after the pv lookup, or have the application check for the version first and skip the statement when it's missing. Then neither subquery runs for a missing version. I'm not including a full rewritten statement because the shape as captured is truncated and garbled in the middle (around the soft-delete comment and the changelog CREATE section). Any diff I produced would have to invent clauses. The change is to add `WHERE pv IS NOT NULL` after the line `WITH pv, ... AS versionLabel` and delete it from the inner MATCH. One catch: if found=false must still come back as a row, use a CASE-guarded CALL or a separate existence check in the application, because filtering rows out early would otherwise return zero rows.
Expected: This only changes calls where the version doesn't exist, cutting their cost to a single index seek. Calls where the version is found should be unchanged. With 3 calls a week the gain is small, so do it after the index. · Evidence: Get the full un-normalised statement text from execsvc. PROFILE it with an effectiveVersionId that doesn't exist and check that the Query/BELONGS_TO operators show 0 rows and 0 db hits. Confirm the caller handles found=false (or no row at all) the same way as before.

virtual-assistant · B464AC8D — 3 runs · p95 1.85 s · 1 s total · 4d ago · proposed open shape & trend →

rewrite Start the workplan lookup from the milestone item, and run the study/creator/template lookups after LIMIT
The issueThe workplan OPTIONAL MATCH `(wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone)-[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem)` has no node already tied to `b`. The only link to `b` is in the WHERE clause: `wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id`. Because that is an OR across two properties, the planner usually can't seek on either one. It tends to scan every WorkPlanInstance (or every item), walk all instance→milestone→item paths, and filter, once for each of up to $fetch_limit configs. This is the most likely cause of the 1850 ms max. Separately, the OPTIONAL MATCHes for study, creator and tmpl sit before `ORDER BY ... LIMIT $fetch_limit`, so they expand for every matching config, including the ones LIMIT then throws away.
The fixMove the three 1:1 OPTIONAL MATCHes (study, creator, tmpl) after the LIMIT so they only run for the configs you keep. Replace the workplan OPTIONAL MATCH with a CALL subquery that has two UNION branches. Each branch starts from WorkplanInstanceMilestoneItem through an equality on one property (`{tildaid: b.wp_object_id}` or `{cfgMilestoneItemId: b.wp_object_id}`) and walks backwards to WorkPlanInstance. Each branch can then use an index seek (see the index suggestion). The coalesce sentinel guard stays in each branch, and `head(collect(...))` ignores the null rows OPTIONAL MATCH returns, so workplan_id and workplan_name keep their meaning. Note: the captured statement is cut off after its final `WITH ... wo`. That tail is copied as-is and must be filled in from the source.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND b.study_id = $study_id AND b.study_id IN $lists.accessible_study_ids OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(wc:WorkOrder) WHERE wc.delpending = false AND wc.status <> ? WITH b, count(wc) AS _nonarchived_count WHERE b.status <> ? OR _nonarchived_count > ? OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_STUDY]->(study:Study) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CREATOR]->(creator:User) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) WITH b, study, creator, tmpl, b.createdat AS _sort_key ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC LIMIT $fetch_limit WITH b, study, creator, tmpl, _sort_key // Resolve WorkPlanInstance id/name for workplan-created configs (wp_object_id set). // Mirrors get_workplan_info_for_work_order_config: cfg.wp_object_id -> milestone item // (by tildaid or cfgMilestoneItemId) -> WorkPlanInstance. Non-workplan configs yield null. OPTIONAL MATCH (wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone) -[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem:WorkplanInstanceMilestoneItem) WHERE coalesce(b.wp_object_id, ?) <> ? AND (wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id) WITH b, study, creator, tmpl, _sort_key, head(collect(wpInstance.tildaid)) AS workplan_id, head(collect(wpInstance.name)) AS workplan_name OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(w:WorkOrder) WHERE w.delpending = false AND w.status <> ? OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(wStudy:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(wSite:Site) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_CREATOR]->(wCreator:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ASSIGNEE]->(wAssignee:User) // Resolve EventScope for Event-type child WOs (null for Standard-type) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_EVENT_SCOPE]->(wES:EventScope) // Look up the Site node only for Site-scoped events; guards against null wES and non-Site scopes. // wES.scopeId is a site tildaid (UUID) for Site scopes, country name for Country, ? for Trial. OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId}) WHERE wES.scopeType = ? OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key}) OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_TASK]->(wTask:WorkOrderTask) WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, w, wStudy, wSite, wCreator, wAssignee, wES, wES_Site, wPartner, wMember, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_pending_count, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_resolved_count // ENG-?: order children in the DB so children_limit keeps the first N in // DISPLAY order. collect() below preserves incoming row order. // The PARENT keyset terms MUST lead. This ORDER BY runs in a WITH stage AFTER // the parent ORDER BY/LIMIT, and a later ORDER BY re-sorts the whole row stream // — ordering on the child value alone silently re-ranked the CONFIGS by their // child?s QUERY order, not display order: the backward // reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC, (w.createdat IS NULL), w.createdat DESC, w.tildaid WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, collect(DISTINCT CASE WHEN w IS NOT NULL THEN { tildaid: w.tildaid, work_order_id: w.work_order_id, title: w.title, work_order_type: w.work_order_type, status: w.status, priority: w.priority, creator_id: w.creator_id, assigned_to_id: w.assigned_to_id, site_id: w.site_id, study_id: w.study_id, document_ids: w.document_ids, contact_list: w.contact_list, cc_list: w.cc_list, goal: w.goal, due_date: w.due_date, last_reachout: w.last_reachout, last_response: w.last_response, resolved_at: w.resolved_at, need_attention_reason: w.need_attention_reason, accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, // Mandatory documents this child WO still expects to accept. Pattern // comprehension (like site_number below) keeps the task→subtask // traversal out of the children collect()/count aggregation. Predicate // MUST stay in sync with WorkOrderRepository._count_expected_documents. expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags // Aggregates are computed over the FULL matching child set, BEFORE the // children_limit slice, so a truncated page still renders a correct parent row // (ENG-?). Without these the client can only derive them from `children`, // which is exactly what stops it asking for fewer. WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _late_flags, [child IN _children_raw WHERE child IS NOT NULL] AS _children WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, _children, size(_children) AS _child_total, reduce(acc = ?, c IN _children | acc + coalesce(c.pending_count, ?)) AS _agg_pending, reduce(acc = ?, c IN _children | acc + coalesce(c.resolved_count, ?)) AS _agg_resolved, size([f IN _late_flags WHERE f = true]) > ? AS _has_late_reply, (size(_children) > ? AND all(c IN _children WHERE c.status = ?)) AS _all_draft RETURN { tildaid: b.tildaid, work_order_config_id: COALESCE(b.work_order_config_id, substring(b.tildaid, ?, ?)), study_id: b.study_id, title: b.title, email_subject: b.email_subject, instructions: b.instructions, email: b.email, recipients: b.recipients, nonsite_recipients: b.nonsite_recipients, site_recipients: b.site_recipients, event_scope_recipients: b.event_scope_recipients, scope_recipients: b.scope_recipients, tasks: b.tasks, // ENG-?: without this, GET /work-order-configs returns plan_steps: [] // for every config and _raw_plan_steps() is dead code. plan_steps: b.plan_steps, plan_instructions: b.plan_instructions, status: b.status, work_order_config_type: b.work_order_config_type, entity_type: b.entity_type, event_id: b.event_id, progress: b.progress, child_count: b.child_count, sent_count: b.sent_count, pending_count: b.pending_count, child_creation_pending: coalesce(b.child_creation_pending, false), // ENG-?: the client recomputes child_creation_pending from these two // (isChildCreationPending), so an unprojected pair reads as ? // and kills the row spinner and the spawn poll. Both, or neither. activated_at: b.activated_at, children_created_at: b.children_created_at, creator_id: b.creator_id, org_id: b.org_id, document_ids: b.document_ids, context_document_ids: COALESCE(b.context_document_ids, []), reasoning: b.reasoning, reasoning_summary: b.reasoning_summary, warning: b.warning, error: b.error, createdat: b.createdat, updatedat: b.updatedat, studyName: CASE WHEN coalesce(b.study_id, ?) = ? THEN ? ELSE COALESCE(study.name, ?) END, creatorName: COALESCE(creator.firstName + ? + creator.lastName, creator.email, ?), // $children_limit < ? is the ? sentinel: a null upper bound makes // the whole slice null in Cypher, so the unlimited case takes the CASE branch. children: CASE WHEN $children_limit < ? THEN _children ELSE _children[?..$children_limit] END, // ENG-? additive aggregates. Always over the full matching child set. child_total: _child_total, children_pending_count: _agg_pending, children_resolved_count: _agg_resolved, has_late_reply: _has_late_reply, all_children_draft: _all_draft, template_id: tmpl.tildaid, template_title: tmpl.title, workplan_id: workplan_id, workplan_name: workplan_name } as work_order_config_with_children, _sort_key // Re-assert the parent order AFTER the child aggregation. The child ORDER BY // above leads with these same terms, but group emission order out of an // aggregation is not guaranteed by Cypher — and the Python loop trims the // lookahead by position, so a re-ordered stream drops the WRONG config and // builds cursors from the wrong boundary. Query order, not display order: // the backward reversal happens later in Python. ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC
+ MATCH (o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND b.study_id = $study_id AND b.study_id IN $lists.accessible_study_ids OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(wc:WorkOrder) WHERE wc.delpending = false AND wc.status <> ? WITH b, count(wc) AS _nonarchived_count WHERE b.status <> ? OR _nonarchived_count > ? WITH b, b.createdat AS _sort_key ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC LIMIT $fetch_limit OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_STUDY]->(study:Study) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CREATOR]->(creator:User) OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) WITH b, study, creator, tmpl, _sort_key // Resolve WorkPlanInstance id/name for workplan-created configs (wp_object_id set). // Anchored on the milestone item by an indexed equality per branch instead of an OR filter. CALL { WITH b OPTIONAL MATCH (wpItem:WorkplanInstanceMilestoneItem {tildaid: b.wp_object_id})<-[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]-(:WorkPlanMilestone)<-[:WORKPLAN_INSTANCE_TO_MILESTONE]-(wpInstance:WorkPlanInstance) WHERE coalesce(b.wp_object_id, ?) <> ? RETURN wpInstance UNION WITH b OPTIONAL MATCH (wpItem:WorkplanInstanceMilestoneItem {cfgMilestoneItemId: b.wp_object_id})<-[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]-(:WorkPlanMilestone)<-[:WORKPLAN_INSTANCE_TO_MILESTONE]-(wpInstance:WorkPlanInstance) WHERE coalesce(b.wp_object_id, ?) <> ? RETURN wpInstance } WITH b, study, creator, tmpl, _sort_key, head(collect(wpInstance.tildaid)) AS workplan_id, head(collect(wpInstance.name)) AS workplan_name OPTIONAL MATCH (b)-[:WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER]->(w:WorkOrder) WHERE w.delpending = false AND w.status <> ? OPTIONAL MATCH (w)-[:WORK_ORDER_TO_STUDY]->(wStudy:Study) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_SITE]->(wSite:Site) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_CREATOR]->(wCreator:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_ASSIGNEE]->(wAssignee:User) // Resolve EventScope for Event-type child WOs (null for Standard-type) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_EVENT_SCOPE]->(wES:EventScope) OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId}) WHERE wES.scopeType = ? OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key}) OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User) OPTIONAL MATCH (w)-[:WORK_ORDER_TO_TASK]->(wTask:WorkOrderTask) WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, w, wStudy, wSite, wCreator, wAssignee, wES, wES_Site, wPartner, wMember, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_pending_count, sum(CASE WHEN wTask.status = ? THEN ? ELSE ? END) AS w_resolved_count ORDER BY (CASE WHEN _sort_key IS NULL THEN ? ELSE ? END) ASC, _sort_key DESC, b.tildaid DESC, (w.createdat IS NULL), w.createdat DESC, w.tildaid WITH b, study, creator, tmpl, workplan_id, workplan_name, _sort_key, collect(DISTINCT CASE WHEN w IS NOT NULL THEN { tildaid: w.tildaid, work_order_id: w.work_order_id, title: w.title, work_order_type: w.work_order_type, status: w.status, priority: w.priority, creator_id: w.creator_id, assigned_to_id: w.assigned_to_id, site_id: w.site_id, study_id: w.study_id, document_ids: w.document_ids, contact_list: w.contact_list, cc_list: w.cc_list, goal: w.goal, due_date: w.due_date, last_reachout: w.last_reachout, last_response: w.last_response, resolved_at: w.resolved_at, need_attention_reason: w.need_attention_reason, accepted_count: w.accepted_count, pending_count: w_pending_count, resolved_count: w_resolved_count, expected_documents_count: size([(w)-[:WORK_ORDER_TO_TASK]->(t2:WorkOrderTask)-[:TASK_HAS_SUBTASK]->(s:WorkOrderSubtask) WHERE coalesce(t2.status, ?) <> ? AND coalesce(s.is_optional, false) = false AND coalesce(s.status, ?) <> ? AND (coalesce(s.expects_document, false) = true OR s.expects_document IS NULL OR s.placeholder_id IS NOT NULL OR s.sent_doc_id IS NOT NULL OR s.received_doc_id IS NOT NULL) | s]), createdat: w.createdat, updatedat: w.updatedat, thread_id: w.thread_id, rfc_msgid: w.rfc_msgid, parent_id: w.parent_id, // The child? ?Unknown User? ?Unknown User?site?Site?country?Country?trial?Trial?study?Trial???Organization??partner?Partner??studyteam?Study Team?Site?Site?Country?partner??Partner?studyTeam?? ???? ????Study Team?Non-site?s // `late_reply`: that field is projected only when include_late_reply AND a // child_status filter are both supplied, so reading it here reported False // for every unfiltered list (Codex review, ENG-?). collect(DISTINCT CASE WHEN w IS NOT NULL AND (w.status = ? AND w.last_response IS NOT NULL AND coalesce(w.resolved_at, w.status_updated_at) IS NOT NULL AND w.last_response > coalesce(w.resolved_at, w.status_updated_at) AND w.last_response > coalesce(w.late_reply_ack_at, ?)) THEN true ELSE null END) AS _late_flags WITH b, study, creator, tmpl, workplan_id, wo
Expected: The workplan step should go from a label scan plus path expansion per config to one or two index seeks plus a two-hop expansion per config. On a large workplan graph that could cut the slow runs from about 1.8 s to tens of ms. This is hedged: it depends on how many WorkPlanInstance and item nodes exist. With 3 runs a week the total time saved is small, but the worst-case latency for the user should improve noticeably. · Evidence: PROFILE the old and new versions with the same parameters. For the old version, confirm a NodeByLabelScan or AllNodesScan on WorkPlanInstance or WorkplanInstanceMilestoneItem with high db hits under the Apply. For the new version, confirm NodeIndexSeek or NodeUniqueIndexSeek on WorkplanInstanceMilestoneItem in both UNION branches. Check that each config has at most one STUDY, CREATOR and TEMPLATE relationship, so moving those matches after LIMIT doesn't change which configs are returned. Compare the result sets for parity, then watch p95 after the deploy.
index Index WorkplanInstanceMilestoneItem.cfgMilestoneItemId so the workplan lookup can seek
The issueThe workplan step compares `wpItem.cfgMilestoneItemId = b.wp_object_id`. `cfgMilestoneItemId` isn't the node key, and nothing suggests it has an index. Even after the rewrite above, the cfgMilestoneItemId branch would fall back to a label scan of WorkplanInstanceMilestoneItem for each config without one. tildaid is probably already covered by a uniqueness constraint, but check that too.
The fixCreate a range index on cfgMilestoneItemId. If `SHOW CONSTRAINTS` shows no uniqueness constraint on WorkplanInstanceMilestoneItem.tildaid, add one, or at least a range index on tildaid as well. Together these let both UNION branches do a NodeIndexSeek.
Add this index
CREATE INDEX workplan_item_cfg_milestone_item_id IF NOT EXISTS FOR (n:WorkplanInstanceMilestoneItem) ON (n.cfgMilestoneItemId)
Expected: Each per-config lookup of a workplan item should drop from scanning every item to a seek that returns about one row. The gain grows with the number of milestone items. Most of the slow-run improvement should come from this combined with the rewrite. · Evidence: `SHOW INDEXES WHERE 'WorkplanInstanceMilestoneItem' IN labelsOrTypes` before and after. PROFILE of the rewritten query should show NodeIndexSeek on cfgMilestoneItemId in the second branch. The index should be ONLINE before you compare p95.
index Composite index on WorkOrderConfig(study_id, delpending) for the config filter
The issueConfigs are only reached by expanding from `(o:Organization {tildaid: $org_id})` over WORK_ORDER_CONFIG_TO_ORGANIZATION. The filters `b.delpending = false AND b.study_id = $study_id` are then checked on every config the organization owns. For a large organization, that means reading every config's properties and then running the child-WorkOrder count expansion, when only one study's configs are needed.
The fixAdd a composite range index on (study_id, delpending). The planner can then choose to seek configs by study and check the organization relationship afterwards, which is cheaper when an organization has many studies. It only helps if the study is more selective than the organization, so let PROFILE decide.
Add this index
CREATE INDEX work_order_config_study_delpending IF NOT EXISTS FOR (n:WorkOrderConfig) ON (n.study_id, n.delpending)
Expected: Might cut the rows feeding the child count from all of the organization's configs down to one study's configs. Whether it helps depends on how the data is spread. If the planner keeps anchoring on Organization, it does nothing. · Evidence: Count configs per organization and per (organization, study) to check selectivity. After creating the index, PROFILE should show a NodeIndexSeek on WorkOrderConfig(study_id, delpending) and fewer rows going into the OPTIONAL MATCH on WORK_ORDER_CONFIG_TO_CHILD_WORK_ORDER. If the plan doesn't change, drop the index.

docsvc · 21C57270 — 1 runs · p95 1.95 s · 1 s total · 1d ago · proposed open shape & trend →

index Make sure the Document.tildaid entry point uses an index seek
The issueThe first clause, `MATCH (doc:Document) WHERE doc.tildaid IN $lists.documentIds0`, sets the size of everything after it. If Document.tildaid has no constraint or index, Neo4j has to scan every Document node and test each one against the list before any of the per-document subqueries run. The other entry points (Organization, Study and User by tildaid) are probably key lookups already, so this is the one most likely to cost real time. Low priority: the statement ran only once in 7 days, for 1.2 s total.
The fixCheck SHOW CONSTRAINTS / SHOW INDEXES for a uniqueness constraint on (:Document {tildaid}). tildaid looks like the service's node key, so it probably exists already. If it doesn't, add the constraint. The planner can then turn the IN list into one seek per id instead of a full label scan. Do the same check for Organization, Study and User tildaid, since this query also looks those up by tildaid.
Add this index
CREATE CONSTRAINT document_tildaid_unique IF NOT EXISTS FOR (d:Document) REQUIRE d.tildaid IS UNIQUE
Expected: If the constraint is missing, the first step goes from a full scan of Document nodes (cost grows with the size of the label) to roughly |documentIds0| seeks, which could save a large part of the ~1.2 s. If the constraint already exists, nothing changes. · Evidence: Run EXPLAIN/PROFILE on the statement. Today, look for NodeByLabelScan + Filter on Document; after the change, look for NodeUniqueIndexSeek(Locking) or NodeIndexSeek on Document(tildaid). Compare db hits on that first operator.
rewrite Build the study/actor/policy context once per study, not once per document
The issueThe query collects the documents, UNWINDs them, matches Organization again, and then runs `CALL { WITH org, doc UNWIND [doc.studyId] ... }` for every document row. That call re-resolves the Study, the STUDY_TO_ACTOR → FOR_USER actor, the site memberships (StudySiteActor → DslPlanDefinition → Site), the StudyCountryActor memberships, and the DocumentAccessPolicy EXISTS checks. Documents in one request usually share one or a few studies, so this heavy multi-hop work repeats N times with the same result. The `collect(DISTINCT doc)` / `count` / `UNWIND` at the start, and matching `org` per row, also add rows without changing the result.
The fixMatch Organization once at the top. Compute the distinct study ids first (`WITH collect(DISTINCT doc.studyId) AS studyIds`), run the study/actor/sites/countries/policies block once per distinct studyId, and put the results in a map keyed by study id. Then UNWIND the documents and look up each one's context in that map. The inline comment about `studyById` suggests this was intended, but the CALL still takes `doc` as input. I've left out a full rewritten statement because the statement text I received is cut off mid-query, so I can't produce a complete, correct rewrite. Apply the change to the full source in docsvc.
Expected: The study-context work drops from N documents to the number of distinct studies. For a batch of tens of documents from one study, that part of the plan should shrink by about that factor, depending on how much of the 1.2 s it accounts for. · Evidence: In PROFILE, compare the row counts going into the STUDY_TO_ACTOR / STUDYSITEACTOR_TO_ACTOR / POLICY_TO_STUDY expands with the number of distinct studyIds. After the change they should match the distinct count, not the document count. Then check the end-to-end time on the same id list.
rewrite Limit the variable-length INCLUDES ancestor traversal
The issue`OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder)` runs once per document and walks every INCLUDES path up the folder tree. If a folder can be reached by more than one path (shared folders), the number of paths grows quickly. Then `collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder)` collapses them back down in memory, so the extra paths cost time and add nothing.
The fixKeep the upper bound as small as the TMF hierarchy allows. If folders have exactly one parent, switch to a single directed path, e.g. `MATCH p=(docFolder)<-[:INCLUDES*0..N]-(root:Folder) WHERE NOT ()-[:INCLUDES]->(root)` and use `nodes(p)`, so each document gets one path instead of every path. Another option is to store the ancestor id list on the Folder when it is created or moved and read that list directly. As with the suggestion above, this needs to be applied to the full source because the statement I received is cut off.
Expected: Fewer rows out of the var-length expand and less memory for DISTINCT/collect. A small gain if the folder trees are shallow and each folder has one parent; a larger one if folders are shared. · Evidence: In PROFILE, compare the rows out of the VarLengthExpand(Into/All) on INCLUDES with the number of distinct ancestor folders. If rows are much higher, many redundant paths are being walked.

docsvc · F00D0864 — 3 runs · p95 1.85 s · 1 s total · 3d ago · proposed open shape & trend →

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.

tusker · 826C56EA — 3 runs · p95 1.85 s · 1 s total · 1d ago · proposed open shape & trend →

index Index Study.name so the plan can start from the scoped study
The issue`(Study:Study{name:$scopeStudyName})` in the second MATCH is the most selective predicate in the statement. Without a lookup index on Study.name, the planner has to start from a Task label scan, or from Organization. It then expands every task through BELONGS_TO/MANAGED_BY and runs the OR/NOT EXISTS predicate on each one before the study filter removes most rows. That fits the 1850 ms p95 against a 402 ms average: tenants with many tasks get a much larger scan.
The fixCreate a range index on :Study(name). The planner can then use NodeIndexSeek on Study, expand STUDY_TO_ORG to Organization and MANAGED_BY back to the Inbox, and reach only that study's tasks. Pair it with the Organization.alias index if EXPLAIN still picks the Organization side. If study names are unique, a uniqueness constraint gives the same lookup and also protects the data.
Add this index
CREATE INDEX study_name IF NOT EXISTS FOR (s:Study) ON (s.name)
Expected: Probably cuts db hits by roughly the ratio of all tasks to tasks in one study. The slow p95 case may drop from about 1.8 s to tens of ms, but this is not verified. With only 3 runs in 7 days the total saving is small; the main benefit is shorter waits for the people running it. · Evidence: PROFILE before and after. Look for NodeIndexSeek on Study(name) near the leaf of the plan, replacing NodeByLabelScan(Task) or NodeByLabelScan(Organization), and a lower db-hit total. Check the p95 over the next week.
index Index Organization.alias for the $scopeOrgAlias / $scopeCroOrgAlias predicates
The issueThree WHERE clauses filter `Organization.alias = $scopeOrgAlias OR Organization.alias = $scopeCroOrgAlias`, and the NOT EXISTS subqueries filter `o.alias <> $scopeOrgAlias`. With no index on Organization.alias, the OR has to be checked row by row after expansion. The planner also cannot use a two-seek union on Organization as an alternative starting point.
The fixAdd a range index on :Organization(alias). The planner can then seek both aliases directly and expand inward. The index is cheap to keep, and it probably helps other tusker queries that scope by org alias. If alias is a natural key, use a uniqueness constraint instead.
Add this index
CREATE INDEX organization_alias IF NOT EXISTS FOR (o:Organization) ON (o.alias)
Expected: Makes Organization a cheap anchor of two seeks, and gives the planner a cheap way to rule out non-matching organizations. The gain is smaller than the Study index if study names are more selective. · Evidence: EXPLAIN showing NodeIndexSeek, or a Union of seeks, on Organization(alias). Compare db hits with and without the index.
rewrite Drop the duplicate scope predicate, anchor on Study, and apply LIMIT before apoc.do.case
The issue1) The second MATCH reuses the same `task` and `Organization` variables, so its WHERE repeats the whole OR/NOT EXISTS check that the first MATCH already applied. The NOT EXISTS subquery therefore runs twice per row for no reason. 2) `ORDER BY t.category LIMIT $pageLimit` comes after `CALL apoc.do.case(...)`, so the 26-branch case procedure and the head() pattern comprehension run for every matching task, not just one page. apoc.do.case also runs its branch in a write-capable inner transaction, which costs more than evaluating a CASE.
The fixMerge the two MATCH clauses into one path that starts from the study. Keep the scope predicate once and leave the category/state filters as they are. Sort and limit on t.category before the procedure call. apoc.do.case returns exactly one row per input, so limiting first gives the same rows. If the branch queries are read-only, also consider switching to apoc.case (read mode). That change is not applied below because the branch bodies are not visible.
Proposed rewrite — current query in red −, proposed in green +
- match (task:Task)-[:BELONGS_TO]->(inbox:Inbox)-[:MANAGED_BY]->(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) match (task)-[:BELONGS_TO]->(SInbox:Inbox{type:?})-[:MANAGED_BY]->(Study:Study{name:$scopeStudyName})-[:STUDY_TO_ORG]->(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) and task.category in $lists.scopeCategories and task.state in $lists.scopeStates with task match (task)-[:WITH_CONTEXT]->() where task.state <> ? optional match (task)-[:BELONGS_TO]-(SInbox:Inbox{type:?})-[:MANAGED_BY]-(UserStudy:Study)-[:STUDY_TO_ORG]-(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) with task, SInbox, UserStudy where (SInbox IS NULL) OR (SInbox IS NOT NULL AND exists((:User{email:$scopeUserEmail})-[:FOR_USER]-(:Actor)-[:STUDY_TO_ACTOR]-(UserStudy))) with distinct task as t call apoc.do.case([ t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext IN [?, ?, ?], ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ? ], ?, {t: t}) yield value return value, {inbox: {InboxStudy: head([(t)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(ist:Study) | ist])}} as inboxStudy order by t.category limit $pageLimit
+ match (Study:Study{name:$scopeStudyName})-[:STUDY_TO_ORG]->(Organization), (task:Task)-[:BELONGS_TO]->(SInbox:Inbox{type:?})-[:MANAGED_BY]->(Study), (task)-[:BELONGS_TO]->(inbox:Inbox)-[:MANAGED_BY]->(Organization) where task.category in $lists.scopeCategories and task.state in $lists.scopeStates and (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) with task match (task)-[:WITH_CONTEXT]->() where task.state <> ? optional match (task)-[:BELONGS_TO]-(SInbox:Inbox{type:?})-[:MANAGED_BY]-(UserStudy:Study)-[:STUDY_TO_ORG]-(Organization) where (Organization.alias = $scopeOrgAlias or (Organization.alias = $scopeCroOrgAlias and NOT EXISTS { MATCH (task)-[:BELONGS_TO]-(:Inbox{type:?})-[:MANAGED_BY]-(:Site)-[:SITE_TO_ORGANIZATION]->(o:Organization) WHERE o.alias <> $scopeOrgAlias } and coalesce(task.category, ?) <> $scopeTrainingCategory)) with task, SInbox, UserStudy where (SInbox IS NULL) OR (SInbox IS NOT NULL AND exists((:User{email:$scopeUserEmail})-[:FOR_USER]-(:Actor)-[:STUDY_TO_ACTOR]-(UserStudy))) with distinct task as t order by t.category limit $pageLimit call apoc.do.case([ t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext IN [?, ?, ?], ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ?, t.relationshipContext = ?, ? ], ?, {t: t}) yield value return value, {inbox: {InboxStudy: head([(t)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(ist:Study) | ist])}} as inboxStudy order by t.category
Expected: Removes one NOT EXISTS evaluation per candidate row. The apoc call and the pattern comprehension then run at most $pageLimit times instead of once per matching task. For large tenants this probably saves an amount roughly proportional to (matches − pageLimit) × the cost per case call. · Evidence: PROFILE both versions with the same parameters. Check that results are identical, including order when categories tie; add a secondary sort key such as elementId(t) if ties matter. Then compare the rows feeding ProcedureCall and the total db hits.

virtual-assistant · 0AA0EDED — 1 runs · p95 1.95 s · 1 s total · 4d ago · proposed open shape & trend →

index Index WorkplanInstanceMilestoneItem.cfgMilestoneItemId so the workplan lookup can seek instead of scan
The issueThe clause `OPTIONAL MATCH (wpInstance:WorkPlanInstance)-[:WORKPLAN_INSTANCE_TO_MILESTONE]->(:WorkPlanMilestone)-[:REL_WORKPLAN_INSTANCE_MILESTONE_TO_ITEM]->(wpItem:WorkplanInstanceMilestoneItem) WHERE ... (wpItem.tildaid = b.wp_object_id OR wpItem.cfgMilestoneItemId = b.wp_object_id)` has no node already bound in the pattern. The only anchor is an OR across two properties. Unless both properties are indexed, the planner can't turn the OR into a union of index seeks. It falls back to a label scan of WorkplanInstanceMilestoneItem, or of WorkPlanInstance plus a 2-hop expand, and repeats that scan for every config row that survives `LIMIT $fetch_limit`. This is the most likely reason one call takes over a second.
The fixAdd a lookup index on cfgMilestoneItemId. Also check that WorkplanInstanceMilestoneItem.tildaid has a uniqueness constraint or index (SHOW CONSTRAINTS / SHOW INDEXES), and add one if it doesn't. With both in place, Neo4j 5 can plan the OR as a union of two NodeIndexSeeks per row. It then expands back from wpItem to the milestone and the instance, instead of scanning.
Add this index
CREATE INDEX wp_milestone_item_cfg_item_id IF NOT EXISTS FOR (i:WorkplanInstanceMilestoneItem) ON (i.cfgMilestoneItemId)
Expected: The workplan step should go from scanning every item for each config row to a few index seeks per row. If this step is where most of the time goes, the call could get several-fold faster. This is unverified. · Evidence: PROFILE the query before and after. Before: look for a NodeByLabelScan or AllNodesScan under the OPTIONAL MATCH Apply for wpItem or wpInstance. After: look for NodeIndexSeek and Union operators on WorkplanInstanceMilestoneItem with far fewer db hits.
index Index WorkOrderConfig.study_id so configs can be found without expanding every one in the organization
The issueThe first MATCH, `(o:Organization {tildaid: $org_id})<-[:WORK_ORDER_CONFIG_TO_ORGANIZATION]-(b:WorkOrderConfig) WHERE b.delpending = false AND b.study_id = $study_id ...`, can only start from the Organization. It expands every config the org owns and filters study_id, delpending and status row by row. For a large organization most of those configs belong to other studies and are thrown away.
The fixAdd an index on WorkOrderConfig(study_id). The planner can then seek the configs for `$study_id` and check the single relationship to the org, or use whichever side has fewer rows. delpending and status stay as cheap filters on the result.
Add this index
CREATE INDEX work_order_config_study_id IF NOT EXISTS FOR (b:WorkOrderConfig) ON (b.study_id)
Expected: Fewer rows read in the first stage, roughly in proportion to how many of the org's configs belong to other studies. There is little benefit if most organizations have only one study. · Evidence: Compare the org's total config count with the count for a typical study. After adding the index, PROFILE should show NodeIndexSeek on WorkOrderConfig(study_id), or the planner keeping the org anchor, with lower db hits on the first stage.
index Make sure the tildaid lookups on Actor, Partner and Site, done once per child, are backed by an index
The issueFor every child WorkOrder row, the query runs lookups that are not tied to any already-matched node: `OPTIONAL MATCH (wPartner:Partner {tildaid: w.recipient_key})`, `OPTIONAL MATCH (:Actor {tildaid: w.recipient_key})-[:FOR_USER]->(wMember:User)` and `OPTIONAL MATCH (wES_Site:Site {tildaid: wES.scopeId})`. If any of these labels has no tildaid index or constraint, each child row triggers a full label scan. The cost grows as configs × children.
The fixRun SHOW CONSTRAINTS and SHOW INDEXES for Actor, Partner and Site on tildaid. Create a uniqueness constraint where tildaid is the key, or a plain index where it isn't. The DDL below is for Actor, the one least likely to already have one. Apply the same pattern to Partner and Site if they turn out to be missing too.
Add this index
CREATE CONSTRAINT actor_tildaid_unique IF NOT EXISTS FOR (a:Actor) REQUIRE a.tildaid IS UNIQUE
Expected: If one of these is unindexed, each child row's cost drops from a full label scan to an index seek. That could be a large share of the 1.1 s. If all three are already constrained, this changes nothing. · Evidence: In PROFILE, NodeByLabelScan on Actor, Partner or Site inside the per-child Apply branches would confirm the problem; after the fix these should be NodeUniqueIndexSeek or NodeIndexSeek. Note: the shape ran only once in 7 days (1.1 s in total), so treat all of these as low priority unless call volume goes up.

execsvc · F8026E52 — 2 runs · p95 1.90 s · 1 s total · 1d ago · proposed open shape & trend →

rewrite Drop the unbounded NEXT_PARTICIPANT_ACTIVITY* check in the CALL (ta) subquery
The issueIn `CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA...) WHERE CASE ... ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END ...}`, every completed ParticipantActivity follows the whole NEXT_PARTICIPANT_ACTIVITY chain to the end with no length limit. For an assignment with n attempts that is O(n²) expansions. The work is mostly redundant, because the same subquery already runs `ORDER BY trainingPA.createdat DESC LIMIT ?`, which picks the newest completed PA anyway.
The fixIf createdat always increases along NEXT_PARTICIPANT_ACTIVITY (a newer attempt is created after the one it follows), the newest completed PA by createdat is the same as the completed PA with no newer completed successor. In that case you can remove the NOT EXISTS clause with the variable-length path and let ORDER BY/LIMIT choose. Only the CALL (ta) block changes. Keep the 1-hop NOT EXISTS for the assigned-state branch, because it picks the chain tail, not a status match.
Proposed rewrite — current query in red −, proposed in green +
- UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta) WHERE (coalesce(training.scopeType, ?) = $orgScopeType) AND (training.orgId <> ? AND training.orgId = $orgId) RETURN ta, training, null AS study UNION MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{deltimestamp:?}) WHERE ta.studyId IN $studyIds AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta), (training)-[:PLAN_DEFINITION_TO_STUDY]->(study:Study{delpending:false}) WHERE study.tildaid = ta.studyId AND (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId)) RETURN ta, training, study } CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END RETURN trainingPA ORDER BY trainingPA.createdat DESC LIMIT ? } WITH uid, training, ta, study, trainingPA WHERE trainingPA IS NOT NULL MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2) CALL (trainingPA, trainingActivity) { OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH trainingPA, trainingActivity, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals WHERE x[?] = ? | x[?]]), max: head([x IN tpVals WHERE x[?] = ? | x[?]]), result: head([x IN tpVals WHERE x[?] = ? | x[?]])} END) AS tpQuizzes WITH trainingPA, trainingActivity, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores RETURN tpFailed = ? AND (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues } WITH uid, training, ta, study, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog ORDER BY training.name, training.tildaid RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, studyId:study.tildaid, studyName:study.name, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
+ UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta) WHERE (coalesce(training.scopeType, ?) = $orgScopeType) AND (training.orgId <> ? AND training.orgId = $orgId) RETURN ta, training, null AS study UNION MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{deltimestamp:?}) WHERE ta.studyId IN $studyIds AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta), (training)-[:PLAN_DEFINITION_TO_STUDY]->(study:Study{delpending:false}) WHERE study.tildaid = ta.studyId AND (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId)) RETURN ta, training, study } CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses END RETURN trainingPA ORDER BY trainingPA.createdat DESC LIMIT ? } WITH uid, training, ta, study, trainingPA WHERE trainingPA IS NOT NULL MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2) CALL (trainingPA, trainingActivity) { OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH trainingPA, trainingActivity, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals WHERE x[?] = ? | x[?]]), max: head([x IN tpVals WHERE x[?] = ? | x[?]]), result: head([x IN tpVals WHERE x[?] = ? | x[?]])} END) AS tpQuizzes WITH trainingPA, trainingActivity, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores RETURN tpFailed = ? AND (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues } WITH uid, training, ta, study, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog ORDER BY training.name, training.tildaid RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, studyId:study.tildaid, studyName:study.name, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
Expected: This removes the variable-length expansions for re-attempted or recertified trainings. For users with long attempt chains, the subquery could get several times cheaper. For users with only one or two attempts there may be little change. The total gain is small because the query ran only twice in 7 days. · Evidence: First confirm the assumption by running a query that counts NEXT_PARTICIPANT_ACTIVITY pairs (a)-[:NEXT_PARTICIPANT_ACTIVITY]->(b) where b.createdat < a.createdat. The count must be 0. Then compare PROFILE db hits for the CALL (ta) block before and after, using a real $userIds set, and diff the result rows to check they are identical.
index Make sure the User.tildaid anchor uses an index
The issueBoth UNION branches start at `MATCH (:User{tildaid:uid})`, run once per element of $userIds. This is the only selective entry point in the statement. Without a lookup on User.tildaid, each uid needs a NodeByLabelScan over every User. With a large $userIds list, that would explain the 1.9 s outlier. The schema for this property was not provided, so it is unverified.
The fixRun SHOW INDEXES WHERE 'User' IN labelsOrTypes. If nothing covers tildaid, create the range index below. A uniqueness constraint is better if tildaid is a unique key, but it will fail if duplicates already exist. If a constraint-backed index already exists, this statement is a no-op, and the anchor is not the cost.
Add this index
CREATE INDEX user_tildaid IF NOT EXISTS FOR (u:User) ON (u.tildaid)
Expected: If the index was missing: each UNWIND row changes from a label scan to an index seek, likely cutting latency many times over when $userIds is large. If the index already exists: no change. The weekly saving is small either way, since there were only 2 executions. · Evidence: PROFILE showing NodeIndexSeek on User(tildaid) for both UNION branches instead of NodeByLabelScan/Filter. Also note that the reported p95 (1900 ms) is higher than the 7-day total (1090 ms), so check the metrics source before relying on the outlier.

docsvc · FE26D0B8 — 2 runs · p95 1.90 s · 1 s total · 4d ago · proposed open shape & trend →

rewrite Stop the annotation subquery from multiplying signers × fields × changelogs
The issueIn the per-document `CALL { with doc optional match (doc)-[:HAS]-(ann:DocumentAnnotation) optional match (ann)-[:ANNOTATION_SIGNER]-(ds) optional match (ann)-[:ANNOTATION_FIELD]-(df) optional match (ann)-[:ALL_CHANGELOG]->(cl) ... }`, the three OPTIONAL MATCHes hang off `ann` independently, so each annotation produces |ds| × |df| × |cl| rows. That product is then sorted (`order by cl.createdat`) and reduced again with `collect(distinct ...)`. An annotation with 5 signers, 30 fields and 40 changelog entries makes 6,000 rows, where 75 would do. That fits the gap between the 540 ms average and the 1,900 ms p95: the slow call probably hit a document with many annotations or a long audit log.
The fixReplace the three OPTIONAL MATCHes with one pattern comprehension per relationship type, keeping a single row per annotation. Inside the CALL block: CALL { WITH doc OPTIONAL MATCH (doc)-[:HAS]-(ann:DocumentAnnotation) WITH doc, ann, apoc.convert.fromJsonList(ann.signers) AS signersOld, [(ann)-[:ANNOTATION_SIGNER]-(ds:DocumentSigner) | ds{.*}] AS signers, [(ann)-[:ANNOTATION_FIELD]-(df:DocumentField) | df{.*}] AS fields, apoc.coll.sortMaps([(ann)-[:ALL_CHANGELOG]->(cl:ChangeLog) | cl{.*}], '^createdat') AS auditLog WITH doc, ann, auditLog, CASE WHEN size(signers) <> ? THEN signers WHEN size(signers) = ? AND signersOld IS NULL THEN [] ELSE signersOld END AS signers1, coalesce(fields, []) AS fields1 RETURN ann{.*, signers: signers1, fields: fields1} AS annotationSpec, CASE WHEN doc.template = false THEN ann{.*, signers: signers1, fields: fields1} ELSE NULL END AS annotationVal } Two things differ from the original. The original sorts the auditLog by cl.createdat before collecting it, and sortMaps keeps that order. Wrap the lists in apoc.coll.toSet if duplicate maps were relied on being removed. I couldn't give the full rewritten statement because the shape is truncated (it cuts off in the DocumentShare subquery). Apply this change to the CALL block in the source query.
Expected: Rows per annotation drop from a product to roughly 1, and the in-memory sort and DISTINCT over the product go away. The gain should be largest on documents with many annotations or long changelogs (the p95 case). It may be small on the average call. This is unverified. · Evidence: Run PROFILE on the slowest documentIds list before and after the change. Compare the row counts going into the Sort/EagerAggregation operators of this subquery and the total db hits. Also check that the annotation output is the same, including auditLog order.
index Confirm Document.tildaid has a unique constraint backing the IN lookup
The issueThe entry clause `MATCH (doc:Document) WHERE doc.tildaid in $lists.documentIds0` is the only seed for the whole query. Without a range or unique index on Document.tildaid, it turns into a NodeByLabelScan over every Document, and the rest of the expensive pipeline then runs on top of it. tildaid looks like the node key, so a constraint may already exist. The listed properties don't show it, so it needs checking. Organization.tildaid, Study.tildaid and User.tildaid are used the same way in `{tildaid:$policyOrgId0}`, `study.tildaid = studyId` and `{tildaid:$ctxUserId0}`.
The fixRun SHOW CONSTRAINTS / SHOW INDEXES. If Document.tildaid isn't covered, add the uniqueness constraint (it also enforces the id semantics). Then the IN list plans as a NodeUniqueIndexSeek, with one seek per id. Do the same check for Organization, Study and User tildaid.
Add this index
CREATE CONSTRAINT document_tildaid_unique IF NOT EXISTS FOR (d:Document) REQUIRE d.tildaid IS UNIQUE
Expected: If the index is missing, the seed step goes from O(all Documents) to O(|documentIds0|), which could account for a large share of the 1.9 s p95. If the constraint already exists, nothing changes and this item can be closed. · Evidence: Check the output of SHOW CONSTRAINTS. The EXPLAIN plan's first operator should be NodeUniqueIndexSeek(Document, tildaid) rather than NodeByLabelScan, and the same should hold for the Organization, Study and User lookups.
none Low volume: tune only if the p95 call matters to a user
The issueThis shape ran just 2 times in 7 days, for about 1.1 s of total DB time. One of the two runs probably caused the 1,900 ms p95, likely from a cold page cache or a large documentIds0 list. The policy and folder-hierarchy subquery (`OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder)`, the policy EXISTS checks) runs for every document before `order by ... skip $skip0`. With no visible LIMIT, the skip doesn't cut that work. Its traversals are anchored on bound nodes, though, so no further index would help.
The fixBeyond the annotation rewrite and the constraint check, the cluster-wide gain from this statement is negligible: at best about 1 s per week. Only restructure the access-policy section, for example by computing the paging window before evaluating the folder hierarchy, if a user-facing screen actually waits on this call.
Expected: No measurable change in cluster-wide load is expected from further work on this shape. · Evidence: Look at the execution count and p95 trend over a longer window. Only revisit this if volume grows or the p95 call is tied to a user complaint.

execsvc · B438C318 — 6 runs · p95 1.70 s · 1 s total · 2d ago · proposed open shape & trend →

index Index User.email for the supportUser lookup
The issue`OPTIONAL MATCH (supportUser:User{email:$supportUserEmail})` is a separate starting point that is not connected to currentUser. If there is no index on User.email, the planner has to scan every User node (NodeByLabelScan + Filter) on each call, even though supportUser is only used when a SUPPORTS_ORG edge exists. A full label scan like this is a likely cause of the 1.7 s p95, which is far above the 175 ms average.
The fixAdd a lookup index on User(email). If email is unique per user, a uniqueness constraint is better: it creates the backing index and also guarantees that the OPTIONAL MATCH returns at most one row, so it can't multiply the result rows. Check SHOW INDEXES first, because an index or constraint may already exist.
Add this index
CREATE INDEX user_email IF NOT EXISTS FOR (u:User) ON (u.email)
Expected: If a scan is happening today, this lookup should become a NodeIndexSeek costing a few DB hits instead of one per User node, and the slow tail should shrink a lot. The statement only ran 6 times in 7 days (about 1 s total), so the overall time saved is small. The main benefit is latency for the person making the request. · Evidence: Run SHOW INDEXES WHERE 'User' IN labelsOrTypes. Run PROFILE with real parameters and confirm the supportUser branch changes from NodeByLabelScan to NodeIndexSeek on user_email. After deploying, compare p95 for shape B438C318.
index Index Organization.alias (used twice: targetOrg and the TildaScale org)
The issue`OPTIONAL MATCH (targetOrg:Organization{delpending:false, alias:$orgAlias})` and the `(:Organization{alias:$tildaScaleAlias})` pattern inside the EXISTS subquery both look up Organization by alias. Without an index on alias, the targetOrg lookup becomes a label scan plus filter. Because the planner can't seek on alias, it may also start the EXISTS subquery from the wrong end.
The fixCreate an index on Organization(alias). If aliases are unique, use a uniqueness constraint instead. The delpending:false check is cheap to apply after the seek, so a single-property index is enough. With the index, the planner can seek both organizations directly and join them to the currentUser-anchored expansions.
Add this index
CREATE INDEX organization_alias IF NOT EXISTS FOR (o:Organization) ON (o.alias)
Expected: Both alias lookups should become index seeks. The EXISTS branch, which only runs for non-members, should also do fewer DB hits because it can be anchored at both ends. The size of the gain depends on how many Organization nodes there are and whether an index already exists. · Evidence: Run SHOW INDEXES for the Organization label. Run PROFILE with a non-member user, so the delegated branch runs, and confirm NodeIndexSeek on organization_alias for both the targetOrg and TildaScale nodes. Compare total DB hits before and after.
rewrite Look up supportUser only when a SUPPORTS_ORG edge exists, and drop the dead repOf flag
The issueThe supportUser lookup runs on every call, but its value is only used in the `WHEN supports is not null THEN coalesce(supportUser, currentUser)` branch. `WITH *, false AS repOf` is a constant that is never true, so the `WHEN repOf` checks never do anything and only make the query harder to read. Also, the map `{deltimestamp:?,deltimestamp:?}` repeats the same key, so only the second value applies.
The fixMove the supportUser lookup after the SUPPORTS_ORG match. Put it inside a CALL subquery that returns a row only when supports is not null, so most calls never look up User by email. Remove repOf and its CASE branches. The output is unchanged: user, org and tokenUser are computed the same way. Also check the duplicated deltimestamp key with the application owners.
Proposed rewrite
MATCH (currentUser:User{deltimestamp:?,tildaid:$actAsUserId}) 
WHERE (currentUser.isDummy IS NULL OR currentUser.isDummy <> true) 
OPTIONAL MATCH (targetOrg:Organization{delpending:false, alias:$orgAlias}) 
OPTIONAL MATCH (currentUser)-[:FOR_USER]-(orgActor:Actor{delpending:false})-[:ORGANIZATION_TO_ACTOR]-(targetOrg) 
WHERE coalesce(orgActor.state, ?) <> ? 
OPTIONAL MATCH (currentUser)-[supports:SUPPORTS_ORG]-(targetOrg) 
CALL { 
WITH supports 
WITH supports 
WHERE supports IS NOT NULL 
OPTIONAL MATCH (su:User{email:$supportUserEmail}) 
RETURN su AS supportUser 
UNION 
WITH supports 
WITH supports 
WHERE supports IS NULL 
RETURN null AS supportUser } 
WITH *, CASE WHEN orgActor IS NULL THEN EXISTS { 
MATCH (currentUser)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(targetOrg), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) 
WHERE st.srcorgid = targetOrg.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) } ELSE false END as delegated 
RETURN CASE WHEN orgActor is not null THEN currentUser WHEN delegated THEN currentUser WHEN supports is not null THEN coalesce(supportUser, currentUser) ELSE currentUser END as user, CASE WHEN orgActor is not null OR delegated OR supports is not null THEN targetOrg ELSE null END as org, currentUser as tokenUser
Expected: For callers without a SUPPORTS_ORG edge, which is probably most of them, the email lookup is skipped entirely. This matters most if User.email has no index. If it does have one, the gain is small, mainly a simpler plan and clearer code. · Evidence: Run PROFILE on the old and new versions with the same parameters, once with and once without a SUPPORTS_ORG edge. Confirm the email lookup operator shows 0 rows and 0 DB hits when supports is null. Diff the returned user, org and tokenUser across representative test cases to confirm the results are the same.

dashrsvc · 5F3CD381 — 2 runs · p95 950 ms · 1 s total · 4d ago · proposed open shape & trend →

index Index FolderManager.scopeId for the per-study attribute-definition lookup
The issue`OPTIONAL MATCH (fm:FolderManager{scopeId:d.studyId})-[:HAS]->(dad:DocAttributeDefinition)` looks up FolderManager by a non-key property, using a value that comes from the bound Document row. scopeId is not in the property list for indexes this statement uses. Without a range index, the planner has to scan every FolderManager node and filter on scopeId for each incoming row. That is the only anchor in the statement that isn't a tildaid key, so it is the most likely source of the 950 ms p95.
The fixAdd a range index on FolderManager(scopeId) so the planner can do a NodeIndexSeek and then expand :HAS to only that manager's DocAttributeDefinitions. The dad.name filter is applied after the expansion from fm, so it doesn't need its own index.
Add this index
CREATE INDEX folder_manager_scope_id IF NOT EXISTS FOR (fm:FolderManager) ON (fm.scopeId)
Expected: If the plan currently shows NodeByLabelScan + Filter on FolderManager, it should change to NodeIndexSeek. Per-call latency could drop from hundreds of ms to tens of ms, depending on how many FolderManager nodes there are. Because the statement ran only 2 times in 7 days (781 ms total), the cluster-wide saving is small. The gain is in latency for the request that runs this write. · Evidence: Run PROFILE on the statement with a real $id. Check whether the FolderManager row is a NodeByLabelScan/Filter and how many db hits it has, then confirm it becomes NodeIndexSeek(scopeId) after the index is created. Also compare p95 for shape 5F3CD381 over the next week. Check SHOW INDEXES first to make sure an equivalent index doesn't already exist.
index Make sure the tildaid anchors on SubArtifact and Folder are indexed
The issue`OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(...)})` and `OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section)...` both look up nodes by tildaid. The value is computed per row. If Folder.tildaid has no index or uniqueness constraint, the artifact match becomes a label scan over all Folders, followed by a 3-hop INCLUDES walk up to the collection folder. That happens for every row produced by the earlier OPTIONAL MATCHes (Binder study/site, currentFolder), which multiply together. SubArtifact.tildaid is less critical because it is reached by expanding from artifact. Still, an index lets the planner pick a seek if it reorders the plan.
The fixRun SHOW CONSTRAINTS / SHOW INDEXES. If Folder.tildaid or SubArtifact.tildaid isn't covered, add uniqueness constraints. Ids like these should be unique, and a constraint also creates the backing index. Create the SubArtifact constraint below, and the matching one for Folder if it's missing (CREATE CONSTRAINT folder_tildaid_unique IF NOT EXISTS FOR (f:Folder) REQUIRE f.tildaid IS UNIQUE).
Add this index
CREATE CONSTRAINT subartifact_tildaid_unique IF NOT EXISTS FOR (s:SubArtifact) REQUIRE s.tildaid IS UNIQUE
Expected: If either lookup is currently a label scan, it should become a NodeUniqueIndexSeek, removing a scan that runs once per intermediate row. If both are already constrained, this changes nothing and can be dropped. · Evidence: Look at the PROFILE rows for the artifact (Folder) and subArtifact anchors: NodeByLabelScan with high db hits versus NodeUniqueIndexSeek. Also check that existing data has no duplicate tildaid values before creating the constraints.

execsvc · 81733763 — 4 runs · p95 900 ms · 1 s total · 3d ago · proposed open shape & trend →

rewrite Replace the fan-out OPTIONAL MATCH chain with per-node COLLECT subqueries
The issueAfter the GRAPH_NEXT variable-length expansion produces one row per gnode, the statement chains about 10 OPTIONAL MATCHes. Five of them only feed list aggregations: nodeBranchCfg→nodeBranch, cfgRel→BranchConfig→ActivationRule→nodeChain, cfg→cfgRef, acBranchCfg→acCfgRef and aqcfg→aqBranchCfg. Each of these multiplies the row count by its own match count, so a gnode with 3 branches, 3 chains, 3 cfgRefs and 2 auto-query configs becomes 54+ rows. Those rows are then folded back by the big `WITH ... collect(DISTINCT ...)`, which also has to group on whole nodes and maps (gnode, page, refNode, cfg, questionSpec, question, acc, acBranchCfg, graphVars, rr). This cartesian blow-up followed by in-memory DISTINCT/grouping is the most likely reason p95 is 900 ms while the average is 182 ms: large graphs explode and small ones don't.
The fixKeep as row-producing OPTIONAL MATCHes only the patterns whose variables are grouping keys in the original: page, refNode, question, questionSpec, cfg, rel/acc and acBranchCfg. Compute branchIds, activationChainIds, cfgRefIds, autoComputeBranchRefIds and autoQueryConfigs with COLLECT { } subqueries (Neo4j 5.6+), which run once per row and never multiply rows. The final aggregation step then becomes a plain projection with no aggregation. The duplicated-key map `{graphid:root.tildaid, graphid:$relPropId}` is kept exactly as in the original, but it should be checked: only one of the two keys takes effect, which is likely a latent bug.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (root:GraphNode{tildaid:$graphId}) OPTIONAL MATCH (root)-[:GRAPH_TO_VARIABLE{graphid:root.tildaid}]-(var:VariableV2) WITH root, collect(DISTINCT var) as graphVars MATCH (root)-[rr:GRAPH_NEXT*?..{graphid:root.tildaid}]->(gnode:GraphNode) WHERE size(rr) = ? OR rr[-?].path[?] = root.tildaid OPTIONAL MATCH (gnode)-[:GRAPH_NODE_TO_PAGE{graphid:root.tildaid}]->(page:GraphPage) OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{graphid:$relPropId}]->(refNode:GraphNode) WHERE gnode.objtype = ? OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_QUESTION{graphid:$relPropId}]->(question:QuestionV2) WHERE refNode.objtype = ? WITH root, gnode, page, refNode, case when refNode.objtype = ? then question when refNode.objtype = ? then refNode else null end as question, rr, graphVars OPTIONAL MATCH (question)-[:QUESTION_TO_QUESTION_SPEC]->(questionSpec:QuestionSpec) OPTIONAL MATCH (gnode)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]- (nodeBranchCfg:BranchConfig)-[:BRANCH_TO_BRANCH_CONFIG{graphid:$relPropId}]- (nodeBranch:BranchV2) OPTIONAL MATCH (gnode)-[cfgRel:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]- (:BranchConfig)-[:ACTIVATION_RULE_TO_BRANCH_CONFIG{graphid:cfgRel.graphid}]- (:ActivationRule)-[:ACTIVATION_RULE_TO_CHAIN{graphid:cfgRel.graphid}]- (nodeChain:ActivationRuleChain) OPTIONAL MATCH (gnode)-[:BRANCH_TO_BRANCH_CONFIG{graphid:root.tildaid, graphid:$relPropId}]->(cfg:BranchConfig) WHERE gnode.objtype = ? OPTIONAL MATCH (cfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]->(cfgRef:GraphNodeRef) OPTIONAL MATCH (gnode)-[rel:QUESTION_TO_AUTO_COMPUTE_CFG|QUESTION_TO_CORRECTNESS_CHECK{graphid:$relPropId}]->(acc:AutoComputeConfig) OPTIONAL MATCH (acc)-[:AUTO_COMPUTE_TO_BRANCH_CONFIG]->(acBranchCfg:BranchConfig) OPTIONAL MATCH (acBranchCfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]->(acCfgRef:GraphNodeRef) OPTIONAL MATCH (gnode)-[aqRel:QUESTION_TO_AUTO_QUERY{graphid:$relPropId}]->(aqcfg:AutoQueryConfig{enabled:true}) WHERE aqRel.sourceprotocolid IS NULL OPTIONAL MATCH (aqcfg)-[:AUTO_QUERY_TO_BRANCH_CONFIG]->(aqBranchCfg:BranchConfig) WITH gnode, page, refNode, cfg, rr, questionSpec, question, CASE WHEN size(rr) > ? THEN rr[-?].path ELSE [root.tildaid] END as nodePath, CASE WHEN size(rr) > ? THEN rr[-?].flags ELSE ? END as nodeFlags, CASE WHEN size(rr) > ? THEN rr[-?].who ELSE [] END as nodeWho, CASE WHEN type(rel) = ? THEN true ELSE false END as isCorrectnessCheckConfig, collect(DISTINCT cfgRef.tildaid) as cfgRefIds, collect(DISTINCT nodeBranch.tildaid) as branchIds, collect(DISTINCT nodeChain.tildaid) as activationChainIds, acc, acBranchCfg, collect(DISTINCT acCfgRef.tildaid) as autoComputeBranchRefIds, graphVars, collect(DISTINCT CASE WHEN aqcfg IS NOT NULL THEN {aqcfg: aqcfg, aqBranchCfg: aqBranchCfg} END) as autoQueryConfigs WITH gnode as node, page, refNode, cfg as branchCfg, cfgRefIds as branchRefIds, questionSpec, question, branchIds, activationChainIds, nodePath as path, gnode.objtype as objType, nodeFlags as flags, nodeWho as who, acc as autoComputeConfig, acBranchCfg as autoComputeBranchCfg, autoComputeBranchRefIds, autoQueryConfigs, isCorrectnessCheckConfig, graphVars RETURN {node:node, page:page, refNode:refNode, questionSpec:questionSpec, question:question, branchCfg:branchCfg, branchRefIds:branchRefIds, path:path, objType:objType, flags:flags, who:who, branchIds:branchIds, activationChainIds:activationChainIds, autoComputeConfig:autoComputeConfig, autoComputeBranchCfg:autoComputeBranchCfg, autoComputeBranchRefIds:autoComputeBranchRefIds, autoQueryConfigs:autoQueryConfigs, isCorrectnessCheckConfig:isCorrectnessCheckConfig, graphVars:graphVars}
+ MATCH (root:GraphNode{tildaid:$graphId})
+ OPTIONAL MATCH (root)-[:GRAPH_TO_VARIABLE{graphid:root.tildaid}]-(var:VariableV2)
+ WITH root, collect(DISTINCT var) as graphVars
+ MATCH (root)-[rr:GRAPH_NEXT*?..{graphid:root.tildaid}]->(gnode:GraphNode)
+ WHERE size(rr) = ? OR rr[-?].path[?] = root.tildaid
+ OPTIONAL MATCH (gnode)-[:GRAPH_NODE_TO_PAGE{graphid:root.tildaid}]->(page:GraphPage)
+ OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{graphid:$relPropId}]->(refNode:GraphNode) WHERE gnode.objtype = ?
+ OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_QUESTION{graphid:$relPropId}]->(question:QuestionV2) WHERE refNode.objtype = ?
+ WITH root, gnode, page, refNode, case when refNode.objtype = ? then question when refNode.objtype = ? then refNode else null end as question, rr, graphVars
+ OPTIONAL MATCH (question)-[:QUESTION_TO_QUESTION_SPEC]->(questionSpec:QuestionSpec)
+ OPTIONAL MATCH (gnode)-[:BRANCH_TO_BRANCH_CONFIG{graphid:root.tildaid, graphid:$relPropId}]->(cfg:BranchConfig) WHERE gnode.objtype = ?
+ OPTIONAL MATCH (gnode)-[rel:QUESTION_TO_AUTO_COMPUTE_CFG|QUESTION_TO_CORRECTNESS_CHECK{graphid:$relPropId}]->(acc:AutoComputeConfig)
+ OPTIONAL MATCH (acc)-[:AUTO_COMPUTE_TO_BRANCH_CONFIG]->(acBranchCfg:BranchConfig)
+ WITH root, gnode, page, refNode, question, questionSpec, cfg, rel, acc, acBranchCfg, rr, graphVars,
+ COLLECT { MATCH (cfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]->(cfgRef:GraphNodeRef) WHERE cfgRef.tildaid IS NOT NULL RETURN DISTINCT cfgRef.tildaid } as cfgRefIds,
+ COLLECT { MATCH (gnode)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]-(:BranchConfig)-[:BRANCH_TO_BRANCH_CONFIG{graphid:$relPropId}]-(nodeBranch:BranchV2) WHERE nodeBranch.tildaid IS NOT NULL RETURN DISTINCT nodeBranch.tildaid } as branchIds,
+ COLLECT { MATCH (gnode)-[cfgRel:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]-(:BranchConfig)-[:ACTIVATION_RULE_TO_BRANCH_CONFIG{graphid:cfgRel.graphid}]-(:ActivationRule)-[:ACTIVATION_RULE_TO_CHAIN{graphid:cfgRel.graphid}]-(nodeChain:ActivationRuleChain) WHERE nodeChain.tildaid IS NOT NULL RETURN DISTINCT nodeChain.tildaid } as activationChainIds,
+ COLLECT { MATCH (acBranchCfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{graphid:$relPropId}]->(acCfgRef:GraphNodeRef) WHERE acCfgRef.tildaid IS NOT NULL RETURN DISTINCT acCfgRef.tildaid } as autoComputeBranchRefIds,
+ COLLECT { MATCH (gnode)-[aqRel:QUESTION_TO_AUTO_QUERY{graphid:$relPropId}]->(aqcfg:AutoQueryConfig{enabled:true}) WHERE aqRel.sourceprotocolid IS NULL OPTIONAL MATCH (aqcfg)-[:AUTO_QUERY_TO_BRANCH_CONFIG]->(aqBranchCfg:BranchConfig) RETURN DISTINCT {aqcfg: aqcfg, aqBranchCfg: aqBranchCfg} } as autoQueryConfigs
+ WITH gnode as node, page, refNode, cfg as branchCfg, cfgRefIds as branchRefIds, questionSpec, question, branchIds, activationChainIds,
+ CASE WHEN size(rr) > ? THEN rr[-?].path ELSE [root.tildaid] END as path,
+ gnode.objtype as objType,
+ CASE WHEN size(rr) > ? THEN rr[-?].flags ELSE ? END as flags,
+ CASE WHEN size(rr) > ? THEN rr[-?].who ELSE [] END as who,
+ acc as autoComputeConfig, acBranchCfg as autoComputeBranchCfg, autoComputeBranchRefIds, autoQueryConfigs,
+ CASE WHEN type(rel) = ? THEN true ELSE false END as isCorrectnessCheckConfig, graphVars
+ RETURN {node:node, page:page, refNode:refNode, questionSpec:questionSpec, question:question, branchCfg:branchCfg, branchRefIds:branchRefIds, path:path, objType:objType, flags:flags, who:who, branchIds:branchIds, activationChainIds:activationChainIds, autoComputeConfig:autoComputeConfig, autoComputeBranchCfg:autoComputeBranchCfg, autoComputeBranchRefIds:autoComputeBranchRefIds, autoQueryConfigs:autoQueryConfigs, isCorrectnessCheckConfig:isCorrectnessCheckConfig, graphVars:graphVars}
Expected: For large graphs, intermediate row counts and db hits should drop sharply, and so should the in-memory EagerAggregation. The p95 should move toward the ~180 ms average, possibly lower. The absolute weekly gain is small: only 4 runs and 728 ms total in 7 days. Treat this as a fix for tail latency in a user-facing graph load, not a throughput win. · Evidence: Run PROFILE on both versions against the graphId that produced the 900 ms run. Compare the row counts going into the aggregation step (EagerAggregation in the old plan, Projection in the new one), total db hits and memory used. Then diff the result sets for several graphs to confirm they are the same. Pay particular attention to cases where the original's grouping removed duplicate rows (for example, two BRANCH_TO_BRANCH_CONFIG relationships pointing at the same cfg).
index Make sure the root GraphNode lookup on tildaid is backed by a constraint or index
The issueEvery row in this query fans out from `MATCH (root:GraphNode{tildaid:$graphId})`. If GraphNode.tildaid has no supporting index or constraint, this anchor becomes a NodeByLabelScan plus a filter over every GraphNode, and the whole expansion waits on it. It probably already exists because tildaid looks like the entity key, but that is not shown here.
The fixCheck with SHOW CONSTRAINTS / SHOW INDEXES. If tildaid is unique per node (it seems to be the entity id), add a uniqueness constraint. This also gives the planner a NodeUniqueIndexSeek. If an index already exists, skip this suggestion: the anchor is already optimal.
Add this index
CREATE CONSTRAINT graphnode_tildaid_unique IF NOT EXISTS FOR (n:GraphNode) REQUIRE n.tildaid IS UNIQUE
Expected: If the index is missing, the root lookup would go from a label scan (cost grows with the number of GraphNodes) to a single seek, saving anywhere from a few ms to hundreds of ms per call. If it already exists, there is no change. · Evidence: Run EXPLAIN on the statement. The first operator should be NodeUniqueIndexSeek/NodeIndexSeek on GraphNode(tildaid), not NodeByLabelScan followed by Filter. SHOW CONSTRAINTS should list the constraint.

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

index Index the per-scope Site lookup on tildaid (skip if a constraint already exists)
The issueInside the per-event CALL subquery, `OPTIONAL MATCH (site:Site {tildaid: scope.scopeId})` runs once for every EventScope row the page returns, and it doesn't come off an expansion. If there's no index or uniqueness constraint on Site.tildaid, every one of those rows does a NodeByLabelScan over all Site nodes and filters on tildaid. With up to $limit events × N scopes per event, that repeated scan is the most likely reason for the 950 ms p95 against a 264 ms average.
The fixGive Site.tildaid an index, so the planner can do a NodeIndexSeek (or NodeUniqueIndexSeek) for each scope row. tildaid looks like a global identifier, so a uniqueness constraint (which also creates the index) is the better choice. If the data has duplicates, use a plain range index instead: CREATE INDEX site_tildaid IF NOT EXISTS FOR (s:Site) ON (s.tildaid). Run SHOW CONSTRAINTS / SHOW INDEXES first. If one already covers Site.tildaid, there's nothing to do here.
Add this index
CREATE CONSTRAINT site_tildaid_unique IF NOT EXISTS FOR (s:Site) REQUIRE s.tildaid IS UNIQUE
Expected: If the plan currently scans Site, the per-scope cost should drop from O(|Site|) to about O(1). That could take p95 down substantially, roughly in line with how many scopes are on a page. The shape only ran twice in 7 days (528 ms in total), so the total time saved is small. The real gain is lower latency for the user who loads the page. · Evidence: PROFILE the statement with real parameters. Before the change, look for NodeByLabelScan(Site) + Filter(tildaid) under the Apply. After it, look for NodeIndexSeek/NodeUniqueIndexSeek on Site(tildaid) with far fewer db hits. Then compare p95 over the next run window.
index Anchor the entry point with a Study.tildaid lookup
The issueThe first clause, `MATCH (ctx:Study)<-[:PLAN_CONTEXT]-(ep:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event) WHERE ctx.tildaid IN $contextIds`, starts from Study nodes filtered by tildaid IN a list. Without an index on Study.tildaid, the planner has to scan every Study (or every EventPlan) to find the starting nodes before it expands to events.
The fixAdd an index (preferably a uniqueness constraint) on Study.tildaid. Then the IN list becomes a multi-value NodeIndexSeek and the expansion starts from only the requested studies. Check SHOW CONSTRAINTS first in case one already exists.
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: The entry point should become a seek over |$contextIds| nodes instead of a full label scan. That's probably a modest win per call, and only if no index exists today. · Evidence: EXPLAIN/PROFILE should show NodeIndexSeek on Study(tildaid) as the leaf of the first MATCH instead of NodeByLabelScan(Study) or a scan starting from EventPlan.
index Index EventPlan.orgId as an alternative anchor or join filter
The issue`(ep:EventPlan {orgId: $orgId})` filters on orgId, which isn't the node key. If the planner decides to start from EventPlan (for example when $contextIds is large or Study has no index), it has to scan every EventPlan and filter on orgId. orgId is also a tenant filter that probably appears in other docsvc queries.
The fixAdd a range index on EventPlan.orgId. Then the planner can seek EventPlans for the tenant and hash-join them with the Study seek, or use whichever side is more selective. Only orgId is indexed because it's the only EventPlan property this statement filters on.
Add this index
CREATE INDEX eventplan_orgid IF NOT EXISTS FOR (ep:EventPlan) ON (ep.orgId)
Expected: This gives the planner a cheap anchor on the EventPlan side. I'd expect a small to moderate improvement in the first MATCH, depending on how many EventPlans each org has. Other queries that filter EventPlan by orgId may also benefit. · Evidence: PROFILE should show NodeIndexSeek on EventPlan(orgId), or a NodeHashJoin between the Study and EventPlan seeks, with lower db hits on the first MATCH. Also run SHOW INDEXES to confirm the index is ONLINE and see its readCount go up.