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.