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