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:12.
› →|
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 8EC5507C · last 1h

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

rewrite Reuse the bound cfg node instead of re-matching cfg2 by tildaid
The issueThe first `WITH cfgId, collect(DISTINCT ...) AS allItems` drops `cfg`. So the second `OPTIONAL MATCH (cfg2:BranchConfig {tildaid: cfgId})-[:BRANCH_CG_ANY_NEXT*..]->...` has to look up the BranchConfig node again for every cfgId. If BranchConfig.tildaid has no index or constraint, that second lookup is a full NodeByLabelScan for each id in $cfgIds. Even with an index, it is a wasted second seek.
The fixKeep `cfg` in the first WITH's grouping key and start the ANY-branch expansion from it. Nothing else changes. One caveat: if several BranchConfig nodes could share a tildaid, grouping by cfg as well as cfgId would split their rows. That can't happen if tildaid is unique (see the constraint suggestion).
Proposed rewrite
UNWIND $cfgIds AS cfgId 
MATCH (cfg:BranchConfig {tildaid: cfgId}) 
OPTIONAL MATCH (cfg)-[:BRANCH_CG_ALL_NEXT*?..]->(gAll:BranchConditionGroup)-[:CG_TO_CONDITION*?..]->(allCond:BranchCondition) 
WITH cfgId, cfg, collect(DISTINCT {kind:?, g:gAll, c:allCond}) AS allItems 
OPTIONAL MATCH (cfg)-[:BRANCH_CG_ANY_NEXT*?..]->(gAny:BranchConditionGroup)-[:CG_TO_CONDITION*?..]->(anyCond:BranchCondition) 
WITH cfgId, allItems, collect(DISTINCT {kind:?, g:gAny, c:anyCond}) AS anyItems 
WITH cfgId, allItems + anyItems AS items 
UNWIND items AS item 
WITH cfgId, item.kind AS kind, item.g AS g, item.c AS c 
WHERE c IS NOT NULL 
OPTIONAL MATCH (ref:GraphNodeRef {tildaid:c.refid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE|GRAPH_NODE_REF_TO_QUESTION {protocolid:$protocolId}]->(q:QuestionV2) 
RETURN cfgId AS cfgId, kind AS groupKind, coalesce(g.tildaid, ?) AS groupId, coalesce(g.matchall, false) AS groupMatchAll, coalesce(c.tildaid, ?) AS conditionId, coalesce(c.refid, ?) AS refId, coalesce(c.variableid, ?) AS variableId, coalesce(c.operator, ?) AS operator, coalesce(c.choices, ?) AS choices, coalesce(c.choicevaluesjson, ?) AS choiceValuesJson, coalesce(c.matchall, false) AS conditionMatchAll, coalesce(head([(:GraphNodeRef {tildaid: c.visitrefid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE {protocolid: $protocolId}]->(v:VisitV2) | v.tildaid]), c.visitid, ?) AS visitId, coalesce(head([(:GraphNodeRef {tildaid: c.activityrefid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE {protocolid: $protocolId}]->(a:ActivityV2) | a.tildaid]), c.activityid, ?) AS activityId, coalesce(head([(:GraphNodeRef {tildaid: c.questionnairerefid})-[:GRAPH_NODE_REF_TO_GRAPH_NODE {protocolid: $protocolId}]->(qnr:QuestionnaireV2) | qnr.tildaid]), c.questionnaireid, ?) AS questionnaireId, coalesce(c.tableid, ?) AS tableId, coalesce(q.tildaid, ?) AS questionId
Expected: Removes one BranchConfig lookup per cfgId. That is a small gain if tildaid is indexed. If it isn't, it removes a label scan per id, which could explain part of the 440 ms p95. The volume is only 4 runs a week, so the absolute saving is modest. · Evidence: PROFILE before and after: the second NodeIndexSeek or NodeByLabelScan on BranchConfig should disappear, and db hits should drop. Run the old and new versions on the same $cfgIds and diff the results to confirm the rows are identical.
index Unique constraint on GraphNodeRef.tildaid (used in 4 lookups per condition row)
The issueEvery condition row runs four GraphNodeRef lookups by tildaid. One is `OPTIONAL MATCH (ref:GraphNodeRef {tildaid:c.refid})`. The other three are the pattern comprehensions on c.visitrefid, c.activityrefid and c.questionnairerefid. Without an index or constraint on GraphNodeRef(tildaid), each lookup is a NodeByLabelScan plus a filter, so the cost grows as conditions × 4 × the number of GraphNodeRef nodes. This is the most likely source of the 440 ms p95 outlier.
The fixIf tildaid is the node key (the naming suggests it is), add a uniqueness constraint. That creates a backing range index, and the planner uses it for every `{tildaid: ...}` lookup in this query. First check SHOW CONSTRAINTS / SHOW INDEXES. If an index already exists, skip this.
Add this index
CREATE CONSTRAINT graphnoderef_tildaid_unique IF NOT EXISTS FOR (n:GraphNodeRef) REQUIRE n.tildaid IS UNIQUE
Expected: If the constraint is missing today, each GraphNodeRef lookup should become a NodeUniqueIndexSeek, and the p95 for config sets with many conditions could fall substantially. If an index already exists, nothing changes. · Evidence: Run SHOW INDEXES WHERE 'GraphNodeRef' IN labelsOrTypes first. After creating the constraint, PROFILE should show NodeUniqueIndexSeek(GraphNodeRef.tildaid) instead of NodeByLabelScan. Then compare p95 over the next week.
index Unique constraint on BranchConfig.tildaid for the UNWIND entry point
The issue`UNWIND $cfgIds AS cfgId MATCH (cfg:BranchConfig {tildaid: cfgId})` (and cfg2 in the original) starts the whole query with one BranchConfig lookup per id. Without a supporting index, each id costs a full label scan.
The fixAdd a uniqueness constraint on BranchConfig.tildaid. This guarantees the per-id lookup is a seek, and it also makes the cfg-reuse rewrite safe, because each cfgId then matches exactly one node. Skip it if SHOW CONSTRAINTS already lists one.
Add this index
CREATE CONSTRAINT branchconfig_tildaid_unique IF NOT EXISTS FOR (n:BranchConfig) REQUIRE n.tildaid IS UNIQUE
Expected: If no index exists, each per-id lookup goes from a label scan to an O(log n) seek. The gain grows with the size of $cfgIds and the number of BranchConfig nodes. If an index already exists, nothing changes. · Evidence: SHOW INDEXES for BranchConfig. PROFILE should show NodeUniqueIndexSeek on BranchConfig(tildaid) as the leaf operator after the Unwind.

Status: proposed · all suggestions

No rollups for this shape in the window.