Xray
hourly rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 48 hours▾
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:41:21.
› →|
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 94FF1D22 · last 48h

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

index Index Organization.alias (used three times per call)
The issue`Organization` is looked up by `alias` in three places: `OPTIONAL MATCH (targetOrg:Organization{delpending:false, alias:$orgAlias})`, and `(:Organization{alias:$tildaScaleAlias})` inside both the SUPPORTS_ORG EXISTS and the `delegated` EXISTS. If no index covers alias, each of these is a NodeByLabelScan plus a property filter over every Organization. The two EXISTS subqueries run once for each driving row, so the scans repeat. That fits the gap between the 354 ms average and the 1.8 s p95.
The fixAdd a lookup on Organization.alias so every alias predicate becomes a NodeIndexSeek. If alias is meant to be unique, use a uniqueness constraint instead (`CREATE CONSTRAINT organization_alias_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.alias IS UNIQUE`). It creates the backing index and also guarantees one row per alias. If SHOW INDEXES already lists an index on this property, skip this item.
Add this index
CREATE INDEX organization_alias IF NOT EXISTS FOR (o:Organization) ON (o.alias)
Expected: Each alias lookup should become an index seek costing a few DB hits instead of a scan of all Organizations. The biggest gain should come where the EXISTS subqueries currently start from a scanned Organization, which could cut the p95 a lot. This depends on how many Organization nodes there are and what the plan does today. · Evidence: Run SHOW INDEXES to check for an existing index on Organization(alias). Then PROFILE the query with a non-member user (so the `delegated` branch runs) before and after the change. Look for NodeIndexSeek on Organization(alias) in place of NodeByLabelScan, and compare total DB hits and the 7-day p95.
index Index User.email for currentUser and supportUser anchors
The issueThe whole query is anchored on `MATCH (currentUser:User{deltimestamp:?,deltimestamp:?,email:$email})`, and `OPTIONAL MATCH (supportUser:User{email:$supportUserEmail})` does a second lookup by email. Without an email index, both are label scans over all User nodes with property filters. Every other clause depends on these anchors.
The fixCreate a range index on User.email so both lookups become NodeIndexSeek. A single-property index serves both: the deltimestamp predicate is checked cheaply on the one or few matching rows, and supportUser has no deltimestamp predicate, so a composite (email, deltimestamp) index would not cover it. Also check the normalised `deltimestamp:?,deltimestamp:?` duplicate in the source. If the two literals ever differ, the MATCH can never return a row.
Add this index
CREATE INDEX user_email IF NOT EXISTS FOR (u:User) ON (u.email)
Expected: Each User lookup should drop from a cost proportional to the User count to a handful of DB hits. That could save tens to hundreds of ms per call, depending on how many User nodes there are. · Evidence: Run SHOW INDEXES for User(email). Compare PROFILE output before and after: look for NodeIndexSeek on User(email) for both currentUser and supportUser, and fewer rows and DB hits on the first operator.
rewrite Look up the TildaScale org once and skip the delegated check when targetOrg is null
The issueBoth EXISTS subqueries contain an inline anonymous pattern `(:Organization{alias:$tildaScaleAlias})`, so that org is found again inside every subquery evaluation. In the `delegated` CASE, the long 6-hop pattern (FOR_USER → STUDY_TO_DELEGATED_ACTOR → STUDY_TO_ORG plus APPOINTED_AS/DELEGATED_FROM, then apoc.text.split on dr.tags) is attempted whenever orgActor is NULL. That includes rows where targetOrg itself is NULL and the pattern can never match. The planner may expand outward from currentUser through all of the user's Actors and Studies before applying the targetOrg and scaleOrg filters.
The fixLook up the TildaScale org once with an early `OPTIONAL MATCH (scaleOrg:Organization{alias:$tildaScaleAlias})` and refer to the bound `scaleOrg` inside both EXISTS blocks. A bound node lets the planner anchor or prune from it instead of re-matching by label and property. Also guard the delegated CASE with `targetOrg IS NOT NULL`. A null targetOrg already makes the EXISTS false, so the result is the same, but the subquery is skipped entirely. This assumes alias is unique. If several orgs shared the TildaScale alias, the new OPTIONAL MATCH would multiply rows.
Proposed rewrite
MATCH (currentUser:User{deltimestamp:?,deltimestamp:?,email:$email}) WHERE (currentUser.isDummy IS NULL OR currentUser.isDummy <> true)
OPTIONAL MATCH (supportUser:User{email:$supportUserEmail})
OPTIONAL MATCH (targetOrg:Organization{delpending:false, alias:$orgAlias})
OPTIONAL MATCH (scaleOrg:Organization{alias:$tildaScaleAlias})
// A deactivated (Inactive) org actor is not a membership (ENG-?).
OPTIONAL MATCH (currentUser)-[:FOR_USER]-(orgActor:Actor{delpending:false})-[:ORGANIZATION_TO_ACTOR]-(targetOrg) WHERE coalesce(orgActor.state, ?) <> ?
OPTIONAL MATCH (currentUser)-[supports:SUPPORTS_ORG]-(targetOrg) WHERE EXISTS { MATCH (currentUser)<-[:FOR_USER]-(scaleActor:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(scaleOrg), (scaleActor)-[:APPOINTED_AS]->(scaleRole:Role) WHERE coalesce(scaleActor.state, ?) <> ? AND scaleRole.name <> ? }
WITH *, false AS repOf
// Evaluated only for non-members with a resolved target org; members are decided by orgActor alone.
WITH *, CASE WHEN orgActor IS NULL AND targetOrg IS NOT 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]-(scaleOrg) 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 repOf 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 repOf OR supports is not null THEN targetOrg ELSE null END as org, currentUser as tokenUser
Expected: This should mostly help the slow tail: calls for non-members, or where the org alias doesn't resolve, should no longer pay for the multi-hop expansion. Expect fewer DB hits in the SemiApply branches. The size of the gain depends on how many Actors and Studies the user has. · Evidence: Check that the TildaScale alias resolves to exactly one Organization. PROFILE both versions with (a) a member user, (b) a non-member with a delegated study, and (c) an unknown orgAlias, and confirm the returned user/org/tokenUser values match in all three. Compare DB hits on the SemiApply/RollUpApply operators and watch the p95 after deploy.

Status: proposed · all suggestions

No rollups for this shape in the window.