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

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

(internal) · 96097A0D — 3 runs · p95 2.0 min · 169 s total · 3d ago · proposed open shape & trend →

index Index Organization.tildaid so the final MATCH becomes a seek
The issue`match (o:Organization) where o.tildaid = case when c contains ? then p.orgid end` runs once for every distinct (q, p, c) row coming out of the UNWIND. The equality's right-hand side depends on the row, and no lookup index on Organization.tildaid is listed. Without one, the planner most likely builds a Cartesian product with a NodeByLabelScan on Organization and filters every row. That means rows × organizations comparisons, which fits a p95 of 120 000 ms (the timeout).
The fixCreate a range index on Organization.tildaid. The planner can then use an Apply with NodeIndexSeek(o.tildaid = <row value>) for each row instead of scanning the label. If tildaid is a real business key, a uniqueness constraint (CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE) gives the same index and also guarantees one row per key.
Add this index
CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid)
Expected: The Organization step should drop from a label scan per row to a single index seek per row. If that is the current bottleneck, total runtime could fall by an order of magnitude or more. The upstream traversal is unaffected. · Evidence: Run EXPLAIN/PROFILE before and after. Before: look for CartesianProduct or NodeByLabelScan(o:Organization) with large db hits. After: a NodeIndexSeek on Organization(tildaid) under an Apply. Also compare p95 over the next executions; it should stop hitting the 120 s timeout.
rewrite Filter choices before the join instead of UNWIND plus CASE-to-null
The issue`with distinct q,p,rs.choices as cList unwind cList as cl with distinct q,p,cl as c` expands every ResponseSet's choices list into one row per choice and de-duplicates it in memory. The CASE expression then only drops rows whose choice lacks ? by turning the key into null, so the Organization lookup is attempted for every choice. The only thing c is used for is checking whether some choice contains ?. Duplicate rows for the same p are also re-joined to Organization, and the old-style `not exists((pattern))` predicate is deprecated in Neo4j 5.
The fixReplace the UNWIND and CASE with an `any()` predicate on rs.choices applied right after the first MATCH. That prunes (p, rs) pairs early and removes the per-choice rows. Then de-duplicate (q, p) once and join Organization directly on p.orgid, which lets the planner use the tildaid index. The results match the original: a null or empty choices list, or a null orgid, produced no rows before and produces none now. RETURN DISTINCT is dropped because grouping by o.alias already makes each row unique. The EXISTS {} subquery is the Neo4j 5 syntax.
Proposed rewrite
MATCH (p:ProtocolVersionV2)--(gnr:GraphNodeRef)--(rs:ResponseSet)
WHERE NOT EXISTS { (p)<-[:EDITED_FROM]-(:ProtocolVersionV2) }
  AND any(c IN rs.choices WHERE c CONTAINS ?)
MATCH (gnr)--(q:QuestionV2)
WITH DISTINCT q, p
MATCH (o:Organization)
WHERE o.tildaid = p.orgid
RETURN o.alias, collect(DISTINCT p.name), collect(DISTINCT q)
Expected: Far fewer intermediate rows: one per (q, p) instead of one per (q, p, choice). The Organization join runs once per qualifying (q, p). Together with the index, the query should complete well under the timeout, though the exact gain depends on the average length of choices. · Evidence: PROFILE both versions on the same data. Compare rows out of the Unwind/Distinct operators against the new Filter, and total db hits. Check that the result sets match (same aliases, names and question collections). Track p95 after the deploy.

(internal) · 5BCBC7BE — 9 runs · p95 2.0 min · 151 s total · 3d ago · proposed open shape & trend →

rewrite Filter on choices before the Organization match, use EXISTS checks, drop the unwind/distinct stages
The issueThe query unwinds every ResponseSet's `choices` list into one row per choice, runs two in-memory DISTINCT steps over (q,p,c), then runs `MATCH (o:Organization) WHERE o.tildaid = CASE WHEN c CONTAINS ? THEN p.orgid END` once per row. The CASE expression returns null for rows that don't match, and the planner usually can't turn it into an index seek, so this tends to become an Organization label scan, or a Cartesian product, for each (q,p,c) row. `q` and `o` are only there to check that something exists, but they are carried through every row anyway. With undirected `--` expansions on both sides of GraphNodeRef, the row count gets large, which fits the 120 s p95 (probably the timeout).
The fixTurn each "must exist" part into a predicate on the (p, gnr, rs) row. Replace the UNWIND + CASE with `any(c IN rs.choices WHERE c CONTAINS ?)`. Replace the Organization match with an `EXISTS { }` subquery that compares `o.tildaid = p.orgid` directly, which an index can seek on. Replace `match (gnr)--(q:QuestionV2)` with an EXISTS check. The result should be the same: in the original, a p is returned only if some choice contains the literal (otherwise CASE gives null and nothing matches) and an Organization with tildaid = p.orgid exists. One difference: if `choices` is ever a plain string rather than a list, UNWIND treats it as one value but `any()` will raise an error. Check the data type first.
Proposed rewrite
MATCH (p:ProtocolVersionV2)--(gnr:GraphNodeRef)--(rs:ResponseSet)
WHERE NOT EXISTS { (p)<-[:EDITED_FROM]-(:ProtocolVersionV2) }
  AND any(c IN rs.choices WHERE c CONTAINS ?)
  AND EXISTS { (gnr)--(:QuestionV2) }
  AND EXISTS { MATCH (o:Organization) WHERE o.tildaid = p.orgid }
RETURN DISTINCT p.tildaid
Expected: This should remove the per-choice row explosion, both DISTINCT stages and the per-row Organization scan. On a large graph it may bring runtime down from timeout territory to seconds or less. That's a guess until it's profiled. · Evidence: PROFILE the original and the rewrite on the same dataset. Compare total db hits and rows, check that the result sets of p.tildaid are identical, and check that the plan no longer shows CartesianProduct or NodeByLabelScan(Organization). Run `MATCH (r:ResponseSet) RETURN DISTINCT valueType(r.choices)` to confirm `choices` is a list.
index Index Organization.tildaid
The issue`MATCH (o:Organization) WHERE o.tildaid = ...` filters on a property that isn't a lookup key unless an index or uniqueness constraint exists. Without one, every evaluation scans all Organization nodes, and in this query that happens once per (q,p,choice) row.
The fixCreate a RANGE index, or a uniqueness constraint if tildaid really is unique per Organization, so that `o.tildaid = p.orgid` becomes a NodeIndexSeek. This works best together with the rewrite above, which removes the CASE so the planner can use the index. If tildaid is an identifier, prefer `CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE`. Check SHOW INDEXES first, because one may already exist.
Add this index
CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid)
Expected: Each Organization lookup should go from a label scan (O(#Organizations)) to a single seek. This probably matters most if there are thousands of Organization nodes. · Evidence: Run SHOW INDEXES WHERE 'Organization' IN labelsOrTypes to check for an existing index. After creating it, PROFILE should show NodeIndexSeek (or NodeUniqueIndexSeek) on Organization(tildaid) and no NodeByLabelScan for Organization.

(internal) · AF34F8C8 — 3 runs · p95 2.0 min · 148 s total · 3d ago · proposed open shape & trend →

rewrite Filter choices before the join, and look up Organization by a plain key equality
The issue`WITH DISTINCT q,p,rs.choices AS cList UNWIND cList AS cl WITH DISTINCT q,p,cl AS c` turns every (q, p, rs) row into one row per choice and runs two in-memory DISTINCTs. Only after that does the CONTAINS test run, and it is hidden inside `o.tildaid = CASE WHEN c CONTAINS ? THEN p.orgid END`. Rows that fail the test become `tildaid = null`, which can never match. They still reach the Organization step, and the CASE expression makes it less likely the planner will seek on o.tildaid. The usual fallback is a NodeByLabelScan of Organization for each row. All 3 runs averaged 49 s, and at least one hit the 120 s timeout.
The fixDo the CONTAINS test on the ResponseSet as an `any()` predicate right after the first MATCH. That drops non-matching rs early and removes the UNWIND and both choice-level DISTINCTs. Reduce to DISTINCT (p, q) before touching Organization. Then match Organization with a plain equality `{tildaid: p.orgid}`, which the planner can turn into an index seek. The meaning stays the same: a (p, q) pair qualifies only if some rs on the same gnr has a choice containing the literal. A null `choices` value is still filtered out. The original `RETURN DISTINCT` was redundant next to the aggregation, so it is dropped. The old-style `exists((pattern))` is replaced with an EXISTS {} subquery.
Proposed rewrite
MATCH (p:ProtocolVersionV2)--(gnr:GraphNodeRef)--(rs:ResponseSet)
WHERE NOT EXISTS { (p)<-[:EDITED_FROM]-(:ProtocolVersionV2) }
  AND any(c IN rs.choices WHERE c CONTAINS ?)
MATCH (gnr)--(q:QuestionV2)
WITH DISTINCT p, q
MATCH (o:Organization {tildaid: p.orgid})
RETURN o.alias, collect(DISTINCT p), collect(DISTINCT q)
Expected: Far fewer intermediate rows: rows scale with matching rs instead of with rs × choices. Organization becomes one seek per distinct p instead of a scan per row. This could plausibly take the query from timing out to seconds. That is not guaranteed, because the first expansion from ProtocolVersionV2 is still a label scan. · Evidence: Run PROFILE on the old and new queries with the same literal. Compare total db hits and the row counts going into the Organization operator, check the new plan shows NodeIndexSeek on Organization(tildaid), and confirm both queries return the same alias/p/q sets. Also check how this was logged: a statement that really ran under EXPLAIN would not take 49 s, so the logged text probably doesn't match what actually ran.
index Index Organization.tildaid
The issueThe final `MATCH (o:Organization) WHERE o.tildaid = …` filters on a property that is not shown to have any index. Without one, each driving row causes a full Organization label scan.
The fixCreate a range index on Organization(tildaid). If tildaid is a unique business key, create a uniqueness constraint instead, which also gives you the index. Together with the rewrite above, this makes the Organization step a seek.
Add this index
CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid)
Expected: The Organization step should go from a label scan per row to one index seek per row. How much this saves depends on how many Organization nodes there are. · Evidence: Run SHOW INDEXES to confirm no index exists today and that the new one is ONLINE. Then check that the PROFILE plan shows NodeIndexSeek(o:Organization(tildaid)) instead of NodeByLabelScan plus Filter.
rewrite Add relationship types and directions to the undirected expansions
The issue`(p)--(gnr)--(rs)` and `(gnr)--(q)` are untyped and undirected. Every relationship on each GraphNodeRef is expanded both ways, and target labels are only checked afterwards. GraphNodeRef is a hub label linking protocols, response sets and questions, so this fan-out is probably where most of the remaining time goes.
The fixReplace each `--` with the real typed, directed relationship from the data model, for example `(p)-[:HAS_NODE]->(gnr)<-[:FOR_NODE]-(rs)`. You can find the types with `MATCH (:GraphNodeRef)-[r]-(x) RETURN type(r), labels(x), count(*)`. The planner can then use typed expands, which avoids reading unrelated relationships. The exact types can't be filled in here because the shape doesn't show them.
Expected: Fewer relationships traversed per GraphNodeRef. The gain is proportional to how many relationships of other types those nodes have. · Evidence: Check relationship type counts around GraphNodeRef. Then compare db hits on the Expand(All)/Expand(Into) operators in PROFILE before and after adding the types.

(internal) · AAD6A404 — 8 runs · p95 2.0 min · 136 s total · 3d ago · proposed open shape & trend →

index Index Organization.tildaid so the final MATCH becomes a seek
The issue`match (o:Organization) where o.tildaid = case when c contains ? then p.orgid end` has no lookup on Organization.tildaid, and its value depends on the row. So for every distinct (q,p,c) row the planner has to scan or filter all Organization nodes, or build a hash join over every Organization. That is the same number of rows the unwind produced, multiplied by the number of Organizations.
The fixCreate a range index on Organization(tildaid). The equality predicate can then be answered with a NodeIndexSeek per row. If tildaid really is a unique business key, use a uniqueness constraint instead (CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE). The constraint also creates the index and stops duplicate keys.
Add this index
CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid)
Expected: The Organization step should change from a label scan, or a hash join over all Organizations, to roughly one index lookup per row. It likely won't fix the whole 120 s timeout alone, because the unanchored traversal at the start is still there, but it should remove the scan that grows with the number of Organizations. · Evidence: Compare EXPLAIN/PROFILE before and after. Before, expect NodeByLabelScan(o:Organization) + Filter or a ValueHashJoin. After, expect NodeIndexSeek on Organization(tildaid). Check SHOW INDEXES to confirm the index is ONLINE, then check p95 over the next week.
rewrite Filter choices before the unwind and join on p.orgid directly
The issue`with distinct q,p,rs.choices as cList unwind cList as cl with distinct q,p,cl as c` expands every ResponseSet's choices list into one row per (q,p,choice) and runs DISTINCT twice over those rows in memory. Only afterwards does the CASE in the Organization predicate throw away choices that don't match `?`: the CASE gives null, and `o.tildaid = null` matches nothing. Because the CASE is opaque, the planner can't see that the join key is really p.orgid, so it can't push the filter down or seek on it.
The fixTurn the CASE into an early existence test: keep (p,q) only when some choice in rs.choices contains `?`, using any(). Then match the Organization by tildaid = p.orgid. This gives the same results: the original only keeps an o when some choice contains `?` and o.tildaid = p.orgid, and there's no NULL difference because a null orgid matches nothing in either version. The trailing RETURN DISTINCT is redundant because the aggregation already groups by o.alias. Every choice row disappears, along with the second DISTINCT. The equality is also seekable once the tildaid index exists. I kept the EXPLAIN prefix to match the logged text. Note that a plain EXPLAIN doesn't execute, so the 120 s elapsed time suggests the client really runs the query without the prefix, or that planning itself is that slow.
Proposed rewrite
EXPLAIN MATCH (p:ProtocolVersionV2)--(gnr:GraphNodeRef)--(rs:ResponseSet)
WHERE any(c IN rs.choices WHERE c CONTAINS ?)
MATCH (gnr)--(q:QuestionV2)
WITH DISTINCT q, p
MATCH (o:Organization)
WHERE o.tildaid = p.orgid
RETURN o.alias, collect(DISTINCT p.name), collect(DISTINCT q)
Expected: Far fewer intermediate rows (one per (p,q) instead of one per (p,q,choice)) and much less DISTINCT memory. This could shrink the middle of the plan by a large factor, depending on how long the choices lists are. Results should be identical. · Evidence: PROFILE both versions on the same data. Compare the row count after Unwind/Distinct with the row count after the any() Filter. Confirm the result sets match (same aliases, names and question sets). Check that the Organization step becomes a NodeIndexSeek.
caching Treat this as a report: give the traversal types/direction or precompute it
The issueThe query has no anchor. It starts from every ProtocolVersionV2 and runs untyped, undirected expansions `--(gnr:GraphNodeRef)--(rs:ResponseSet)` and `(gnr)--(q:QuestionV2)`. Every relationship of every GraphNodeRef hub gets traversed in both directions, then label-checked. That full-graph scan is what pushes p95 to the 120 s timeout. It runs only 8 times a week, so it behaves like a report, not an OLTP query.
The fixFirst, if the schema allows it, add relationship types and directions to the three patterns, e.g. (p)-[:HAS_REF]->(gnr) (use the real type names). The expander then skips unrelated relationships on the GraphNodeRef hubs. Second, because this is low-volume analytics, run it on a schedule (or on a secondary/analytics member) and cache the alias → (protocol names, questions) result, rather than running a full-graph traversal on demand in the service.
Expected: Typed, directed expansion cuts db hits roughly in proportion to how many of the GraphNodeRef relationships are irrelevant. Caching takes the cost off the request path completely, so callers would see a cache read instead of a run of up to 120 s. · Evidence: PROFILE db hits on the Expand(All) operators with and without relationship types. Check the GraphNodeRef degree distribution: MATCH (g:GraphNodeRef) RETURN avg(COUNT { (g)--() }), max(COUNT { (g)--() }). Confirm with the service owners that slightly stale results are acceptable before caching.

