Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 3 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 12:54:16.
› →|
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 F0A5B6CF · service workplansvc · read · last 3d

Query
MATCH (cfg:WorkplanCfg)-[:WORKPLAN_CFG_TO_MILESTONE]->(milestone:WorkPlanMilestone)-[:WORKPLAN_MILESTONE_TO_MILESTONE_ITEM]->(item:WorkPlanMilestoneItem) 
WHERE item.itemType = ? AND coalesce(item.cfgItemInvalid, false) = false AND milestone.scoringMode = ? AND item.checkpointPendingGenId > coalesce(item.checkpointLastComputedGenId, ?) AND (coalesce(item.checkpointGenerateRequestGenId, ?) <> item.checkpointPendingGenId OR $nowMs - coalesce(item.checkpointGenerateRequestSentAt, ?) >= ?) AND NOT (coalesce(item.checkpointUnsupportedGenId, ?) = item.checkpointPendingGenId AND coalesce(item.checkpointUnsupportedCount, ?) >= $maxUnsupported) AND coalesce(cfg.state, ?) <> ? 
OPTIONAL MATCH (org:Organization {tildaid: cfg.orgid}) 
RETURN item.tildaid AS cfgMilestoneItemId, cfg.tildaid AS cfgId, milestone.tildaid AS milestoneId, coalesce(item.itemConfig, ?) AS itemConfig, item.checkpointPendingGenId AS pendingGenId, coalesce(org.alias, ?) AS orgAlias, coalesce(item.checkpointPendingBy, ?) AS pendingBy 
LIMIT $
limit

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

