An Overview of the Starburst Trino Query Optimizer
summary
History and specifics of Trino's optimizer: engine evolves from expensive transformation passes to a fast rule-based planner, adds a cost-based optimizer in 2017 using CPU-weighted cost, full join reordering and bushy trees, and runtime dynamic partition pruning and dynamic filtering that cut some federated queries from hours to minutes by skipping partitions and waiting briefly before issuing remote SQL. Connectors (Parquet/Hive/AWS Glue/Oracle) get operator, projection and aggregation pushdowns; prototypes for materialized-view caching and fault-tolerant multi-stage planning are under development.
AI-written summary · about
chapters (28)
- 0:00Start
- 5:23New Way: Single Point of Access
- 6:26Architecture
- 7:40Data Source Connectors
- 8:07Federation is challenging
- 11:36Early state (2016)
- 16:16New data types and syntax support (2016)
- 17:01Rule based optimizer (2016) support (2016)
- 20:05CBO (2017)
- 21:15Filter estimation
- 29:47Join type selection
- 30:19Join left/right side decision
- 31:02Join reordering with filter
- 31:43Benchmark results with CBO
- 33:10Assume distinct values match
- 33:51Assume predicates are correlated
- 34:46Fallback to table size for join type/side selection
- 36:20Dynamic filtering (2019-...)
- 37:26Node local dynamic filtering
- 38:32Dynamic partition pruning
- 40:32Awaitable dynamic filters
- 44:11Advanced connector pushdown (2019-...)
- 44:48Dereference pushdown
- 46:16Projection pushdown
- 46:56Current initiatives
- 47:20Plan substitutions/Caching
- 49:18Multistage planning
- 56:26Thank you for joining!
related talks
27mHarnessing the Power of Dual Query Engines.
47mJOINs Are Slowing You Down: Why Spark and Trino Won’t Replace Your DWH
24mCracking the Cost Code: A Cost Optimized Approach to Distributed...
15mOptimizing Query Performance in Big Data Systems
34mCloud Agnostic Data Platform with DBT, Trino, Iceberg and MinIO
28mAn Apache Spark query's journey through the stack
01h 02mStardog Query Optimiser: Architecture and Cardinality Estimations for Graph Queries
30mBringing SQL to Aerospike and the Enterprise – Rick DeMare