(internal) · B903F249 — 1 runs · p95 1.9 min · 105 s total · 3d ago · proposed open shape & trend →

rewrite Run the 'is this the earliest version' NOT EXISTS check once per (d, dv), not once per document changelog
The issueIn the second OPTIONAL MATCH, `(d)-[:ALL_CHANGELOG]->(dch:ChangeLog) WHERE d IS NOT NULL AND dch.oldValue CONTAINS ? AND NOT EXISTS { MATCH (d)-[:DOCUMENT_VERSION]->(prev:DocumentVersion) ... }`, the NOT EXISTS subquery only uses d and dv. It never uses dch. Because it sits in the WHERE of the dch match, the planner can run it again for every ChangeLog hanging off the Document. That means walking all of d's DOCUMENT_VERSION relationships and reading versionNumber on each one, per changelog, per DocumentVersion. For documents with many versions and many changelogs, that work grows roughly as versions × changelogs × versions.
The fixWork out the check once, right after collecting vchs, and store it as a boolean `isFirst`. Then the dch OPTIONAL MATCH only needs a cheap check on that boolean plus the CONTAINS filter. If isFirst is false, the planner can skip expanding to the document's changelogs entirely. The results stay the same. Note that the order of the ? placeholders changes: the two coalesce defaults now come before the dch CONTAINS literal, so make sure the parameters are bound in the right order.
Proposed rewrite
MATCH (dv:DocumentVersion) 
WHERE dv.originalFileName IS NULL 
WITH dv 
OPTIONAL MATCH (d:Document)-[:DOCUMENT_VERSION]->(dv) 
CALL { 
WITH dv, d 
OPTIONAL MATCH (dv)-[:ALL_CHANGELOG]->(vch:ChangeLog) 
WHERE vch.oldValue CONTAINS ? 
WITH dv, d, collect(vch) AS vchs 
WITH dv, d, vchs, (d IS NOT NULL AND NOT EXISTS { 
MATCH (d)-[:DOCUMENT_VERSION]->(prev:DocumentVersion) 
WHERE coalesce(prev.versionNumber, ?) < coalesce(dv.versionNumber, ?) }) AS isFirst 
OPTIONAL MATCH (d)-[:ALL_CHANGELOG]->(dch:ChangeLog) 
WHERE isFirst AND dch.oldValue CONTAINS ? 
WITH vchs, collect(dch) AS dchs 
WITH [ch IN (vchs + dchs) 
WHERE ch IS NOT NULL] AS chs 
UNWIND CASE WHEN size(chs) = ? THEN [null] ELSE chs END AS ch 
WITH ch 
ORDER BY ch.createdat ASC, CASE WHEN ch.oldValue CONTAINS ? THEN ? ELSE ? END 
WITH head(collect(ch)) AS clog 
RETURN clog.tildaid AS clogId, clog.oldValue AS oldValue } 
RETURN dv.tildaid, clogId, oldValue
Expected: This could plausibly remove most of the 105 s if documents have many changelogs and many versions. If most documents have one or two changelogs, the gain will be small. Keep in mind the shape ran only once in 7 days (about 105 s total), so the absolute saving is small unless this backfill is run again or on a schedule. · Evidence: Run PROFILE on both versions against the same data. Compare db hits on the Expand(All)/Filter operators under the existential subquery (the DOCUMENT_VERSION expansion to prev), and compare total db hits and elapsed time. Also check that both versions return identical rows (clogId, oldValue for each dv.tildaid).
none No index can serve the `originalFileName IS NULL` anchor
The issueThe query starts with `MATCH (dv:DocumentVersion) WHERE dv.originalFileName IS NULL`. Neo4j 5 range and text indexes only store nodes that have the property, so an IS NULL predicate cannot use an index lookup. This step will always be a NodeByLabelScan on DocumentVersion. The CONTAINS filters on ChangeLog.oldValue are applied after expanding from dv or d, so a TEXT index on oldValue would not be used either.
The fixDon't add an index on DocumentVersion.originalFileName or ChangeLog.oldValue for this query, because the plan won't use it. If this backfill becomes a recurring job, the structural fix is to mark the rows that need it explicitly. Either set a sentinel value (e.g. originalFileName = '' or a boolean needsFileName = true) or add a label such as :MissingFileName, which gives a label scan over just those nodes. For a one-off migration, the label scan is acceptable.
Expected: None from indexing. Only a data-model change (sentinel or label) would shrink the scan, and only if the nodes with a null file name are a small share of all DocumentVersions. · Evidence: In EXPLAIN, the first operator should be NodeByLabelScan(dv:DocumentVersion) followed by a Filter on originalFileName IS NULL. Compare the label count (MATCH (n:DocumentVersion) RETURN count(n)) with the count of nodes where the file name is null to decide whether a marker label is worth it.

(internal) · EAD5D682 — 8 runs · p95 9.00 s · 29 s total · 3d ago · proposed open shape & trend →

