Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 30 days▾
Ends now. Anything up to this cluster's 30-day retention.
Start and end times
Cancel
Interpreted in UTC (change it at the right of this bar). Earliest available: 2026-10-19 18:19:35.
› →|
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 5

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

ontologysvc · 746FCA47 — 4 runs · p95 1.95 s · 4 s total · 3d ago · proposed open shape & trend →

index Index User.email so the anchor lookup and supportUser lookup can seek
The issue`MATCH (currentUser:User {deltimestamp:?}) WHERE ... currentUser.email = $ident` and `OPTIONAL MATCH (supportUser:User {email:$supportEmail})` both filter User only by property. If User(email) has no index, each one becomes a NodeByLabelScan over every User followed by a Filter. That happens twice per call, and every later expansion waits on it. A p95 of 1950 ms for what should be a single-user check fits a full label scan.
The fixCreate a range index on User(email). Both the anchor and the supportUser OPTIONAL MATCH then become NodeIndexSeek. The deltimestamp and isDummy checks stay as a cheap filter on the one or few rows that come back. If User.email is meant to be unique, a uniqueness constraint (CREATE CONSTRAINT user_email_unique IF NOT EXISTS FOR (u:User) REQUIRE u.email IS UNIQUE) does the same job and also stops supportUser from adding duplicate rows. Only do that if the data really is unique, including soft-deleted users with deltimestamp set.
Add this index
CREATE INDEX user_email IF NOT EXISTS FOR (u:User) ON (u.email)
Expected: The two User lookups could drop from a scan proportional to the User count to a seek of roughly 1 ms. Call volume is low (4 calls in 7 days), so the total saving is small, but p95 latency for the caller could fall sharply if the scan is the main cost. · Evidence: Run EXPLAIN/PROFILE before and after. Before should show NodeByLabelScan(User) feeding Filter(email = $ident). After should show NodeIndexSeek on User(email), with fewer db hits on the first operator. Also run SHOW INDEXES to check that no email index exists yet.
index Index Organization.alias for targetOrg and the Tilda scale org lookups
The issue`OPTIONAL MATCH (targetOrg:Organization {delpending:false, alias:$org})` filters by alias, and both EXISTS subqueries match `(:Organization{alias:$tildaScaleAlias})`. Without an index on Organization(alias), targetOrg is a label scan. Inside the EXISTS the planner may also scan Organization to anchor from the alias side instead of expanding from currentUser.
The fixCreate a range index on Organization(alias). targetOrg becomes an index seek, and the planner can seek the scale org and join it to the short expansion from currentUser. If alias is a business key, use a uniqueness constraint instead. delpending stays as a residual filter.
Add this index
CREATE INDEX organization_alias IF NOT EXISTS FOR (o:Organization) ON (o.alias)
Expected: targetOrg resolution becomes a seek instead of a scan. The EXISTS subqueries may get cheaper if the planner now anchors on the seek. The gain depends on how many Organization nodes there are. · Evidence: PROFILE should show NodeIndexSeek on Organization(alias) for targetOrg. Inside the EXISTS subqueries there should be no NodeByLabelScan(Organization). Compare db hits and p95 after the deploy.
rewrite Replace the orgActor and supports OPTIONAL MATCHes with EXISTS booleans
The issue`OPTIONAL MATCH (currentUser)-[:FOR_USER]-(orgActor:Actor...)-[:ORGANIZATION_TO_ACTOR]-(targetOrg)` and `OPTIONAL MATCH (currentUser)-[supports:SUPPORTS_ORG]-(targetOrg)` keep one row per matching actor or relationship. Yet orgActor and supports are only ever checked for IS NULL / IS NOT NULL. A user with several actors in the org, or several SUPPORTS_ORG relationships, multiplies the rows. That means repeated delegated-EXISTS evaluation and duplicate output rows. Both patterns are also undirected, so each relationship is checked from both sides.
The fixTurn both into existence checks that short-circuit at the first match and always leave one row. Also skip the costly delegated subquery when targetOrg is null. Semantics change: when several orgActors exist, the original returned duplicate identical rows, and this version returns one. Check that callers don't depend on the row count. If the relationship directions are known (for example FOR_USER points Actor->User, as the EXISTS clauses suggest), add arrows to the orgActor pattern as well.
Proposed rewrite
MATCH (currentUser:User {deltimestamp:?}) WHERE (currentUser.isDummy IS NULL OR currentUser.isDummy <> true) AND currentUser.email = $ident
OPTIONAL MATCH (supportUser:User {email:$supportEmail})
OPTIONAL MATCH (targetOrg:Organization {delpending:false, alias:$org})
WITH currentUser, supportUser, targetOrg, false AS repOf,
  (targetOrg IS NOT NULL AND EXISTS { MATCH (currentUser)-[:FOR_USER]-(orgActor:Actor{delpending:false})-[:ORGANIZATION_TO_ACTOR]-(targetOrg) WHERE coalesce(orgActor.state, ?) <> ? }) AS hasOrgActor,
  (targetOrg IS NOT NULL AND EXISTS { MATCH (currentUser)-[:SUPPORTS_ORG]-(targetOrg) } AND EXISTS { MATCH (currentUser)<-[:FOR_USER]-(scaleActor:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}), (scaleActor)-[:APPOINTED_AS]->(scaleRole:Role) WHERE coalesce(scaleActor.state, ?) <> ? AND scaleRole.name <> ? }) AS hasSupports
