Top-K Query Acceleration

Dremio automatically accelerates eligible Top-K queries on Apache Iceberg tables. A Top-K query uses ORDER BY with LIMIT to return only the first K rows in sorted order.

Dremio skips scanning data whose metadata proves that it cannot contribute a row to the result. Such pruning occurs at runtime and can reduce the amount of data the query scans.

Dremio applies Top-K runtime pruning automatically to eligible queries over native Apache Iceberg table scans. The optimization does not change query results.

How Top-K Runtime Pruning Works

While a Top-K query runs, Dremio holds an intermediate Top-K result: the K rows that sort first among the rows read so far. The bound of that result, its maximum value for an ascending sort or its minimum value for a descending sort, is the boundary value. Any row that sorts after the boundary value cannot belong to the final result.

Iceberg and Parquet metadata can record the minimum and maximum value of a column: Iceberg for each data file, and Parquet for each row group and each column page within a data file. Dremio sends the boundary value to the scans while the query is still running and compares it against that metadata. When the minimum and maximum prove that every row in a data file, row group, or page sorts after the boundary value, Dremio skips it without reading it.

For example, in an ascending sort, Dremio skips a row group whose minimum value is greater than the boundary value.

Dremio applies this comparison at four levels of metadata, from coarsest to finest:

• Manifest files — Dremio skips a manifest file, and every data file it lists, using the range of partition values that the table metadata records for that manifest. This level applies only when the first ORDER BY column is an identity partition column of the table.
• Data files — Dremio skips a data file using the minimum and maximum recorded for the column, or using the file's partition values.
• Parquet row groups — Dremio skips a row group inside a data file using the minimum and maximum recorded for the column in that row group.
• Parquet column pages — Dremio skips a page inside a row group using the minimum and maximum that the column index records for that page.

Scans do not wait for a boundary value. If a boundary has not arrived, the metadata does not contain usable bounds, or Dremio cannot evaluate the bounds, the scan continues without that pruning. Dremio retains data whose bounds equal the boundary value so that rows tied at the boundary remain available to the query.

Factors That Affect Pruning

Dremio can skip data only when the bounds recorded for the first ORDER BY column are narrow enough to rule it out entirely. How narrow those bounds are depends on how the table stores its rows, so pruning saves more work on some tables than on others.

Top-K runtime pruning is most effective when the first ORDER BY column is also an identity partition column or a clustering key of the table. Both layouts store rows with similar values together, which narrows the bounds recorded at every level, so one boundary value rules out many files at once. An identity partition column has a further advantage: it is the only case in which Dremio can prune at the manifest level, ruling out every data file in a manifest from a single comparison.

Two other conditions affect how much work pruning saves:

• K is small compared to the number of rows in the table.
• The scan produces rows that sort early, which makes a selective boundary value available sooner.

Pruning saves little or no work when the first ORDER BY column is unrelated to the table layout, because the bounds of most data files then overlap. The same is true when the metadata records no bounds for the column, when the query returns fewer than K rows, or when the query is already fast.

If Top-K queries on a column matter to your workload, consider partitioning or clustering the table on that column. Clustering can avoid the many small files that partitioning a high-cardinality column, such as a timestamp, can produce. For details, see Clustering and Automatic Optimization.

Query Eligibility

Dremio accelerates a Top-K query when all of the following are true:

Requirement	Details
Query shape	The query has both ORDER BY and LIMIT.
Limit size	LIMIT is from 1 through 1,000. If the query also has OFFSET, the sum of OFFSET and LIMIT must not exceed 1,000.
First sort column	The first ORDER BY item names a column directly, rather than an expression, function call, or cast.
Column source	Dremio reads that column from a native Apache Iceberg table. Between the scan and the sort, the query can filter rows and project the column through unchanged. Other operations, such as aggregation, DISTINCT, and UNION, make the query ineligible.
Data type	The column uses one of the supported data types.
Null ordering	If the column allows null values, the sort places null values last.
Joins	If the column comes from a join, the join is a hash join and the column comes from its probe side. Dremio reads the build side in full before the join produces any rows, so no boundary value exists in time to prune that side.

Additional ORDER BY items are allowed, and they determine the order of rows that share the same first-column value. Only the first item drives pruning.

A query that does not meet these requirements runs normally, without pruning. Dremio decides this from the query plan, so you can confirm the outcome for a specific query in its profile.

Supported Data Types

The first ORDER BY column can use any of the following Dremio data types:

• DECIMAL
• INT
• BIGINT
• FLOAT
• DOUBLE
• VARCHAR
• DATE
• TIMESTAMP

Top-K runtime pruning does not support the other Dremio data types: VARBINARY, UUID, BOOLEAN, TIME, INTERVAL, VARIANT, STRUCT, ARRAY, and MAP.

Null Ordering

ASC places null values last by default, and DESC places them first. So for a first ORDER BY column that allows null values:

• ASC, ASC NULLS LAST, and DESC NULLS LAST are eligible.
• DESC and any order with NULLS FIRST are not.

When null values sort first, they come ahead of every non-null value. A data file that holds even one null value might therefore contain a row that belongs in the result. The boundary value is always a non-null value, and the recorded bounds do not say whether a file holds null values, so Dremio cannot rule that file out.

A column that does not allow null values can use any sort order.

Eligible Query Example

The following query returns the 100 most recent completed orders from an Iceberg table:

Top-K query on an Iceberg table

SELECT
  order_id,
  customer_id,
  order_timestamp,
  net_amount
FROM lakehouse.sales.orders
WHERE order_status = 'COMPLETE'
ORDER BY order_timestamp DESC NULLS LAST, order_id DESC
LIMIT 100;

The order_timestamp column drives pruning. The order_id column orders rows that share the same timestamp, but it does not add pruning of its own.

Confirm Top-K Runtime Pruning in a Query Profile

The Planning view in a job's Raw Profiles includes Final Physical Transformation. For an eligible plan, it shows a topn_boundary runtime filter on the Iceberg scan.

In the operator metrics, compare the applicable checked and pruned counts:

• TOPN_RUNTIME_MANIFESTS_CHECKED and TOPN_RUNTIME_MANIFESTS_PRUNED
• TOPN_RUNTIME_DATA_FILES_CHECKED and TOPN_RUNTIME_DATA_FILES_PRUNED
• TOPN_RUNTIME_PARTITIONS_CHECKED and TOPN_RUNTIME_PARTITIONS_PRUNED
• TOPN_RUNTIME_ROW_GROUPS_CHECKED and TOPN_RUNTIME_ROW_GROUPS_PRUNED

The TopN operator also reports TOPN_RUNTIME_PRODUCER_BOUNDARY_UPDATES when it updates a boundary and TOPN_RUNTIME_PRODUCER_OOB_SENDS when it sends boundary filters to scans.

A query can be eligible and still prune nothing. This happens when a scan finishes before a boundary value reaches it, or when the bounds never prove that data can be skipped.