Optimizer suggestions — proposals from claude-opus-5-5, 3d ago; suggests only, a human applies
rewrite Apply the q.state = $dashState filter inside each Query branch instead of after the UNION
The issueThe only selective filter on Query, `WHERE q.state = $dashState`, runs after the whole nested CALL/UNION tree has finished. Every Query reachable from every ParticipantActivity (four pa branches, including the variable-length PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?.. expansion) and from every Binder/Document chain gets built into a row. UNION then deduplicates all of those rows on (q, pa, pv, doc) before the planner can drop the ones with the wrong state. Neo4j usually does not push a predicate on a subquery's output back into its UNION branches, so all of that work is wasted on rows that are thrown away.
The fixAdd `q.state = $dashState` to the WHERE clause right after each of the three `MATCH (q:Query)...` patterns, and drop the outer WHERE. Rows with the wrong state are then removed before the UNION dedup and before the `(task:Task)-[:WITH_CONTEXT]-(q)` expansion. The result does not change: the predicate depends only on q, and q is fixed by the time it leaves each branch. The existing delpending filter on the second branch stays as it is. I did not change the asymmetry where the first and third branches have no delpending filter, because that would change the result.
Proposed rewrite
MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId})
call (org,st) {
match (org)-[:STUDY_TO_ORG]-(st)-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)
where (org.orgType=? or org.orgType=?) and pd.delpending=false
return pd, site
union
match (org)-[:IS_AN_ORG]-(:Smo)-[:SITE_TO_SMO]-(site:Site)-[:PLAN_DEFINITION_TO_SITE]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(st)
where pd.delpending=false
return pd,site }
WITH pd, site AS s
MATCH (p:Patient)-[:RESEARCH_SUBJECT]-(cp:CarePlan)-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd)
call {
with cp
call {
with cp
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp)
return pa, pv
union
with cp
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(ae:AdverseEvent)-[:OBJ_TO_ADVERSE_EVENT]-(cp)
return pa, null as pv
union
with cp
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pv:ParticipantVisit)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(cp)
return pa, pv
union
with cp
match (pa:ParticipantActivity)-[:PARTICIPANT_ACTIVITY|OBJ_TO_MIGRATED*?..]-(cp)
return pa, null as pv }
call {
with pa
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa)
where q.state = $dashState
return q
union
with pa
MATCH (q:Query)-[:HAS_QUERY]-(pqn:ParticipantQuestion)- [:PARTICIPANT_QUESTION]-(:ParticipantTable)-[:PARTICIPANT_TABLE]-(pqnr:ParticipantQuestionnaire)-[:PARTICIPANT_QUESTIONNAIRE]-(pa)
where q.state = $dashState and (q.delpending is null or q.delpending = false)
return q }
return q, pa, pv, null as doc
union
with cp, p
call {
with cp, p
match (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:Visit|VisitV2)-[:BINDER_FOR]-(vb:Binder)-[:INCLUDES]-(doc:Document)-[:INCLUDES]-(pb:Binder)-[:BINDER_FOR]-(p)
MATCH (q:Query)-[:HAS_QUERY]-(doc)
where q.state = $dashState
return doc, pv, q }
return q, null as pa, pv, doc }
MATCH (task:Task)-[:WITH_CONTEXT]-(q)
RETURN q.queryType AS QueryType, count(q) AS CountPerType
ORDER BY CountPerType DESC
Expected: Fewer rows reach the UNION dedup and the Task expansion, roughly in proportion to the share of Query nodes that are not in $dashState. If most queries are closed or resolved, the saving could be large. If most share the requested state, there may be little change. The statement ran only once in 7 days, so the total-time saving is small either way. · Evidence: PROFILE the old and new versions with the same parameters and compare db hits and rows at the Union / Distinct operators and at the Task Expand. Also confirm that both versions return the same QueryType counts.
index Make sure the Organization and Study anchors use a tildaid lookup
The issueThe whole traversal starts from `MATCH (org:Organization {tildaid:$dashOrgId})-[:STUDY_TO_ORG]-(st:Study {tildaid:$dashStudyId})`. If neither label has an index or constraint on tildaid, the planner has to scan every Organization or Study node and filter on the property before any expansion starts.
The fixCheck with SHOW INDEXES / SHOW CONSTRAINTS. If tildaid is the business key, a uniqueness constraint on each label is the right fix: it also creates the backing range index, so both anchors become NodeUniqueIndexSeek. Apply the same DDL to Study with `CREATE CONSTRAINT study_tildaid IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE`. If the constraint already exists, skip this suggestion.
Add this index
CREATE CONSTRAINT organization_tildaid IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE
Expected: If the index is missing, the anchor step goes from a label scan to a single index seek, saving roughly in proportion to the Organization and Study node counts. If it already exists, nothing changes. · Evidence: An EXPLAIN plan that shows NodeByLabelScan for Organization or Study today and NodeUniqueIndexSeek or NodeIndexSeek after the change. Also check that the existing tildaid values are unique before adding the constraint.
Status: proposed · all suggestions
No rollups for this shape in the window.