Add time series support to compute engine

[martijnvg]

This is the meta issue that tracks the work to be done to the compute engine in order to power time series support. This for now at least doesn't include the language changes to ES|QL. The compute engine components should only be active via enabling specific query pragmas, until the time series compute engine components are more stable and the es|ql language is ready to adopt it.

General overview

(an overview of how time series aggregation can work in the compute engine (assuming all time series don't cross backing index boundary))

The idea is that a new source operator will emit all matching document in time series order (_tsid ascending, @timestamp descending). Documents are sorted in that order at the segment level, but not at the shard level. A page will additionally also include tsid and timestamp blocks. Documents of the same time serie should be contained by the block. A new time series grouping operator will make use of the sorted nature of the pages that the source operator emits and groups by tsid or tsid and timestamp interval. The output of this operator can be used by other operates such as the HashAggregationOperator.

Sometimes not all samples or a time series are in the same shard. This can happen when a query targets multiple backing indices of a tsdb data stream. In this case we need for the affected time series post pone grouping in the new time series grouping operator. The new time series grouping operator needs to group these time series on the coordinating node (when the aggregation mode is final in AggregateExec). Initially we will build a time series grouping operator that assumes that time series are always scattered across multiple backing indices and thus performs the grouping when the aggregate mode is final. In follow ups, we can then improve the new time series grouping operator to detect when time series don't cross backing index boundaries. In that case the grouping can perform locally, when aggregation mode is partial.

Initially we will only allow filtering on dimension fields. More specifically the filters that get pushed down to the time series source operator. If filters on labels or metrics get pushed down to the source operator we run at risk of breaking the ordered samples of a time serie apart.

Tasks

• Add a time series source operator, that gets activated when a time serie pragma is enabled. (Add time series source operator. #105398)
• Implement time series grouping. #106411
• Update the grouping operator to perform grouping can happen locally when ever the query allows for it. For example a @timestamp filter in WHERE clause doesn't cross the boundary of a backing index. Or timestamp interval group is contained within a backing index.
• Update Aggregator and GroupingAggregator interfaces to accept sorted pages/blocks. #106414
• Enhancing the time serie grouping operator to also group by time series and time interval. A typical use case would group by time serie and time interval. This is when the BUCKET syntax is used.
• Integrate the time series operators in the proposed TSTATS syntax.
• Develop an alternative parallelization strategy for tsdb indices in the time serie source operator.
• Add es|ql rate aggregate function #106415
• Add support of sparse index to easily navigate a time series documents (Sparse index for tsdb #95701). This is required for determining the last value of a metric and skipping to the next last value of the next time serie. And other functionally like interpolation and geo fencing. Additionally a query may be too selective, and mask documents which are valid metric of a time serie. A sparse index would allow us to access the metrics even if that would be the case.
• Add support for aggregate_double_metric field type in es|ql in order to support downsampling. Add support for aggregate_metric_double field in es|ql #110649
• Add support for _doc_count field in es|ql in order to support downsampling.
• Query planning should understand the index.time_series.start_time and index.time_series.end_time index settings, so that backing indices that will never match with the ES|QL query will be excluded from the query execution. (This is based on where filter on @timestamp field.)

Optional:

• Ordinal-based BytesRef block for TSID Ordinal-based BytesRef block for TSID #106387
• ...

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[elasticsearchmachine]

Pinging @elastic/es-storage-engine (Team:StorageEngine)