Lecture 9. Query optimization

Readings:

• P. Selinger, M. Astrahan, D. Chamberlin, R. Lorie, and T. Price. Access Path Selection in a Relational Database Management System. Proceedings of ACM SIGMOD, 1979. Pages 22-34. Also in the Red Book (3rd ed and 4th ed). [pdf]

While reading this paper, try to focus on the following questions

• Query optimization is highly dependent on the effectiveness of cost estimation. How does the paper propose to compute the cost of a single relation access path? How about the cost of a complete query plan? What statistics are used? What if these statistics are not available for one relation? What are the benefits and limitations of this approach?
• In addition to computing the cost of a query plan, a query optimizer also needs (1) to define the space of possible plans that it will search and (2) it needs an algorithm to enumerate possible query plans within that space. What query plans does the paper consider? What algorithm does the paper propose to find the best plan in that space? What are the benefits and limitations of this approach?

Lecture notes:

• lecture9.pdf

Additional resources:

• Chapter 15 (in R&G, third edition).

Optional, additional reading:

• Surajit Chaudhuri. An Overview of Query Optimization in Relational Systems. PODS 1998. [pdf]