index Lookup index on WorkPlanMilestoneItem.itemType to anchor the poller
The issueThe only predicate that can start the plan is `item.itemType = ?`. Every other item filter compares one property of the node with another (`checkpointPendingGenId > coalesce(checkpointLastComputedGenId, ?)`) or wraps the property in coalesce(), and an index can't serve either form. Without an index on itemType, the planner most likely runs a NodeByLabelScan over all WorkPlanMilestoneItem nodes (or over WorkplanCfg/WorkPlanMilestone) and then checks every filter on every row. This statement looks like a poller (about 1,000 runs a day), so it repeats that full scan whether or not any work is pending.
The fixCreate a range index on WorkPlanMilestoneItem(itemType) so the plan starts with NodeIndexSeek on the requested item type. The plan then only expands to milestone and cfg for items of that type. The coalesce and property-to-property filters still run per row, but only over the subset with that item type rather than the whole label.
Add this index
CREATE INDEX workplan_milestone_item_itemtype IF NOT EXISTS FOR (i:WorkPlanMilestoneItem) ON (i.itemType)
Expected: How much this helps depends on how selective itemType is. If the requested type is a small share of items, per-call time could fall from about 8 ms to roughly 1–3 ms, which would save most of the about 56 s of total time per week. If one type covers most items, the gain will be small, and suggestion 3 (the hint) or a dedicated 'pending' flag would be needed. · Evidence: Run PROFILE before and after creating the index. Check that the first operator changes from NodeByLabelScan(item) to NodeIndexSeek(item:WorkPlanMilestoneItem(itemType)) and that total db hits drop. Run `MATCH (i:WorkPlanMilestoneItem) RETURN i.itemType, count(*)` to check selectivity. Compare the shape's p95 over the week after the deploy.
index Unique constraint on Organization.tildaid for the OPTIONAL MATCH lookup
The issue`OPTIONAL MATCH (org:Organization {tildaid: cfg.orgid})` runs for each result row. If Organization.tildaid has no index or constraint, each of those lookups scans the Organization label. It also means more than one org can match a cfg, which would duplicate rows.
The fixIf the schema doesn't already have it, add a uniqueness constraint on Organization.tildaid. The constraint creates a backing index, so the optional match becomes a NodeUniqueIndexSeek, and it guarantees at most one org per cfg. If SHOW CONSTRAINTS already lists one, skip this.
Add this index
CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE
Expected: Each org lookup goes from a label scan to an O(1) seek. The overall saving grows with $limit and with how many Organization nodes there are. If a constraint already exists, nothing changes. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES for Organization. Then run PROFILE and check that the operator under the Optional / Apply branch is NodeUniqueIndexSeek, not NodeByLabelScan with a Filter.
rewrite Start from the indexed item, apply LIMIT before the org lookup
The issueBecause the pattern is written starting from cfg, the planner can choose to start the traversal at WorkplanCfg or WorkPlanMilestone and filter the items last. Also, LIMIT comes after the OPTIONAL MATCH, so nothing guarantees the Organization lookup runs only for the rows that are actually returned.
The fixWrite the pattern starting from item, with a `USING INDEX` hint on itemType (this needs the index from suggestion 1). Apply the item-only filters first, then expand to milestone and cfg. Take `LIMIT $limit` in a WITH before the OPTIONAL MATCH, so the org is looked up for at most $limit rows. Results are the same as long as tildaid is unique per org, which suggestion 2 guarantees.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (cfg:WorkplanCfg)-[:WORKPLAN_CFG_TO_MILESTONE]->(milestone:WorkPlanMilestone)-[:WORKPLAN_MILESTONE_TO_MILESTONE_ITEM]->(item:WorkPlanMilestoneItem) WHERE item.itemType = ? AND coalesce(item.cfgItemInvalid, false) = false AND milestone.scoringMode = ? AND item.checkpointPendingGenId > coalesce(item.checkpointLastComputedGenId, ?) AND (coalesce(item.checkpointGenerateRequestGenId, ?) <> item.checkpointPendingGenId OR $nowMs - coalesce(item.checkpointGenerateRequestSentAt, ?) >= ?) AND NOT (coalesce(item.checkpointUnsupportedGenId, ?) = item.checkpointPendingGenId AND coalesce(item.checkpointUnsupportedCount, ?) >= $maxUnsupported) AND coalesce(cfg.state, ?) <> ? OPTIONAL MATCH (org:Organization {tildaid: cfg.orgid}) RETURN item.tildaid AS cfgMilestoneItemId, cfg.tildaid AS cfgId, milestone.tildaid AS milestoneId, coalesce(item.itemConfig, ?) AS itemConfig, item.checkpointPendingGenId AS pendingGenId, coalesce(org.alias, ?) AS orgAlias, coalesce(item.checkpointPendingBy, ?) AS pendingBy LIMIT $limit
+ MATCH (item:WorkPlanMilestoneItem)
+ USING INDEX item:WorkPlanMilestoneItem(itemType)
+ WHERE item.itemType = ?
+ AND coalesce(item.cfgItemInvalid, false) = false
+ AND item.checkpointPendingGenId > coalesce(item.checkpointLastComputedGenId, ?)
+ AND (coalesce(item.checkpointGenerateRequestGenId, ?) <> item.checkpointPendingGenId OR $nowMs - coalesce(item.checkpointGenerateRequestSentAt, ?) >= ?)
+ AND NOT (coalesce(item.checkpointUnsupportedGenId, ?) = item.checkpointPendingGenId AND coalesce(item.checkpointUnsupportedCount, ?) >= $maxUnsupported)
+ MATCH (cfg:WorkplanCfg)-[:WORKPLAN_CFG_TO_MILESTONE]->(milestone:WorkPlanMilestone)-[:WORKPLAN_MILESTONE_TO_MILESTONE_ITEM]->(item)
+ WHERE milestone.scoringMode = ?
+ AND coalesce(cfg.state, ?) <> ?
+ WITH cfg, milestone, item
+ LIMIT $limit
+ OPTIONAL MATCH (org:Organization {tildaid: cfg.orgid})
+ RETURN item.tildaid AS cfgMilestoneItemId, cfg.tildaid AS cfgId, milestone.tildaid AS milestoneId, coalesce(item.itemConfig, ?) AS itemConfig, item.checkpointPendingGenId AS pendingGenId, coalesce(org.alias, ?) AS orgAlias, coalesce(item.checkpointPendingBy, ?) AS pendingBy
Expected: The plan should consistently start at the item index seek, and org lookups should be capped at $limit. If the planner already picks this plan, expect only a small improvement. If it currently starts at cfg or milestone, the improvement should be larger. · Evidence: Compare PROFILE of the old and new statement on the same parameters: same rows returned, first operator is NodeIndexSeek on item, fewer db hits, and the Optional branch runs at most $limit times. Then check p95 after the deploy.

Status: proposed · all suggestions

p95 latency over time
95th-percentile elapsedTimeMs per bucket
0ms2.5ms5ms7.5ms10ms00:0000:00 9.8ms p95
p50 latency over time
Median elapsedTimeMs per bucket
0ms2ms4ms6ms8ms00:0000:00 7.5ms p50
Executions / bucket
How often this shape ran
02505007501k1.2k00:0000:00 1.2k

p50/p95 are recovered from the per-bucket latency histogram. A rising p95 with a flat p50 means the tail is getting slower (lock contention, GC, a cold cache) even when the typical case is fine.