Postgres search: accurate stats favor the ordered-index scan, regressing sparse/zero-match token & date shapes

[mauripunzueta]

Summary

PR #260 added a 3-column MCV so Postgres estimates token selectivity correctly, which fixed the high-match shapes dramatically (Encounter?status=finished p95 27s → 54ms). But accurate statistics also make the planner commit harder to the ordered-index early-stop plan (idx_resources_search, walk resources in last_updated DESC and stop at LIMIT 21). That plan is optimal when many rows match and pathological when almost none do — it then walks the entire resource type before it can return an empty page.

Net effect in the benchmark is positive (p95 1.7x better, throughput +23%), but two shapes regressed and this is the next thing to attack.

Spun off from #224 / PR #260.

Evidence (real benchmark dataset, 656,737 Observations)

shape	p95 before this PR	p95 after
Observation?code	5,747 ms	30,018 ms
Encounter?date	2,582 ms	17,757 ms

Both are shapes where a randomly-chosen value from the benchmark's pool matches few or no rows, so the ordered idx_resources_search scan walks the whole Observation / Encounter partition looking for 21 hits that are sparse or absent. With the old (wrong) estimate the planner instead built the match set first and probed by PK, which is faster in the sparse case.

This is the genuine two-sided nature of the ordered index: it is the 149x median win for the common case and a cliff for the sparse case. The fix is to get the planner to switch strategies based on actual selectivity of the specific value, not just the parameter.

Directions to investigate

• idx_search_* covering/INCLUDE resource_id so the sparse pre-filter path (materialize matching ids, probe resources by PK) is cheap enough that the planner prefers it when selectivity is high — removing the incentive to gamble on the ordered scan.
• Per-value statistics: the regression is value-dependent (code=8302-2 matches many, code=non-existent matches none). A wider MCV on value_token_code / value_date may already let the planner tell these apart — measure whether bumping SET STATISTICS further separates them.
• A LIMIT-aware cost tweak or a planner hint is a last resort; prefer fixing the estimate.
• Confirm the same effect on value_quantity_value range shapes.

Method note (important)

These plans cannot be A/B'd on a laptop-sized replica: the dominant cost is random heap I/O on a ~10M-row search_index, which does not exist when the table fits in cache. A small replica ranked PR #260's winning fixes backwards. Use the EXPLAIN (ANALYZE, BUFFERS) capture step added to .github/workflows/fhir-benchmark.yml (.github/scripts/pg-search-plans.sql) and read Buffers: shared read=, not Execution Time.

Acceptance

• Observation?code and Encounter?date p95 back under a few seconds at 30 VUs, without regressing the high-match shapes PR perf(postgres): fix search collapse at volume (#224) #260 fixed (status, class, category, quantity).

[bomanaps]

Hello

[bomanaps]

Hey @mauripunzueta , could you please trigger the FHIR Server Benchmark workflow (fhir-benchmark.yml) against main with backend set to postgres so we can validate the #281 fix from PR #426?

[mauripunzueta]

@bomanaps — running it now: run 30580745815, backend=postgres, suites prewarm,import,search.

Two deliberate deviations from what you asked for, both worth explaining.

It runs from fix/benchmark-harness-disk-safety (#453), not main. That branch contains zero Rust changes — git diff origin/main...HEAD touches only .github/ — and its history includes #426 and #393 exactly as main does. So the binary under test is byte-identical to main's; only the workflow around it differs. You get the measurement you asked for.

The reason not to use main is that an attempt was already made for you this morning and it did not just fail, it took the shared Docker host down with it: run 30550776427. Postgres was started with no volume, so the Synthea import filled the host's containerd store; PG became unreachable ~22 minutes in; and the runner was then killed, which skips if: always() cleanup, so its containers were orphaned still holding the corpus. Every testcontainers job in the repo failed with disk quota exceeded for hours afterwards. Running against main again would re-expose exactly that path, because #453 is the PR that fixes it and it has not merged. On #453 the run gets a disk canary that fails in ~10s if the host is short, a named PG volume, and a start-of-job reaper.

Suites are prewarm,import,search, not all. #281 is a search-shape regression, so crud (300 VUs × 5 min) adds nothing to it and roughly doubles the host exposure. import is still included because search is meaningless without loaded data.

What you'll want when it finishes is the "Search latency by query shape (postgres)" table in the step summary — it breaks the search suite down per shape sorted by p95, which is the granularity #281 is about. The raw search-points.json is in the fhir-benchmark-postgres-30580745815 artifact if you want to slice it differently. Both are now uploaded before the post-processing steps, so they survive even if the run dies later — that was another thing lost in the 14:15 attempt.

Caveat on interpreting the absolute numbers: this runner is not the benchmark's reference hardware (8 vCPU / 30 G for the DB), so treat them as a before/after signal rather than something comparable to the published report. If you have a pre-#426 baseline from the same runner, that's the honest comparison; if not, I can run one against the parent commit for a paired measurement — say the word.

I'll post the results here when it lands.

[bomanaps]

@bomanaps — running it now: run 30580745815, backend=postgres, suites prewarm,import,search.

Two deliberate deviations from what you asked for, both worth explaining.

It runs from fix/benchmark-harness-disk-safety (#453), not main. That branch contains zero Rust changes — git diff origin/main...HEAD touches only .github/ — and its history includes #426 and #393 exactly as main does. So the binary under test is byte-identical to main's; only the workflow around it differs. You get the measurement you asked for.

The reason not to use main is that an attempt was already made for you this morning and it did not just fail, it took the shared Docker host down with it: run 30550776427. Postgres was started with no volume, so the Synthea import filled the host's containerd store; PG became unreachable ~22 minutes in; and the runner was then killed, which skips if: always() cleanup, so its containers were orphaned still holding the corpus. Every testcontainers job in the repo failed with disk quota exceeded for hours afterwards. Running against main again would re-expose exactly that path, because #453 is the PR that fixes it and it has not merged. On #453 the run gets a disk canary that fails in ~10s if the host is short, a named PG volume, and a start-of-job reaper.

Suites are prewarm,import,search, not all. #281 is a search-shape regression, so crud (300 VUs × 5 min) adds nothing to it and roughly doubles the host exposure. import is still included because search is meaningless without loaded data.

What you'll want when it finishes is the "Search latency by query shape (postgres)" table in the step summary — it breaks the search suite down per shape sorted by p95, which is the granularity #281 is about. The raw search-points.json is in the fhir-benchmark-postgres-30580745815 artifact if you want to slice it differently. Both are now uploaded before the post-processing steps, so they survive even if the run dies later — that was another thing lost in the 14:15 attempt.

Caveat on interpreting the absolute numbers: this runner is not the benchmark's reference hardware (8 vCPU / 30 G for the DB), so treat them as a before/after signal rather than something comparable to the published report. If you have a pre-#426 baseline from the same runner, that's the honest comparison; if not, I can run one against the parent commit for a paired measurement — say the word.

I'll post the results here when it lands.

Yes please before/after baseline will be great to have, thank you