Window

Describes the window relational operator in the multi-stage query engine.

The window operator is used to define a window over which to perform calculations.

This page describes the window operator defined in the relational algebra used by multi-stage queries. This operator is generated by the multi-stage query engine when you window functions in a query. You can read more about window functions in the windows functions reference documentation.

Unlike the aggregate operator, which will output one row per group, the window operator will output as many rows as input rows.

Implementation details

Window operators take a single input relation and apply window functions to it. For each input row, a window of rows is calculated and one or many aggregations are applied to it.

In general window operator are expensive in terms of CPU and memory usage, but they open the door to a wide range of analytical queries.

The operator also supports SQL EXCLUDE frame modifiers for SUM, COUNT, AVG, MIN, MAX, BOOL_AND, BOOL_OR, FIRST_VALUE, and LAST_VALUE. Pinot supports EXCLUDE NO OTHERS (default), EXCLUDE CURRENT ROW, EXCLUDE GROUP, and EXCLUDE TIES on supported ROWS frames and supported RANGE frames. Ranking functions and LAG / LEAD do not accept explicit frame clauses, so they also do not accept EXCLUDE.

If users expect non-default EXCLUDE behavior during a rolling upgrade, upgrade servers before brokers. Older servers fall back to the default EXCLUDE NO OTHERS.

Polymorphic Window Function Aggregators

As of apache/pinot#18169, the multi-stage query engine uses type-aware window function aggregators for improved precision:

• SUM aggregator: For INT/LONG types, a specialized aggregator avoids precision loss when summing values beyond 2^53. BIG_DECIMAL SUM preserves full decimal precision.

• MIN/MAX aggregators: Primitive-backed implementations for INT/LONG types use fastutil sliding-window queues for efficient computation.

These optimizations ensure window functions maintain numeric precision across all supported data types.

Blocking nature

The window operator is a blocking operator. It needs to consume all the input data before emitting the result.

Hints

Window hints are configured with the windowOptions hint, which accepts as argument a map of options and values.

For example:

SELECT
/*+  windowOptions(option1='value1', option2='value2') */
    col1, SUM(intCol) OVER() as sum FROM table

is_partitioned_by_window_keys

Type: Boolean

Default: planner chosen

Use this hint on window queries with a PARTITION BY clause to control whether Pinot treats the input as already partitioned by the window keys. It applies to both PARTITION BY windows and PARTITION BY ... ORDER BY ... windows.

• true: Force a pre-partitioned exchange under the window and avoid a shuffle.

• false: Disable auto-detected pre-partitioning and force a regular shuffle.

If the input table is explicitly partitioned by the same key, prefer declaring that at the table scan with tableOptions(partition_function='hashcode', partition_key='user_id', partition_size='4'). Pinot can then infer the pre-partitioned plan without forcing it.

Use is_partitioned_by_window_keys='true' when you know the input is already partitioned by the window's PARTITION BY keys but Pinot cannot infer it, such as with implicit partitioning or when comparing plans during query tuning. Use is_partitioned_by_window_keys='false' when you want to disable inferred pre-partitioning for debugging or benchmarking.

Caution: Only set is_partitioned_by_window_keys='true' when the data is actually partitioned by the window keys. If matching rows are split across workers, Pinot will evaluate separate partial windows and return incorrect results.

Example:

SELECT
/*+ windowOptions(is_partitioned_by_window_keys='true') */
    user_id,
    event_time,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC)
FROM events

Disable the pre-partitioned plan and fall back to a shuffle:

SELECT
/*+ windowOptions(is_partitioned_by_window_keys='false') */
    user_id,
    SUM(metric) OVER (PARTITION BY user_id)
FROM events /*+ tableOptions(partition_function='hashcode', partition_key='user_id', partition_size='4') */

max_rows_in_window

Type: Integer

Default: 1048576

Max rows allowed to cache the rows in window for further processing.

window_overflow_mode

Type: THROW or BREAK

Default: 'THROW'

Mode when window overflow happens, supported values:

• THROW: Break window cache build process, and throw exception, no further WINDOW operation performed.

• BREAK: Break window cache build process, continue to perform WINDOW operation, results might be partial.

Stats

executionTimeMs

Type: Long

The summation of time spent by all threads executing the operator. This means that the wall time spent in the operation may be smaller that this value if the parallelism is larger than 1. This number is affected by the number of received rows and the complexity of the window function.

emittedRows

Type: Long

The number of groups emitted by the operator. A large number of emitted rows can indicate that the query is not well optimized.

Unlike the aggregate operator, which will output one row per group, the window operator will output as many rows as input rows.

maxRowsInWindowReached

Type: Boolean

This attribute is set to true if the maximum number of rows in the window has been reached.

Explain attributes

The window operator is represented in the explain plan as a LogicalWindow explain node.

window#

Type: Expression

The window expressions used by the operator. There may be more than one of these attributes depending on the number of window functions used in the query, although sometimes multiple window function clauses in SQL can be combined into a single window operator.

The expression may use indexed columns ($0, $1, etc) that represent the columns of the virtual row generated by the upstream.

Tips and tricks

None