index Index the ProtocolVersionV2.tildaid anchor
The issueThe whole 6-hop chain starts from `where p.tildaid in [35 values]`. Without an index on ProtocolVersionV2(tildaid), the planner either scans every ProtocolVersionV2 node by label and filters, or starts from another label in the untyped, undirected chain and expands across Patient/Phi/CarePlan before it reaches the filter. Also note the statement is prefixed with EXPLAIN, so the 3.6 s avg / 9 s p95 is probably all planning time. With no selective indexed anchor, the planner has more join orders to weigh for this 11-node pattern.
The fixCreate a range index (or a uniqueness constraint if tildaid really is unique per protocol version). Then the IN list can be solved by a NodeIndexSeek on 35 values, the planner has one clear starting point, and expansion only begins from those few nodes.
Add this index
CREATE INDEX protocolversionv2_tildaid IF NOT EXISTS FOR (n:ProtocolVersionV2) ON (n.tildaid)
Expected: The plan should start with NodeIndexSeek on ProtocolVersionV2(tildaid) instead of NodeByLabelScan. Runtime for the PROFILE'd version should drop by orders of magnitude if a label scan or another start point is used today. Planning time may also shrink somewhat. · Evidence: Run SHOW INDEXES to confirm no index exists. Compare EXPLAIN before and after: the leaf operator should become NodeIndexSeek(p:ProtocolVersionV2(tildaid)). Then compare the elapsed time of the EXPLAIN call and the PROFILE db hits.
index Index Organization.tildaid for the per-row lookup
The issue`match (o:Organization) where o.tildaid = case when c contains ? then p.orgid end` runs once for every distinct (q,p,phi,s,c) row produced after the UNWIND, and that row count can be large. The value is only known at runtime, so without an index on Organization(tildaid) each row needs a label scan plus filter, or a cartesian product with every Organization.
The fixCreate a range index (or a unique constraint if tildaid is the organisation key) so each row is a NodeIndexSeek. This is needed both by the original query and by the EXISTS subquery in the rewrite below.
Add this index
CREATE INDEX organization_tildaid IF NOT EXISTS FOR (n:Organization) ON (n.tildaid)
Expected: Replaces a per-row Organization label scan, or a CartesianProduct plus Filter, with an index seek. That's a large reduction in db hits when there are many organisations. · Evidence: Check that the PROFILE plan shows NodeIndexSeek on Organization(tildaid) in the Apply branch instead of NodeByLabelScan(o:Organization), CartesianProduct or ValueHashJoin.
rewrite Filter on p and choices first, collect questions per p, then expand to patients
The issueEverything is matched in one huge pattern: the 6-hop undirected, untyped chain down to Phi, joined with Study, GraphNodeRef, ResponseSet and QuestionV2. The result is a cross product of phi × q × s × choice rows. Then `unwind cList` multiplies it again by every choice, two DISTINCTs dedupe it in memory, and only after that does the Organization match drop rows. `o` is never returned, so it is just an existence filter on `c contains ?` and on an org existing for p.orgid. Neither check depends on phi, s or q, so it could be applied before any expansion. The single long pattern is probably also what makes planning (EXPLAIN) take seconds.
The fixChange the Organization MATCH to an EXISTS check on p. Replace UNWIND plus CONTAINS with any() over rs.choices, so no extra rows are created. Collect question names per p before expanding the Patient/Phi chain, so q and phi never multiply each other. This matches the original output: rows are still grouped by p.tildaid, p.name and s.name, and question names depend only on p. If you know the relationship types and directions, also add them to each hop to cut expansion and planning further.
Proposed rewrite
MATCH (p:ProtocolVersionV2)
WHERE p.tildaid IN [?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?]
  AND EXISTS { MATCH (o:Organization) WHERE o.tildaid = p.orgid }
MATCH (p)--(gnr:GraphNodeRef)--(rs:ResponseSet)
WHERE any(c IN rs.choices WHERE c CONTAINS ?)
MATCH (gnr)--(q:QuestionV2)
WITH p, collect(DISTINCT q.name) AS qNames
MATCH (p)--(pd:DslPlanDefinition)--(soa:ScheduleOfActivities)--(cp:CarePlan)--(pa:Patient)--(phi:Phi)
MATCH (pd)--(s:Study)
RETURN p.tildaid, p.name, s.name, collect(DISTINCT phi.firstname + ? + phi.lastname), qNames
Expected: Fewer intermediate rows, because there's no phi×q×choice product and no Unwind or Distinct over it. Planning should also get simpler, since the query is split into smaller pattern parts joined through WITH. Runtime and EXPLAIN latency could both drop a lot, but measure before assuming it. · Evidence: Compare PROFILE of the old and new query on the same parameters: total db hits, peak rows into EagerAggregation/Distinct, and memory. Also check that the result sets are identical. Compare EXPLAIN elapsed time to confirm planning got cheaper.

(internal) · 9B7116E1 — 7 runs · p95 16.50 s · 27 s total · 3d ago · proposed open shape & trend →

index Index Organization.tildaid for the final org lookup
The issueThe last clause `match (o:Organization) where o.tildaid = case when c contains ? then p.orgid end` runs once for every distinct (phi, q, p, c) row. Without a lookup on Organization.tildaid, each of those rows becomes a NodeByLabelScan of every Organization plus a property filter. That row count is already large because the Phi chain multiplies it.
The fixCreate a range index on Organization.tildaid so the planner can use a NodeIndexSeek keyed on the bound value p.orgid. If tildaid is unique per organization, use a uniqueness constraint instead (CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE), which also gives you the index. Run SHOW INDEXES first, because one may already exist.
Add this index
CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid)
Expected: Each Organization probe should go from a full label scan to a single seek. How much this saves depends on how many Organization nodes there are and how many rows reach this clause. It likely accounts for a meaningful share of the 16.5 s p95, but that is unverified. · Evidence: PROFILE before and after. The Organization operator should change from NodeByLabelScan + Filter to NodeIndexSeek (or NodeUniqueIndexSeek), and db hits on that branch should drop. Then check p95 for shape 9B7116E1 over the next week.
rewrite Filter choices first, check for Phi with EXISTS, and look up the org once per protocol
The issueThe query expands the whole chain p–DslPlanDefinition–ScheduleOfActivities–CarePlan–Patient–Phi for every gnr/rs/q row, before any filtering. It then UNWINDs rs.choices and only applies `c contains ?` inside the CASE, after the Organization MATCH. So the row count before the first DISTINCT is roughly protocols × questions × response sets × Phi paths × choices. On top of that, the Organization probe repeats for every phi and choice of the same protocol, even though the org depends only on p.orgid.
The fixMove the choice test into a WHERE with `any(c IN rs.choices WHERE c CONTAINS ?)`. Reduce to DISTINCT (p, q) before expanding further. Seek the Organization by p.orgid directly instead of through the CASE. Gather each protocol's Phi nodes in a CALL subquery and drop protocols that have none. The original inner MATCH also required at least one Phi, so the size(phis) > 0 filter keeps that behaviour. Only then cross phis with qs for the final DISTINCT collects. The output should be the same: per o.alias you get distinct protocol maps, distinct Phi names, and distinct (protocol, question) maps, limited to protocols with no EDITED_FROM successor, at least one matching choice, and at least one reachable Phi. The old `return distinct` is redundant once you group by alias, so it is removed.
Proposed rewrite
MATCH (p:ProtocolVersionV2)--(gnr:GraphNodeRef)--(rs:ResponseSet)
WHERE NOT EXISTS { (p)<-[:EDITED_FROM]-(:ProtocolVersionV2) }
  AND any(c IN rs.choices WHERE c CONTAINS ?)
MATCH (gnr)--(q:QuestionV2)
WITH DISTINCT p, q
WITH p, collect(q) AS qs
MATCH (o:Organization {tildaid: p.orgid})
CALL {
  WITH p
  MATCH (p)--(:DslPlanDefinition)--(:ScheduleOfActivities)--(:CarePlan)--(:Patient)--(phi:Phi)
  RETURN collect(DISTINCT phi) AS phis
}
WITH o, p, qs, phis WHERE size(phis) > 0
UNWIND phis AS phi
UNWIND qs AS q
RETURN o.alias,
       collect(DISTINCT {name:p.name, version:p.version, tildaid:p.tildaid}),
       collect(DISTINCT phi.firstname + ? + phi.lastname),
       collect(DISTINCT {pname:p.name, qtitle:q.title, qname:q.name})
Expected: Intermediate rows should drop sharply because the choice multiplier and the per-row Organization probe are gone, and the Phi chain is expanded once per protocol instead of once per question/response-set row. This could plausibly cut the 3.8 s average by a large factor, but that is unproven. · Evidence: PROFILE both versions on delta with the same parameter. Compare total db hits and the peak 'Rows' before the first Distinct/EagerAggregation. Diff the result sets to confirm they are identical (both sides sorted).
rewrite Add relationship types and directions to the six-hop undirected chain
The issueEvery hop except EDITED_FROM is written as an untyped, undirected `--`, for example `(p)--(pd:DslPlanDefinition)--(soa:ScheduleOfActivities)--(cp:CarePlan)--(:Patient)--(phi:Phi)`. So at each step the engine expands every relationship of every type in both directions, then filters on the neighbour's label. A Patient or CarePlan node with many unrelated relationships (audit, edit-history, and so on) makes each hop expensive. Long untyped patterns also enlarge the planner's search space. That matters here: the logged statement starts with EXPLAIN, which only plans and does not run the query, so planning itself may be a real part of the 16.5 s p95.
The fixReplace each `--` with the actual type and direction from the schema, e.g. (p)-[:HAS_PLAN_DEFINITION]->(pd), (cp)-[:FOR_PATIENT]->(pt), (pt)-[:HAS_PHI]->(phi). Those type names are placeholders: confirm the real ones with CALL db.schema.visualization(). Apply the same change to the (p)--(gnr), (gnr)--(rs) and (gnr)--(q) hops in the rewrite above. Separately, find out why the service sends this statement with EXPLAIN. If it only needs the plan or column metadata, cache that result instead of sending the statement again every time.
Expected: Expand(All) operators turn into typed, directional expands, which should cut db hits on nodes with many relationships and may shorten planning time. The size of the gain depends on how many relationships those nodes have. · Evidence: Use PROFILE to compare db hits on each Expand operator before and after. Compare 'planning' versus 'execution' time in query.log (with db.logs.query.time_logging_enabled) to see whether the EXPLAIN calls are spending their time in planning.

(internal) · 1C2D4153 — 5 runs · p95 17.50 s · 18 s total · 3d ago · proposed open shape & trend →

