Accelerating TopK Queries
summary
Snowflake adds runtime pruning for TopK SQL by taking the boundary from sort-with-limit during execution and comparing it to per-partition minmax + nulls metadata in the table scan to skip entire partitions before downloading them. It supports predicates but not Group By aggregations because aggregations are pipeline breakers and aggregated values lack metadata. Tests and customer reports show extreme wins, sometimes up to 500x faster, with early regressions fixed; the metadata already exists so no extra storage is required and the feature is enabled by default.
AI-written summary · about
chapters (8)
- 0:00Introduction
- 0:41Snowflake architecture
- 2:38The pruning mechanism
- 4:56Top-K query optimization
- 8:47Refining dynamic pruning
- 11:40Constraints and limitations
- 14:03Performance results
- 16:24Q&A and conclusion
related talks
26mGraph Analytics in Snowflake: Beyond Traditional SQL
39mColumnar Table Performance Enhancements Of Greenplum Database
29mScaling Python-Powered Machine Learning With Snowflake
38mAccelerating Query Performance in CrateDB
13mLightning Talk: Highlights of the Snowflake Elastic Data Warehouse
35mAccelerating PostgreSQL Full Table Scan OLAP queries
24mCracking the Cost Code: A Cost Optimized Approach to Distributed...
28mBuilding A Governed Data Lake