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 19:26:57.
› →|
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 EAD5D682 · last 7d

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

index Index the ProtocolVersionV2.tildaid anchor
The issueThe whole 6-hop chain starts from `where p.tildaid in [35 values]`. Without an index on ProtocolVersionV2(tildaid), the planner either scans every ProtocolVersionV2 node by label and filters, or starts from another label in the untyped, undirected chain and expands across Patient/Phi/CarePlan before it reaches the filter. Also note the statement is prefixed with EXPLAIN, so the 3.6 s avg / 9 s p95 is probably all planning time. With no selective indexed anchor, the planner has more join orders to weigh for this 11-node pattern.
The fixCreate a range index (or a uniqueness constraint if tildaid really is unique per protocol version). Then the IN list can be solved by a NodeIndexSeek on 35 values, the planner has one clear starting point, and expansion only begins from those few nodes.
Add this index
CREATE INDEX protocolversionv2_tildaid IF NOT EXISTS FOR (n:ProtocolVersionV2) ON (n.tildaid)
Expected: The plan should start with NodeIndexSeek on ProtocolVersionV2(tildaid) instead of NodeByLabelScan. Runtime for the PROFILE'd version should drop by orders of magnitude if a label scan or another start point is used today. Planning time may also shrink somewhat. · Evidence: Run SHOW INDEXES to confirm no index exists. Compare EXPLAIN before and after: the leaf operator should become NodeIndexSeek(p:ProtocolVersionV2(tildaid)). Then compare the elapsed time of the EXPLAIN call and the PROFILE db hits.
index Index Organization.tildaid for the per-row lookup
The issue`match (o:Organization) where o.tildaid = case when c contains ? then p.orgid end` runs once for every distinct (q,p,phi,s,c) row produced after the UNWIND, and that row count can be large. The value is only known at runtime, so without an index on Organization(tildaid) each row needs a label scan plus filter, or a cartesian product with every Organization.
The fixCreate a range index (or a unique constraint if tildaid is the organisation key) so each row is a NodeIndexSeek. This is needed both by the original query and by the EXISTS subquery in the rewrite below.
Add this index
CREATE INDEX organization_tildaid IF NOT EXISTS FOR (n:Organization) ON (n.tildaid)
Expected: Replaces a per-row Organization label scan, or a CartesianProduct plus Filter, with an index seek. That's a large reduction in db hits when there are many organisations. · Evidence: Check that the PROFILE plan shows NodeIndexSeek on Organization(tildaid) in the Apply branch instead of NodeByLabelScan(o:Organization), CartesianProduct or ValueHashJoin.
rewrite Filter on p and choices first, collect questions per p, then expand to patients
The issueEverything is matched in one huge pattern: the 6-hop undirected, untyped chain down to Phi, joined with Study, GraphNodeRef, ResponseSet and QuestionV2. The result is a cross product of phi × q × s × choice rows. Then `unwind cList` multiplies it again by every choice, two DISTINCTs dedupe it in memory, and only after that does the Organization match drop rows. `o` is never returned, so it is just an existence filter on `c contains ?` and on an org existing for p.orgid. Neither check depends on phi, s or q, so it could be applied before any expansion. The single long pattern is probably also what makes planning (EXPLAIN) take seconds.
The fixChange the Organization MATCH to an EXISTS check on p. Replace UNWIND plus CONTAINS with any() over rs.choices, so no extra rows are created. Collect question names per p before expanding the Patient/Phi chain, so q and phi never multiply each other. This matches the original output: rows are still grouped by p.tildaid, p.name and s.name, and question names depend only on p. If you know the relationship types and directions, also add them to each hop to cut expansion and planning further.
Proposed rewrite
MATCH (p:ProtocolVersionV2)
WHERE p.tildaid IN [?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?]
  AND EXISTS { MATCH (o:Organization) WHERE o.tildaid = p.orgid }
MATCH (p)--(gnr:GraphNodeRef)--(rs:ResponseSet)
WHERE any(c IN rs.choices WHERE c CONTAINS ?)
MATCH (gnr)--(q:QuestionV2)
WITH p, collect(DISTINCT q.name) AS qNames
MATCH (p)--(pd:DslPlanDefinition)--(soa:ScheduleOfActivities)--(cp:CarePlan)--(pa:Patient)--(phi:Phi)
MATCH (pd)--(s:Study)
RETURN p.tildaid, p.name, s.name, collect(DISTINCT phi.firstname + ? + phi.lastname), qNames
Expected: Fewer intermediate rows, because there's no phi×q×choice product and no Unwind or Distinct over it. Planning should also get simpler, since the query is split into smaller pattern parts joined through WITH. Runtime and EXPLAIN latency could both drop a lot, but measure before assuming it. · Evidence: Compare PROFILE of the old and new query on the same parameters: total db hits, peak rows into EagerAggregation/Distinct, and memory. Also check that the result sets are identical. Compare EXPLAIN elapsed time to confirm planning got cheaper.

Status: proposed · all suggestions

No rollups for this shape in the window.