WITH *, CASE WHEN NOT hasOrgActor AND targetOrg IS NOT 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 $delegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) } ELSE false END AS delegated
RETURN CASE WHEN hasOrgActor THEN currentUser WHEN delegated THEN currentUser WHEN repOf THEN currentUser WHEN hasSupports THEN coalesce(supportUser, currentUser) ELSE currentUser END AS user, CASE WHEN hasOrgActor OR delegated OR repOf OR hasSupports THEN targetOrg ELSE null END AS org, currentUser AS tokenUser
Expected: The output is always 1 row per currentUser/supportUser/targetOrg combination, and each check stops at the first match. That should cut db hits for users with many actors or relationships. The effect is small for users with exactly one match. · Evidence: PROFILE both versions for a user with several Actor nodes in the target org. Compare rows coming out of the OptionalExpand operators and total db hits, and check that the user/org/tokenUser results are the same, apart from the duplicate rows going away.

authzd · 94FF1D22 — 10 runs · p95 1.83 s · 4 s total · 3d ago · proposed open shape & trend →

index Index Organization.alias (used three times per call)
The issue`Organization` is looked up by `alias` in three places: `OPTIONAL MATCH (targetOrg:Organization{delpending:false, alias:$orgAlias})`, and `(:Organization{alias:$tildaScaleAlias})` inside both the SUPPORTS_ORG EXISTS and the `delegated` EXISTS. If no index covers alias, each of these is a NodeByLabelScan plus a property filter over every Organization. The two EXISTS subqueries run once for each driving row, so the scans repeat. That fits the gap between the 354 ms average and the 1.8 s p95.
The fixAdd a lookup on Organization.alias so every alias predicate becomes a NodeIndexSeek. If alias is meant to be unique, use a uniqueness constraint instead (`CREATE CONSTRAINT organization_alias_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.alias IS UNIQUE`). It creates the backing index and also guarantees one row per alias. If SHOW INDEXES already lists an index on this property, skip this item.
Add this index
CREATE INDEX organization_alias IF NOT EXISTS FOR (o:Organization) ON (o.alias)
Expected: Each alias lookup should become an index seek costing a few DB hits instead of a scan of all Organizations. The biggest gain should come where the EXISTS subqueries currently start from a scanned Organization, which could cut the p95 a lot. This depends on how many Organization nodes there are and what the plan does today. · Evidence: Run SHOW INDEXES to check for an existing index on Organization(alias). Then PROFILE the query with a non-member user (so the `delegated` branch runs) before and after the change. Look for NodeIndexSeek on Organization(alias) in place of NodeByLabelScan, and compare total DB hits and the 7-day p95.
index Index User.email for currentUser and supportUser anchors
The issueThe whole query is anchored on `MATCH (currentUser:User{deltimestamp:?,deltimestamp:?,email:$email})`, and `OPTIONAL MATCH (supportUser:User{email:$supportUserEmail})` does a second lookup by email. Without an email index, both are label scans over all User nodes with property filters. Every other clause depends on these anchors.
The fixCreate a range index on User.email so both lookups become NodeIndexSeek. A single-property index serves both: the deltimestamp predicate is checked cheaply on the one or few matching rows, and supportUser has no deltimestamp predicate, so a composite (email, deltimestamp) index would not cover it. Also check the normalised `deltimestamp:?,deltimestamp:?` duplicate in the source. If the two literals ever differ, the MATCH can never return a row.
Add this index
CREATE INDEX user_email IF NOT EXISTS FOR (u:User) ON (u.email)
Expected: Each User lookup should drop from a cost proportional to the User count to a handful of DB hits. That could save tens to hundreds of ms per call, depending on how many User nodes there are. · Evidence: Run SHOW INDEXES for User(email). Compare PROFILE output before and after: look for NodeIndexSeek on User(email) for both currentUser and supportUser, and fewer rows and DB hits on the first operator.
rewrite Look up the TildaScale org once and skip the delegated check when targetOrg is null
The issueBoth EXISTS subqueries contain an inline anonymous pattern `(:Organization{alias:$tildaScaleAlias})`, so that org is found again inside every subquery evaluation. In the `delegated` CASE, the long 6-hop pattern (FOR_USER → STUDY_TO_DELEGATED_ACTOR → STUDY_TO_ORG plus APPOINTED_AS/DELEGATED_FROM, then apoc.text.split on dr.tags) is attempted whenever orgActor is NULL. That includes rows where targetOrg itself is NULL and the pattern can never match. The planner may expand outward from currentUser through all of the user's Actors and Studies before applying the targetOrg and scaleOrg filters.
The fixLook up the TildaScale org once with an early `OPTIONAL MATCH (scaleOrg:Organization{alias:$tildaScaleAlias})` and refer to the bound `scaleOrg` inside both EXISTS blocks. A bound node lets the planner anchor or prune from it instead of re-matching by label and property. Also guard the delegated CASE with `targetOrg IS NOT NULL`. A null targetOrg already makes the EXISTS false, so the result is the same, but the subquery is skipped entirely. This assumes alias is unique. If several orgs shared the TildaScale alias, the new OPTIONAL MATCH would multiply rows.
Proposed rewrite
MATCH (currentUser:User{deltimestamp:?,deltimestamp:?,email:$email}) WHERE (currentUser.isDummy IS NULL OR currentUser.isDummy <> true)
OPTIONAL MATCH (supportUser:User{email:$supportUserEmail})
OPTIONAL MATCH (targetOrg:Organization{delpending:false, alias:$orgAlias})
OPTIONAL MATCH (scaleOrg:Organization{alias:$tildaScaleAlias})
// A deactivated (Inactive) org actor is not a membership (ENG-?).
OPTIONAL MATCH (currentUser)-[:FOR_USER]-(orgActor:Actor{delpending:false})-[:ORGANIZATION_TO_ACTOR]-(targetOrg) WHERE coalesce(orgActor.state, ?) <> ?
OPTIONAL MATCH (currentUser)-[supports:SUPPORTS_ORG]-(targetOrg) WHERE EXISTS { MATCH (currentUser)<-[:FOR_USER]-(scaleActor:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(scaleOrg), (scaleActor)-[:APPOINTED_AS]->(scaleRole:Role) WHERE coalesce(scaleActor.state, ?) <> ? AND scaleRole.name <> ? }
WITH *, false AS repOf
// Evaluated only for non-members with a resolved target org; members are decided by orgActor alone.
WITH *, CASE WHEN orgActor IS NULL AND targetOrg IS NOT 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]-(scaleOrg) 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 repOf 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 repOf OR supports is not null THEN targetOrg ELSE null END as org, currentUser as tokenUser
Expected: This should mostly help the slow tail: calls for non-members, or where the org alias doesn't resolve, should no longer pay for the multi-hop expansion. Expect fewer DB hits in the SemiApply branches. The size of the gain depends on how many Actors and Studies the user has. · Evidence: Check that the TildaScale alias resolves to exactly one Organization. PROFILE both versions with (a) a member user, (b) a non-member with a delegated study, and (c) an unknown orgAlias, and confirm the returned user/org/tokenUser values match in all three. Compare DB hits on the SemiApply/RollUpApply operators and watch the p95 after deploy.

