Postgres composite search is I/O-bound at volume — needs a denormalized one-row-per-group storage layout

[mauripunzueta]

Summary

At full data volume the Postgres composite search shapes (code-value-quantity, combo-code-value-quantity, component-code-value-quantity) run at ~12s median under 30 concurrent VUs and time out at the 30s statement_timeout. This is the one search shape that PR #260 could not fix — it is I/O-bound, and no query rewrite or index shape addresses it. It needs a storage-layer change.

Spun off from #224 / PR #260.

Why no query fix works

The composite is extremely sparse: only 222 of 656,737 Observations match a typical code-value-quantity query. But each component of a composite instance is stored as its own search_index row sharing a composite_group, so answering "code = X AND value > Y within one group" requires reading many rows per resource and aggregating.

The shipped query (PR #260) prefilters and then GROUP BY resource_id, composite_group HAVING MAX(CASE …). Measured against the real dataset, its cost is dominated by random heap I/O:

Bitmap Heap Scan on search_index (actual rows=113014)
  Buffers: shared hit=212 read=111367

~112k buffer reads to fetch ~113k rows — close to one random page per row, because a parameter's rows are scattered across a ~10M-row table. That is I/O, not CPU or plan choice.

Every alternative was measured against the real 656,737-Observation dataset in CI and is worse (documented in build_composite_condition in crates/persistence/src/backends/postgres/search/query_builder.rs):

candidate	real cost
shipped (OR-prefilter + IN + GROUP BY/HAVING)	~1.0s cold / ~12s at 30 VUs
correlated EXISTS	8.9s on a match, 20s on a zero-match
drop prefilter + covering INCLUDE index	16.5s
self-join correlated on composite_group	flattened → ordered-scan trap

Proposed fix: one denormalized row per composite group

Write one search_index row per (resource, composite_group) with every component's value column populated, instead of one row per component. Then a single composite query becomes a plain multi-column predicate on one row:

WHERE param_name = 'code-value-quantity'
  AND value_token_code = '8867-4'
  AND value_quantity_value > 100

which a single covering index can answer directly — no per-group aggregation, no fan-out, no scattered heap reads. The sparse match becomes an index range scan returning ~222 rows.

Work required

• Writer (crates/persistence/src/search/extractor.rs::extract_composite, backends/postgres/search/writer.rs): emit one combined row per group rather than one per component. Decide column layout for composites with >2 components and for repeated components.
• Query builder (build_composite_condition): switch to the flat single-row predicate when the combined rows are present.
• Migration + backfill: existing composite rows are one-per-component; need a v15 migration that re-derives combined rows (a targeted reindex of composite params, not a full re-extraction).
• Covering index on the combined layout.
• SQLite parity: the SQLite backend has the identical one-row-per-component composite shape (backends/sqlite/search/query_builder.rs). Keep the two backends in step or explicitly scope this to Postgres.

Acceptance

• Observation?combo-code-value-quantity= median well under 100ms at 30 VUs on the FHIR benchmark, no 500s.
• The composite correctness tests added in PR perf(postgres): fix search collapse at volume (#224) #260 (multi-group blood-pressure panel, OR-list value isolation) stay green.