Optimizer suggestions — proposals from claude-opus-5-5, 2d ago; suggests only, a human applies
index Index User.email for the supportUser lookup
The issue`OPTIONAL MATCH (supportUser:User{email:$supportUserEmail})` is a separate starting point that is not connected to currentUser. If there is no index on User.email, the planner has to scan every User node (NodeByLabelScan + Filter) on each call, even though supportUser is only used when a SUPPORTS_ORG edge exists. A full label scan like this is a likely cause of the 1.7 s p95, which is far above the 175 ms average.
The fixAdd a lookup index on User(email). If email is unique per user, a uniqueness constraint is better: it creates the backing index and also guarantees that the OPTIONAL MATCH returns at most one row, so it can't multiply the result rows. Check SHOW INDEXES first, because an index or constraint may already exist.
Add this index
CREATE INDEX user_email IF NOT EXISTS FOR (u:User) ON (u.email)
Expected: If a scan is happening today, this lookup should become a NodeIndexSeek costing a few DB hits instead of one per User node, and the slow tail should shrink a lot. The statement only ran 6 times in 7 days (about 1 s total), so the overall time saved is small. The main benefit is latency for the person making the request. · Evidence: Run SHOW INDEXES WHERE 'User' IN labelsOrTypes. Run PROFILE with real parameters and confirm the supportUser branch changes from NodeByLabelScan to NodeIndexSeek on user_email. After deploying, compare p95 for shape B438C318.
index Index Organization.alias (used twice: targetOrg and the TildaScale org)
The issue`OPTIONAL MATCH (targetOrg:Organization{delpending:false, alias:$orgAlias})` and the `(:Organization{alias:$tildaScaleAlias})` pattern inside the EXISTS subquery both look up Organization by alias. Without an index on alias, the targetOrg lookup becomes a label scan plus filter. Because the planner can't seek on alias, it may also start the EXISTS subquery from the wrong end.
The fixCreate an index on Organization(alias). If aliases are unique, use a uniqueness constraint instead. The delpending:false check is cheap to apply after the seek, so a single-property index is enough. With the index, the planner can seek both organizations directly and join them to the currentUser-anchored expansions.
Add this index
CREATE INDEX organization_alias IF NOT EXISTS FOR (o:Organization) ON (o.alias)
Expected: Both alias lookups should become index seeks. The EXISTS branch, which only runs for non-members, should also do fewer DB hits because it can be anchored at both ends. The size of the gain depends on how many Organization nodes there are and whether an index already exists. · Evidence: Run SHOW INDEXES for the Organization label. Run PROFILE with a non-member user, so the delegated branch runs, and confirm NodeIndexSeek on organization_alias for both the targetOrg and TildaScale nodes. Compare total DB hits before and after.
rewrite Look up supportUser only when a SUPPORTS_ORG edge exists, and drop the dead repOf flag
The issueThe supportUser lookup runs on every call, but its value is only used in the `WHEN supports is not null THEN coalesce(supportUser, currentUser)` branch. `WITH *, false AS repOf` is a constant that is never true, so the `WHEN repOf` checks never do anything and only make the query harder to read. Also, the map `{deltimestamp:?,deltimestamp:?}` repeats the same key, so only the second value applies.
The fixMove the supportUser lookup after the SUPPORTS_ORG match. Put it inside a CALL subquery that returns a row only when supports is not null, so most calls never look up User by email. Remove repOf and its CASE branches. The output is unchanged: user, org and tokenUser are computed the same way. Also check the duplicated deltimestamp key with the application owners.
Proposed rewrite
MATCH (currentUser:User{deltimestamp:?,tildaid:$actAsUserId})
WHERE (currentUser.isDummy IS NULL OR currentUser.isDummy <> true)
OPTIONAL MATCH (targetOrg:Organization{delpending:false, alias:$orgAlias})
OPTIONAL MATCH (currentUser)-[:FOR_USER]-(orgActor:Actor{delpending:false})-[:ORGANIZATION_TO_ACTOR]-(targetOrg)
WHERE coalesce(orgActor.state, ?) <> ?
OPTIONAL MATCH (currentUser)-[supports:SUPPORTS_ORG]-(targetOrg)
CALL {
WITH supports
WITH supports
WHERE supports IS NOT NULL
OPTIONAL MATCH (su:User{email:$supportUserEmail})
RETURN su AS supportUser
UNION
WITH supports
WITH supports
WHERE supports IS NULL
RETURN null AS supportUser }
WITH *, CASE WHEN orgActor IS NULL THEN EXISTS {
MATCH (currentUser)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(targetOrg), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias})
WHERE st.srcorgid = targetOrg.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) } ELSE false END as delegated
RETURN CASE WHEN orgActor is not null THEN currentUser WHEN delegated THEN currentUser WHEN supports is not null THEN coalesce(supportUser, currentUser) ELSE currentUser END as user, CASE WHEN orgActor is not null OR delegated OR supports is not null THEN targetOrg ELSE null END as org, currentUser as tokenUser
Expected: For callers without a SUPPORTS_ORG edge, which is probably most of them, the email lookup is skipped entirely. This matters most if User.email has no index. If it does have one, the gain is small, mainly a simpler plan and clearer code. · Evidence: Run PROFILE on the old and new versions with the same parameters, once with and once without a SUPPORTS_ORG edge. Confirm the email lookup operator shows 0 rows and 0 DB hits when supports is null. Diff the returned user, org and tokenUser across representative test cases to confirm the results are the same.
Status: proposed · all suggestions
No rollups for this shape in the window.