Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 5 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 22:14:10.
› →|
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 281E5AFB · last 5d

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

index Composite index for the Advocate lookup by orgid + userid
The issue`optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId})` runs once per surviving (phi, p, mg) row. Nothing in this statement shows an index on Advocate(orgid, userid). Without one, each row triggers a NodeByLabelScan over every Advocate node plus a property filter, so the cost grows with candidate rows × Advocate count.
The fixAdd a composite range index on Advocate(orgid, userid) so each optional lookup becomes a NodeIndexSeek. If either property is already indexed alone, the planner may use that instead, but the composite index is the closest fit for the equality pair.
Add this index
CREATE INDEX advocate_orgid_userid IF NOT EXISTS FOR (a:Advocate) ON (a.orgid, a.userid)
Expected: Should remove a per-row label scan. The saving depends on how many Advocate nodes exist and how many patients match the PHI filter. It is probably most of the gap between the 178 ms average and the 455 ms p95 when the search returns many matches, but this is unverified. · Evidence: Run PROFILE before and after. Look for the Advocate operator changing from NodeByLabelScan+Filter to NodeIndexSeek, with fewer db hits. Then compare p95 over the next 7 days, keeping in mind that 3 executions is a very small sample.
index Make sure Organization.tildaid is a uniqueness constraint (the query's anchor)
The issueThe only selective starting point is `(:Organization{tildaid:$phiOrgId})`. The `tolower(phi.x) contains tolower(...)` predicates wrap the property in a function, so no index can serve them. If Organization.tildaid has no index, the planner may start from a Phi label scan and run tolower/contains on every Phi node in the database.
The fixCreate a uniqueness constraint on Organization.tildaid (or confirm one exists). This lets the plan start with a unique NodeIndexSeek on the organization, expand ORGANIZATION_TO_PATIENT to that org's patients, then HAS_PROTECTED_DATA_IN to their Phi, and apply the string filters only to that one org's Phi nodes.
Add this index
CREATE CONSTRAINT organization_tildaid_unique IF NOT EXISTS FOR (o:Organization) REQUIRE o.tildaid IS UNIQUE
Expected: If the plan currently starts from a Phi or Patient scan, this could cut the number of rows the string filter checks by orders of magnitude. If the constraint already exists, nothing changes. · Evidence: Run SHOW CONSTRAINTS / SHOW INDEXES for Organization.tildaid. In EXPLAIN, the leaf operator should be NodeUniqueIndexSeek on Organization, not NodeByLabelScan on Phi or Patient.
rewrite Apply the required EhrPhi match before the costly optional matches and aggregations
The issue`match(phi)-[]-(ehrPhi:EhrPhi)` is a required match, but it sits at the very end. Every candidate (phi, p, mg) row first goes through the User, AdvCampaignCondition and Binder→Document→ParticipantDocOverlay optional expansions and the eager collect()/count() aggregations, and only then gets dropped if it has no EhrPhi. Because the aggregations are eager, `limit $pgLimit` cannot cut this work short.
The fixMove the required MedicalGroup and EhrPhi matches up, straight after the PHI filter, so rows with no EhrPhi are dropped before the optional expansions run. The result set is unchanged: the same rows survive, and the grouping keys used by collect/count now just include ehrPhi, which is a carried variable that appeared in the final output anyway. Only the position of a required pattern changes.
Proposed rewrite
match(phi:Phi)<-[r:HAS_PROTECTED_DATA_IN]-(p:Patient)-[:ORGANIZATION_TO_PATIENT]-(:Organization{tildaid:$phiOrgId}) 
where (tolower(phi.firstname) contains tolower($phiFinder0) and tolower(phi.lastname) contains tolower($phiFinder1) and tolower(phi.dob) contains tolower($phiFinder2) ) or tolower(phi.mobilephone) contains tolower($phiFinder3) 
with phi, p 
match(p)-[]-(mg:MedicalGroup) 
with phi, p, mg 
match(phi)-[]-(ehrPhi:EhrPhi) 
with phi, p, mg, ehrPhi 
optional match(p)-[r:USER_TO_PATIENT]-(u:User) 
with u.tildaid as patUserId, phi, p, mg, ehrPhi 
optional match(p)-[r:INTERESTED_CONDITIONS]->(cond:AdvCampaignCondition) 
with patUserId, phi, p, mg, ehrPhi, collect(cond.tildaid) as condIds 
optional match(p)-[:BINDER_FOR]-(:Binder{binderType:?, scope:?})-[:INCLUDES]-(doc:Document{docTypeName:?})-[:BELONGS_TO]-(pdo:ParticipantDocOverlay{participantReviewed:true}) 
with count(pdo) as pdoCount, phi, p, mg, ehrPhi, condIds, patUserId 
optional match(adv:Advocate{orgid:$phiOrgId, userid:patUserId}) 
return phi, p, mg, ehrPhi, condIds, patUserId, pdoCount, adv 
limit $pgLimit
Expected: Fewer rows reach the optional expansions and the eager aggregations. The gain depends on what share of matched Phi nodes have no EhrPhi neighbour, and could be small if almost all of them do. · Evidence: Run PROFILE on both versions with the same parameters. Compare estimated and actual rows going into the EagerAggregation operators, and total db hits. Also check that the returned rows match between the two versions.

Status: proposed · all suggestions

No rollups for this shape in the window.