Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 30 days▾
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 14:24:40.
› →|
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 746FCA47 · last 30d

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

index Index User.email so the anchor lookup and supportUser lookup can seek
The issue`MATCH (currentUser:User {deltimestamp:?}) WHERE ... currentUser.email = $ident` and `OPTIONAL MATCH (supportUser:User {email:$supportEmail})` both filter User only by property. If User(email) has no index, each one becomes a NodeByLabelScan over every User followed by a Filter. That happens twice per call, and every later expansion waits on it. A p95 of 1950 ms for what should be a single-user check fits a full label scan.
The fixCreate a range index on User(email). Both the anchor and the supportUser OPTIONAL MATCH then become NodeIndexSeek. The deltimestamp and isDummy checks stay as a cheap filter on the one or few rows that come back. If User.email is meant to be unique, a uniqueness constraint (CREATE CONSTRAINT user_email_unique IF NOT EXISTS FOR (u:User) REQUIRE u.email IS UNIQUE) does the same job and also stops supportUser from adding duplicate rows. Only do that if the data really is unique, including soft-deleted users with deltimestamp set.
Add this index
CREATE INDEX user_email IF NOT EXISTS FOR (u:User) ON (u.email)
Expected: The two User lookups could drop from a scan proportional to the User count to a seek of roughly 1 ms. Call volume is low (4 calls in 7 days), so the total saving is small, but p95 latency for the caller could fall sharply if the scan is the main cost. · Evidence: Run EXPLAIN/PROFILE before and after. Before should show NodeByLabelScan(User) feeding Filter(email = $ident). After should show NodeIndexSeek on User(email), with fewer db hits on the first operator. Also run SHOW INDEXES to check that no email index exists yet.
index Index Organization.alias for targetOrg and the Tilda scale org lookups
The issue`OPTIONAL MATCH (targetOrg:Organization {delpending:false, alias:$org})` filters by alias, and both EXISTS subqueries match `(:Organization{alias:$tildaScaleAlias})`. Without an index on Organization(alias), targetOrg is a label scan. Inside the EXISTS the planner may also scan Organization to anchor from the alias side instead of expanding from currentUser.
The fixCreate a range index on Organization(alias). targetOrg becomes an index seek, and the planner can seek the scale org and join it to the short expansion from currentUser. If alias is a business key, use a uniqueness constraint instead. delpending stays as a residual filter.
Add this index
CREATE INDEX organization_alias IF NOT EXISTS FOR (o:Organization) ON (o.alias)
Expected: targetOrg resolution becomes a seek instead of a scan. The EXISTS subqueries may get cheaper if the planner now anchors on the seek. The gain depends on how many Organization nodes there are. · Evidence: PROFILE should show NodeIndexSeek on Organization(alias) for targetOrg. Inside the EXISTS subqueries there should be no NodeByLabelScan(Organization). Compare db hits and p95 after the deploy.
rewrite Replace the orgActor and supports OPTIONAL MATCHes with EXISTS booleans
The issue`OPTIONAL MATCH (currentUser)-[:FOR_USER]-(orgActor:Actor...)-[:ORGANIZATION_TO_ACTOR]-(targetOrg)` and `OPTIONAL MATCH (currentUser)-[supports:SUPPORTS_ORG]-(targetOrg)` keep one row per matching actor or relationship. Yet orgActor and supports are only ever checked for IS NULL / IS NOT NULL. A user with several actors in the org, or several SUPPORTS_ORG relationships, multiplies the rows. That means repeated delegated-EXISTS evaluation and duplicate output rows. Both patterns are also undirected, so each relationship is checked from both sides.
The fixTurn both into existence checks that short-circuit at the first match and always leave one row. Also skip the costly delegated subquery when targetOrg is null. Semantics change: when several orgActors exist, the original returned duplicate identical rows, and this version returns one. Check that callers don't depend on the row count. If the relationship directions are known (for example FOR_USER points Actor->User, as the EXISTS clauses suggest), add arrows to the orgActor pattern as well.
Proposed rewrite
MATCH (currentUser:User {deltimestamp:?}) WHERE (currentUser.isDummy IS NULL OR currentUser.isDummy <> true) AND currentUser.email = $ident
OPTIONAL MATCH (supportUser:User {email:$supportEmail})
OPTIONAL MATCH (targetOrg:Organization {delpending:false, alias:$org})
WITH currentUser, supportUser, targetOrg, false AS repOf,
  (targetOrg IS NOT NULL AND EXISTS { MATCH (currentUser)-[:FOR_USER]-(orgActor:Actor{delpending:false})-[:ORGANIZATION_TO_ACTOR]-(targetOrg) WHERE coalesce(orgActor.state, ?) <> ? }) AS hasOrgActor,
  (targetOrg IS NOT NULL AND EXISTS { MATCH (currentUser)-[:SUPPORTS_ORG]-(targetOrg) } AND EXISTS { MATCH (currentUser)<-[:FOR_USER]-(scaleActor:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}), (scaleActor)-[:APPOINTED_AS]->(scaleRole:Role) WHERE coalesce(scaleActor.state, ?) <> ? AND scaleRole.name <> ? }) AS hasSupports
WITH *, CASE WHEN NOT hasOrgActor 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]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = targetOrg.tildaid AND da.state <> ? AND sa.state <> ? AND $delegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) } ELSE false END AS delegated
RETURN CASE WHEN hasOrgActor THEN currentUser WHEN delegated THEN currentUser WHEN repOf THEN currentUser WHEN hasSupports THEN coalesce(supportUser, currentUser) ELSE currentUser END AS user, CASE WHEN hasOrgActor OR delegated OR repOf OR hasSupports THEN targetOrg ELSE null END AS org, currentUser AS tokenUser
Expected: The output is always 1 row per currentUser/supportUser/targetOrg combination, and each check stops at the first match. That should cut db hits for users with many actors or relationships. The effect is small for users with exactly one match. · Evidence: PROFILE both versions for a user with several Actor nodes in the target org. Compare rows coming out of the OptionalExpand operators and total db hits, and check that the user/org/tokenUser results are the same, apart from the duplicate rows going away.

Status: proposed · all suggestions

No rollups for this shape in the window.