Optimizer suggestions 9
What a model proposes for nexus'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.
patientsvc · 281E5AFB — 3 runs · p95 455 ms · 1 s total · 3d ago · proposed open shape & trend →
index Composite index for the Advocate lookup by orgid + userid
The issue`optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId})` runs once per surviving (phi, p, mg) row. Nothing in this statement shows an index on Advocate(orgid, userid). Without one, each row triggers a NodeByLabelScan over every Advocate node plus a property filter, so the cost grows with candidate rows × Advocate count.
The fixAdd a composite range index on Advocate(orgid, userid) so each optional lookup becomes a NodeIndexSeek. If either property is already indexed alone, the planner may use that instead, but the composite index is the closest fit for the equality pair.
Add this index
CREATE INDEX advocate_orgid_userid IF NOT EXISTS FOR (a:Advocate) ON (a.orgid, a.userid)
Expected: Should remove a per-row label scan. The saving depends on how many Advocate nodes exist and how many patients match the PHI filter. It is probably most of the gap between the 178 ms average and the 455 ms p95 when the search returns many matches, but this is unverified. · Evidence: Run PROFILE before and after. Look for the Advocate operator changing from NodeByLabelScan+Filter to NodeIndexSeek, with fewer db hits. Then compare p95 over the next 7 days, keeping in mind that 3 executions is a very small sample.
index Make sure Organization.tildaid is a uniqueness constraint (the query's anchor)
The issueThe only selective starting point is `(:Organization{tildaid:$phiOrgId})`. The `tolower(phi.x) contains tolower(...)` predicates wrap the property in a function, so no index can serve them. If Organization.tildaid has no index, the planner may start from a Phi label scan and run tolower/contains on every Phi node in the database.
The fixCreate a uniqueness constraint on Organization.tildaid (or confirm one exists). This lets the plan start with a unique NodeIndexSeek on the organization, expand ORGANIZATION_TO_PATIENT to that org's patients, then HAS_PROTECTED_DATA_IN to their Phi, and apply the string filters only to that one org's Phi nodes.
Add this index
CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE
Expected: If the plan currently starts from a Phi or Patient scan, this could cut the number of rows the string filter checks by orders of magnitude. If the constraint already exists, nothing changes. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES for Organization.tildaid. In EXPLAIN, the leaf operator should be NodeUniqueIndexSeek on Organization, not NodeByLabelScan on Phi or Patient.
rewrite Apply the required EhrPhi match before the costly optional matches and aggregations
The issue`match(phi)-[]-(ehrPhi:EhrPhi)` is a required match, but it sits at the very end. Every candidate (phi, p, mg) row first goes through the User, AdvCampaignCondition and Binder→Document→ParticipantDocOverlay optional expansions and the eager collect()/count() aggregations, and only then gets dropped if it has no EhrPhi. Because the aggregations are eager, `limit $pgLimit` cannot cut this work short.
The fixMove the required MedicalGroup and EhrPhi matches up, straight after the PHI filter, so rows with no EhrPhi are dropped before the optional expansions run. The result set is unchanged: the same rows survive, and the grouping keys used by collect/count now just include ehrPhi, which is a carried variable that appeared in the final output anyway. Only the position of a required pattern changes.
Proposed rewrite
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId})
where (tolower(phi.firstname) contains tolower($phiFinder0) and tolower(phi.lastname) contains tolower($phiFinder1) and tolower(phi.dob) contains tolower($phiFinder2) ) or tolower(phi.mobilephone) contains tolower($phiFinder3)
with phi, p
match(p)-[]-(mg:MedicalGroup)
with phi, p, mg
match(phi)-[]-(ehrPhi:EhrPhi)
with phi, p, mg, ehrPhi
optional match(p)-[r:USER_TO_PATIENT]-(u:User)
with u.tildaid as patUserId, phi, p, mg, ehrPhi
optional match(p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition)
with patUserId, phi, p, mg, ehrPhi, collect(cond.tildaid) as condIds
optional match(p)-[:BINDER_FOR]-(:Binder{binderType:?, scope:?})-[:INCLUDES]-(doc:Document{docTypeName:?})-[:BELONGS_TO]-(pdo:ParticipantDocOverlay{participantReviewed:true})
with count(pdo) as pdoCount, phi, p, mg, ehrPhi, condIds, patUserId
optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId})
return phi, p, mg, ehrPhi, condIds, patUserId, pdoCount, adv
limit $pgLimit
Expected: Fewer rows reach the optional expansions and the eager aggregations. The gain depends on what share of matched Phi nodes have no EhrPhi neighbour, and could be small if almost all of them do. · Evidence: Run PROFILE on both versions with the same parameters. Compare estimated and actual rows going into the EagerAggregation operators, and total db hits. Also check that the returned rows match between the two versions.
execsvc · 31360F19 — 3 runs · p95 455 ms · 0 s total · 3d ago · proposed open shape & trend →
index Make sure the Training.tildaid lookup is backed by an index or constraint
The issueThe first clause, `MATCH (t:Training) WHERE t.tildaid IN $trainingIds AND t.scopeId = $studyId AND t.delpending = false`, drives the whole query. If Training.tildaid has no index or uniqueness constraint, Neo4j has to scan every Training node (NodeByLabelScan) and then filter on tildaid, scopeId and delpending, so the cost grows with the size of the Training label and not with the size of $trainingIds. That is one likely reason p95 (455 ms) is more than 3x the average (136 ms).
The fixRun SHOW CONSTRAINTS and SHOW INDEXES first. If tildaid is the unique business key for Training, which the name suggests, a uniqueness constraint gives you a seek index and also protects the data. The DDL is below. If duplicates are possible, use `CREATE INDEX training_tildaid IF NOT EXISTS FOR (t:Training) ON (t.tildaid)` instead. scopeId and delpending can then be cheap filters on the few rows the seek returns. If a constraint already exists, this suggestion gives no gain and can be dropped.
Add this index
CREATE CONSTRAINT training_tildaid_unique IF NOT EXISTS FOR (t:Training) REQUIRE t.tildaid IS UNIQUE
Expected: With the index, the first operator changes from a label scan to a NodeUniqueIndexSeek / NodeIndexSeek over the IN list. That should cut this stage from scaling with the Training count to scaling with |$trainingIds|. The whole statement only ran 3 times in 7 days, so the total saving is small. The main benefit is a lower and steadier p95. · Evidence: An EXPLAIN/PROFILE before and after, showing the Training operator change from NodeByLabelScan + Filter to an index seek, with fewer db hits. A p95 for shape 31360F19 after the change.
index Index User.tildaid for the per-trainee lookup in the subquery
The issueInside the CALL subquery, `MATCH (u:User{tildaid:uid})` runs once for each distinct trainee uid across the whole page, which can be hundreds of users. Without an index or constraint on User.tildaid, each of those lookups is a scan of the User label, so the cost is (distinct uids) × (User count). That is the most likely cause of the 455 ms outlier when a page has many site-scoped trainees.
The fixCheck SHOW CONSTRAINTS for User. If tildaid is unique, which is likely because it is used as an identifier, create the uniqueness constraint below so every uid becomes a single seek. If there is already an index or constraint on User.tildaid, skip this.
Add this index
CREATE CONSTRAINT user_tildaid_unique IF NOT EXISTS FOR (u:User) REQUIRE u.tildaid IS UNIQUE
Expected: Each uid lookup should change from a label scan plus filter to a NodeUniqueIndexSeek. The subquery's cost then scales with the number of uids and not with the size of the User label. The improvement is largest on pages with many siteUserIds. · Evidence: PROFILE showing NodeUniqueIndexSeek on User(tildaid) inside the Apply/subquery branch, and a lower db-hit count for that branch. A p95 comparison on calls with large trainee lists.
rewrite Start the site-membership OPTIONAL MATCH from the bound user
The issueThe subquery's `OPTIONAL MATCH (cs:Site{delpending:false})-[:PLAN_DEFINITION_TO_SITE]-(cpd:DslPlanDefinition{...studyId:$studyId})-...-(ca:Actor{scopeId:$studyId,...})-[:FOR_USER]->(u:User)` is a five-hop pattern with filters spread over Site, DslPlanDefinition and Actor. Because it repeats `(u:User)` and offers several labelled, filtered starting points, the planner may begin from DslPlanDefinition(studyId) or Site, expand to every actor in the study, and only then join on u (a NodeHashJoin or a repeated expand for each uid). It would do much less work by starting at u, which is already bound, and walking FOR_USER to that one user's few Actors. The relationship patterns are also undirected, so each hop is checked in both directions.
The fixPut the expansion in a correlated `CALL (u) { ... }` subquery whose first hop is `(u)<-[:FOR_USER]-(ca:Actor)`. This makes u the argument, so the traversal starts from one user's Actors and fans out only from there. The repeated `:User` label is removed. Directions are added where the model implies them (STUDYSITEACTOR_TO_ACTOR and FOR_USER are already directed in the original). The other hops are left undirected, because the original does not state their direction. If you know it, add arrows to halve the expansion checks. Results and placeholders are unchanged.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (t:Training) WHERE t.tildaid IN $trainingIds AND t.scopeId = $studyId AND t.delpending = false OPTIONAL MATCH (t)-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{state:$assignedState, deltimestamp:?}) WHERE $viewerOrgId = ? OR (ta.ownerOrgId = $viewerOrgId OR $viewerOrgId IN ta.deliveredOrgIds) WITH t, ta, [(ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity{deltimestamp:?}) WHERE NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY]->() } | [pa.createdat, pa.status, pa.dueDate]] AS attempts WITH t, collect(ta.userId) AS userIds, collect(reduce(latest = [], x IN attempts | CASE WHEN size(latest) = ? OR x[?] > latest[?] THEN x ELSE latest END)) AS taAttempts WITH collect({id:t.tildaid, trainees:size(userIds), siteUserIds:CASE WHEN (coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType) THEN [] ELSE userIds END, completed:size([a IN taAttempts WHERE size(a) > ? AND a[?] IN $completedStatuses]), overdue:size([a IN taAttempts WHERE size(a) > ? AND NOT a[?] IN $completedStatuses AND a[?] > ? AND a[?] < $nowMillis])}) AS page CALL (page) { UNWIND page AS row UNWIND row.siteUserIds AS uid WITH DISTINCT uid MATCH (u:User{tildaid:uid}) OPTIONAL MATCH (cs:Site{delpending:false})-[:PLAN_DEFINITION_TO_SITE]-(cpd:DslPlanDefinition{delpending:false, studyId:$studyId})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(cssa:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_ACTOR]->(ca:Actor{scopeId:$studyId, state:$activeActorState, delpending:false})-[:FOR_USER]->(u:User) WHERE ca.orgId = cs.orgId AND NOT EXISTS { (cs)-[:SITE_TO_ORGANIZATION]->(siteOrg:Organization) WHERE siteOrg.deltimestamp <> ? } AND ($viewerSiteOrgId = ? OR cs.orgId = $viewerSiteOrgId) WITH uid, collect(DISTINCT cs.tildaid) AS siteIds RETURN apoc.map.fromPairs(collect([uid, siteIds])) AS sitesByUser } UNWIND page AS row RETURN row.id, row.trainees, row.completed, row.overdue, size(apoc.coll.toSet(apoc.coll.flatten([uid IN row.siteUserIds | coalesce(sitesByUser[uid], [])])))
+ MATCH (t:Training) WHERE t.tildaid IN $trainingIds AND t.scopeId = $studyId AND t.delpending = false OPTIONAL MATCH (t)-[:TRAINING_TO_ASSIGNMENT]->(ta:TrainingAssignment{state:$assignedState, deltimestamp:?}) WHERE $viewerOrgId = ? OR (ta.ownerOrgId = $viewerOrgId OR $viewerOrgId IN ta.deliveredOrgIds) WITH t, ta, [(ta)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity{deltimestamp:?}) WHERE NOT EXISTS { (pa)-[:NEXT_PARTICIPANT_ACTIVITY]->() } | [pa.createdat, pa.status, pa.dueDate]] AS attempts WITH t, collect(ta.userId) AS userIds, collect(reduce(latest = [], x IN attempts | CASE WHEN size(latest) = ? OR x[?] > latest[?] THEN x ELSE latest END)) AS taAttempts WITH collect({id:t.tildaid, trainees:size(userIds), siteUserIds:CASE WHEN (coalesce(t.siteScope, ?) = $studyTeamScope) OR (coalesce(t.scopeType, ?) = $orgScopeType) THEN [] ELSE userIds END, completed:size([a IN taAttempts WHERE size(a) > ? AND a[?] IN $completedStatuses]), overdue:size([a IN taAttempts WHERE size(a) > ? AND NOT a[?] IN $completedStatuses AND a[?] > ? AND a[?] < $nowMillis])}) AS page CALL (page) { UNWIND page AS row UNWIND row.siteUserIds AS uid WITH DISTINCT uid MATCH (u:User{tildaid:uid}) CALL (u) { OPTIONAL MATCH (u)<-[:FOR_USER]-(ca:Actor{scopeId:$studyId, state:$activeActorState, delpending:false})<-[:STUDYSITEACTOR_TO_ACTOR]-(cssa:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(cpd:DslPlanDefinition{delpending:false, studyId:$studyId})-[:PLAN_DEFINITION_TO_SITE]-(cs:Site{delpending:false}) WHERE ca.orgId = cs.orgId AND NOT EXISTS { (cs)-[:SITE_TO_ORGANIZATION]->(siteOrg:Organization) WHERE siteOrg.deltimestamp <> ? } AND ($viewerSiteOrgId = ? OR cs.orgId = $viewerSiteOrgId) RETURN collect(DISTINCT cs.tildaid) AS siteIds } WITH uid, siteIds RETURN apoc.map.fromPairs(collect([uid, siteIds])) AS sitesByUser } UNWIND page AS row RETURN row.id, row.trainees, row.completed, row.overdue, size(apoc.coll.toSet(apoc.coll.flatten([uid IN row.siteUserIds | coalesce(sitesByUser[uid], [])])))
Expected: The subquery's work should become proportional to (uids × actors per user × sites per actor) instead of (actors in the study) joined against the uids. This may matter for large studies. Because the shape runs rarely, the gain is mostly in worst-case latency. It needs to be confirmed with PROFILE, because the planner may already choose the u-anchored plan. · Evidence: A PROFILE of the original showing whether the subquery starts at DslPlanDefinition/Site, or contains a hash join on u. Then a PROFILE of the rewrite showing an Argument(u) → Expand(FOR_USER) start, fewer db hits, and identical result rows on the same parameters.
dashrsvc · C242D5FA — 2 runs · p95 470 ms · 0 s total · 3d ago · proposed open shape & trend →
index Index Organization.tildaid for the study.srcorgid join and the recruiterOrg lookup
The issue`MATCH(org:Organization) WHERE org.tildaid=study.srcorgid` and `OPTIONAL MATCH (recruiterOrg:Organization{tildaid: adv.orgid})` both look up Organization by a value that is only known at runtime. Without an index on Organization(tildaid), each one becomes a NodeByLabelScan plus a Filter, and it runs once for every lead/study/patient row. That fits the gap between the 183 ms average and the 470 ms p95.
The fixFirst run SHOW INDEXES / SHOW CONSTRAINTS. If nothing covers Organization(tildaid) yet, create a range index on it. With the index, both lookups become NodeIndexSeek. If tildaid is meant to be a unique business key, a uniqueness constraint would be better (CREATE CONSTRAINT ... REQUIRE o.tildaid IS UNIQUE), because it also backs lookups with an index. Check for duplicates before adding the constraint.
Add this index
CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid)
Expected: If a label scan is happening now, each Organization lookup should drop from scanning every Organization node to a single seek, likely cutting several ms to tens of ms per call. With only 2 runs in 7 days, the total saving is small (well under 366 ms a week). The index would mainly help other statements that also filter Organization by tildaid. · Evidence: PROFILE the statement before and after. Look for NodeByLabelScan(Organization) changing to NodeIndexSeek on Organization(tildaid), and for lower db hits on those operators.
index Make sure the Lead entry point uses a tildaid lookup
The issue`MATCH(lead:Lead {tildaid: $id})` is where the whole query starts. If Lead(tildaid) has no index, the query begins by scanning every Lead node, and every later expansion waits on that scan.
The fixIf SHOW INDEXES shows nothing covering Lead(tildaid), create the index (or a uniqueness constraint if tildaid is unique per Lead). The anchor then becomes a single NodeIndexSeek.
Add this index
CREATE INDEX lead_tildaid IF NOT EXISTS FOR (l:Lead) ON (l.tildaid)
Expected: If a scan is happening now, the cost of finding the starting node should drop from proportional to the number of Leads to roughly constant. If an index already exists, nothing changes. · Evidence: In PROFILE, the first operator should show NodeIndexSeek on Lead(tildaid), not NodeByLabelScan. Then compare p95 over the next window.
index Index User.tildaid for the recruiter lookup
The issue`OPTIONAL MATCH (user:User{tildaid: adv.userid})` looks up a User by a runtime value taken from the advocate. Without an index on User(tildaid), this is a label scan over all Users for every advocate row.
The fixIf nothing covers User(tildaid) yet, create a range index on it (or a uniqueness constraint if tildaid is a key). The optional lookup then becomes a NodeIndexSeek.
Add this index
CREATE INDEX user_tildaid IF NOT EXISTS FOR (u:User) ON (u.tildaid)
Expected: This removes a per-row scan of all Users if one exists today. Because the statement runs so rarely, the total benefit is small, but every other User-by-tildaid lookup in dashrsvc would also get faster. · Evidence: PROFILE should show NodeIndexSeek on User(tildaid) for the user OPTIONAL MATCH. SHOW INDEXES should confirm the index is ONLINE.
schedsvc · 5264C101 — 7 runs · p95 395 ms · 0 s total · 3d ago · proposed open shape & trend →
index Index the anchor lookup User.email
The issueThe whole query starts from `match(:User{email:$staffEmail})`. `email` is not in the list of indexed/touched properties (only tildaid and type are). If User.email has no index, the planner has to scan every User node and filter on email, and that cost is paid before any expansion. One heavy scan like this could explain the gap between a 47 ms average and a 395 ms p95.
The fixAdd a lookup on User.email so the query starts with a NodeIndexSeek. If emails are unique per user, use a uniqueness constraint instead (CREATE CONSTRAINT user_email_unique IF NOT EXISTS FOR (u:User) REQUIRE u.email IS UNIQUE). That gives the same seek and also guarantees data integrity. Run SHOW INDEXES first, because an index may already exist under another name.
Add this index
CREATE INDEX user_email IF NOT EXISTS FOR (u:User) ON (u.email)
Expected: If no index exists today, the starting step should go from a full scan of all User nodes to a single seek. That probably cuts several to tens of ms per call, more on the slow p95 calls. The total saving is small because the query ran only 7 times in 7 days. · Evidence: Run SHOW INDEXES WHERE labelsOrTypes = ['User'] to see whether an index exists. Compare PROFILE before and after: the first operator should change from NodeByLabelScan + Filter to NodeIndexSeek (or NodeUniqueIndexSeek), and db hits for that operator should drop.
index Index Organization.alias used in the Site/SMO optional match
The issue`optional match (v)-[]-(s:Site)-[:SITE_TO_SMO]->(smo:Smo)-[:IS_AN_ORG]->(o:Organization{alias:$orgAlias})` filters on Organization.alias, which is not listed as indexed. This optional match runs once per VisitAppointment row, after those rows have already been multiplied by every matching Phi. With no index, the planner can only expand from v through an untyped `-[]-` relationship and check alias at the end. It cannot start from the single matching Organization and join back.
The fixCreate an index on Organization.alias. The planner can then seek the one Organization, expand o<-IS_AN_ORG-smo<-SITE_TO_SMO-s, and value-hash-join on s instead of expanding every relationship of every v. This works best if alias is selective; if it's unique, a uniqueness constraint is better.
Add this index
CREATE INDEX organization_alias IF NOT EXISTS FOR (o:Organization) ON (o.alias)
Expected: Probably a moderate reduction in db hits for the optional-match branch on calls that return many visits. The effect will be small on calls that return only a few visits. · Evidence: Look at PROFILE output for the OptionalExpand/Filter on Organization.alias before the change, and check whether a NodeIndexSeek on Organization(alias) plus a join appears afterwards. Compare db hits and p95 over the next week.
studiosvc · D8455F04 — 3 runs · p95 455 ms · 0 s total · 3d ago · proposed open shape & trend →
rewrite Count all four buckets in a single expansion with conditional counts
The issueThe statement walks (org)-[:ORGANIZATION_TO_FORM]->(form:FormV2 {type:$formType}) four times: once in the MATCH and once in each of the three OPTIONAL MATCHes (published, draft, archived). Each pass re-expands every form on the org and re-checks type/state. Cost grows with the number of forms per org, which may explain why p95 (455 ms) is about 5× the average (90 ms) for orgs with many forms.
The fixExpand once, then compute each bucket with count(CASE WHEN … THEN 1 END). The allCount CASE uses the same activityType/tag/state predicates as the original WHERE. A predicate that evaluates to null is not counted, which matches how WHERE filters out null. The published, draft and archived counts don't use those extra filters, just as the original OPTIONAL MATCHes didn't. The original returns no row when the first MATCH finds nothing, so `WHERE allCount > 0` keeps that behaviour. Drop it if the caller would rather get a row of zeros.
Proposed rewrite
MATCH (org:Organization {tildaid:$orgId})-[:ORGANIZATION_TO_FORM]->(form:FormV2 {type:$formType})
WITH org,
count(CASE WHEN (form.activityType IS NULL OR NOT form.activityType IN $excludeActivityTypes) AND NOT coalesce(form.tag, ?) IN $systemFormTags AND NOT form.state = ? THEN 1 END) AS allCount,
count(CASE WHEN form.state = ? THEN 1 END) AS publishedCount,
count(CASE WHEN form.state = ? THEN 1 END) AS draftCount,
count(CASE WHEN form.state = ? THEN 1 END) AS archivedCount
WHERE allCount > 0
RETURN allCount, publishedCount, draftCount, archivedCount
Expected: About 4× fewer relationship expansions and property reads per call. That could make the worst-case orgs roughly 2–4× faster. But the shape ran only 3 times in 7 days (270 ms total), so the absolute saving is tiny. Do it for tail latency and readability, not throughput. · Evidence: Run PROFILE on both versions for an org with many forms. Compare total db hits and check that Expand(All) appears once instead of four times. Compare results on several orgs, including one with no matching forms, to confirm the output is the same.
index Make sure Organization.tildaid is backed by a uniqueness constraint
The issueThe whole query is anchored on (org:Organization {tildaid:$orgId}). If tildaid has no index or constraint, the planner runs a NodeByLabelScan plus a Filter over every Organization before it expands, and the expansion is repeated per anchor.
The fixIf SHOW CONSTRAINTS / SHOW INDEXES doesn't already list tildaid on Organization, add a uniqueness constraint. It creates a backing range index and guarantees a single anchor row. If one already exists, nothing needs doing here.
Add this index
CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE
Expected: Only matters if no index exists today. In that case the anchor step drops from a scan that grows with the number of Organization nodes to a single index seek, which is probably most of the 455 ms p95. If the index already exists, there is no change. · Evidence: EXPLAIN before and after: the first operator should change from NodeByLabelScan+Filter to NodeUniqueIndexSeek on Organization(tildaid). Then check p95 for shape D8455F04 after the deploy.
execsvc · 8EC5507C — 4 runs · p95 440 ms · 0 s total · 3d ago · proposed open shape & trend →
rewrite Reuse the bound cfg node instead of re-matching cfg2 by tildaid
The issueThe first `WITH cfgId, collect(DISTINCT ...) AS allItems` drops `cfg`. So the second `OPTIONAL MATCH (cfg2:BranchConfig {tildaid: cfgId})-[:BRANCH_CG_ANY_NEXT*..]->...` has to look up the BranchConfig node again for every cfgId. If BranchConfig.tildaid has no index or constraint, that second lookup is a full NodeByLabelScan for each id in $cfgIds. Even with an index, it is a wasted second seek.
The fixKeep `cfg` in the first WITH's grouping key and start the ANY-branch expansion from it. Nothing else changes. One caveat: if several BranchConfig nodes could share a tildaid, grouping by cfg as well as cfgId would split their rows. That can't happen if tildaid is unique (see the constraint suggestion).
Proposed rewrite
UNWIND $cfgIds AS cfgId
MATCH (cfg:BranchConfig {tildaid: cfgId})
OPTIONAL MATCH (cfg)-[:BRANCH_CG_ALL_NEXT*?..]->(gAll:BranchConditionGroup)-[:CG_TO_CONDITION*?..]->(allCond:BranchCondition)
WITH cfgId, cfg, collect(DISTINCT {kind:?, g:gAll, c:allCond}) AS allItems
OPTIONAL MATCH (cfg)-[:BRANCH_CG_ANY_NEXT*?..]->(gAny:BranchConditionGroup)-[:CG_TO_CONDITION*?..]->(anyCond:BranchCondition)
WITH cfgId, allItems, collect(DISTINCT {kind:?, g:gAny, c:anyCond}) AS anyItems
WITH cfgId, allItems + anyItems AS items
UNWIND items AS item
WITH cfgId, item.kind AS kind, item.g AS g, item.c AS c
WHERE c IS NOT NULL
OPTIONAL MATCH (ref:GraphNodeRef {tildaid:c.refid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION {protocolid:$protocolId}]->(q:QuestionV2)
RETURN cfgId AS cfgId, kind AS groupKind, coalesce(g.tildaid, ?) AS groupId, coalesce(g.matchall, false) AS groupMatchAll, coalesce(c.tildaid, ?) AS conditionId, coalesce(c.refid, ?) AS refId, coalesce(c.variableid, ?) AS variableId, coalesce(c.operator, ?) AS operator, coalesce(c.choices, ?) AS choices, coalesce(c.choicevaluesjson, ?) AS choiceValuesJson, coalesce(c.matchall, false) AS conditionMatchAll, coalesce(head([(:GraphNodeRef {tildaid: c.visitrefid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE {protocolid: $protocolId}]->(v:VisitV2) | v.tildaid]), c.visitid, ?) AS visitId, coalesce(head([(:GraphNodeRef {tildaid: c.activityrefid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE {protocolid: $protocolId}]->(a:ActivityV2) | a.tildaid]), c.activityid, ?) AS activityId, coalesce(head([(:GraphNodeRef {tildaid: c.questionnairerefid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE {protocolid: $protocolId}]->(qnr:QuestionnaireV2) | qnr.tildaid]), c.questionnaireid, ?) AS questionnaireId, coalesce(c.tableid, ?) AS tableId, coalesce(q.tildaid, ?) AS questionId
Expected: Removes one BranchConfig lookup per cfgId. That is a small gain if tildaid is indexed. If it isn't, it removes a label scan per id, which could explain part of the 440 ms p95. The volume is only 4 runs a week, so the absolute saving is modest. · Evidence: PROFILE before and after: the second NodeIndexSeek or NodeByLabelScan on BranchConfig should disappear, and db hits should drop. Run the old and new versions on the same $cfgIds and diff the results to confirm the rows are identical.
index Unique constraint on GraphNodeRef.tildaid (used in 4 lookups per condition row)
The issueEvery condition row runs four GraphNodeRef lookups by tildaid. One is `OPTIONAL MATCH (ref:GraphNodeRef {tildaid:c.refid})`. The other three are the pattern comprehensions on c.visitrefid, c.activityrefid and c.questionnairerefid. Without an index or constraint on GraphNodeRef(tildaid), each lookup is a NodeByLabelScan plus a filter, so the cost grows as conditions × 4 × the number of GraphNodeRef nodes. This is the most likely source of the 440 ms p95 outlier.
The fixIf tildaid is the node key (the naming suggests it is), add a uniqueness constraint. That creates a backing range index, and the planner uses it for every `{tildaid: ...}` lookup in this query. First check SHOW CONSTRAINTS / SHOW INDEXES. If an index already exists, skip this.
Add this index
CREATE CONSTRAINT graphnoderef_tildaid_unique IF NOT EXISTS FOR (n:GraphNodeRef) REQUIRE n.tildaid IS UNIQUE
Expected: If the constraint is missing today, each GraphNodeRef lookup should become a NodeUniqueIndexSeek, and the p95 for config sets with many conditions could fall substantially. If an index already exists, nothing changes. · Evidence: Run SHOW INDEXES WHERE 'GraphNodeRef' IN labelsOrTypes first. After creating the constraint, PROFILE should show NodeUniqueIndexSeek(GraphNodeRef.tildaid) instead of NodeByLabelScan. Then compare p95 over the next week.
index Unique constraint on BranchConfig.tildaid for the UNWIND entry point
The issue`UNWIND $cfgIds AS cfgId MATCH (cfg:BranchConfig {tildaid: cfgId})` (and cfg2 in the original) starts the whole query with one BranchConfig lookup per id. Without a supporting index, each id costs a full label scan.
The fixAdd a uniqueness constraint on BranchConfig.tildaid. This guarantees the per-id lookup is a seek, and it also makes the cfg-reuse rewrite safe, because each cfgId then matches exactly one node. Skip it if SHOW CONSTRAINTS already lists one.
Add this index
CREATE CONSTRAINT branchconfig_tildaid_unique IF NOT EXISTS FOR (n:BranchConfig) REQUIRE n.tildaid IS UNIQUE
Expected: If no index exists, each per-id lookup goes from a label scan to an O(log n) seek. The gain grows with the size of $cfgIds and the number of BranchConfig nodes. If an index already exists, nothing changes. · Evidence: SHOW INDEXES for BranchConfig. PROFILE should show NodeUniqueIndexSeek on BranchConfig(tildaid) as the leaf operator after the Unwind.
studiosvc · AF480177 — 1 runs · p95 485 ms · 0 s total · 3d ago · proposed open shape & trend →
rewrite Count all four buckets in one traversal using conditional counts
The issueThe statement walks the same (org)-[:ORGANIZATION_TO_FORM]->(form:FormV2 {type:$formType}) neighbourhood four times: one MATCH and three OPTIONAL MATCHes. Each pass re-reads every related FormV2 node and re-checks scopeid, activityType and state. That's about 4× the relationship expansions and property reads needed, so cost grows with the organisation's form count times four. The 485 ms p95 next to a 238 ms average suggests a large org.
The fixExpand once and apply the shared filters (type, scopeid, activityType) once. Then compute the four counts with count(CASE WHEN ... THEN 1 END), grouped by org. Comparisons against a null state give null, so those forms are not counted, the same as in the original filters. The original first MATCH is not optional, so it returns no row when allCount is 0. `WHERE allCount > 0` after the aggregation keeps that behaviour. The four state placeholders stay in their original order: the excluded state first, then published, draft and archived.
Proposed rewrite
MATCH (org:Organization {tildaid:$orgId})-[:ORGANIZATION_TO_FORM]->(form:FormV2 {type:$formType})
WHERE form.scopeid = ? AND (form.activityType IS NULL OR NOT form.activityType IN $excludeActivityTypes)
WITH org, form.state AS state
WITH org,
count(CASE WHEN NOT state = ? THEN 1 END) AS allCount,
count(CASE WHEN state = ? THEN 1 END) AS publishedCount,
count(CASE WHEN state = ? THEN 1 END) AS draftCount,
count(CASE WHEN state = ? THEN 1 END) AS archivedCount
WHERE allCount > 0
RETURN allCount, publishedCount, draftCount, archivedCount
Expected: About 4× fewer relationship expansions and FormV2 property reads, so latency could drop by roughly 50–75% for orgs with many forms. But this ran only once in 7 days (238 ms total), so the absolute saving is small. Treat it as a cleanup, not a priority. · Evidence: Run PROFILE on both versions against the same org with a large form count. Compare total db hits and rows on the Expand(All) operators: there should be one expand instead of four. Also check that the four counts match exactly for several orgs, including one whose forms are all in the excluded state, which should return no row in both versions.
index Make sure the Organization.tildaid anchor uses a unique lookup
The issueEverything depends on finding the starting node with (org:Organization {tildaid:$orgId}). tildaid is not in the listed properties for this shape, so I can't confirm it is indexed. Without an index or constraint, the planner does a NodeByLabelScan over every Organization and filters by tildaid on each call.
The fixtildaid looks like an external ID, so back it with a uniqueness constraint. That also creates the index used for the lookup. If duplicates already exist the constraint will fail to create. In that case, use CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid) instead.
Add this index
CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE
Expected: If the anchor is currently a label scan, the start becomes a single NodeUniqueIndexSeek, which could save tens of milliseconds or more depending on how many Organization nodes there are. If a constraint already exists, nothing changes. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES WHERE 'Organization' IN labelsOrTypes. Then EXPLAIN the statement: the first operator should be NodeUniqueIndexSeek (or NodeIndexSeek), not NodeByLabelScan.
dashrsvc · E60362D8 — 4 runs · p95 440 ms · 0 s total · 3d ago · proposed open shape & trend →
index Make sure the ParticipantVisit.tildaid anchor is backed by a uniqueness constraint
The issueThe whole statement starts at `MATCH (pv:ParticipantVisit {tildaid: $id})`. If no index or constraint covers ParticipantVisit.tildaid, the planner does a NodeByLabelScan plus a Filter over every ParticipantVisit, and then runs the 5-hop CarePlan→SOA→PlanDefinition→Study chain from there. The 440 ms p95 against a 54 ms average fits either a scan or a cold page cache. With only 4 runs in 7 days I can't tell which.
The fixtildaid works like an external ID, so declare it unique. A uniqueness constraint creates a backing range index, so the anchor becomes a NodeUniqueIndexSeek that returns at most one row. If SHOW CONSTRAINTS already lists one for this label and property, skip this step: the anchor is already optimal.
Add this index
CREATE CONSTRAINT participantvisit_tildaid_unique IF NOT EXISTS FOR (pv:ParticipantVisit) REQUIRE pv.tildaid IS UNIQUE
Expected: If the anchor is scanning today, the per-call time should fall to a few ms because the remaining expansions are small and walk from one node. If an index already exists, expect no change. Total cost is small either way (214 ms over 7 days), so the main gain is a more stable p95. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES for ParticipantVisit(tildaid). Run PROFILE on the statement and check whether the first operator is NodeUniqueIndexSeek or NodeIndexSeek rather than NodeByLabelScan. Also check that the label has no duplicate tildaid values before you create the constraint.
rewrite Turn the unused Visit match into an existence check
The issue`MATCH (pv)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2)` binds `v`, but nothing reads it later. It only acts as a filter. If a ParticipantVisit links to more than one Visit/VisitV2 node, every later expansion (CarePlan chain, three OPTIONAL MATCHes) and every apoc.create.vNode call is repeated once per visit, giving duplicate virtual nodes.
The fixSwap the pattern for an EXISTS subquery. It stops at the first matching Visit, so no extra rows are produced. The filter is the same: rows without a visit are still excluded. The only difference is that you get one row instead of N duplicates when there are several visits.
Proposed rewrite
MATCH (pv:ParticipantVisit {tildaid: $id})
WHERE EXISTS { (pv)-[:BELONGS_TO_VISIT]-(:Visit|VisitV2) }
MATCH (pv)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)-[:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(s:Study)
OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)
OPTIONAL MATCH (site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(org:Organization)
OPTIONAL MATCH (cp)-[:RESEARCH_SUBJECT]-(p:Patient)
CALL apoc.
create.vNode([?], { siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), studyId: s.tildaid, studyName: s.name, orgId: coalesce(org.tildaid, ?), orgName: coalesce(org.name, ?), orgAlias: coalesce(org.alias, ?), createdat: pv.createdat, updatedat: pv.updatedat, branchLevel: pv.branchLevel, pre: pv.pre, migrationReady: pv.migrationReady, sex: p.sex, ethnicity: p.ethnicity, carePlanStatus: cp.status, pvStatus: pv.status, edcStatus: pv.edcStatus })
YIELD node
RETURN node
Expected: If each pv has exactly one visit, results and cost stay the same. If any pv has several visits, rows and vNode calls should drop by that factor, and callers stop getting duplicate nodes. · Evidence: Count visits per pv: MATCH (pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) WITH pv, count(v) AS c WHERE c > 1 RETURN count(pv). Then compare PROFILE row counts after the first MATCH, old vs new, and confirm callers never relied on the duplicate rows.
regsvc · 0BFC7296 — 4 runs · p95 440 ms · 0 s total · 3d ago · proposed open shape & trend →
index Composite index for the DslPlanDefinition anchor lookup
The issue`MATCH (pd:DslPlanDefinition{studyId:$studyId, scopeId:$siteId})` filters on two properties that are not a node key. If neither is indexed, the planner has to scan every DslPlanDefinition node and filter it (NodeByLabelScan + Filter). That is a likely cause of the 440 ms p95 against a 53 ms average. Because this anchor comes first, every later expansion depends on it.
The fixAdd a composite range index on (studyId, scopeId) so the anchor becomes a NodeIndexSeek. Check SHOW INDEXES first, since an index may already exist. If each study/site pair can have only one plan definition, use a composite uniqueness constraint instead (CREATE CONSTRAINT ... REQUIRE (pd.studyId, pd.scopeId) IS UNIQUE). It creates the backing index and also protects the MERGE's semantics.
Add this index
CREATE INDEX dsl_plan_definition_study_scope IF NOT EXISTS FOR (pd:DslPlanDefinition) ON (pd.studyId, pd.scopeId)
Expected: If the plan currently shows a label scan, the anchor cost should drop from O(#DslPlanDefinition) to a few db hits, probably taking the tail into single-digit ms. The volume is only 4 calls per week, so the total time saved is small. The main gain is a lower tail latency and shorter write-lock hold time. · Evidence: Run EXPLAIN/PROFILE before the change and look for NodeByLabelScan(pd:DslPlanDefinition) + Filter. After the change, look for NodeIndexSeek on DslPlanDefinition(studyId, scopeId). Also compare p95 for shape 0BFC7296 over the next window.
index Uniqueness constraint on WorkflowV2.tildaid
The issue`MATCH (w:WorkflowV2{tildaid:$workflowId})` looks up by an external ID. Without an index or constraint on tildaid, it scans all WorkflowV2 nodes, and that label probably grows with every workflow version. The planner might even choose to start from Trigger or pd and filter w later.
The fixtildaid looks like a unique external identifier, so declare it with a uniqueness constraint. This creates the backing index, gives the planner a cardinality-1 estimate (a stable plan), and prevents duplicates that would make MERGE create several adopted edges. If duplicates are legitimate, use a plain CREATE INDEX ... ON (w.tildaid) instead.
Add this index
CREATE CONSTRAINT workflowv2_tildaid_unique IF NOT EXISTS FOR (w:WorkflowV2) REQUIRE w.tildaid IS UNIQUE
Expected: w should resolve with a NodeUniqueIndexSeek, which removes a possible full-label scan. The improvement is probably largest on the slow tail executions. · Evidence: Run SHOW CONSTRAINTS/SHOW INDEXES to see whether one already exists. Check that MATCH (w:WorkflowV2) WITH w.tildaid AS id, count(*) AS c WHERE c > 1 RETURN count(*) returns 0 before creating the constraint. Afterwards, PROFILE should show NodeUniqueIndexSeek(w).
rewrite Remove the self-referential Trigger filter and stop deleting the edge MERGE just matched
The issueThe OPTIONAL MATCH reuses the already-bound `t` as `(t:Trigger{scope: t.scope, operation: t.operation})`. That filter always passes (t compared with itself), so it only costs extra property reads. There is also a bigger problem: when pd has already adopted w, the OPTIONAL MATCH binds oldWorkflow = w and r = that same edge. MERGE then matches that edge, and `delete r` removes it, so a re-adopt call silently drops the adoption. If w has several triggers, every row also re-runs MERGE/DELETE.
The fixWrite the old-workflow pattern against plain `(t)`, which gives identical matching without the redundant property predicate. Add `WHERE oldWorkflow <> w` so the existing adoption edge for w is never deleted. Note that this is a behaviour change: it fixes the self-delete case. Separately, check with the service owner whether the intent was to find an old workflow on a *different* trigger with the same scope and operation. If so, bind a new variable, e.g. (t2:Trigger) WHERE t2.scope = t.scope AND t2.operation = t.operation, and the query needs that version instead.
Proposed rewrite
MATCH (pd:DslPlanDefinition{studyId:$studyId, scopeId:$siteId})
MATCH (w:WorkflowV2{tildaid:$workflowId})-[:TRIGGER_TO_WORKFLOW]-(t:Trigger)
OPTIONAL MATCH (pd)-[r:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]->(oldWorkflow:WorkflowV2)-[:TRIGGER_TO_WORKFLOW]-(t)
WHERE oldWorkflow <> w
MERGE (pd)-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]->(w)
DELETE r
RETURN oldWorkflow.tildaid AS oldWorkflowId, t.operation
Expected: Expect a small per-call saving (fewer property reads and Filter rows). The main effect is correctness: re-adopting the current workflow leaves the edge in place instead of deleting it. · Evidence: In a test DB, call the statement twice with the same $workflowId and check whether the PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS edge from pd to w still exists afterwards. With the original query it should be gone; with the rewrite it should remain. Also compare PROFILE db hits on the OPTIONAL MATCH branch.