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 22:51:13.
› →|
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 D8455F04 · last 1h

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

rewrite Count all four buckets in a single expansion with conditional counts
The issueThe statement walks (org)-[:ORGANIZATION_TO_FORM]->(form:FormV2 {type:$formType}) four times: once in the MATCH and once in each of the three OPTIONAL MATCHes (published, draft, archived). Each pass re-expands every form on the org and re-checks type/state. Cost grows with the number of forms per org, which may explain why p95 (455 ms) is about 5× the average (90 ms) for orgs with many forms.
The fixExpand once, then compute each bucket with count(CASE WHEN … THEN 1 END). The allCount CASE uses the same activityType/tag/state predicates as the original WHERE. A predicate that evaluates to null is not counted, which matches how WHERE filters out null. The published, draft and archived counts don't use those extra filters, just as the original OPTIONAL MATCHes didn't. The original returns no row when the first MATCH finds nothing, so `WHERE allCount > 0` keeps that behaviour. Drop it if the caller would rather get a row of zeros.
Proposed rewrite
MATCH (org:Organization {tildaid:$orgId})-[:ORGANIZATION_TO_FORM]->(form:FormV2 {type:$formType})
WITH org,
  count(CASE WHEN (form.activityType IS NULL OR NOT form.activityType IN $excludeActivityTypes) AND NOT coalesce(form.tag, ?) IN $systemFormTags AND NOT form.state = ? THEN 1 END) AS allCount,
  count(CASE WHEN form.state = ? THEN 1 END) AS publishedCount,
  count(CASE WHEN form.state = ? THEN 1 END) AS draftCount,
  count(CASE WHEN form.state = ? THEN 1 END) AS archivedCount
WHERE allCount > 0
RETURN allCount, publishedCount, draftCount, archivedCount
Expected: About 4× fewer relationship expansions and property reads per call. That could make the worst-case orgs roughly 2–4× faster. But the shape ran only 3 times in 7 days (270 ms total), so the absolute saving is tiny. Do it for tail latency and readability, not throughput. · Evidence: Run PROFILE on both versions for an org with many forms. Compare total db hits and check that Expand(All) appears once instead of four times. Compare results on several orgs, including one with no matching forms, to confirm the output is the same.
index Make sure Organization.tildaid is backed by a uniqueness constraint
The issueThe whole query is anchored on (org:Organization {tildaid:$orgId}). If tildaid has no index or constraint, the planner runs a NodeByLabelScan plus a Filter over every Organization before it expands, and the expansion is repeated per anchor.
The fixIf SHOW CONSTRAINTS / SHOW INDEXES doesn't already list tildaid on Organization, add a uniqueness constraint. It creates a backing range index and guarantees a single anchor row. If one already exists, nothing needs doing here.
Add this index
CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE
Expected: Only matters if no index exists today. In that case the anchor step drops from a scan that grows with the number of Organization nodes to a single index seek, which is probably most of the 455 ms p95. If the index already exists, there is no change. · Evidence: EXPLAIN before and after: the first operator should change from NodeByLabelScan+Filter to NodeUniqueIndexSeek on Organization(tildaid). Then check p95 for shape D8455F04 after the deploy.

Status: proposed · all suggestions

No rollups for this shape in the window.