Amazon Redshift introduces key performance optimization for Top-K queries

Amazon Redshift further optimizes the processing of top-k queries (queries with ORDER BY and LIMIT clauses) by intelligently skipping irrelevant data blocks to return results faster, dramatically reducing the amount of data processed. This optimization reorders and efficiently adjusts the data blocks to be read based on the ORDER BY column's min/max values, maintaining only the K most qualifying rows in memory. When the ORDER BY column is sorted or partially sorted, Amazon Redshift now processes only the minimal data blocks needed rather than scanning entire tables, eliminating unnecessary I/O and compute overhead.

This enhancement particularly benefits top-k queries when the data permanently stores in descending order (ORDER BY ... DESC LIMIT K) on large tables where qualifying rows are appended at the end of the data storage. Common examples include:

• Finding the k most recent orders from millions or billions of transactions
• Retrieving top-k best performing products or k worst performing products (top-k in descending order) from your sales catalog containing hundreds of thousands stock keeping units (SKUs) and millions or billions of sales transactions associated with all product SKUs in your sales catalog
• Finding the top-k most recent or top-k oldest (top k in descending order) prompts inferred by a foundational large language model (LLM) out of billions of prompts.

With this new optimization, top-k query performance improves dramatically. This optimization for top-k queries is now available in Amazon Redshift at no additional cost starting with patch release P199 across all AWS regions where Amazon Redshift is available. This optimization automatically applies to eligible queries without requiring any query rewrites or configuration changes.