[FEATURE] Fine-grained implicit sorting

[LantaoJin]

Is your feature request related to a problem?

1. When the index.sort.field and index.sort.order settings are specified, Ref link, implicit sorting by the fields of index.sort.field.

2. Data stream is time-series data such as logs, events, and metrics into OpenSearch. Ref link. Time series based workload sort optimization through Lucene segment sorting.. Implicit sorting for data stream could be specified to @timestamp instead of _id or _doc.

What solution would you like?
If an index contains index.sort.field, implicit sorting by index.sort.field.
If an data stream contains @timestamp, implicit sorting by @timestamp.
Excepts, keep as is sorting by _id + _doc

Do you have any additional context?
#3064

[penghuo]

similar issue, #3925 (comment)

@selsong is helping verify sort by [_doc, @timestamp] with Big5 dataset.

[LantaoJin]

similar issue, #3925 (comment)

@selsong is helping verify sort by [_doc, @timestamp] with Big5 dataset.

The big5 contains @timestamp field, for others, opensearch-project/OpenSearch#18924 should resolve our problem.

[selsong]

Thanks for the context! I set up a 1TB Big5 test environment on 3 EC2 nodes (balanced with ~150GB per node) to benchmark performance for implicit sorting by _doc vs. @timestamp.
I used this query:
source=big5 | head [number of rows] | reverse
Results: See #3925 for detailed performance table.

• Default behavior: sorting is by _doc AND _id. Works only for 10K rows; fails for larger row counts.
  I compared only sorting by _doc and only sorting by @timestamp:
• Up to 50K rows: both _doc and @timestamp succeed, with @timestamp slightly faster (avg ~1.8–3.7s vs. _doc ~1.9–4.1s).
• 100K rows: _doc is slower (~9.0s avg) but stable. @timestamp appears faster (~2.4s avg), but 2 out of 3 runs failed with backend resource errors.
• 200K rows: both _doc and @timestamp hit resource errors.

This aligns with the proposal that implicit sorting by index.sort.field (or @timestamp for data streams) could improve performance for typical time-series workloads, while still allowing _doc sorting as a fallback for heavy scans.