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 18:51:32.
› →|
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 CA13A9C0 · read · last 1h

Query
MATCH (o:Organization {tildaid: $org_id})-[:STUDY_TO_ORG]-(study:Study {tildaid: $study_id}) -[:PLAN_DEFINITION_TO_STUDY]-(pd:DslPlanDefinition) -[:SOA_TO_PLAN_DEFINITION]-(:ScheduleOfActivities) -[:EXECUTE_SOA]-(cp:CarePlan {tildaid: $careplan_id}) 
WHERE coalesce(pd.deltimestamp, ?) = ? AND coalesce(cp.isDeleted, false) = false 
WITH DISTINCT cp, pd, study 
MATCH (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:VisitV2) 
RETURN pv.tildaid AS pv_id, v.tildaid AS visit_id, coalesce(v.title, ?) AS title, coalesce(v.studyPeriod, ?) AS period, coalesce(pv.status, ?) AS status, v.numOfDaysFromAnchor AS days_from_anchor, v.interval AS interval, CASE WHEN pv.appointmentDate IS NULL THEN null ELSE toInteger(pv.appointmentDate.epochMillis) END AS appointment_at, pv.startedAt AS started_at, pv.completedAt AS completed_at, coalesce(pv.signedOffByTeam, ?) AS signed_off_by_team, coalesce(pv.edcBitMap, ?) AS edc_bitmap 
ORDER BY days_from_anchor, title, pv_id

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

