Query
match (n:Smo{orgId:$orgId})-[:PLAN_DEFINITION_TO_SMO]-(pd:DslPlanDefinition{studyId:$studyId,delpending:false})
return pd
Optimizer suggestions — proposals from claude-opus-5-5, 3d ago; suggests only, a human applies
index Index DslPlanDefinition on (studyId, delpending)
The issueThe node pattern `(pd:DslPlanDefinition{studyId:$studyId,delpending:false})` filters on two non-key properties. Without an index on them, the planner either label-scans every DslPlanDefinition or starts from Smo and expands. That would fit the gap between the 536 ms average and the 4250 ms p95: studies or orgs with many nodes cost far more per call.
The fixAdd a composite range index so the planner can seek straight to the plan definitions for one study that are not pending deletion, then expand the few relationships to Smo and filter on orgId. Both predicates are equality checks, so the composite index fully covers the seek.
Add this index
CREATE INDEX dsl_plan_definition_study_delpending IF NOT EXISTS FOR (pd:DslPlanDefinition) ON (pd.studyId, pd.delpending)
Expected: If the plan currently starts with NodeByLabelScan on DslPlanDefinition, this should become a NodeIndexSeek. p95 could drop from seconds to tens of milliseconds, depending on how many plan definitions a study has. The total benefit is modest because the query ran only 5 times in 7 days, but each slow call costs seconds. · Evidence: Run PROFILE before and after with a real $studyId/$orgId from a slow call. Confirm that the leaf operator changes from NodeByLabelScan to NodeIndexSeek on DslPlanDefinition(studyId, delpending), check that db hits fall, and compare p95 for this shape after deploy.
index Index Smo on orgId
The issue`(n:Smo{orgId:$orgId})` filters on orgId. If no index exists, any plan that anchors on Smo, or that has to check orgId cheaply after a join, depends on a label scan. When the studyId side is not very selective, the planner may pick Smo as its starting point.
The fixAdd a range index on Smo.orgId. The planner then has a cheap anchor on both ends and can choose the more selective side, or use a hash join between two index seeks, instead of scanning.
Add this index
CREATE INDEX smo_org_id IF NOT EXISTS FOR (n:Smo) ON (n.orgId)
Expected: This should remove any NodeByLabelScan on Smo and give the planner a selective alternative. The gain is likely smaller than from the DslPlanDefinition index if studyId is the more selective filter. · Evidence: Run SHOW INDEXES to confirm no index on Smo(orgId) already exists. Then PROFILE the query and check whether Smo is reached by NodeIndexSeek or by label scan, and compare estimated against actual rows on each side.
rewrite Give the relationship a direction and anchor on the plan definition
The issue`-[:PLAN_DEFINITION_TO_SMO]-` has no direction, so the planner has to check both directions of every relationship on each node it expands from. The pattern also leaves the choice of start side entirely to the planner's estimates, and with skewed data those estimates can be wrong.
The fixThe relationship's name suggests it is stored as (DslPlanDefinition)->(Smo). Give the expansion that direction and start from pd, the side covered by the composite index, so each plan definition only expands its outgoing relationships to Smo. Check the real direction first, e.g. MATCH ()-[r:PLAN_DEFINITION_TO_SMO]->() RETURN labels(startNode(r)) LIMIT 1. If any relationships are stored the other way round, keep the undirected form. Results are unchanged, including duplicate pd rows when several matching Smo nodes are linked.
Proposed rewrite — current query in red −, proposed in green +
- match (n:Smo{orgId:$orgId})-[:PLAN_DEFINITION_TO_SMO]-(pd:DslPlanDefinition{studyId:$studyId,delpending:false}) return pd
+ MATCH (pd:DslPlanDefinition {studyId: $studyId, delpending: false})-[:PLAN_DEFINITION_TO_SMO]->(n:Smo {orgId: $orgId}) RETURN pd
Expected: The gain is small to moderate: it avoids checking relationships in both directions and makes the plan more predictable. It mainly helps once the indexes above exist. · Evidence: Check that the result row count matches the original query for a few sample parameters. In PROFILE, the Expand(All)/Expand(Into) operator should show fewer db hits.
Status: proposed · all suggestions
No rollups for this shape in the window.