none The timings come from EXPLAIN (planning only), not from running the query
The issueThis statement starts with EXPLAIN, so Neo4j only plans it and never runs the MATCH. The 3.6 s average and 17.5 s p95 therefore can't be the cost of scanning ProtocolVersionV2 or Study. They are most likely planning time, a plan-cache miss after a schema change, waiting for a lock or the transaction, or the client/tool's own round-trip. It ran 5 times in 7 days, which looks like someone checking plans by hand from a tool, not application traffic. No index would help either, because the pattern has no property filter to look up.
The fixDo not add indexes because of this shape. Find out where the time goes: run the same EXPLAIN in cypher-shell and compare its reported planning time with the 17.5 s figure. Also check query.log for the planning and waiting columns (db.logs.query.time_logging_enabled=true) on the slow runs. If planning really is slow, look at plan-cache churn (db.query_cache_size, how often the schema changes) and at GC pauses around those timestamps.
Expected: Probably no change to the workload, since 18 s in total over a week from an ad hoc tool is negligible. The value is in fixing the wrong idea that this MATCH is slow when it was never run. · Evidence: For the slow runs, query.log entries showing planning time compared with waiting time, plus the client application name for this shape. If planning is under 100 ms, the latency is outside the query engine.
rewrite If the real query is run (PROFILE or without EXPLAIN), give the relationship a type and direction, return only needed fields, and page the results
The issueIn `MATCH (p:ProtocolVersionV2)--(s:Study)`, the relationship has no type and no direction. The planner has to scan one label, follow every relationship of every node in either direction, and then check the label on the far end. On densely connected nodes that is a lot of wasted work. `RETURN p,s` also sends back every property of both nodes for the whole cross-product, and with no LIMIT the full result is built and sent at once.
The fixUse the real relationship type and direction from the data model (REL_TYPE below is a placeholder you need to replace). Then the expansion only follows that one relationship type, and Neo4j can use the per-type counts it keeps on dense nodes. Return only the properties you need instead of whole nodes, and add SKIP/LIMIT for paging if the caller doesn't need everything at once. If `p.id` and `s.id` don't exist, use the real key properties.
Proposed rewrite
MATCH (p:ProtocolVersionV2)-[:REL_TYPE]->(s:Study)
RETURN p.id AS protocolVersionId, s.id AS studyId
SKIP $skip LIMIT $limit
Expected: Fewer database hits on the Expand(All) step, roughly in proportion to how many of each node's relationships are of other types. Much less data sent over the wire. The size of the effect depends on how many relationships each node has and how many rows match, so it could be negligible or large. · Evidence: PROFILE both versions and compare total db hits and rows on the Expand(All)/Filter steps. Also confirm the relationship type and direction with `MATCH (:ProtocolVersionV2)-[r]-(:Study) RETURN type(r), count(*)` before you rely on it.

(internal) · A9FBACF4 — 4 runs · p95 18.00 s · 14 s total · 3d ago · proposed open shape & trend →

rewrite Stop the undirected pattern from returning every pair twice
The issue`(pv:ProtocolVersionV2)--(pv1:ProtocolVersionV2)` has no direction and no relationship type. The planner has to scan every ProtocolVersionV2 node by label, expand all of its relationships of any type in both directions, and then check the label on the far node. Every connected pair comes back twice, once as (a,b) and once as (b,a), so the expand and the rows shipped to the client are about double what is needed. No property is filtered, so no index can help here. The cost comes from the shape of the pattern.
The fixReturn each pair once by keeping only one side of the mirrored result. The rewrite below uses `elementId(pv) < elementId(pv1)`, which doesn't need any knowledge of the schema. If the relationship between versions has a known direction and type (for example `-[:SUPERSEDES]->`), it is better to write that in the pattern, e.g. `(pv:ProtocolVersionV2)-[:TYPE]->(pv1:ProtocolVersionV2)` with no WHERE. Expand(Into/All) can then follow only one relationship type in one direction instead of every relationship on the node. That matters most if ProtocolVersionV2 nodes are also linked to many other kinds of node.
Proposed rewrite
MATCH (pv:ProtocolVersionV2)--(pv1:ProtocolVersionV2) 
WHERE elementId(pv) < elementId(pv1) 
RETURN pv, pv1
Expected: Should cut output rows by about half. If a typed, directed pattern is used, the expand should also get noticeably cheaper when these nodes have many unrelated relationships. How much it helps depends on how many relationships each node has. · Evidence: PROFILE the original and the rewrite on cluster delta and compare rows and db hits on the Expand and Filter operators. Check that the result count is about half the original. Also check the degree distribution, e.g. `MATCH (p:ProtocolVersionV2) RETURN avg(COUNT { (p)--() })`.
none This is an EXPLAIN, so the timings show planning, not execution
The issueThe statement starts with `EXPLAIN`, so Neo4j only plans it and does not run it. With 4 runs in 7 days it looks like someone checking the plan by hand. An 18 s p95 for planning alone is unusual. It points to waiting (for locks, a busy planner or a cold query cache) or to how the client measures time, not to the cost of running the query. No index or rewrite will fix time spent waiting outside the query.
The fixTreat this shape as a diagnostic, not production load. Only 14 s in total over a week, so it's low priority. If the 18 s outlier matters, look at what else was running on delta at that time (GC pauses, a large write transaction, plan cache evictions). Don't tune the query for that number.
Expected: Probably nothing to gain from changing the query itself. The total cost over a week is small. · Evidence: Check query.log or SHOW TRANSACTIONS around the slow run for planning time and pageCacheFaults vs. waitTime. Also check GC logs for a pause at the same time.

(internal) · 865AE07B — 3 runs · p95 18.50 s · 11 s total · 3d ago · proposed open shape & trend →

index Range index on ParticipantQuestion.values to anchor the IS NOT NULL filter
The issue`WHERE p.values IS NOT NULL` has no index to use, so the planner either label-scans every ParticipantQuestion node or label-scans QuestionV2 and expands every relationship. It then reads `values` from the property store for each p. On a cold cache that is a full read of the store, which fits the gap between the 3.8 s average and the 18.5 s p95.
The fixNeo4j 5 range indexes can answer IS NOT NULL predicates, and they only contain nodes where the property exists. The planner can then use a NodeIndexScan to fetch only the ParticipantQuestion nodes that have `values`, then expand from those nodes. It no longer needs to scan every node and read its property. Note that the index stores `values` for every node. If `values` is a list, check that index population succeeds and the index does not end up FAILED.
Add this index
CREATE INDEX participantquestion_values IF NOT EXISTS FOR (p:ParticipantQuestion) ON (p.values)
Expected: Probably a large drop if only a minority of ParticipantQuestion nodes have `values`, for example from seconds to well under a second. If almost every node has `values`, the gain will be small, because the expansion is still the main cost. · Evidence: Compare `MATCH (p:ParticipantQuestion) RETURN count(p)` with `count(p.values)` to see how selective the filter is. Run PROFILE before and after: the plan should show NodeIndexScan on participantquestion_values instead of NodeByLabelScan and Filter, with fewer db hits. Check that SHOW INDEXES reports the index as ONLINE.
rewrite Start from the filtered side and count neighbours with a COUNT subquery
The issueThe pattern `(q:QuestionV2)--(p:ParticipantQuestion)` is undirected and has no relationship type. The planner may anchor on QuestionV2 and expand all of its relationships of every type and direction, checking the label of each neighbour. Also, `count(p)` counts paths, not distinct participants. Every relationship (in either direction) between the same q and p adds 1 to the count, so the number is easy to misread.
The fixAnchor on ParticipantQuestion with the filter, so the index above can be used. Then count QuestionV2 neighbours per p with a COUNT {} subquery and sum the results. This gives the same result as the original but makes the plan anchor explicit. If the relationship type and direction are known, for example `(p)<-[:HAS_PARTICIPANT_QUESTION]-(:QuestionV2)`, add them so the planner only expands that type. If what you actually want is the number of participants, return count(p) filtered with `WHERE EXISTS { (p)--(:QuestionV2) }` instead. That needs only one hit per p.
Proposed rewrite
MATCH (p:ParticipantQuestion) 
WHERE p.values IS NOT NULL 
RETURN sum(COUNT { (p)--(:QuestionV2) }) AS cnt
Expected: The expansion is limited to participants that have `values`, and only their QuestionV2 neighbours are counted. Combined with the index, this could cut runtime by several times. With a typed, directed relationship it could be faster still. · Evidence: Check that the rewrite returns the same number as the original on a sample. PROFILE both versions and compare total db hits, and check that the plan starts from ParticipantQuestion (an index scan) rather than QuestionV2.
caching Cache or precompute this count if it backs a report
The issueThis is a whole-graph aggregate. It ran only 3 times in 7 days (11.4 s in total), which looks like an ad-hoc or reporting query. Any version of it has to touch every relevant node, so even with the index it scales with graph size.
The fixIf a dashboard or job uses this number, compute it on a schedule, for example hourly, and serve the stored value. You could also keep a counter up to date when ParticipantQuestion.values is set or cleared. Run it on a secondary or analytics member so it doesn't compete with the internal service's primary reads.
Expected: Removes the multi-second stalls from the caller's path. At this volume the total cluster saving is small (about 11 s a week), so do this after the index and rewrite, and only if the 18.5 s p95 is visible to users. · Evidence: Find out who calls this shape and how stale the number can be. After the change, this shape should disappear from the internal service's query log or move to a single scheduled run.

docsvc · A79F3DA6 — 17 runs · p95 5.75 s · 7 s total · 3d ago · proposed open shape & trend →

index Composite range index on Document(status, archived)
The issue`MATCH (d:Document {status: $status, archived: true})` filters on status and archived. Neither is the node key (tildaid), and nothing suggests either one is indexed. Without an index the planner has to run NodeByLabelScan on every :Document and Filter each row before expanding DOC_TO_ORG. LIMIT only stops the scan early when matches are common. When a status value has few archived documents, the scan reads most of the label. That would explain the gap between the 383 ms average and the 5,750 ms p95.
The fixCreate a composite range index on both equality predicates. The planner can then use NodeIndexSeek on (status, archived) and touch only the matching documents before expanding to Organization. Both predicates are equalities, so a composite index fits exactly. Put status first, since it is probably the more selective of the two.
Add this index
CREATE INDEX document_status_archived IF NOT EXISTS FOR (d:Document) ON (d.status, d.archived)
Expected: The tail should drop the most. Slow calls for rare status values could go from seconds to tens of milliseconds, because the work would scale with the number of matching docs rather than all :Document nodes. Total savings are small because it only ran 17 times in 7 days (about 6.5 s total). The main gain is removing the multi-second worst case. · Evidence: Run EXPLAIN/PROFILE before and after with a status value from a slow call. Before, look for NodeByLabelScan(d:Document) + Filter with high db hits. After, look for NodeIndexSeek on document_status_archived. Check that SHOW INDEXES lists it ONLINE. Then compare p95 for shape A79F3DA6 over the next week.
index Fallback: single-property index on Document(status)
The issueComposite indexes only help when the query uses both properties. If other docsvc queries filter Document by status alone, they won't benefit from the composite index. Also, archived is a boolean with low selectivity, so it does almost nothing in the composite. The cost here is driven by status.
The fixIf PROFILE shows status alone narrows Document to a small set, a single-property index on status is enough. Seek on status, then filter archived = true in memory. That index can also serve other status-only queries. Create this only if the composite index isn't wanted or isn't picked, so you don't keep two indexes.
Add this index
CREATE INDEX document_status IF NOT EXISTS FOR (d:Document) ON (d.status)
Expected: Similar to the composite index if few documents share each status value. Less effective if most documents for a status are not archived, because those rows still get filtered after the seek. · Evidence: Count documents per status, and per status with archived = true, e.g. MATCH (d:Document) RETURN d.status, d.archived, count(*). PROFILE should show NodeIndexSeek on document_status, with Filter rows close to the LIMIT.

data_monitor · CA13A9C0 — 9 runs · p95 1.89 s · 6 s total · 3d ago · proposed open shape & trend →