index Index CarePlan.tildaid so the chain can start from the single care plan
The issueThe first MATCH filters three nodes by tildaid: Organization, Study and CarePlan. The only one that narrows things to a single row is `cp:CarePlan {tildaid: $careplan_id}`. If CarePlan.tildaid has no index, the planner either scans every CarePlan node or starts at the Study/Organization end. Starting there means walking every DslPlanDefinition, then every ScheduleOfActivities, then every CarePlan in the study through EXECUTE_SOA, and only then dropping all but one care plan. A cost that grows with study size fits the gap between avg 682 ms and p95 1887 ms, with the slow runs likely being large studies.
The fixAdd a lookup index on CarePlan.tildaid. If tildaid really is a unique key, a uniqueness constraint is better because it also gives the planner a guaranteed single row (CREATE CONSTRAINT careplan_tildaid_unique IF NOT EXISTS FOR (c:CarePlan) REQUIRE c.tildaid IS UNIQUE). Check for duplicates first, or the constraint will fail to create. With either one, the plan can start with NodeIndexSeek on cp, then expand outward a few hops and check the study, org and pd filters along the way.
Add this index
CREATE INDEX careplan_tildaid IF NOT EXISTS FOR (c:CarePlan) ON (c.tildaid)
Expected: If the current plan anchors on Study or scans CarePlan, the first MATCH should shrink from thousands of db hits to a few dozen. p95 could plausibly drop from about 1.9 s to tens of ms. If an index already exists and is used, nothing changes. · Evidence: Run SHOW INDEXES WHERE 'CarePlan' IN labelsOrTypes to see if an index exists. Then PROFILE the statement before and after with real parameters. You want NodeIndexSeek on cp:CarePlan(tildaid) as the leaf operator, in place of NodeByLabelScan or a Study-anchored Expand(All) with large row counts. Afterwards, compare total db hits and the p95 from the next 7 days.
rewrite Anchor the query explicitly on the CarePlan and check the study/org path from there
The issueThe pattern is written Organization → Study → DslPlanDefinition → ScheduleOfActivities → CarePlan, all with undirected relationships, and the single-row care plan comes last. With three tildaid filters, the planner can pick the org or study as the anchor and expand through the whole study's plan definitions and care plans. `coalesce(cp.isDeleted, false) = false` is also checked only after that expansion.
The fixMatch the CarePlan first and pin it with a USING INDEX hint. Apply the isDeleted filter right away, then walk from cp out to the study and org to confirm they match. The result rows and the WITH DISTINCT handling stay exactly the same. The hint needs the CarePlan.tildaid index from the first suggestion and will raise an error if that index is missing, so deploy them together.
Proposed rewrite — current query in red −, proposed in green +
- MATCH (o:Organization {tildaid: $org_id})-[:STUDY_TO_ORG]-(study:Study {tildaid: $study_id}) -[:PLAN_DEFINITION_TO_STUDY]-(pd:DslPlanDefinition) -[:SOA_TO_PLAN_DEFINITION]-(:ScheduleOfActivities) -[:EXECUTE_SOA]-(cp:CarePlan {tildaid: $careplan_id}) WHERE coalesce(pd.deltimestamp, ?) = ? AND coalesce(cp.isDeleted, false) = false WITH DISTINCT cp, pd, study MATCH (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:VisitV2) RETURN pv.tildaid AS pv_id, v.tildaid AS visit_id, coalesce(v.title, ?) AS title, coalesce(v.studyPeriod, ?) AS period, coalesce(pv.status, ?) AS status, v.numOfDaysFromAnchor AS days_from_anchor, v.interval AS interval, CASE WHEN pv.appointmentDate IS NULL THEN null ELSE toInteger(pv.appointmentDate.epochMillis) END AS appointment_at, pv.startedAt AS started_at, pv.completedAt AS completed_at, coalesce(pv.signedOffByTeam, ?) AS signed_off_by_team, coalesce(pv.edcBitMap, ?) AS edc_bitmap ORDER BY days_from_anchor, title, pv_id
+ MATCH (cp:CarePlan {tildaid: $careplan_id}) USING INDEX cp:CarePlan(tildaid) WHERE coalesce(cp.isDeleted, false) = false MATCH (cp)-[:EXECUTE_SOA]-(:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(study:Study {tildaid: $study_id})-[:STUDY_TO_ORG]-(o:Organization {tildaid: $org_id}) WHERE coalesce(pd.deltimestamp, ?) = ? WITH DISTINCT cp, pd, study MATCH (cp)-[:CAREPLAN_TO_ALL_PARTICIPANTVISITS]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]-(v:VisitV2) RETURN pv.tildaid AS pv_id, v.tildaid AS visit_id, coalesce(v.title, ?) AS title, coalesce(v.studyPeriod, ?) AS period, coalesce(pv.status, ?) AS status, v.numOfDaysFromAnchor AS days_from_anchor, v.interval AS interval, CASE WHEN pv.appointmentDate IS NULL THEN null ELSE toInteger(pv.appointmentDate.epochMillis) END AS appointment_at, pv.startedAt AS started_at, pv.completedAt AS completed_at, coalesce(pv.signedOffByTeam, ?) AS signed_off_by_team, coalesce(pv.edcBitMap, ?) AS edc_bitmap ORDER BY days_from_anchor, title, pv_id
Expected: The plan always starts from one node and expands about four hops. Run time no longer depends on how many care plans a study has, which should flatten the p95 tail. The gain is likely large for big studies and small for small ones. · Evidence: PROFILE should show NodeIndexSeek on cp first, with every Expand(Into/All) after it handling only single-digit rows. Also run the old and new queries on the same parameter sets and check they return the same rows in the same order.
index Index Study.tildaid so the study check is a seek, not a label scan
The issue`study:Study {tildaid: $study_id}` is the second identifying filter in the first MATCH. With no index on Study.tildaid, the planner has no cheap way to resolve the study. If it anchors there, it scans every Study node. If it anchors on cp, it has no seek-based option such as joining from both ends, so it can only filter during the expand. Organization.tildaid has the same issue and is worth checking the same way.
The fixCreate a lookup index on Study.tildaid, or a uniqueness constraint if the key is unique. That lets the planner seek the study and either confirm it with Expand(Into) or join from both ends. Check SHOW INDEXES for Organization.tildaid too, and add the matching index if it is missing (CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid)).
Add this index
CREATE INDEX study_tildaid IF NOT EXISTS FOR (s:Study) ON (s.tildaid)
Expected: This mainly makes the plan robust when the planner picks the study end, keeping that a seek of about 1 row instead of a label scan. The gain is modest compared with the CarePlan index. It helps most if the CarePlan index can't be added or the planner keeps choosing the study as the anchor. · Evidence: First check SHOW INDEXES for Study and Organization tildaid. After creating the index, PROFILE should show NodeIndexSeek (or NodeUniqueIndexSeek) on study:Study(tildaid) or an Expand(Into) check, with no NodeByLabelScan on Study anywhere in the plan.

Status: proposed · all suggestions

No rollups for this shape in the window.