ontologysvc · 3423BD53 — 10 runs · p95 1.50 s · 2 s total · 3d ago · proposed open shape & trend →

index Make sure the Document lookup by tildaid is index-backed (preferably a uniqueness constraint)
The issueThe query starts with `MATCH (d:Document {tildaid: $docId})`. Every later clause depends on this one anchor. If `Document.tildaid` has no index or constraint, the planner has to scan every :Document node and filter on tildaid. A label scan whose cost grows with the number of Documents would explain a p95 of 1500 ms against an average of 218 ms better than anything else in the visible text. tildaid looks like a global ID, so it should be unique.
The fixRun `SHOW CONSTRAINTS` and `SHOW INDEXES`, and look for an entry covering `(:Document).tildaid`. If there is none, add a uniqueness constraint. It creates a backing range index and also lets the planner treat the lookup as returning at most one row. If you can't guarantee uniqueness yet, use `CREATE INDEX document_tildaid IF NOT EXISTS FOR (n:Document) ON (n.tildaid)` instead. If either one already exists, skip this suggestion: the anchor is fine, and the slow runs come from somewhere else (see the other items).
Add this index
CREATE CONSTRAINT document_tildaid_unique IF NOT EXISTS FOR (n:Document) REQUIRE n.tildaid IS UNIQUE
Expected: If no index exists today, the anchor should change from a NodeByLabelScan + Filter to a NodeUniqueIndexSeek. That is likely to cut the slow executions to single-digit milliseconds for this step. If an index already exists, expect no change. · Evidence: `SHOW CONSTRAINTS` / `SHOW INDEXES` output for Document.tildaid. An EXPLAIN of the statement whose first operator is NodeUniqueIndexSeek or NodeIndexSeek on Document(tildaid) rather than NodeByLabelScan. p95 for shape 3423BD53 after the deploy.
none PROFILE the full statement: the sub-artifact and fallback-site section is cut off in this shape
The issueIn the normalised text, the part between `MATCH (d:Document …)` and `WITH d, saT, lastSaR, s, fnPrefix, collect(DISTINCT s2) AS fallbackSites` was swallowed by a comment with mis-encoded characters. The MATCH/OPTIONAL MATCH clauses that bind saT, lastSaR, s, s2 and fnPrefix can't be seen. `collect(DISTINCT s2)` is only used to check whether exactly one fallback site exists. That suggests an unanchored or wide expansion to candidate sites, which would get collected and de-duplicated in memory for every row. That is the most likely source of the 1.5 s outlier, but it can't be confirmed or rewritten from this text.
The fixPull the full statement text from the application source or query.log and run PROFILE with a docId from a slow run. Look for a NodeByLabelScan, or a large Expand(All), feeding the collect of s2. Possible fixes: index-back whatever property is used to find s2 (for example a site-number or study property), or cap the collection, since you only need to tell 0, 1 and more than 1 apart. Applying `[x IN collect(DISTINCT s2) WHERE x IS NOT NULL][..2]` doesn't save the expansion, so limit the expansion itself, e.g. with a CALL subquery and LIMIT 2. No rewrite is given here, because writing one against the truncated text would mean inventing clauses.
Expected: Unknown until profiled. If the fallback-site expansion is unanchored, anchoring it or limiting it to 2 rows could take most of the remaining cost out of the slow executions. · Evidence: A PROFILE of the full statement showing db hits and rows per operator, especially the operators before the EagerAggregation for fallbackSites. Also compare the 1500 ms run's docId against typical ones.
none No index needed for the DslPlanDefinition {studyId} filter, and the overall impact is small
The issue`OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {studyId: d.studyId})` is anchored on the already-bound `site`. The planner expands from site and filters pd on studyId and delpending. An index on DslPlanDefinition.studyId would not be used here, and could tempt the planner into a seek plus join that costs more than the expansion. Also, the shape ran only 10 times in 7 days for 2.2 s in total, so the only worthwhile target is the single slow outlier.
The fixLeave this clause as it is. `coalesce(pd.delpending, false) = false` is a cheap per-row filter after a short expansion. Focus on the anchor index and the hidden fallback-site section. If the outlier was a cold page cache or the first-run plan compile, check the planning time and page faults for that run in query.log. Neither of those problems is fixed by schema changes.
Expected: No change from this clause. With only about 10 runs a week, even a large per-call improvement saves very little total time. · Evidence: A PROFILE showing Expand(All)/OptionalExpand from site with low row counts on the pd branch. The query.log entry for the slow run, showing whether planning time or page faults made up most of the 1500 ms.