index Index CarePlan.tildaid so the chain can start from the single care plan
The issueThe first MATCH filters three nodes by tildaid: Organization, Study and CarePlan. The only one that narrows things to a single row is `cp:CarePlan {tildaid: $careplan_id}`. If CarePlan.tildaid has no index, the planner either scans every CarePlan node or starts at the Study/Organization end. Starting there means walking every DslPlanDefinition, then every ScheduleOfActivities, then every CarePlan in the study through EXECUTE_SOA, and only then dropping all but one care plan. A cost that grows with study size fits the gap between avg 682 ms and p95 1887 ms, with the slow runs likely being large studies.
The fixAdd a lookup index on CarePlan.tildaid. If tildaid really is a unique key, a uniqueness constraint is better because it also gives the planner a guaranteed single row (CREATE CONSTRAINT careplan_tildaid_unique IF NOT EXISTS FOR (c:CarePlan) REQUIRE c.tildaid IS UNIQUE). Check for duplicates first, or the constraint will fail to create. With either one, the plan can start with NodeIndexSeek on cp, then expand outward a few hops and check the study, org and pd filters along the way.
Add this index
CREATE INDEX careplan_tildaid IF NOT EXISTS FOR (c:CarePlan) ON (c.tildaid)
Expected: If the current plan anchors on Study or scans CarePlan, the first MATCH should shrink from thousands of db hits to a few dozen. p95 could plausibly drop from about 1.9 s to tens of ms. If an index already exists and is used, nothing changes. · Evidence: Run SHOW INDEXES WHERE 'CarePlan' IN labelsOrTypes to see if an index exists. Then PROFILE the statement before and after with real parameters. You want NodeIndexSeek on cp:CarePlan(tildaid) as the leaf operator, in place of NodeByLabelScan or a Study-anchored Expand(All) with large row counts. Afterwards, compare total db hits and the p95 from the next 7 days.
rewrite Anchor the query explicitly on the CarePlan and check the study/org path from there
The issueThe pattern is written Organization → Study → DslPlanDefinition → ScheduleOfActivities → CarePlan, all with undirected relationships, and the single-row care plan comes last. With three tildaid filters, the planner can pick the org or study as the anchor and expand through the whole study's plan definitions and care plans. `coalesce(cp.isDeleted, false) = false` is also checked only after that expansion.
The fixMatch the CarePlan first and pin it with a USING INDEX hint. Apply the isDeleted filter right away, then walk from cp out to the study and org to confirm they match. The result rows and the WITH DISTINCT handling stay exactly the same. The hint needs the CarePlan.tildaid index from the first suggestion and will raise an error if that index is missing, so deploy them together.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (o:Organization {tildaid: $org_id})-[:STUDY_TO_ORG]-(study:Study {tildaid: $study_id}) -[:PLAN_DEFINITION_TO_STUDY]-(pd:DslPlanDefinition) -[:SOA_TO_PLAN_DEFINITION]-(:ScheduleOfActivities) -[:EXECUTE_SOA]-(cp:CarePlan {tildaid: $careplan_id}) WHERE coalesce(pd.deltimestamp, ?) = ? AND coalesce(cp.isDeleted, false) = false WITH DISTINCT cp, pd, study MATCH (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:VisitV2) RETURN pv.tildaid AS pv_id, v.tildaid AS visit_id, coalesce(v.title, ?) AS title, coalesce(v.studyPeriod, ?) AS period, coalesce(pv.status, ?) AS status, v.numOfDaysFromAnchor AS days_from_anchor, v.interval AS interval, CASE WHEN pv.appointmentDate IS NULL THEN null ELSE toInteger(pv.appointmentDate.epochMillis) END AS appointment_at, pv.startedAt AS started_at, pv.completedAt AS completed_at, coalesce(pv.signedOffByTeam, ?) AS signed_off_by_team, coalesce(pv.edcBitMap, ?) AS edc_bitmap ORDER BY days_from_anchor, title, pv_id
+ MATCH (cp:CarePlan {tildaid: $careplan_id}) USING INDEX cp:CarePlan(tildaid) WHERE coalesce(cp.isDeleted, false) = false MATCH (cp)-[:EXECUTE_SOA]-(:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(study:Study {tildaid: $study_id})-[:STUDY_TO_ORG]-(o:Organization {tildaid: $org_id}) WHERE coalesce(pd.deltimestamp, ?) = ? WITH DISTINCT cp, pd, study MATCH (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:VisitV2) RETURN pv.tildaid AS pv_id, v.tildaid AS visit_id, coalesce(v.title, ?) AS title, coalesce(v.studyPeriod, ?) AS period, coalesce(pv.status, ?) AS status, v.numOfDaysFromAnchor AS days_from_anchor, v.interval AS interval, CASE WHEN pv.appointmentDate IS NULL THEN null ELSE toInteger(pv.appointmentDate.epochMillis) END AS appointment_at, pv.startedAt AS started_at, pv.completedAt AS completed_at, coalesce(pv.signedOffByTeam, ?) AS signed_off_by_team, coalesce(pv.edcBitMap, ?) AS edc_bitmap ORDER BY days_from_anchor, title, pv_id
Expected: The plan always starts from one node and expands about four hops. Run time no longer depends on how many care plans a study has, which should flatten the p95 tail. The gain is likely large for big studies and small for small ones. · Evidence: PROFILE should show NodeIndexSeek on cp first, with every Expand(Into/All) after it handling only single-digit rows. Also run the old and new queries on the same parameter sets and check they return the same rows in the same order.
index Index Study.tildaid so the study check is a seek, not a label scan
The issue`study:Study {tildaid: $study_id}` is the second identifying filter in the first MATCH. With no index on Study.tildaid, the planner has no cheap way to resolve the study. If it anchors there, it scans every Study node. If it anchors on cp, it has no seek-based option such as joining from both ends, so it can only filter during the expand. Organization.tildaid has the same issue and is worth checking the same way.
The fixCreate a lookup index on Study.tildaid, or a uniqueness constraint if the key is unique. That lets the planner seek the study and either confirm it with Expand(Into) or join from both ends. Check SHOW INDEXES for Organization.tildaid too, and add the matching index if it is missing (CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid)).
Add this index
CREATE INDEX study_tildaid IF NOT EXISTS FOR (s:Study) ON (s.tildaid)
Expected: This mainly makes the plan robust when the planner picks the study end, keeping that a seek of about 1 row instead of a label scan. The gain is modest compared with the CarePlan index. It helps most if the CarePlan index can't be added or the planner keeps choosing the study as the anchor. · Evidence: First check SHOW INDEXES for Study and Organization tildaid. After creating the index, PROFILE should show NodeIndexSeek (or NodeUniqueIndexSeek) on study:Study(tildaid) or an Expand(Into) check, with no NodeByLabelScan on Study anywhere in the plan.

enrollsvc · 6343E8CE — 10 runs · p95 3.50 s · 5 s total · 3d ago · proposed open shape & trend →

index Make sure both anchors (Study.tildaid, User.tildaid) can be looked up through a constraint
The issueThe pattern has two anchors: `(o:Study{tildaid:$scopeId})` and `(u:User{tildaid:$userId})`. The listed properties don't include tildaid, so I can't tell whether either one has a lookup index. If one is missing, the planner has to start from a NodeByLabelScan with a Filter on Study or User. That fits the gap here: an average of 545 ms but a p95 of 3,500 ms. A plain key lookup on both ends should finish in a few ms.
The fixRun SHOW CONSTRAINTS and SHOW INDEXES. If Study.tildaid or User.tildaid has no uniqueness constraint (or at least a range index), add one. A uniqueness constraint also tells the planner that each side matches at most one row. With that, it can start from the more selective end, usually the single User, then expand FOR_USER backwards to a few Actors and join to the Study, instead of walking every STUDY_TO_ACTOR edge of a large study. Run the second statement only if User needs it too: CREATE CONSTRAINT user_tildaid_unique IF NOT EXISTS FOR (u:User) REQUIRE u.tildaid IS UNIQUE
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: If either lookup is missing today, I'd expect the slow executions to drop from seconds to the low tens of ms. If both constraints already exist, nothing changes and you can skip this. · Evidence: Check SHOW CONSTRAINTS/SHOW INDEXES for Study.tildaid and User.tildaid. Then compare PROFILE before and after: you want NodeUniqueIndexSeek (or NodeIndexSeek) on both Study and User, and no NodeByLabelScan. The p95 should also go down over the next 7 days.
rewrite Replace the Role MATCH with an EXISTS check, so DISTINCT isn't needed to remove duplicates
The issue`MATCH (a)-[:APPOINTED_AS]->(r:Role)` only checks that the actor has some role, since r is never returned. But it produces one row per role per actor. `RETURN distinct a` then has to collapse those extra rows in memory, and the a.state / u.status filters can end up applied after the expansion.
The fixMove the role check into an EXISTS subquery. The planner turns that into a semi-apply that stops at the first APPOINTED_AS edge it finds. Put all the filters in one WHERE clause on the main pattern so they run before the existence check. I kept DISTINCT so the result stays the same if there are parallel STUDY_TO_ACTOR or FOR_USER edges. I also dropped the unused `scope` variable.
Proposed rewrite
MATCH (o:Study {tildaid:$scopeId})-[:STUDY_TO_ACTOR]->(a:Actor)-[:FOR_USER]->(u:User {tildaid:$userId})
WHERE u.status <> ? AND a.state <> ? AND EXISTS { (a)-[:APPOINTED_AS]->(:Role) }
RETURN DISTINCT a
Expected: Fewer intermediate rows and less DISTINCT work. The gain grows with how many roles each actor has, roughly proportional to that number. It may be small if actors usually have only one role. · Evidence: Compare PROFILE of the old and new query with the same parameters. Look for lower rows/db hits after the Role expansion, and check that a SemiApply/Exists operator replaces Expand(All)+Distinct. Both queries should return the same result set.
rewrite Start from the User and join to the Study, so large studies don't fan out
The issueEven with both anchors indexed, the planner may expand `(o)-[:STUDY_TO_ACTOR]->(a)` from the Study first. For a study with many actors, that walks every actor before filtering on FOR_USER. That would explain why only some executions take seconds (p95 3,500 ms) while the average stays at 545 ms. A user normally has only a few Actor nodes, so starting from the User is cheaper.
The fixAnchor the query on the User and expand backwards over FOR_USER. Then check the link to the Study with a pattern on the already-bound o and a. That way the cost depends on how many actors the user has, not how many the study has.
Proposed rewrite
MATCH (u:User {tildaid:$userId})<-[:FOR_USER]-(a:Actor)
WHERE u.status <> ? AND a.state <> ?
MATCH (o:Study {tildaid:$scopeId})-[:STUDY_TO_ACTOR]->(a)
WHERE EXISTS { (a)-[:APPOINTED_AS]->(:Role) }
RETURN DISTINCT a
Expected: Stable latency close to the fast-path average for large studies. The heavy p95 executions should improve the most. Only worth doing if PROFILE shows the Study-side expansion is the hot operator. · Evidence: Run PROFILE on a slow parameter pair (a big study) and look for a large row count on the Expand from Study. After the rewrite, the plan should start at a User index seek, and Expand(Into) should handle the Study–Actor check. Compare p95 over a week.

