How ClickHouse makes Top-N queries faster with granule-level data skipping

ClickHouse enhances the efficiency of Top-N queries by utilizing advanced optimizations that significantly reduce data processing time and resource usage. These optimizations include streaming execution, which limits memory usage by only keeping current Top-N candidates; read-in-order, which avoids sorting by reading data in an already ordered state; and lazy reading, which defers the reading of non-order columns until necessary. A new technique involves using data-skipping indexes to skip granules entirely by relying on min/max metadata, reducing the rows processed before any data is read. This method, part of a broader strategy to treat Top-N queries as metadata-driven pruning problems, improves execution speed by 5× to 10× and decreases data read by orders of magnitude. It is particularly beneficial for large tables or when caches are cold, as it minimizes unnecessary reads, preserving computing and network resources. This approach allows ClickHouse to handle Top-N queries at scale effectively, maintaining high performance even with vast datasets while integrating seamlessly with existing query optimizations.