Xray
1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 1 hour▾
Ends now. Anything up to this cluster's 30-day retention.
Start and end times
Cancel
Interpreted in UTC (change it at the right of this bar). Earliest available: 2026-10-19 17:17:37.
› →|
updated 1d ago
Sample data. This database was generated by scripts/seed_dev_db.py — every service, deploy, person and figure on this page is synthetic. Names are sample.*@seed.invalid and image tags are prefixed SEED-. Nothing here happened.
← query trends

Query shape trend

plan 6343E8CE · last 1h

Optimizer suggestions — proposals from claude-opus-5-5, 3d ago; suggests only, a human applies

index Make sure both anchors (Study.tildaid, User.tildaid) can be looked up through a constraint
The issueThe pattern has two anchors: `(o:Study{tildaid:$scopeId})` and `(u:User{tildaid:$userId})`. The listed properties don't include tildaid, so I can't tell whether either one has a lookup index. If one is missing, the planner has to start from a NodeByLabelScan with a Filter on Study or User. That fits the gap here: an average of 545 ms but a p95 of 3,500 ms. A plain key lookup on both ends should finish in a few ms.
The fixRun SHOW CONSTRAINTS and SHOW INDEXES. If Study.tildaid or User.tildaid has no uniqueness constraint (or at least a range index), add one. A uniqueness constraint also tells the planner that each side matches at most one row. With that, it can start from the more selective end, usually the single User, then expand FOR_USER backwards to a few Actors and join to the Study, instead of walking every STUDY_TO_ACTOR edge of a large study. Run the second statement only if User needs it too: CREATE CONSTRAINT user_tildaid_unique IF NOT EXISTS FOR (u:User) REQUIRE u.tildaid IS UNIQUE
Add this index
CREATE CONSTRAINT study_tildaid_unique IF NOT EXISTS FOR (s:Study) REQUIRE s.tildaid IS UNIQUE
Expected: If either lookup is missing today, I'd expect the slow executions to drop from seconds to the low tens of ms. If both constraints already exist, nothing changes and you can skip this. · Evidence: Check SHOW CONSTRAINTS/SHOW INDEXES for Study.tildaid and User.tildaid. Then compare PROFILE before and after: you want NodeUniqueIndexSeek (or NodeIndexSeek) on both Study and User, and no NodeByLabelScan. The p95 should also go down over the next 7 days.
rewrite Replace the Role MATCH with an EXISTS check, so DISTINCT isn't needed to remove duplicates
The issue`MATCH (a)-[:APPOINTED_AS]->(r:Role)` only checks that the actor has some role, since r is never returned. But it produces one row per role per actor. `RETURN distinct a` then has to collapse those extra rows in memory, and the a.state / u.status filters can end up applied after the expansion.
The fixMove the role check into an EXISTS subquery. The planner turns that into a semi-apply that stops at the first APPOINTED_AS edge it finds. Put all the filters in one WHERE clause on the main pattern so they run before the existence check. I kept DISTINCT so the result stays the same if there are parallel STUDY_TO_ACTOR or FOR_USER edges. I also dropped the unused `scope` variable.
Proposed rewrite
MATCH (o:Study {tildaid:$scopeId})-[:STUDY_TO_ACTOR]->(a:Actor)-[:FOR_USER]->(u:User {tildaid:$userId})
WHERE u.status <> ? AND a.state <> ? AND EXISTS { (a)-[:APPOINTED_AS]->(:Role) }
RETURN DISTINCT a
Expected: Fewer intermediate rows and less DISTINCT work. The gain grows with how many roles each actor has, roughly proportional to that number. It may be small if actors usually have only one role. · Evidence: Compare PROFILE of the old and new query with the same parameters. Look for lower rows/db hits after the Role expansion, and check that a SemiApply/Exists operator replaces Expand(All)+Distinct. Both queries should return the same result set.
rewrite Start from the User and join to the Study, so large studies don't fan out
The issueEven with both anchors indexed, the planner may expand `(o)-[:STUDY_TO_ACTOR]->(a)` from the Study first. For a study with many actors, that walks every actor before filtering on FOR_USER. That would explain why only some executions take seconds (p95 3,500 ms) while the average stays at 545 ms. A user normally has only a few Actor nodes, so starting from the User is cheaper.
The fixAnchor the query on the User and expand backwards over FOR_USER. Then check the link to the Study with a pattern on the already-bound o and a. That way the cost depends on how many actors the user has, not how many the study has.
Proposed rewrite
MATCH (u:User {tildaid:$userId})<-[:FOR_USER]-(a:Actor)
WHERE u.status <> ? AND a.state <> ?
MATCH (o:Study {tildaid:$scopeId})-[:STUDY_TO_ACTOR]->(a)
WHERE EXISTS { (a)-[:APPOINTED_AS]->(:Role) }
RETURN DISTINCT a
Expected: Stable latency close to the fast-path average for large studies. The heavy p95 executions should improve the most. Only worth doing if PROFILE shows the Study-side expansion is the hot operator. · Evidence: Run PROFILE on a slow parameter pair (a big study) and look for a large row count on the Expand from Study. After the rewrite, the plan should start at a User index seek, and Expand(Into) should handle the Study–Actor check. Compare p95 over a week.

Status: proposed · all suggestions

No rollups for this shape in the window.