notifsvc · BEDC0EDE — 4 runs · p95 1.93 s · 5 s total · 3d ago · proposed open shape & trend →

index Add a lookup index on Phi.email
The issueThe only filter is `WHERE p.email=$email` on `(p:Phi)`. Nothing in the statement suggests Phi.email is indexed. Without an index the planner has to run a NodeByLabelScan over every Phi node and check email on each one before it expands HAS_PROTECTED_DATA_IN to Patient. That fits the avg of 1152 ms and p95 of 1933 ms for a query that should only return a handful of rows. The scan cost also grows with the number of Phi nodes, and these are PHI records that keep accumulating.
The fixCreate a range index on :Phi(email) so the planner can start with NodeIndexSeek on p, then expand the relationship to reach the few connected Patient nodes. If email is truly one-per-Phi-node, use a uniqueness constraint instead (CREATE CONSTRAINT phi_email_unique IF NOT EXISTS FOR (p:Phi) REQUIRE p.email IS UNIQUE). It gives you the same backing index and also enforces the data rule. Check for duplicates before creating it. The query text doesn't need to change.
Add this index
CREATE INDEX phi_email IF NOT EXISTS FOR (p:Phi) ON (p.email)
Expected: Each call should go from a full label scan (around 1 s) to an index seek plus a small expansion, likely single-digit milliseconds. This only runs 4 times a week, so total time saved is small (about 4.6 s/week). The main benefit is that latency stops growing with the size of Phi, and full scans of sensitive PHI data stop. If notification volume increases, this lookup becomes far more frequent. · Evidence: Run `SHOW INDEXES YIELD labelsOrTypes, properties WHERE 'Phi' IN labelsOrTypes` to confirm no index covers email yet. Compare PROFILE before and after: the plan should change from NodeByLabelScan(p:Phi) + Filter to NodeIndexSeek(p:Phi(email)), with far fewer db hits. Then watch the p95 for shape BEDC0EDE over the next 7 days. Before choosing the unique constraint, run `MATCH (p:Phi) WITH p.email AS e, count(*) AS c WHERE c > 1 RETURN count(*)` to check for duplicate emails.

docsvc · 98AE3433 — 1 runs · p95 4.85 s · 4 s total · 3d ago · proposed open shape & trend →

rewrite Stop re-expanding the INCLUDES*?..? ancestor chain separately for every document
The issueFor every candidate doc1, `OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder)` walks the folder tree again, then `collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder)` builds a folderHierarchy list per document. Documents in the same folder share the same ancestors, so the walk is repeated once per document instead of once per folder. In a count-over-all-documents query like this, that walk is probably most of the 4.3 s. This is a guess and needs PROFILE to confirm.
The fixTurn the check around. First collect the folders the user's policies point at (policies are already gathered once per study in the subquery). Then test each document with one bounded existence check, e.g. `EXISTS { (doc1)<-[:INCLUDES*1..N]-(f:Folder) WHERE f IN policyFolders }` (N = 1 + the current upper bound), or the equivalent with a plain list-membership check. This removes the per-document collect, and the walk can stop at the first matching folder. Folder-less (study-wide) policies keep their existing branch. I have not written out the full statement: the captured text is cut off where comments were normalised (the policy and folder filter and the doc1 MATCH are missing), so any full rewrite built from it would be wrong. Apply the change to the source query in docsvc.
Expected: Fewer db hits on the Expand(Into)/VarLengthExpand rows and less memory used by the per-document collect. The gain could be large if many documents share deep folder chains, and small if folders are shallow. Note that this statement ran only once in 7 days (about 4.3 s in total), so the gain matters for the user waiting on the call, not for cluster load. · Evidence: PROFILE with real parameters. Confirm that the VarLengthExpand on INCLUDES and the EagerAggregation for folderHierarchy have the highest db hits and rows. After the change, compare total db hits and peak memory in the PROFILE output, and confirm the seven counts match the old query for the same user.
index Make sure Study.tildaid is indexed for the UNWIND lookup
The issue`UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId` runs once per allowed study id. If Study.tildaid has no index or constraint, each id costs a full NodeByLabelScan on Study plus a Filter, and the cost grows with (number of allowed studies × number of Study nodes).
The fixIf SHOW INDEXES has no range index or uniqueness constraint on Study.tildaid, create one. A uniqueness constraint is better if tildaid really is unique; the plain index below is the safe choice if you have not checked that. The same check applies to Organization.tildaid and User.tildaid, which are looked up by $scopeOrgId0 and $ctxUserId0. Those are probably already constrained as node keys.
Add this index
CREATE INDEX study_tildaid IF NOT EXISTS FOR (s:Study) ON (s.tildaid)
Expected: If the index is missing, the per-id lookup goes from a label scan to a NodeIndexSeek, which saves time in proportion to how many Study nodes there are. If it already exists, nothing changes. · Evidence: Run SHOW INDEXES WHERE 'Study' IN labelsOrTypes. Run EXPLAIN before and after, and look for NodeIndexSeek on Study(tildaid) instead of NodeByLabelScan + Filter under the UNWIND.
index Index DocumentSigner.userId so the signer checks can start from the signer
The issueThe signer check `EXISTS { (doc1)-[:HAS]-(:DocumentAnnotation)-[:ANNOTATION_SIGNER]-(:DocumentSigner{userId:$signerUserId1}) }` appears in the access filter, and again with $signerUserId2 in the UNION branch. Each time, it walks from every candidate document through all of its annotations and their signers before filtering on userId. Without an index on DocumentSigner.userId, the planner cannot start from the few signer nodes that belong to this user and work back to the documents.
The fixCreate a range index on DocumentSigner.userId. The planner can then seek the user's signers and use the result as a semi-join, or at least has the option to. This matters most when the user signs few documents but the scope holds many documents with many annotations.
Add this index
CREATE INDEX document_signer_user_id IF NOT EXISTS FOR (s:DocumentSigner) ON (s.userId)
Expected: Could noticeably reduce the number of HAS/ANNOTATION_SIGNER expansions per document. If the planner keeps anchoring on doc1, it will have no effect, so treat this as worth trying rather than certain. · Evidence: Run PROFILE before and after creating the index. Check whether a NodeIndexSeek on DocumentSigner(userId) appears and whether db hits drop on the Expand rows under the EXISTS subqueries.

execsvc · 0C68BC89 — 1 runs · p95 4.85 s · 4 s total · 3d ago · proposed open shape & trend →

index Index User.tildaid for the per-row assignee lookup
The issue`optional match (user:User{tildaid:q.assignTo})` runs once for every row coming out of the nested UNION subqueries and the Task join. That can be thousands of rows. The value comes from another node (q.assignTo), so without a lookup index on User.tildaid every row needs a NodeByLabelScan of User plus a filter. That is a full label scan repeated once per row.
The fixAdd a lookup index on User.tildaid so the planner can use NodeIndexSeek for each row. If tildaid is meant to be unique per user, a uniqueness constraint (CREATE CONSTRAINT user_tildaid IF NOT EXISTS FOR (u:User) REQUIRE u.tildaid IS UNIQUE) gives the same seek and also enforces the rule. Skip this if such an index or constraint already exists.
Add this index
CREATE INDEX user_tildaid IF NOT EXISTS FOR (u:User) ON (u.tildaid)
Expected: If no index exists today, the User part of the plan should go from a scan per row to a seek per row. That could remove a large share of the ~3.9 s, depending on the User count and the row count. This statement ran only once in 7 days, so the total saving is small unless the same lookup appears in other shapes. · Evidence: Run SHOW INDEXES WHERE 'User' IN labelsOrTypes to check whether an index exists. Then run PROFILE before and after: the operator for `user` should change from NodeByLabelScan + Filter to NodeIndexSeek, and db hits on that branch should drop.
rewrite Turn the Task join into an existence check and deduplicate before the OPTIONAL MATCHes
The issue`MATCH (task:Task)-[:WITH_CONTEXT]-(q)` binds `task`, but `task` is never used. Its only effect is to multiply each q row by the number of Tasks linked to it. The rows also still carry pd, pa and doc, which create duplicates of (s, q, pv, cp). Both OPTIONAL MATCHes (User, Visit) then run on every duplicate row, only for collect(DISTINCT ...) to throw the duplicates away at the end.
The fixCollapse the rows to WITH DISTINCT s, q, pv, cp, the only variables the RETURN uses. Apply the q.state filter there, and replace the Task MATCH with an EXISTS subquery. EXISTS stops at the first Task it finds and does not add rows. Every returned collect is DISTINCT and depends only on s, q, pv, cp, user and v, so the result stays the same.
Proposed rewrite
MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId}) 
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 
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 
return pd,site } 
WITH pd, site AS s 
MATCH (p:Patient)-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd) 
call { 
with cp 
call { 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) 
return pa, pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(cp) 
return pa, null as pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) 
return pa, pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]-(cp) 
return pa, null as pv } 
call { 
with pa 
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) 
return q 
union 
with pa 
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(:ParticipantTable)-[:PARTICIPANT_TABLE]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) 
where q.delpending is null or q.delpending = false 
return q } 
return q, pa, pv, null as doc 
union 
with cp, p 
call { 
with cp, p 
match (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2)-[:BINDER_FOR]-(vb:Binder)-[:INCLUDES]-(doc:Document)-[:INCLUDES]-(pb:Binder)-[:BINDER_FOR]-(p) 
MATCH (q:Query)-[:HAS_QUERY]-(doc) 
return doc, pv, q } 
return q, null as pa, pv, doc } 
WITH DISTINCT s, q, pv, cp 
WHERE q.state <> ? AND EXISTS { 
MATCH (:Task)-[:WITH_CONTEXT]-(q) } 
optional match (user:User{tildaid:q.assignTo}) 
optional match (pv)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) 
return collect(distinct s.name) as siteNames, collect(distinct q.who) as whos, collect(distinct user{.tildaid, .name}) as assignToUsers, collect(distinct q.context) as contexts, collect(distinct v.title) as visitTitles, collect(distinct cp.subjectid) as subjectIds
Expected: Fewer rows should reach the two OPTIONAL MATCHes and the aggregation. The drop is roughly the average number of Tasks per Query multiplied by the pa/doc fan-out. That could cut the tail of the plan noticeably, but the size of the gain depends on the data. · Evidence: PROFILE both versions and compare the row counts going into the OptionalExpand/Apply for `user` and `v`, plus total db hits. Also check that all six returned collections are identical on the same $dashOrgId/$dashStudyId.
index Make sure the Study anchor lookup is indexed
The issueThe whole query starts from `MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId})`. If neither Organization.tildaid nor Study.tildaid has an index, the planner has to label-scan one side before it can expand. Every later branch is driven from these two nodes, so a slow start affects the whole query.
The fixAdd a lookup index (or a uniqueness constraint, if tildaid is a key) on Study.tildaid. Do the same for Organization.tildaid if SHOW INDEXES shows it is missing. The planner can then seek both endpoints and join them with an Expand(Into).
Add this index
CREATE INDEX study_tildaid IF NOT EXISTS FOR (s:Study) ON (s.tildaid)
Expected: This only matters if the index is missing. If so, the scan at the start becomes a single seek, saving roughly the cost of one scan over the Study or Organization label. That is probably small next to the fan-out further down. If the index already exists, this is a no-op. · Evidence: Run SHOW INDEXES and filter for the Study and Organization labels. Then check that the leaf operators in the EXPLAIN plan are NodeIndexSeek (or NodeUniqueIndexSeek), not NodeByLabelScan.

