[RFC] Extend Analytics Engine execution to regular indices for analytical SQL/PPL queries

[dai-chen]

Problem Statement

Today SQL/PPL compiles a Calcite relational plan into Query DSL, which causes three coupled problems:

1. Translation complexity. A relational plan is a composable operator tree; DSL is one flat, fixed-shape request. Translation therefore has to collapse a whole subtree into a single document, and that mismatch is absorbed by two large analyzers (~3,000 lines) re-encoding every expression form and mapping quirk. The script engine only helps expressions but can never add an operator DSL lacks.

2. Performance cliff. Whatever cannot be expressed — a missing rule, or a genuine DSL limitation — silently falls back to shipping raw documents to the coordinator ([BUG] eventstats does not push down to OpenSearch (RexOver excluded from aggregation pushdown) #5483, [BUG] Simple top queries on high-cardinality fields are very slow #5673). Not a graceful slowdown, and invisible to the user. Coverage is combinatorial and hard to test ([RFC] A scalable way to test PPL command combinations and pushdown #5598).

3. PIT exhaustion ([FEATURE] Improve PIT usage for large queries over wildcard index patterns #5698). A query that falls off the pushdown path needs more than index.max_result_window rows, so the plugin opens a PIT per matching shard; a wildcard pattern exhausts the 300-context cap ([BUG] Non-pushdownable stats/eventstats silently forces a full-scan PIT and fails with an opaque error #5634). This is a side effect of (2), not an independent defect.

Current State

PPL/SQL → Calcite RelNode → pushdown rules → a single OpenSearchIndexScan carrying DSL (plus optional script), with the residual plan executed on the coordinator. An Analytics Engine path already exists, but it is gated to composite (Parquet-backed) indices, so a query over a regular index never reaches it and always compiles to DSL. Therefore, DSL is the only vehicle for shard-local work; there is no way to execute an arbitrary relational fragment on a data node.

Long-Term Goals

A query's capability should be determined by its plan, not by what Query DSL can express — adding a PPL command should not require inventing a translation for it. Over time, pushdown coverage should stop being a complexity this plugin maintains alone and instead improve as the shared execution layer improves.

Proposal

Instead of routing queries for composite index only, use the OpenSearch Analytics Engine as a second execution path for analytical queries on Lucene index too, coexisting with the current DSL pushdown path rather than replacing it. Route by plan shape, gated by a plugin setting: the fast DSL path keeps serving the queries it already serves well, and only shapes DSL handles badly — partitioned window operators, unbounded scans, deep multi-stage pipelines — go to AE.

• Pros: The plugin inherits Lucene-level optimizations it does not have to write, plus AE's own execution work — multi-stage DAGs in [Analytics-Engine] MPP framework: supporting multi-stage distributed join and aggregation OpenSearch#21844, Arrow streaming transport in Stream Arrow batches on the data-node fragment execution path OpenSearch#21418, vectorized execution — and everything added to either in the future. The coverage problem changes shape: instead of translating N operators into DSL, we execute a plan, so new commands work by default rather than after a bespoke rule.
• Cons: It takes a dependency on AE's maturity: stability and reliability are unproven for this workload. Native execution moves memory off the JVM heap, so overhead, accounting and failure modes differ from the search path. Two execution paths also means two behaviors to keep semantically identical, and any fix on the AE side needs a core release.

Approach

M1 — AE reads a plain Lucene index. AE today only reads composite (Parquet-primary) indices. Add a doc-values scan capability so a plain shard is a value-producing source: implement reader acquisition, declare the capability, and scope it so composite indices are unaffected.

M2 — distributed DAG cut rules for the operators that matter. Extend the planner beyond join and aggregation. For example, add high-value subtree rules: per-group top-k for dedup (exact by rank monotonicity; cf. Spark InsertWindowGroupLimit/SPARK-37099, Trino TopNRankingNode.partial, ClickHouse LIMIT n BY), and agg-then-annotate for eventstats (Window(count OVER (PARTITION BY k)) ≡ Join(rows, Aggregate(count BY k)), moving only the small grouped result — top-k pushdown does not apply here, since eventstats retains every row).

M3 — routing. Enable AE as a selectable path in the plugin: relax the composite-index gate in RestUnifiedQueryAction, extend AnalyticsExecutionEngine wiring to admit plain indices, add the plan-shape rules that choose AE vs DSL, and put it behind a setting, default off.

Dependencies: M1 → M3. M2 is what makes routing worthwhile, but a narrow initial rule set can ship first.

Alternatives

(A) A new MPP layer inside the SQL plugin.

• Prior art: [RFC] MPP SQL/PPL Engine for OpenSearch #5380 (closed) and [RFC] OLAP—A OS SQL Query Solution Plugin #4812 (open)
• Pros: full control, no core dependency or core release timeline.
• Cons: largest effort, duplicates work core has already merged, and the hard part is not the engine but re-earning Lucene's optimizations plus the security/FGAC, thread-pool and memory-isolation story core already owns.

(B) A custom DSL aggregation as the shard-local vehicle.

• Ships the shard fragment as base64 RelJson inside a plugin-registered aggregation, executes a Janino-compiled Calcite Bindable over doc_values with _source fallback, and reduces in two tiers at the coordinator.
• Pros: works on an ordinary index today with no core change.
• Cons: an aggregation is an unnatural carrier for row-producing operators; custom aggregation may have its limitation; no shuffle.

(C) Keep widening DSL pushdown rule by rule

• Mitigate [FEATURE] Improve PIT usage for large queries over wildcard index patterns #5698 with one of its own narrower options.
• Pros: incremental, low risk per step.
• Cons: the coverage problem and the cliff both remain; the PIT fix treats the symptom.

Implementation Discussion

• What the routing predicate keys on — plan shape, estimated cardinality, presence of a non-pushable operator, or an explicit user hint.
• Setting scope and lifecycle: cluster, index, or per-request; what evidence would promote the default from off to on.
• How semantic parity between the paths is guaranteed and tested — same query, same result, on both paths.
• What happens when a routed query fails or is rejected: transparent fallback to the DSL path, or a surfaced error.
• Whether users can tell which path ran, e.g. in explain output.
• The minimum capability set that makes routing worthwhile for a first release, and which PPL commands it covers.