Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 7 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 23:33:56.
› →|
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 00000000 · service workplansvc · write · last 7d

Query
// First part for StudySiteUser scope 
match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) 
with distinct scopeUser 
match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDefinition{type:?}) 
match (sitePd)-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]->(w:WorkflowV2)<-[:TRIGGER_TO_WORKFLOW]-(t:Trigger{scope:?}) 
match (sitePd)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) 
match (sitePd)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) 
match (scopeUser)-[:STUDYSITEACTOR_TO_ACTOR]->(actor:Actor{delpending:false})<-[:STUDY_TO_ACTOR]-(st) 
where not exists((scopeUser)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) 
return distinct scopeUser.tildaid as scopeTildaid, t.scope as workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, scopeUser.roles as roles 
UNION // Second part for StudySite scope 
match (t:Trigger{scope:?})-[:TRIGGER_TO_WORKFLOW]->(w:WorkflowV2) 
match (w)<-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]-(scopeSite:DslPlanDefinition{type:?}) 
where not exists((scopeSite)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) 
with distinct t.scope as workflowScope, scopeSite 
match (scopeSite)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) 
match (scopeSite)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) 
return distinct scopeSite.tildaid as scopeTildaid, workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, null as roles

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

index Range index on WorkPlanContext.createdat for the cutoff branch
The issueThe second UNION branch, `MATCH (ctx:WorkPlanContext) WHERE ctx.createdat < $cutoff`, is a range predicate. Without a property index Neo4j has to scan every WorkPlanContext node and filter on createdat. The cost grows with the total number of contexts, not with the number that have expired. That fits the large gap between the average (265 ms) and p95 (3,050 ms): runs that find few expired rows still pay for the full scan.
The fixCreate a RANGE index on createdat so the planner can use NodeIndexSeekByRange and read only the nodes older than $cutoff. With an index, the LIMIT can also stop the seek early instead of stopping a scan early.
Add this index
CREATE INDEX workplancontext_createdat IF NOT EXISTS FOR (ctx:WorkPlanContext) ON (ctx.createdat)
Expected: The createdat branch should change from a full label scan to an index range seek. If expired contexts are a small share of the total, this branch's time could fall by an order of magnitude. That is hedged: it depends on how many nodes are expired and how many are stored in total. · Evidence: Run EXPLAIN or PROFILE before and after creating the index. Before, the branch should show NodeByLabelScan followed by Filter on ctx.createdat. After, it should show NodeIndexSeekByRange on workplancontext_createdat. Also check the stored createdat values have one consistent type (all datetime or all epoch numbers) that matches $cutoff, or the range seek will skip rows. Then compare p95 for shape 00000000 over the next 7 days.
index Index on WorkPlanContext.delpending for the pending-delete branch
The issueThe first UNION branch, `MATCH (ctx:WorkPlanContext) WHERE ctx.delpending = true`, is an equality filter on a property that has no lookup. It forces a second full scan of the WorkPlanContext label, even though rows flagged for deletion are probably a small subset.
The fixAdd a RANGE index on delpending. Equality seeks on boolean values work with range indexes in Neo4j 5. Then the pending-delete branch reads only the flagged nodes. Together with the createdat index, neither branch needs to scan the whole label.
Add this index
CREATE INDEX workplancontext_delpending IF NOT EXISTS FOR (ctx:WorkPlanContext) ON (ctx.delpending)
Expected: The first branch should change from a label scan to NodeIndexSeek, so its cost should track the number of flagged nodes rather than the total number of contexts. This helps most when only a few contexts have delpending = true. · Evidence: PROFILE should show NodeIndexSeek on workplancontext_delpending instead of NodeByLabelScan followed by Filter in branch 1. Also compare the db hits for that branch before and after.
rewrite Apply LIMIT inside each UNION branch and drop the redundant DISTINCT
The issue`UNION` already removes duplicates, so the outer `WITH DISTINCT ctx` does the same work a second time. The single LIMIT is applied only after both branches are unioned and deduplicated. DETACH DELETE in the same statement can also bring in Eager operators, which may cause more rows from both branches to be read than $limit before any deletion happens.
The fixPut `LIMIT $limit` inside each branch so neither branch can return more than $limit rows. Keep UNION for deduplication and keep an outer LIMIT so the total stays capped at $limit. Remove DISTINCT because UNION already handles it. The result is the same: at most $limit distinct contexts deleted per call.
Proposed rewrite — current query in red −, proposed in green +
- // First part for StudySiteUser scope match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) with distinct scopeUser match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDefinition{type:?}) match (sitePd)-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]->(w:WorkflowV2)<-[:TRIGGER_TO_WORKFLOW]-(t:Trigger{scope:?}) match (sitePd)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (sitePd)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) match (scopeUser)-[:STUDYSITEACTOR_TO_ACTOR]->(actor:Actor{delpending:false})<-[:STUDY_TO_ACTOR]-(st) where not exists((scopeUser)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) return distinct scopeUser.tildaid as scopeTildaid, t.scope as workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, scopeUser.roles as roles UNION // Second part for StudySite scope match (t:Trigger{scope:?})-[:TRIGGER_TO_WORKFLOW]->(w:WorkflowV2) match (w)<-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]-(scopeSite:DslPlanDefinition{type:?}) where not exists((scopeSite)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) with distinct t.scope as workflowScope, scopeSite match (scopeSite)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (scopeSite)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) return distinct scopeSite.tildaid as scopeTildaid, workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, null as roles
+ CALL { MATCH (ctx:WorkPlanContext) WHERE ctx.delpending = true RETURN ctx LIMIT $limit UNION MATCH (ctx:WorkPlanContext) WHERE ctx.createdat < $cutoff RETURN ctx LIMIT $limit } WITH ctx LIMIT $limit DETACH DELETE ctx RETURN count(ctx) AS deleted
Expected: Each branch's input is capped at $limit rows before the union and the delete. This should stop the occasional run from materialising a large backlog. The gain is modest if the plan already ends each branch early, and larger if PROFILE shows Eager or rows above $limit flowing out of the branches. · Evidence: Compare PROFILE output for the old and new statements on the same data. Check the row counts coming out of each UNION branch, check whether an Eager operator appears before DetachDelete, and confirm that `deleted` returns the same value for the same $limit and $cutoff.

Status: proposed · all suggestions

p95 latency over time
95th-percentile elapsedTimeMs per bucket
0ms20ms40ms60ms14 Nov 00:0015 Nov 00:0016 Nov 00:0017 Nov 00:0000:00 59ms p9500:00 32ms p9500:00 18ms p9500:00 32ms p95
p50 latency over time
Median elapsedTimeMs per bucket
0ms1ms2ms3ms4ms5ms14 Nov 00:0015 Nov 00:0016 Nov 00:0017 Nov 00:0000:00 4.4ms p5000:00 4ms p5000:00 3.9ms p5000:00 4.6ms p50
Executions / bucket
How often this shape ran
050k100k150k200k250k14 Nov 00:0015 Nov 00:0016 Nov 00:0017 Nov 00:0000:00 33.1k00:00 68.9k00:00 229.6k00:00 40.5k

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.