search: chained and _has searches whose resolved id set exceeds 10,000 answer 500 on the Elasticsearch composites (max_result_window)

[mauripunzueta]

Summary

On the Elasticsearch composites, a chained or _has search whose resolved id set exceeds Elasticsearch's index.max_result_window (10,000) answers 500 with an opaque OperationOutcome (code: exception, "An internal error occurred"). Two rows of the manual matrix (MANUAL_TESTING_MATRIX.md §8.2, rows 4.10 and 4.11) fail this way on mongo-es with the full Synthea corpus.

Current behavior

main at 722a0927b, HFS_STORAGE_BACKEND=mongo-es, 18.96 M resources (7.7 M Observations, 11,705 Patients), Elasticsearch 8.15.0 with the default max_result_window:

Matrix row	Request	Result
4.10 chained	GET /Observation?subject:Patient.family=Parker433&_count=5	500
4.10 chained (untyped)	GET /Observation?subject.family=Parker433&_summary=count	500
4.11 reverse chained	GET /Patient?_has:Observation:patient:code=http://loinc.org|8302-2&_count=5	500
control	GET /Observation?subject.identifier=http://hl7.org/fhir/sid/us-ssn|999-33-3920	200, 165
control	GET /Patient?_has:Condition:patient:code=http://snomed.info/sct|706893006	200, 5,294

Server log for each failure:

WARN helios_rest::handlers::search: Chained search resolution failed error=internal error in elasticsearch:
Search failed after 3 attempts (status 400): {"error":{"root_cause":[{"type":"illegal_argument_exception",
"reason":"Result window is too large, from + size must be less than or equal to: [10000] but was [10001].
See the scroll api for a more efficient way to request large data sets. This limit can be set by changing
the [index.max_result_window] index level setting."}], ... "phase":"query"
ERROR helios_rest::error: internal error while processing request error.detail=internal error in elasticsearch: ...
ERROR tower_http::trace::on_failure: response failed classification=Status code: 500 Internal Server Error latency=2198 ms

The controls pass because their resolved sets are small (165 Observations; 5,294 Patients). The failures resolve more than 10,000 ids: the 30 Parker433 patients own roughly 20,000 Observations, and 8302-2 matches 175,355 Observations.

Root cause

crates/persistence/src/search/chain_resolver.rs materialises every hop of a chain as a full id list with offset paging (search_all_pages, RESOLVER_PAGE = 1000, page.offset = Some(offset) until a short page). The Elasticsearch backend clamps size so from + size never exceeds max_result_window (search/query_builder.rs, #1079), so the resolver's eleventh page asks for from=10000, size=1 and Elasticsearch rejects it. The error is a plain BackendError, which the REST layer maps to 500.

The SQL and MongoDB backends have no such window, so the same searches succeed (slowly) there; the composites sqlite-es, pg-es, mongo-es and s3-es all route search to Elasticsearch and are all affected. ElasticsearchConfig::max_result_window is not exposed as an environment variable, so there is no deployment-side workaround.

Expected behavior

Either the chain resolves regardless of size (page the intermediate hops with search_after/PIT, or resolve the terminal hop inside Elasticsearch with a terms lookup instead of materialising ids), or the server answers a 4xx that names the limit (too-costly) instead of a 500. A chained search that succeeds on sqlite should not be an internal error on sqlite-es.

Related

• search: chained-search resolution silently truncates at the default page size (100) #645 introduced the full materialisation (the previous behaviour truncated hops at 100).
• Backward cursor paging on SQLite/PostgreSQL drops the wrong row and hardcodes has_previous; ES phantom next link; _offset leaks into cursor links #1079 added the max_result_window clamp that turns the overflow into an Elasticsearch 400.
• Decide the fate of the REST-unreachable per-backend chain builders (ChainedSearchProvider) #1341 discusses the per-backend ChainedSearchProvider builders that would avoid materialising ids.
• Manual test pass: MongoDB + Elasticsearch backend #941 (manual pass, mongo-es) — rows 4.10 and 4.11 recorded as ❌ against this issue.

[angela-helios]

Re-run on current main with the ES requests traced (step 0 of today's plan)

Setup. main at 0eb99c238, HFS_STORAGE_BACKEND=mongo-es, Elasticsearch 8.15.0 with the default index.max_result_window=10000, HFS_COMPOSITE_SYNC_MODE=synchronous. The 18.96 M corpus is not on this host, so I loaded a targeted slice of the same Synthea export via $bulk-submit: all 11,704 Patients, 186,925 Observations (every Observation of the 30 Parker433 patients and of the SSN control patient, plus every 8302-2 Observation), and the 8,755 Conditions that carry 706893006. Every request HFS sends to Elasticsearch was captured with a logging HTTP proxy on :9201 (HFS_ELASTICSEARCH_NODES=http://localhost:9201 → :9200); the ES slowlog stayed empty even with logger.index.search.slowlog=TRACE, so the proxy log is the source below.

Result: none of the five rows answers 500 on current main.

Row	Request	HTTP	total	time	ES requests
4.10 chained	GET /Observation?subject:Patient.family=Parker433&_count=5	200	11,939	3.46 s	14
4.10 untyped	GET /Observation?subject.family=Parker433&_summary=count	200	11,939	3.37 s	14
4.11 reverse	GET /Patient?_has:Observation:patient:code=http://loinc.org|8302-2&_count=5	200	11,704	52.6 s	177
control	GET /Observation?subject.identifier=http://hl7.org/fhir/sid/us-ssn|999-33-3920	200	165	0.20 s	6
control	GET /Patient?_has:Condition:patient:code=http://snomed.info/sct|706893006	200	5,293	1.82 s	10

(4.11 resolves 175,355 Observations here too; the control differs from the issue's 5,294 by one because this slice only holds the Conditions that carry the code.)

ES requests for row 4.10, in order (index, size, from / search_after):

#	index	from	size	search_after	query
1	t1548_default_patient	–	1001	–	nested string.name=family, parker433*
2	t1548_default_observation	–	1001	–	nested reference.name=subject ∈ 30 Patient/… refs
3	t1548_default_observation	–	1001	[1790607399085, "1b7a9492-…"]	same
4	t1548_default_observation	–	1001	[1790607324215, "9715202a-…"]	same
5–13	t1548_default_observation	–	1001	[<last_updated>, <resource_id>] of the previous page, 9 more pages	same
14	t1548_default_observation	–	6	–	terms resource_id × 11,939 ids

Every page has sort: [last_updated desc, resource_id asc], none carries from; page 13 comes back with 940 hits and has_next=false. So the answer to the concrete doubt is: no page stops returning next_cursor. search_all_ids in chain_resolver.rs follows the backend cursor for every intermediate page and only falls back to offset when a provider reports has_next without a cursor; against Elasticsearch that fallback branch is never entered, so from + size never reaches 10,001 and Result window is too large cannot be raised any more.

This is PR #1501 (bugfix/1452-chain-resolver-paging, merged 2026-09-25, c60042af6), which landed after the 722a0927b this issue was recorded on: search_all_pages / search_all_ids were rewritten to prefer next_cursor, CompositeStorage::search_ids routes to ElasticsearchBackend, and the ES id pages return a search_after cursor. On 722a0927b the resolver paged with offset only, which is exactly the 11-page sequence the issue describes.

What is still there (proposal: re-scope #1548 to this). The resolver still materialises the terminal hop as an id list and issues the final search as a single terms query on resource_id, which has its own Elasticsearch ceiling, index.max_terms_count (65,536):

$ curl -s 'localhost:8080/Observation?subject:Patient.gender=male&_count=5'     # 5,891 male patients
HTTP 400 in 33.9 s
{"resourceType":"OperationOutcome","issue":[{"severity":"error","code":"invalid","details":{"text":"failed to parse search query: the search index rejected a search value as malformed (for example an unparseable date, number or :text-advanced expression)"}}]}

WARN helios_persistence::backends::elasticsearch::search_impl: Elasticsearch rejected the search query as malformed status=400 body="{\"error\":{\"root_cause\":[{\"type\":\"query_shard_exception\",\"reason\":\"failed to create query: The number of terms [104656] used in the Terms Query request has exceeded the allowed maximum of [65536]. This maximum can be set by changing the [index.max_terms_count] index level setting.\", ...

127 ES requests: 6 cursor pages over t1548_default_patient, 120 cursor pages over t1548_default_observation (the 5,891 patient refs are sent in batches, each batch drained with search_after), then one terms resource_id × 104,656 that ES rejects. The status is now 400 instead of 500, but the text blames the client's value, and the 34 s of paging are spent before the failure. On the full corpus any chain whose terminal set exceeds 65,536 Observations (e.g. subject:Patient.gender=…, subject:Patient.birthdate=ge…) hits it; the Parker433 set (~20 k) and the _has rows (the terminal set is Patients, ≤ 11,705) do not. Row 4.11 also shows the cost of reverse hops loading whole resources: 176 pages × 1,000 Observations for a 5-row answer (52.6 s).

I can re-run the same matrix rows with timings on this slice against whatever branch you push, and against plain mongo (same fixture, no ES) for total equality.

[angela-helios]

Thanks — that settles it: the 500 on 722a0927b was the offset-paged resolver, and #1501 (merged the same day this was recorded) already made every intermediate page cursor-driven, so rows 4.10 and 4.11 pass on current main. Re-scoping this issue to what your run shows is still there:

1. The terminal terms resource_id × N hits index.max_terms_count (65,536) and answers 400 with a message that blames the client's value. Fix on this branch: the Elasticsearch query builder chunks an id (or reference) list that exceeds the index's terms ceiling into several terms clauses under one bool.should, the same way search_contained already batches by max_result_window, with the ceiling as a config field next to max_result_window; and when Elasticsearch still refuses a query for a size limit, the error names the limit instead of calling the value malformed.
2. The 34 s spent paging before that failure and the 52 s of row 4.11 loading whole Observations for a five-row answer are the materialisation cost Decide the fate of the REST-unreachable per-backend chain builders (ChainedSearchProvider) #1341 discusses (per-backend chained providers); not in this PR, noted there.

I will push the branch and ask you for the same rows plus subject:Patient.gender=male against it and against plain mongo.

[angela-helios]

Branch pushed: fix/1548-es-chain-window (PR #1552). For the validation run on your fixture, against this branch and against plain mongo with the same data:

• the two matrix rows and the two controls from your table, with times;
• GET /Observation?subject:Patient.gender=male&_count=5 and …&_summary=count — expected now: 200, and the terms request split into two clauses (65,536 + 39,120) in your proxy log;
• GET /Observation?subject:Patient.gender=male&_id:not=<one of them>&_count=5 to see the negated split;
• one request with HFS_ELASTICSEARCH_MAX_TERMS_COUNT=1000 set, to see the same search go out as ~105 clauses and still answer 200 (and how long it takes).

Totals must match mongo's. Post the report here.

[angela-helios]

Addendum: totals against plain mongo (same fixture, no Elasticsearch)

Same 207,384-resource slice loaded into a second server (HFS_STORAGE_BACKEND=mongodb, port 8081) via $bulk-submit, both servers on main 0eb99c238. _summary=count on every row, one run each:

Request (&_summary=count)	mongo-es	mongo	equal
Observation?subject:Patient.family=Parker433	11,939 · 5.6 s	11,939 · 3.1 s	yes
Observation?subject.family=Parker433	11,939 · 4.9 s	11,939 · 2.8 s	yes
Patient?_has:Observation:patient:code=http://loinc.org|8302-2	11,704 · 57.9 s	11,704 · 163.5 s	yes
Observation?subject.identifier=http://hl7.org/fhir/sid/us-ssn|999-33-3920	165 · 0.09 s	165 · 0.15 s	yes
Patient?_has:Condition:patient:code=http://snomed.info/sct|706893006	5,293 · 2.0 s	5,293 · 1.5 s	yes
Observation?subject:Patient.gender=male	400 (max_terms_count, 36.1 s)	104,656 · 19.2 s	no

The _count=5 forms return the same first-page sizes on both (mongo reports no total on those, mongo-es does). So on current main the two matrix rows this issue is about agree with the ES-less backend; the one row that still diverges is the terms ceiling described above, which mongo does not have. Note the reverse chain costs 163 s on plain mongo versus 58 s on mongo-es for a 5-row answer, which is the #1452 "reverse hops load whole resources" cost rather than anything ES-specific.

This second server stays up for the day, so any branch can be compared row by row against it.

[mauripunzueta]

Build caveat: observed on 722a0927b (local main of 2026-09-17), which predates #1497 (#1451) and #1501 (#1452). origin/main's search_all_pages now pages by cursor where the backend offers one, which may already keep the Elasticsearch hop under max_result_window. Rebuilding on 0eb99c238 and re-running rows 4.10/4.11 of the matrix on the same 19 M-resource corpus; will close if they pass.

[angela-helios]

Status 2026-09-28 (end of day)

• Done: traced re-run on current main (every ES request logged): the max_result_window 500 is gone since Fix chain resolver paging for wide intermediate hops #1501; no page loses its cursor. Found the remaining ceiling, index.max_terms_count on the final terms query (Observation?subject:Patient.gender=male → 400 after 34 s). Totals of all rows compared against plain mongo on the same fixture: equal except that one.
• PR: fix(elasticsearch): split an id list past index.max_terms_count into several terms clauses (#1548) #1552 (angela-helios) validated on my mongo-es fixture with the default ceiling and with HFS_ELASTICSEARCH_MAX_TERMS_COUNT=1000: every row 200, totals equal to mongo, split clauses as designed; report on the PR.
• Left: fix(elasticsearch): split an id list past index.max_terms_count into several terms clauses (#1548) #1552 review/merge. The cost of materialising the terminal set (32 s for the gender=male rows, 50 s for row 4.11) stays with Decide the fate of the REST-unreachable per-backend chain builders (ChainedSearchProvider) #1341.