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