Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 14 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 16:05:02.
› →|
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 8E20502F · last 14d

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

rewrite Apply the lead.studyid filter in the first MATCH, not after the patient expansion
The issueThe first clause `match(lead:Lead)-[]-(:Study)-[]-(org:Organization{tildaid:$orgId}) with lead` returns every Lead linked to any study of the organisation. That is one row per path, so duplicates are possible. The predicate `where lead.studyid=$studyId` sits on the second MATCH. Because of that, the planner may expand LEAD_TO_PATIENT for every Lead in the org and only afterwards drop the ones from other studies. The untyped `-[]-` hops also make it walk every relationship type on Study and Lead.
The fixPut `WHERE lead.studyid = $studyId` right after the first MATCH, before `WITH lead`. Then rows from other studies are removed before the patient, advocate, Phi and Task expansions. The row multiplicity is unchanged: duplicate org-study paths, the OPTIONAL MATCH on advocates and the Phi and Task fan-out all stay as they were. So `count(lead)` should return the same number. If you know which relationship types join Lead–Study and Study–Organization, adding them to the `-[]-` hops would prune the walk further. They are not in the shape, so they are left out here.
Proposed rewrite
match(org:Organization{tildaid:$orgId})-[]-(:Study)-[]-(lead:Lead) 
where lead.studyid=$studyId 
with lead 
match(patient:Patient)<-[:LEAD_TO_PATIENT]-(lead) 
with * 
optional match(lead)-[:LEAD_TO_ADVOCATE]-(adv:Advocate) 
with * 
match(patient)-[:HAS_PROTECTED_DATA_IN]->(phi:Phi) 
with * 
match(lead)-[]-(task:Task{state:?}) 
where not (task)-[]-(:Inbox{type:?}) 
return count(lead)
Expected: If the org has Leads across many studies, this could cut db hits roughly in proportion to the share of the org's Leads in $studyId. That might bring the ~1.2 s down to a few hundred ms. With one run a week the total saving is small. · Evidence: Run PROFILE on both versions with the same parameters. Confirm the counts match, then compare total db hits and the row count after the first Expand/Filter. The rewrite should show far fewer rows going into the LEAD_TO_PATIENT expand.
index Index Lead.studyid so the planner can start from the study's Leads
The issue`lead.studyid=$studyId` is the most selective predicate on Lead, but there is no visible index for it. Without one, the planner has to reach Leads by walking from Organization through Study, or use a NodeByLabelScan on Lead and filter on studyid.
The fixCreate a range index on Lead(studyid). The planner can then do a NodeIndexSeek for the Leads in this study and check the org pattern from that small set. It can also join both anchors.
Add this index
CREATE INDEX lead_studyid IF NOT EXISTS FOR (l:Lead) ON (l.studyid)
Expected: If one study's Leads are a small fraction of all Leads, the plan could switch to an index seek. Latency might fall from about a second to tens of ms. Hedged: this depends on the cardinalities. · Evidence: Run EXPLAIN/PROFILE after creating the index. Look for a NodeIndexSeek on Lead(studyid) instead of a NodeByLabelScan or a filter after expansion, and check p95 on the next runs.
index Make sure Organization.tildaid has an index or a uniqueness constraint
The issue`(org:Organization{tildaid:$orgId})` is the natural anchor of the first MATCH. If tildaid has no index, finding the org needs a full scan of the Organization label before any expansion.
The fixRun SHOW INDEXES / SHOW CONSTRAINTS. If tildaid is not covered, add an index. If tildaid is the org's identifier, a uniqueness constraint is better (CREATE CONSTRAINT organization_tildaid IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE). It gives the same lookup and also guarantees one node per key.
Add this index
CREATE INDEX organization_tildaid IF NOT EXISTS FOR (o:Organization) ON (o.tildaid)
Expected: If it is missing today, the org lookup goes from a label scan to a single seek. This saves the scan on every call, and the saving is larger the more Organizations there are. If an index already exists, nothing changes. · Evidence: Check SHOW INDEXES for Organization(tildaid). In PROFILE, look for NodeIndexSeek/NodeUniqueIndexSeek on Organization instead of NodeByLabelScan with a filter.

Status: proposed · all suggestions

No rollups for this shape in the window.