Query Optimization in Modern Database Systems

This lecture establishes fundamental concepts of relational database query optimization, including algebraic transformations and the role of cost-based optimizers in enhancing query performance.

This session explores the historical evolution of query optimizers, focusing on the seminal contributions and architectural design of IBM System R and its influence on modern database systems.

This lecture delves into the IBM Starburst project, examining its innovative query rewriting techniques and extensible optimizer framework, which advanced declarative query processing.

This lecture analyzes the EXODUS and Volcano optimizer generators, highlighting their rule-based query rewrite engines and the iterator model for efficient query plan execution.

This session presents the Cascades framework for query optimization, detailing its dynamic programming approach, memo structure, and use of logical and physical properties for plan enumeration.

This lecture examines various query plan transformations, including predicate pushdown, join reordering, and subquery decorrelation, crucial for optimizing the logical query execution path.

This lecture introduces bottom-up strategies for join ordering, including the use of dynamic programming and bushy tree enumeration to find optimal join sequences for multi-way joins.

This session explores top-down and transformational approaches to join ordering, focusing on heuristic search methods and rule-based systems to determine efficient execution plans.

This lecture discusses bottom-up strategies for parallelizing query plan search, focusing on distributing computation across multiple processors to accelerate optimization.

This session covers top-down approaches to parallelizing query plan search, including techniques for breaking down the optimization problem into concurrently executable subproblems.

This lecture focuses on techniques for unnesting subqueries, converting complex nested SQL statements into equivalent, more efficient join-based queries for better performance.

This session explores the fundamental role of statistical information in query cost models, discussing how data distributions and metadata inform optimizer decisions.

This lecture delves into cardinality estimation techniques for query cost models, analyzing methods like histograms and sampling to predict result set sizes accurately.

This session continues the discussion on advanced cardinality estimation, covering techniques like correlation analysis and learned models to refine cost predictions in complex queries.