[Performance] rex + dedup is ~11× slower than an equivalent request-time derived-field query

[penghuo]

[Performance] rex + dedup is ~11× slower than an equivalent request-time derived-field query

Summary

For queries of the shape source=<index> | rex field=<f> "<pattern>" | dedup <extracted_field>, the current pushdown compiles the extracted column into an OpenSearch composite aggregation whose bucket key and top-hits fields are computed by invoking a REX_EXTRACT UDF via the plugin's compounded-script pipeline. On a 100k-document, single-shard, force-merged corpus, the PPL query takes ~3.6 s end-to-end (server-side took not reported for _ppl).

An equivalent raw _search that uses a request-time (search-time) derived field — declared inline in the _search body, no mapping change — completes the same workload in ~0.31 s server-side (took) — a ~11.5× speedup. Only the field actually referenced downstream (name1) is materialized; name2 is not projected.

Environment

• Branch: main (commit 7c430dda6, "Decouple Calcite PPL planning from ExprType")
• OpenSearch: 3.8.0-SNAPSHOT (integTest bundle via ./gradlew opensearch-sql:run)
• plugins.calcite.enabled=true
• Single node, single shard, single segment (force-merged), refresh_interval=-1
• Request/query cache cleared; solution path uses request_cache=false

Sample data (100k docs)

Each document is alice{k}@example{i}.com where k = i mod 500, so name1 has 500 distinct values (200 dupes each) and name2 is unique per doc. name2 is not referenced by the query, so only name1 needs to be materialized.

Index mapping — plain text field, no derived fields declared:

PUT /log_bench_a
{
  "settings": {"number_of_shards": 1, "number_of_replicas": 0, "refresh_interval": "-1"},
  "mappings": {"properties": {"v": {"type": "text"}}}
}

Bulk generator (100k docs):

N = 100_000
K = 500
for i in range(N):
    k = i % K
    doc = {"v": f"alice{k}@example{i}.com"}
    # bulk-index `doc` into log_bench_a

Sample rows:

_id	v
1	alice0@example0.com
2	alice1@example1.com
…	…
501	alice0@example500.com
100000	alice499@example99999.com

After indexing, force-merge to a single segment:

POST /log_bench_a/_forcemerge?max_num_segments=1

Both the baseline and the solution run against the same index — the solution does not require any mapping change.

Queries

Baseline — PPL rex + dedup:

POST /_plugins/_ppl
{
  "query": "source=log_bench_a | rex field=v \"(?<name1>.+)@(?<name2>.+)\" | dedup name1"
}

Solution — raw _search with a request-time derived field for name1 only, composite over name1, top_hits fetching v and name1. This is the request the planner would emit if it recognized the rex pattern and materialized only the referenced group as a request-time derived field:

POST /log_bench_a/_search?request_cache=false
{
  "size": 0,
  "derived": {
    "name1": {
      "type": "keyword",
      "script": {
        "source": "def m = /(?<name1>.+)@(?<name2>.+)/.matcher(params._source.v); if (m.find()) emit(m.group(1));"
      }
    }
  },
  "aggs": {
    "by_name1": {
      "composite": {
        "size": 10000,
        "sources": [{"name1": {"terms": {"field": "name1"}}}]
      },
      "aggs": {
        "top": {
          "top_hits": {"size": 1, "fields": ["v", "name1"], "_source": false}
        }
      }
    }
  }
}

Both queries produce the same 500 dedup groups with a representative v per group.

Results

Three cache-cleared runs per path. Baseline took is not reported by the PPL endpoint; only end-to-end is available.

Path	End-to-end (ms)	Server took (ms)
Baseline — PPL rex + dedup	3548 / 3626 / 3710 (avg 3628)	n/a
Solution — request-time derived name1 + composite + top_hits	393 / 401 / 413 (avg 402)	308 / 311 / 324 (avg 314)
Speedup	~9.0× end-to-end	~11.5× on took

[penghuo]

The ~11× speedup does not come from derived fields eliminating work. It comes from Painless being roughly 13× cheaper per script invocation than the current opensearch_compounded_script pipeline. Derived fields are just one way to reach Painless; a plain script_fields on Painless gets the same win.

Method

Three DSL bodies, run directly against _search (no PPL), same 100k-doc corpus (log_bench_a), same composite/top_hits shape, same regex logic. Only the script language differs.