execsvc · B5BE080F — 1 runs · p95 4.85 s · 4 s total · 3d ago · proposed open shape & trend →

rewrite Apply the site-name filter right after the plan-definition subquery
The issue`s.name IN $siteNames` sits in the WHERE of `MATCH (task:Task)-[:WITH_CONTEXT]-(q)`. That is far down the query. Before it runs, every (pd, site) pair from the first CALL is expanded through Patient→CarePlan→ScheduleOfActivities. It then goes through four UNION branches over ParticipantActivity (one is a variable-length `[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]` traversal), the nested Query UNIONs, and the Visit→Binder→Document→Binder UNION branch. Rows for sites the caller never asked for get built and then thrown away. `s` only depends on the first CALL, so the filter can be applied as soon as `s` is bound.
The fixAdd the predicate to the `WITH pd, site AS s` that follows the first CALL: `WITH pd, site AS s WHERE s.name IN $siteNames`. Remove it from the later Task WHERE. This gives the same results because `s` never changes after that point. The Patient/CarePlan expansion and all nested subqueries then run only for the requested sites. Nothing else in the statement changes.
Proposed rewrite
MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId}) 
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 
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 
return pd,site } 
WITH pd, site AS s 
WHERE s.name IN $siteNames 
MATCH (p:Patient)-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd) 
call { 
with cp 
call { 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) 
return pa, pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(cp) 
return pa, null as pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) 
return pa, pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]-(cp) 
return pa, null as pv } 
call { 
with pa 
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) 
return q 
union 
with pa 
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(:ParticipantTable)-[:PARTICIPANT_TABLE]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) 
where q.delpending is null or q.delpending = false 
return q } 
return q, pa, pv, null as doc 
union 
with cp, p 
call { 
with cp, p 
match (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2)-[:BINDER_FOR]-(vb:Binder)-[:INCLUDES]-(doc:Document)-[:INCLUDES]-(pb:Binder)-[:BINDER_FOR]-(p) 
MATCH (q:Query)-[:HAS_QUERY]-(doc) 
return doc, pv, q } 
return q, null as pa, pv, doc } 
MATCH (task:Task)-[:WITH_CONTEXT]-(q) 
WHERE q.state = $mqState AND q.externalFlag = true 
optional match (pa)-[:BELONGS_TO_ACTIVITY]-(act:Activity|ActivityV2) 
optional match (pv)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) 
match (p)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi)
OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(study:Study{tildaid:$dashStudyId})-[:STUDY_TO_ACTOR]->(studyActor:Actor)-[:FOR_USER]->(requestingUser:User{tildaid:$dashRequestingUserId}) 
WHERE studyActor.state<>? 
WITH task, q, s, act, v, cp, phi, study, head(collect(studyActor)) AS studyActor 
OPTIONAL MATCH (q)-[:ALL_CHANGELOG]-(cl:ChangeLog) 
optional match (task)-[:BELONGS_TO]->(i:Inbox)-[:OWNED_BY]->(u:User)
WITH task,q,s,act,v,cp.subjectid as subjectid, phi,study,studyActor,collect(distinct u.name) as assignedToUsers, cl 
ORDER BY cl.createdat ASC 
WITH task,q,s,act,v,phi,subjectid,assignedToUsers,study,studyActor, COLLECT(cl) AS changeLogs,count(case when cl.type=? or (cl.type=? and cl.reason <> ?) then ? end) as comments, duration.inDays(datetime({epochMillis: q.createdat}), datetime()).days AS durationDays 
WITH task,q,s,act,v,phi,subjectid,assignedToUsers,study,studyActor, changeLogs, comments, durationDays, reduce(d = ?, cl IN changeLogs | CASE WHEN cl.type = ? AND cl.newValue = ? THEN cl.createdat ELSE d END) AS resolvedAt 
WITH {task: task, q: q, s: s, act: act, v: v, phi: phi, subjectid: subjectid, assignedToUsers: assignedToUsers, study: study, studyActor: studyActor, changeLogs: changeLogs, comments: comments, durationDays: durationDays} as entry 
WITH entry 
WITH entry.q.tildaid as queryId, collect(entry) as entriesPerQuery 
WITH count(queryId) as countOfN, collect(head(entriesPerQuery)) as rows 
UNWIND rows as entry 
WITH countOfN, entry 
order by entry.q.createdat desc 
RETURN distinct entry.q, entry.s, entry.study, entry.studyActor, entry.changeLogs, entry.durationDays, countOfN, apoc.
create.vNode([?], {taskId: entry.task.tildaid, subject: entry.phi.firstname + ? + entry.phi.lastname, subjectid: entry.subjectid, visitTitle: entry.v.title, activityTitle: entry.act.title, type: entry.q.type, comments: entry.comments, assignedTo: entry.assignedToUsers, blindedAccess: entry.act.blindedAccess}) as dashboardInfo 
limit $pgLimit
Expected: How much this helps depends on what fraction of the study's sites are in $siteNames. If dashboards usually ask for a few sites out of many, db hits and elapsed time should drop by roughly that ratio. If $siteNames usually covers every site, there is little gain. The statement ran only once in 7 days (3.8 s), so the absolute saving is small. Treat this as a cheap, low-risk change, not an urgent one. · Evidence: Run PROFILE before and after with the same parameters. Compare the row count leaving the first `WITH pd, site AS s` and entering the Patient/CarePlan Expand, and compare total db hits. Check that both versions return identical result sets. After deploying, compare p95 against the current 4850 ms.
index Make sure the anchor lookups on tildaid use an index or unique constraint
The issueThe statement starts with `MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId})`. It also matches `(study:Study{tildaid:$dashStudyId})` and `(requestingUser:User{tildaid:$dashRequestingUserId})` inside the OPTIONAL MATCH. Without a tildaid lookup on Organization or Study, the planner has to start from a NodeByLabelScan plus a Filter instead of a single NodeUniqueIndexSeek, and everything after it fans out from that start point.
The fixRun SHOW CONSTRAINTS and SHOW INDEXES and check for tildaid on Organization, Study and User. If any is missing, add a uniqueness constraint, which also creates a backing range index. The Organization DDL is below. Use the same form for Study (`study_tildaid`) and User (`user_tildaid`). If tildaid can be duplicated, use `CREATE INDEX ... ON (n.tildaid)` instead. If the constraints already exist, skip this suggestion.
Add this index
CREATE CONSTRAINT organization_tildaid IF NOT EXISTS FOR (n:Organization) REQUIRE n.tildaid IS UNIQUE
Expected: If an index is missing, the anchor goes from a label scan to an index seek. That saves milliseconds to tens of milliseconds per call, depending on how many Organization or Study nodes there are. Most of the 3.8 s is probably spent in the downstream fan-out, so this is a supporting fix, not the main one. If the constraints already exist, nothing changes. · Evidence: Look at the EXPLAIN plan for the leaf operators that bind `org` and `st`. Expect NodeUniqueIndexSeek (or NodeIndexSeek) on tildaid, not NodeByLabelScan followed by Filter. Also confirm with SHOW CONSTRAINTS.

execsvc · 345129F1 — 1 runs · p95 4.85 s · 4 s total · 3d ago · proposed open shape & trend →

rewrite Apply the q.state = $dashState filter inside each Query branch instead of after the UNION
The issueThe only selective filter on Query, `WHERE q.state = $dashState`, runs after the whole nested CALL/UNION tree has finished. Every Query reachable from every ParticipantActivity (four pa branches, including the variable-length PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?.. expansion) and from every Binder/Document chain gets built into a row. UNION then deduplicates all of those rows on (q, pa, pv, doc) before the planner can drop the ones with the wrong state. Neo4j usually does not push a predicate on a subquery's output back into its UNION branches, so all of that work is wasted on rows that are thrown away.
The fixAdd `q.state = $dashState` to the WHERE clause right after each of the three `MATCH (q:Query)...` patterns, and drop the outer WHERE. Rows with the wrong state are then removed before the UNION dedup and before the `(task:Task)-[:WITH_CONTEXT]-(q)` expansion. The result does not change: the predicate depends only on q, and q is fixed by the time it leaves each branch. The existing delpending filter on the second branch stays as it is. I did not change the asymmetry where the first and third branches have no delpending filter, because that would change the result.
Proposed rewrite
MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId}) 
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 
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 
return pd,site } 
WITH pd, site AS s 
MATCH (p:Patient)-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd) 
call { 
with cp 
call { 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) 
return pa, pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(cp) 
return pa, null as pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) 
return pa, pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]-(cp) 
return pa, null as pv } 
call { 
with pa 
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) 
where q.state = $dashState 
return q 
union 
with pa 
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(:ParticipantTable)-[:PARTICIPANT_TABLE]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) 
where q.state = $dashState and (q.delpending is null or q.delpending = false) 
return q } 
return q, pa, pv, null as doc 
union 
with cp, p 
call { 
with cp, p 
match (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2)-[:BINDER_FOR]-(vb:Binder)-[:INCLUDES]-(doc:Document)-[:INCLUDES]-(pb:Binder)-[:BINDER_FOR]-(p) 
MATCH (q:Query)-[:HAS_QUERY]-(doc) 
where q.state = $dashState 
return doc, pv, q } 
return q, null as pa, pv, doc } 
MATCH (task:Task)-[:WITH_CONTEXT]-(q) 
RETURN q.queryType AS QueryType, count(q) AS CountPerType 
ORDER BY CountPerType DESC
Expected: Fewer rows reach the UNION dedup and the Task expansion, roughly in proportion to the share of Query nodes that are not in $dashState. If most queries are closed or resolved, the saving could be large. If most share the requested state, there may be little change. The statement ran only once in 7 days, so the total-time saving is small either way. · Evidence: PROFILE the old and new versions with the same parameters and compare db hits and rows at the Union / Distinct operators and at the Task Expand. Also confirm that both versions return the same QueryType counts.
index Make sure the Organization and Study anchors use a tildaid lookup
The issueThe whole traversal starts from `MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId})`. If neither label has an index or constraint on tildaid, the planner has to scan every Organization or Study node and filter on the property before any expansion starts.
The fixCheck with SHOW INDEXES / SHOW CONSTRAINTS. If tildaid is the business key, a uniqueness constraint on each label is the right fix: it also creates the backing range index, so both anchors become NodeUniqueIndexSeek. Apply the same DDL to Study with `CREATE CONSTRAINT study_tildaid IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE`. If the constraint already exists, skip this suggestion.
Add this index
CREATE CONSTRAINT organization_tildaid IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE
Expected: If the index is missing, the anchor step goes from a label scan to a single index seek, saving roughly in proportion to the Organization and Study node counts. If it already exists, nothing changes. · Evidence: An EXPLAIN plan that shows NodeByLabelScan for Organization or Study today and NodeUniqueIndexSeek or NodeIndexSeek after the change. Also check that the existing tildaid values are unique before adding the constraint.

