Optimizer suggestions — proposals from claude-opus-5-5, 3d ago; suggests only, a human applies
rewrite Count all four buckets in one traversal using conditional counts
The issueThe statement walks the same (org)-[:ORGANIZATION_TO_FORM]->(form:FormV2 {type:$formType}) neighbourhood four times: one MATCH and three OPTIONAL MATCHes. Each pass re-reads every related FormV2 node and re-checks scopeid, activityType and state. That's about 4× the relationship expansions and property reads needed, so cost grows with the organisation's form count times four. The 485 ms p95 next to a 238 ms average suggests a large org.
The fixExpand once and apply the shared filters (type, scopeid, activityType) once. Then compute the four counts with count(CASE WHEN ... THEN 1 END), grouped by org. Comparisons against a null state give null, so those forms are not counted, the same as in the original filters. The original first MATCH is not optional, so it returns no row when allCount is 0. `WHERE allCount > 0` after the aggregation keeps that behaviour. The four state placeholders stay in their original order: the excluded state first, then published, draft and archived.
Proposed rewrite
MATCH (org:Organization {tildaid:$orgId})-[:ORGANIZATION_TO_FORM]->(form:FormV2 {type:$formType})
WHERE form.scopeid = ? AND (form.activityType IS NULL OR NOT form.activityType IN $excludeActivityTypes)
WITH org, form.state AS state
WITH org,
count(CASE WHEN NOT state = ? THEN 1 END) AS allCount,
count(CASE WHEN state = ? THEN 1 END) AS publishedCount,
count(CASE WHEN state = ? THEN 1 END) AS draftCount,
count(CASE WHEN state = ? THEN 1 END) AS archivedCount
WHERE allCount > 0
RETURN allCount, publishedCount, draftCount, archivedCount
Expected: About 4× fewer relationship expansions and FormV2 property reads, so latency could drop by roughly 50–75% for orgs with many forms. But this ran only once in 7 days (238 ms total), so the absolute saving is small. Treat it as a cleanup, not a priority. · Evidence: Run PROFILE on both versions against the same org with a large form count. Compare total db hits and rows on the Expand(All) operators: there should be one expand instead of four. Also check that the four counts match exactly for several orgs, including one whose forms are all in the excluded state, which should return no row in both versions.
index Make sure the Organization.tildaid anchor uses a unique lookup
The issueEverything depends on finding the starting node with (org:Organization {tildaid:$orgId}). tildaid is not in the listed properties for this shape, so I can't confirm it is indexed. Without an index or constraint, the planner does a NodeByLabelScan over every Organization and filters by tildaid on each call.
The fixtildaid looks like an external ID, so back it with a uniqueness constraint. That also creates the index used for the lookup. If duplicates already exist the constraint will fail to create. In that case, use CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid) instead.
Add this index
CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE
Expected: If the anchor is currently a label scan, the start becomes a single NodeUniqueIndexSeek, which could save tens of milliseconds or more depending on how many Organization nodes there are. If a constraint already exists, nothing changes. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES WHERE 'Organization' IN labelsOrTypes. Then EXPLAIN the statement: the first operator should be NodeUniqueIndexSeek (or NodeIndexSeek), not NodeByLabelScan.
Status: proposed · all suggestions
No rollups for this shape in the window.