Optimizer suggestions — proposals from claude-opus-5-5, 3d ago; suggests only, a human applies
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.
Status: proposed · all suggestions
No rollups for this shape in the window.