/
lectern.tv
Play15s30s1x0:00/--:--
DoneWatched
Expand
lectern.tv
17:34/1:10:40
0:50
0:43
0:54
4:34
0:57
1:50
1:39
2:20
4:56
1:14
0:56
1:11
4:19
3:48
1:08
3:00
1:40
1:50
1:30
2:00
3:25
4:41
3:55
6:14
3:09
1:56
1:30
3:50
History of Query Optimizers ft. IBM System R
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.
Lecture guide · Written by AI from the lecture. For the lecture’s own words, open the Transcript tab.
Traces the historical development of database query optimizers, focusing on the foundational design choices introduced by IBM System R in the 1970s. Andy Pavlo details the transition from procedural navigator-style programming to declarative relational queries, and examines both heuristic-based transformations and cost-based dynamic programming search algorithms.
This lecture assumes
- Logical Query Plantaught in SQL Optimization Background at 32:57
- Physical Query Plantaught in SQL Optimization Background at 33:07
- JOINtaught in SQL & Relational Databases at 1:22:02
- Query Optimizationtaught in SQL Optimization Background at 18:45
- Dynamic programmingtaught in Dynamic Programming I: Fibonacci, Shortest Paths at 0:40 and 1 more
- Predicatetaught in Overview and Introduction to Lisp: Procedural & Symbolic Abstraction at 52:46
Key Takeaways
- Declarative SQL separates the logical intent of a query from its physical execution, allowing database systems to adapt to changing data.
- Heuristic-based optimization applies pattern-matching rules and relational algebra equivalences to transform logical plans without relying on cost models.
- System R pioneered cost-based query optimization by estimating query costs using page fetches and CPU metrics.
- The number of possible n-way join orderings grows as a Catalan number, necessitating search space pruning techniques like considering only left-deep trees.
Concepts Explained18
Procedural Querying—A programming style where developers specify the exact access paths and execution order for a database system to compute an answer.
CODASYL—An early standard for database systems that popularized procedural querying and navigational programming.
The Programmer as Navigator—A conceptualization of database programming where developers explicitly navigate through database records using procedural options.
Relational Model Tenets—The foundational principles of the relational model covering structure, integrity, and declarative manipulation.
Examples, Proofs & Derivations
Works & Sources Cited7
Paper
The Programmer as Navigator—Charles Bachman, 1973, Turing Award Lecture on CODASYL and early database work
Paper
A Relational Model of Data for Large Shared Data Banks—Edgar F. Codd, 1970/1969, foundational paper on the relational model
Paper
Starburst paper—Late 1980s IBM research paper on extensible query optimization
Paper
Cascades paper—1990s paper on extensible query optimization framework
Book
Semi-authorized biography of Larry Ellison—Published in the 1990s, cited for accounts of Oracle's early query optimizer and Michael Stonebraker's remarks.