[FEATURE] Improve PIT usage for large queries over w... | opensearch-project/sql

Is your feature request related to a problem?

The plugin creates a PIT whenever a non-aggregate request needs more rows than index.max_result_window (default 10,000). When the query uses a wildcard index pattern, OpenSearch expands the wildcard and opens one reader context per matching shard, so a broad pattern over hundreds of daily indices can exhaust the per-node search.max_open_pit_context limit (default 300) and the query fails:

Trying to create too many point in time contexts. Must be less than or equal to: [300].

Examples

Two common query shapes trigger it:

Explicit large limit: source=logs-* | head 100000 - the limit is pushed into the scan and exceeds the window, so a PIT is created and ten pages are fetched.
Unbounded scan: source=logs-* | streamstats count() as seen - no explicit limit, so the plugins.query.size_limit cap sits above an operator it cannot be pushed through. The scan is left unbounded and a PIT is created, but only 10,000 rows are returned and no second page is ever requested, so the snapshot serves no purpose.

What solution would you like?

Option	Approach	Pros	Cons	Notes
1. Predicate-based index pruning	Narrow the wildcard to only the indices that can match, then open the PIT over that set. Snapshot semantics unchanged.	No consistency change. Smallest change. Works even for shapes that cannot avoid a PIT.	Reimplements pruning core already does, and misses core's ongoing work in this area.	Core could instead expose `can_match`, or new index/field-level stats, as an internal API. See opensearch-project/OpenSearch#21865, #22483, #22451.
2. Incremental execution	Split the resolved index set into batches and scan batch by batch, opening and closing one PIT per batch.	Bounds context count regardless of how broad the pattern is. Complements option 1 when many indices still match after pruning.	Snapshot is per batch rather than global. More PIT create/delete calls and longer wall-clock time.	Could later extend to progressive result delivery, returning rows as each batch completes.
3. Stateless pagination	Drop the PIT and page with a value-based `search_after` cursor. Each page is a plain search holding no server state.	Nothing accumulates against the cap. Every page gets `can_match` and coordinator pruning automatically.	No cross-page snapshot, so concurrent writes may cause missed or duplicated rows. Needs a stable, unique sort key, and none is both cheap and globally unique.	Known as keyset pagination. `_shard_doc` requires a PIT (opensearch-project/OpenSearch#18924); `_seq_no` is the closest alternative but is unique only per shard. Paginate docs
4. Full pipeline pushdown	Compile the whole pipeline into one search so the cluster returns a finished result — no pagination, like aggregation queries today.	Removes the failure mode entirely. Fixes a far broader translation gap than this issue.	Largest effort. Feasibility unverified, and coverage can never be complete, so a fallback is still needed.	#3879 and #5646 are prior art for widening pushdown coverage. Scripted metrics are a possible escape hatch for pipelines with no aggregation equivalent.
5. New search primitive	Add the missing primitive in core: a PIT scoped by predicate, or a search that owns its own pagination state.	Clean for every client, not just SQL/PPL.	A core contribution, so timeline and effort are unknown.	`CreatePitRequest` has no query body or can-match phase today, and no upstream proposal exists. opensearch-project/OpenSearch#22530 is a precedent for adding a can-match phase to an engine.

What alternatives have you considered?

Mitigations and adjacent directions considered, none of which we treat as a fix:

Alternative	Effect	Drawback
Raise `search.max_open_pit_context`	Query untouched; more contexts permitted.	Each context pins segment readers and blocks merged-segment deletion.
Raise `index.max_result_window` and use `from + size`	Avoids the PIT entirely.	Every matching shard then returns up to `size` top hits for coordinator-side reduction, a far larger memory spike than paged reads.
Request fewer rows than `max_result_window`	No PIT; the query succeeds immediately.	Truncates the input to downstream row-consuming operators, so results change.
Fewer primary shards for new indices	Cuts fan-out as indices roll over.	Only affects indices created afterwards, so it does not resolve an active failure.
Precompute with rollups or transforms	Removes the query shape entirely for recurring dashboards.	Only suits known, repeated queries, and adds a pipeline to maintain.
Offload to the async query path	Sidesteps coordinator limits for very large fetches.	Changes the interaction model to submit-and-poll; not a drop-in for dashboard traffic.

Do you have any additional context?

Related work in this repo:

#3879 avoided PIT for queries under max_result_window — prior art for the pushdown direction.
#5634, #5220 are symptom reports of the same failure.
#5631 surfaces the exhaustion with an actionable error message.

[FEATURE] Improve PIT usage for large queries over wildcard index patterns

Description

Examples

Contributor guide

First steps

Research direction

Assessment

[FEATURE] Improve PIT usage for large queries over wildcard index patterns

Description

Examples

Contributor guide

First steps

Research direction

Assessment

Get new issues in your inbox