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