Optimizer suggestions — proposals from claude-opus-5-5, 3d ago; suggests only, a human applies
index Make sure the ParticipantVisit.tildaid anchor is backed by a uniqueness constraint
The issueThe whole statement starts at `MATCH (pv:ParticipantVisit {tildaid: $id})`. If no index or constraint covers ParticipantVisit.tildaid, the planner does a NodeByLabelScan plus a Filter over every ParticipantVisit, and then runs the 5-hop CarePlan→SOA→PlanDefinition→Study chain from there. The 440 ms p95 against a 54 ms average fits either a scan or a cold page cache. With only 4 runs in 7 days I can't tell which.
The fixtildaid works like an external ID, so declare it unique. A uniqueness constraint creates a backing range index, so the anchor becomes a NodeUniqueIndexSeek that returns at most one row. If SHOW CONSTRAINTS already lists one for this label and property, skip this step: the anchor is already optimal.
Add this index
CREATE CONSTRAINT participantvisit_tildaid_unique IF NOT EXISTS FOR (pv:ParticipantVisit) REQUIRE pv.tildaid IS UNIQUE
Expected: If the anchor is scanning today, the per-call time should fall to a few ms because the remaining expansions are small and walk from one node. If an index already exists, expect no change. Total cost is small either way (214 ms over 7 days), so the main gain is a more stable p95. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES for ParticipantVisit(tildaid). Run PROFILE on the statement and check whether the first operator is NodeUniqueIndexSeek or NodeIndexSeek rather than NodeByLabelScan. Also check that the label has no duplicate tildaid values before you create the constraint.
rewrite Turn the unused Visit match into an existence check
The issue`MATCH (pv)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2)` binds `v`, but nothing reads it later. It only acts as a filter. If a ParticipantVisit links to more than one Visit/VisitV2 node, every later expansion (CarePlan chain, three OPTIONAL MATCHes) and every apoc.create.vNode call is repeated once per visit, giving duplicate virtual nodes.
The fixSwap the pattern for an EXISTS subquery. It stops at the first matching Visit, so no extra rows are produced. The filter is the same: rows without a visit are still excluded. The only difference is that you get one row instead of N duplicates when there are several visits.
Proposed rewrite
MATCH (pv:ParticipantVisit {tildaid: $id})
WHERE EXISTS { (pv)-[:BELONGS_TO_VISIT]-(:Visit|VisitV2) }
MATCH (pv)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp:CarePlan)-[:EXECUTE_SOA]->(soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(s:Study)
OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)
OPTIONAL MATCH (site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(org:Organization)
OPTIONAL MATCH (cp)-[:RESEARCH_SUBJECT]-(p:Patient)
CALL apoc.
create.vNode([?], { siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), studyId: s.tildaid, studyName: s.name, orgId: coalesce(org.tildaid, ?), orgName: coalesce(org.name, ?), orgAlias: coalesce(org.alias, ?), createdat: pv.createdat, updatedat: pv.updatedat, branchLevel: pv.branchLevel, pre: pv.pre, migrationReady: pv.migrationReady, sex: p.sex, ethnicity: p.ethnicity, carePlanStatus: cp.status, pvStatus: pv.status, edcStatus: pv.edcStatus })
YIELD node
RETURN node
Expected: If each pv has exactly one visit, results and cost stay the same. If any pv has several visits, rows and vNode calls should drop by that factor, and callers stop getting duplicate nodes. · Evidence: Count visits per pv: MATCH (pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2) WITH pv, count(v) AS c WHERE c > 1 RETURN count(pv). Then compare PROFILE row counts after the first MATCH, old vs new, and confirm callers never relied on the duplicate rows.
Status: proposed · all suggestions
No rollups for this shape in the window.