execsvc · 61D9334F — 1 runs · p95 4.85 s · 4 s total · 3d ago · proposed open shape & trend →

rewrite Apply the site-name and query-state/externalFlag filters early instead of at the end
The issueThe filter `WHERE q.state = $mqState AND q.externalFlag = true AND s.name IN $siteNames` sits on the `MATCH (task:Task)-[:WITH_CONTEXT]-(q)` that comes after both large CALL subqueries. So every (pd, site) row from the site UNION expands Patient→CarePlan→SOA. Every CarePlan then expands into four ParticipantActivity UNION branches, including the variable-length `[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]` path, plus the Query lookups through ParticipantQuestion/ParticipantTable and the Visit→Binder→Document→Binder chain. Rows for sites not in $siteNames, and Queries in the wrong state or with externalFlag=false, are built and then thrown away. That wasted traversal is most of the cost of a 3.7 s run.
The fix`s.name IN $siteNames` only depends on `site`, so it can go straight after `WITH pd, site AS s`. Sites outside the list are then dropped before any patient or care-plan expansion. The `q.state` and `q.externalFlag` checks can go inside each UNION branch that returns q, so non-matching Queries are discarded before they multiply against Task, ChangeLog and Inbox rows. Removing rows earlier gives the same result, because the original WHERE only ever removed rows. While doing this, check the hidden upper bound on `*?..` in the ParticipantActivity branch. If it is large or missing, give it an explicit small maximum depth, because an unbounded expansion over two relationship types is the riskiest part of this plan.
Proposed rewrite
MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId}) 
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 
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 
return pd,site } 
WITH pd, site AS s 
WHERE s.name IN $siteNames 
MATCH (p:Patient)-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd) 
call { 
with cp 
call { 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) 
return pa, pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(cp) 
return pa, null as pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp) 
return pa, pv 
union 
with cp 
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]-(cp) 
return pa, null as pv } 
call { 
with pa 
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) 
where q.state = $mqState and q.externalFlag = true 
return q 
union 
with pa 
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(:ParticipantTable)-[:PARTICIPANT_TABLE]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa) 
where (q.delpending is null or q.delpending = false) and q.state = $mqState and q.externalFlag = true 
return q } 
return q, pa, pv, null as doc 
union 
with cp, p 
call { 
with cp, p 
match (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2)-[:BINDER_FOR]-(vb:Binder)-[:INCLUDES]-(doc:Document)-[:INCLUDES]-(pb:Binder)-[:BINDER_FOR]-(p) 
MATCH (q:Query)-[:HAS_QUERY]-(doc) 
where q.state = $mqState and q.externalFlag = true 
return doc, pv, q } 
return q, null as pa, pv, doc } 
MATCH (task:Task)-[:WITH_CONTEXT]-(q) 
optional match (pa)-[:BELONGS_TO_ACTIVITY]-(act:Activity|ActivityV2) 
optional match (pv)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) 
match (p)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi)
WITH task,q,s,act,v,cp,phi,null as study,null as studyActor 
OPTIONAL MATCH (q)-[:ALL_CHANGELOG]-(cl:ChangeLog) 
optional match (task)-[:BELONGS_TO]->(i:Inbox)-[:OWNED_BY]->(u:User)
WITH task,q,s,act,v,cp.subjectid as subjectid, phi,study,studyActor,collect(distinct u.name) as assignedToUsers, cl 
ORDER BY cl.createdat ASC 
WITH task,q,s,act,v,phi,subjectid,assignedToUsers,study,studyActor, COLLECT(cl) AS changeLogs,count(case when cl.type=? or (cl.type=? and cl.reason <> ?) then ? end) as comments, duration.inDays(datetime({epochMillis: q.createdat}), datetime()).days AS durationDays 
WITH task,q,s,act,v,phi,subjectid,assignedToUsers,study,studyActor, changeLogs, comments, durationDays, reduce(d = ?, cl IN changeLogs | CASE WHEN cl.type = ? AND cl.newValue = ? THEN cl.createdat ELSE d END) AS resolvedAt 
WITH {task: task, q: q, s: s, act: act, v: v, phi: phi, subjectid: subjectid, assignedToUsers: assignedToUsers, study: study, studyActor: studyActor, changeLogs: changeLogs, comments: comments, durationDays: durationDays} as entry 
WITH entry 
WITH entry.q.tildaid as queryId, collect(entry) as entriesPerQuery 
WITH count(queryId) as countOfN, collect(head(entriesPerQuery)) as rows 
UNWIND rows as entry 
WITH countOfN, entry 
RETURN distinct entry.q, entry.s, entry.study, entry.studyActor, entry.changeLogs, entry.durationDays, countOfN, apoc.
create.vNode([?], {taskId: entry.task.tildaid, subject: entry.phi.firstname + ? + entry.phi.lastname, subjectid: entry.subjectid, visitTitle: entry.v.title, activityTitle: entry.act.title, type: entry.q.type, comments: entry.comments, assignedTo: entry.assignedToUsers, blindedAccess: entry.act.blindedAccess}) as dashboardInfo
Expected: This could plausibly cut runtime by a large fraction, in proportion to how many sites and Query states are excluded. If $siteNames usually covers most sites and most Queries already match the state, the gain will be small. Since the query ran only once in 7 days, the total time saved is modest, but this one dashboard call should feel noticeably faster. · Evidence: PROFILE both versions with the same parameters. Compare db hits and rows on the CarePlan/ScheduleOfActivities expand step and inside the ParticipantActivity/Query subqueries; the Filter on s.name should now sit right after the site Union. Check that both versions return the same rows and the same countOfN, then compare elapsed time over the next few runs.
index Make sure the Study and Organization tildaid anchors have unique lookups
The issueThe whole statement starts from `MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId})`. Without a uniqueness constraint or index on tildaid, the planner has to scan all Organization or Study nodes to find the anchor before any traversal starts. The property list for this shape does not show whether tildaid is already indexed.
The fixRun SHOW CONSTRAINTS / SHOW INDEXES. If Study.tildaid or Organization.tildaid does not have a uniqueness constraint (tildaid looks like an application key), add one. The constraint creates a backing range index, so the anchor becomes a NodeUniqueIndexSeek. Only one of the two is strictly needed for the planner to seek and then expand, but having both is cheap. The second DDL is: CREATE CONSTRAINT org_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE.
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: If these keys are currently unindexed, the anchor step changes from a label scan to a constant-time seek. That saves time in proportion to how many Study and Organization nodes exist, probably a few to tens of milliseconds. If the constraints already exist, this changes nothing and should be skipped. · Evidence: Run EXPLAIN on the statement. The leftmost operator should be NodeUniqueIndexSeek on Study(tildaid) or Organization(tildaid), not NodeByLabelScan. Confirm the current state with SHOW CONSTRAINTS before creating anything.

regsvc · E99E74FE — 5 runs · p95 4.25 s · 3 s total · 3d ago · proposed open shape & trend →

index Index DslPlanDefinition on (studyId, delpending)
The issueThe node pattern `(pd:DslPlanDefinition{studyId:$studyId,delpending:false})` filters on two non-key properties. Without an index on them, the planner either label-scans every DslPlanDefinition or starts from Smo and expands. That would fit the gap between the 536 ms average and the 4250 ms p95: studies or orgs with many nodes cost far more per call.
The fixAdd a composite range index so the planner can seek straight to the plan definitions for one study that are not pending deletion, then expand the few relationships to Smo and filter on orgId. Both predicates are equality checks, so the composite index fully covers the seek.
Add this index
CREATE INDEX dsl_plan_definition_study_delpending IF NOT EXISTS FOR (pd:DslPlanDefinition) ON (pd.studyId, pd.delpending)
Expected: If the plan currently starts with NodeByLabelScan on DslPlanDefinition, this should become a NodeIndexSeek. p95 could drop from seconds to tens of milliseconds, depending on how many plan definitions a study has. The total benefit is modest because the query ran only 5 times in 7 days, but each slow call costs seconds. · Evidence: Run PROFILE before and after with a real $studyId/$orgId from a slow call. Confirm that the leaf operator changes from NodeByLabelScan to NodeIndexSeek on DslPlanDefinition(studyId, delpending), check that db hits fall, and compare p95 for this shape after deploy.
index Index Smo on orgId
The issue`(n:Smo{orgId:$orgId})` filters on orgId. If no index exists, any plan that anchors on Smo, or that has to check orgId cheaply after a join, depends on a label scan. When the studyId side is not very selective, the planner may pick Smo as its starting point.
The fixAdd a range index on Smo.orgId. The planner then has a cheap anchor on both ends and can choose the more selective side, or use a hash join between two index seeks, instead of scanning.
Add this index
CREATE INDEX smo_org_id IF NOT EXISTS FOR (n:Smo) ON (n.orgId)
Expected: This should remove any NodeByLabelScan on Smo and give the planner a selective alternative. The gain is likely smaller than from the DslPlanDefinition index if studyId is the more selective filter. · Evidence: Run SHOW INDEXES to confirm no index on Smo(orgId) already exists. Then PROFILE the query and check whether Smo is reached by NodeIndexSeek or by label scan, and compare estimated against actual rows on each side.
rewrite Give the relationship a direction and anchor on the plan definition
The issue`-[:PLAN_DEFINITION_TO_SMO]-` has no direction, so the planner has to check both directions of every relationship on each node it expands from. The pattern also leaves the choice of start side entirely to the planner's estimates, and with skewed data those estimates can be wrong.
The fixThe relationship's name suggests it is stored as (DslPlanDefinition)->(Smo). Give the expansion that direction and start from pd, the side covered by the composite index, so each plan definition only expands its outgoing relationships to Smo. Check the real direction first, e.g. MATCH ()-[r:PLAN_DEFINITION_TO_SMO]->() RETURN labels(startNode(r)) LIMIT 1. If any relationships are stored the other way round, keep the undirected form. Results are unchanged, including duplicate pd rows when several matching Smo nodes are linked.
Proposed rewrite — current query in red −, proposed in green +
- match (n:Smo{orgId:$orgId})-[:PLAN_DEFINITION_TO_SMO]-(pd:DslPlanDefinition{studyId:$studyId,delpending:false}) return pd
+ MATCH (pd:DslPlanDefinition {studyId: $studyId, delpending: false})-[:PLAN_DEFINITION_TO_SMO]->(n:Smo {orgId: $orgId}) RETURN pd
Expected: The gain is small to moderate: it avoids checking relationships in both directions and makes the plan more predictable. It mainly helps once the indexes above exist. · Evidence: Check that the result row count matches the original query for a few sample parameters. In PROFILE, the Expand(All)/Expand(Into) operator should show fewer db hits.