A — current pushdown (opensearch_compounded_script + REX_EXTRACT UDF)

{
  "size": 0,
  "aggregations": {
    "composite_buckets": {
      "composite": {"size": 10000, "sources": [{
        "name1": {"terms": {
          "script": {"source": "<serialized RexNode wrapper>", "lang": "opensearch_compounded_script", "params": {...}},
          "missing_bucket": false
        }}
      }]},
      "aggregations": {
        "top": {"top_hits": {"size": 1, "_source": false, "fields": [{"field": "v"}],
          "script_fields": {"name1": {"script": {"source": "<same wrapper>", "lang": "opensearch_compounded_script", "params": {...}}}}
        }}
      }
    }
  }
}

C — same shape, script language swapped to Painless inline

{
  "size": 0,
  "aggregations": {
    "composite_buckets": {
      "composite": {"size": 10000, "sources": [{
        "name1": {"terms": {
          "script": {"lang": "painless",
                     "source": "def m = /(?<name1>.+)@/.matcher(params._source.v); return m.find() ? m.group(1) : null;"},
          "missing_bucket": false
        }}
      }]},
      "aggregations": {
        "top": {"top_hits": {"size": 1, "_source": false, "fields": [{"field": "v"}],
          "script_fields": {"name1": {"script": {"lang": "painless",
            "source": "def m = /(?<name1>.+)@/.matcher(params._source.v); return m.find() ? m.group(1) : null;"}}}
        }}
      }
    }
  }
}

B — request-time derived field (Painless, same composite + top_hits shape)

{
  "size": 0,
  "derived": {"name1": {"type": "keyword",
    "script": {"source": "def m = /(?<name1>.+)@/.matcher(params._source.v); if (m.find()) emit(m.group(1));"}}},
  "aggregations": {
    "composite_buckets": {
      "composite": {"size": 10000, "sources": [{
        "name1": {"terms": {"field": "name1", "missing_bucket": false}}
      }]},
      "aggregations": {
        "top": {"top_hits": {"size": 1, "_source": false, "fields": [{"field": "v"}, {"field": "name1"}]}}
      }
    }
  }
}

All three return the same 500 buckets with correct name1 and a representative v per bucket.

Results

Six cache-cleared runs per path (request_cache=false, _cache/clear between paths). Server-side took:

Path	Script lang	took avg (ms)	End-to-end avg (ms)	Ratio vs A
A	opensearch_compounded_script (REX_EXTRACT UDF)	3487	3577	1.00×
C	painless (inline script_fields, same DSL shape as A)	272	361	~12.8× faster
B	painless (via request-time derived field)	286	377	~12.2× faster

C vs B: within ~5%. The derived:{} wrapper adds negligible overhead over an inline Painless script.

Script-invocation count is essentially identical across all three paths (~200,000/run — verified for A by instrumenting RexExtractFunction.executeExtraction and grepping opensearch.stdout.log; inferred for B/C from earlier timing-scaling proof). So the speedup is not from fewer script calls — it's from cheaper per-call cost.

[penghuo]

Correction — the 11–13× numbers were wrong; real gap is ~1.2×

Retracting numbers from this issue and my earlier comment. To count REX_EXTRACT invocations I had added System.out.println in RexExtractFunction.executeExtraction. That println fired ~200,500 times per Path A query and dominated latency via log4j write (CPU profile: 54% write, 6% deflate_slow). Path C uses Painless, was never contaminated.

Clean re-measurement

Restarted OpenSearch with clean source. Same 100k-doc corpus, same DSL shape, only script language differs. Six cache-cleared runs each:

Path	Script lang	avg took
A	opensearch_compounded_script	264.5 ms
C	painless (inline)	214.0 ms
Ratio	—	1.24×

What still stands

Planner defects visible in the physical plan are independent of script language and are the real wins:

• dead LITERAL_AGG(1) pushed down but never consumed
• top_hits.script_fields.name1 recomputes the composite bucket key (K redundant calls)
• unreferenced rex groups (name2 when only name1 is used) still materialized
• IS NOT NULL(<rex-group>) becomes an effective double composite pass; should be missing_bucket:false alone

Combined, these cut invocations from ~200,500 to ~100,000 per query — a ~2× win independent of engine.