regsvc · 80A742DC — 2 runs · p95 1.90 s · 1 s total · 3d ago · proposed open shape & trend →

index Make sure the Study.tildaid anchor lookup is backed by a uniqueness constraint
The issueThe only entry point is `match (st:Study{tildaid: $studyId})`. Every other node is reached by traversing from `st`. If `Study.tildaid` has no index, this line becomes a NodeByLabelScan over all Study nodes with a property filter. The rest of the query depends on this lookup, so it should be a single-node seek.
The fixCheck `SHOW CONSTRAINTS` / `SHOW INDEXES` for Study(tildaid). If neither exists, create a uniqueness constraint. tildaid is used as an ID, so uniqueness also protects the data, and the constraint creates a backing range index the planner can use for a NodeUniqueIndexSeek. Other labels are reached by traversal, so they don't need indexes for this statement. Note the volume: 2 runs and 1.2 s total in 7 days, so the total gain is small either way.
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: If no index exists today, the anchor goes from a label scan proportional to the number of Study nodes to a single seek. That probably saves a few ms to tens of ms per call. If a constraint already exists, nothing changes. · Evidence: Run EXPLAIN/PROFILE before and after. The first operator should change from NodeByLabelScan(st:Study) + Filter to NodeUniqueIndexSeek(st:Study(tildaid)). Also confirm the constraint creates without errors (it will fail if duplicate tildaid values exist).
rewrite Gather tasks in per-control COLLECT subqueries instead of a long OPTIONAL MATCH chain that multiplies rows
The issueAfter `c` is found, the query chains about 10 OPTIONAL MATCHes that multiply each other's rows: - two unbounded var-length GRAPH_NEXT expansions (`(c)-[:GRAPH_NEXT*?..]-(...)-(tasklist)-[:GRAPH_NEXT*?..]->(taskconfig)`), one of them undirected, which reach every path and not just every node - ptasklist × ptsn × task1 - untyped `(c)-[]-(td:Todo)-[]-(tds:TodoSpecNode)` × ptd × task2 All of these rows reach `COLLECT(task1) + COLLECT(task2)`, so the same task appears many times. The query then UNWINDs, joins documents, and dedups again with DISTINCT. The p95 (1.9 s) is about 3× the average, which fits a fan-out that depends on the data. Also, `optional match (wf)-...-(pw)` is followed by a mandatory `match (pw)-...`, so it is effectively a required MATCH. Writing it as one makes that clear to the planner.
The fixKeep the anchor, workflow, activity and control matching as it is. Make the pw/scope match a plain MATCH. Reduce to one row per (wf, c, trigger, scope, pc) before collecting tasks. Then get task1 and task2 through two COLLECT { ... RETURN DISTINCT } subqueries (Neo4j 5.6+), so each path family is expanded once per control/participant-control and deduplicated right away instead of being multiplied together. The task1 subquery keeps the original's links: tasklist→ptasklist→pc and taskconfig→ptsn→ptasklist. One semantic caveat: the original `sum(case when task.state ...)` and `count(pc)` count duplicate rows, so numTasksCompleted and controlStatus can be inflated today. With deduplicated tasks those numbers change, most likely to the intended values. Confirm this with the owning team.
Proposed rewrite
match (st:Study{tildaid: $studyId})
match (st)-[:PLAN_DEFINITION_TO_STUDY]-(sitePd:DslPlanDefinition{type:?})
match (sitePd)-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]-(wf:WorkflowV2)-[:TRIGGER_TO_WORKFLOW]-(trigger:Trigger)
match (wf)-[r:GRAPH_NEXT*?..]->(ref:GraphNodeRef) where r[-?].path[?]=wf.tildaid and ref.objtype=?
match (ref)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:wf.tildaid}]->(a:ActivityV2)
match (a)-[:GRAPH_NEXT]-(:GraphNodeRef)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:wf.tildaid}]-(c:ControlV2)
match (wf)-[:PARTICIPANTWORKFLOW_TO_WORKFLOW]-(pw:ParticipantWorkflow)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(scope)
where ( (scope:DslPlanDefinition and (st)-[:PLAN_DEFINITION_TO_STUDY]-(scope)) or (scope:StudySiteActor and (st)--(:DslPlanDefinition)--(scope)) )
optional match (a)-[:BELONGS_TO_ACTIVITY]-(pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw)
optional match (c)-[:PCONTROL_TO_CONTROL]-(pc:ParticipantControl)-[:PARTICIPANT_CONTROL]-(pa)
with distinct wf, c, trigger, scope, pc
with wf, c, trigger, scope, pc,
  COLLECT {
    match (c)-[:GRAPH_NEXT*?..]-(:GraphNodeRef)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:wf.tildaid}]-(tasklist:TaskList)-[:GRAPH_NEXT*?..]->(taskconfig:TaskConfig)
    match (tasklist)-[:BELONGS_TO_TASKLIST]-(ptasklist:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(pc)
    match (taskconfig)-[:BELONGS_TO_TASKCONFIG]-(ptsn:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(ptasklist)
    match (ptsn)-[:CREATED_FROM_SPEC]-(task1:Task)
    return distinct task1
  } AS tasks1,
  COLLECT {
    match (c)-[]-(td:Todo)-[]-(:TodoSpecNode)
    match (td)-[]-(:ParticipantTodo)-[:CREATED_FROM_SPEC]-(task2:Task)
    return distinct task2
  } AS tasks2
with wf, c, trigger, scope, pc, tasks1 + tasks2 AS tasks
unwind tasks as task
optional match (task)<-[:CREATED_IN]-(document:Document)
optional match (scope)-[:BELONGS_TO]-(scopeDoc:Document)
return distinct c, count(distinct task) as totalTasks, sum(case when task.state = ? or task.state = ? then ? else ? end) as numTasksCompleted, case when count(pc) = sum(case when pc.status = ? then ? else ? END) then true else false end as controlStatus, trigger.scope, collect(distinct [{name: document.fileName, id: document.tildaid, url: document.url, type:document.docTypeName}, {name: scopeDoc.fileName, id: scopeDoc.tildaid, url: scopeDoc.url, type: scopeDoc.docTypeName}]) as documents
Expected: Intermediate rows (DB hits and rows in PROFILE) should drop a lot. The gain grows with how many GRAPH_NEXT paths and todos each control has. Plausibly a several-fold cut in p95, but this is unproven. · Evidence: PROFILE both versions with the same $studyId, ideally the one behind the 1.9 s p95. Compare total DB hits, rows into the EagerAggregation, and elapsed time. Diff the result sets: `c`, totalTasks and documents should match. numTasksCompleted and controlStatus may differ only where the original double-counted duplicates.

patientsvc · 8E20502F — 1 runs · p95 1.95 s · 1 s total · 3d ago · proposed open shape & trend →

rewrite Apply the lead.studyid filter in the first MATCH, not after the patient expansion
The issueThe first clause `match(lead:Lead)-[]-(:Study)-[]-(org:Organization{tildaid:$orgId}) with lead` returns every Lead linked to any study of the organisation. That is one row per path, so duplicates are possible. The predicate `where lead.studyid=$studyId` sits on the second MATCH. Because of that, the planner may expand LEAD_TO_PATIENT for every Lead in the org and only afterwards drop the ones from other studies. The untyped `-[]-` hops also make it walk every relationship type on Study and Lead.
The fixPut `WHERE lead.studyid = $studyId` right after the first MATCH, before `WITH lead`. Then rows from other studies are removed before the patient, advocate, Phi and Task expansions. The row multiplicity is unchanged: duplicate org-study paths, the OPTIONAL MATCH on advocates and the Phi and Task fan-out all stay as they were. So `count(lead)` should return the same number. If you know which relationship types join Lead–Study and Study–Organization, adding them to the `-[]-` hops would prune the walk further. They are not in the shape, so they are left out here.
Proposed rewrite
match(org:Organization{tildaid:$orgId})-[]-(:Study)-[]-(lead:Lead) 
where lead.studyid=$studyId 
with lead 
match(patient:Patient)<-[:LEAD_TO_PATIENT]-(lead) 
with * 
optional match(lead)-[:LEAD_TO_ADVOCATE]-(adv:Advocate) 
with * 
match(patient)-[:HAS_PROTECTED_DATA_IN]->(phi:Phi) 
with * 
match(lead)-[]-(task:Task{state:?}) 
where not (task)-[]-(:Inbox{type:?}) 
return count(lead)
Expected: If the org has Leads across many studies, this could cut db hits roughly in proportion to the share of the org's Leads in $studyId. That might bring the ~1.2 s down to a few hundred ms. With one run a week the total saving is small. · Evidence: Run PROFILE on both versions with the same parameters. Confirm the counts match, then compare total db hits and the row count after the first Expand/Filter. The rewrite should show far fewer rows going into the LEAD_TO_PATIENT expand.
index Index Lead.studyid so the planner can start from the study's Leads
The issue`lead.studyid=$studyId` is the most selective predicate on Lead, but there is no visible index for it. Without one, the planner has to reach Leads by walking from Organization through Study, or use a NodeByLabelScan on Lead and filter on studyid.
The fixCreate a range index on Lead(studyid). The planner can then do a NodeIndexSeek for the Leads in this study and check the org pattern from that small set. It can also join both anchors.
Add this index
CREATE INDEX lead_studyid IF NOT EXISTS FOR (l:Lead) ON (l.studyid)
Expected: If one study's Leads are a small fraction of all Leads, the plan could switch to an index seek. Latency might fall from about a second to tens of ms. Hedged: this depends on the cardinalities. · Evidence: Run EXPLAIN/PROFILE after creating the index. Look for a NodeIndexSeek on Lead(studyid) instead of a NodeByLabelScan or a filter after expansion, and check p95 on the next runs.
index Make sure Organization.tildaid has an index or a uniqueness constraint
The issue`(org:Organization{tildaid:$orgId})` is the natural anchor of the first MATCH. If tildaid has no index, finding the org needs a full scan of the Organization label before any expansion.
The fixRun SHOW INDEXES / SHOW CONSTRAINTS. If tildaid is not covered, add an index. If tildaid is the org's identifier, a uniqueness constraint is better (CREATE CONSTRAINT organization_tildaid IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE). It gives the same lookup and also guarantees one node per key.
Add this index
CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid)
Expected: If it is missing today, the org lookup goes from a label scan to a single seek. This saves the scan on every call, and the saving is larger the more Organizations there are. If an index already exists, nothing changes. · Evidence: Check SHOW INDEXES for Organization(tildaid). In PROFILE, look for NodeIndexSeek/NodeUniqueIndexSeek on Organization instead of NodeByLabelScan with a filter.