Query optimization helps a database choose an efficient way to answer a query. The same request can often be processed through different access paths and operation sequences, with large differences in execution time.
A query is a request for information from a database. It may be a simple lookup, such as finding a person by an identifier, or a complex request involving filters, calculations, and comparisons.
All flashcards
What is a query optimizer?
A database component that considers execution alternatives and selects a plan expected to perform well.
How do logical and physical optimization differ?
Logical optimization arranges relational operations; physical optimization chooses how to implement them.
What is cardinality in a query plan?
The number of tuples estimated to flow along an edge of the plan.
What is parametric query optimization?
Optimization that accounts for values unknown during planning and can select plans when those values become known.
What makes a query plan Pareto-optimal?
No other plan is at least as good on every cost metric and strictly better on one.
Quiz questions
In a database plan tree, how do intermediate results generally move?Answer: From child nodes upward to their parent nodes.
Why can join order strongly affect query performance?Answer: Different orders can produce intermediate results of very different sizes.
What does parametric query optimization address?Answer: Query values that are unknown when the system creates a plan.
Which condition describes a Pareto-optimal plan across multiple cost metrics?Answer: No other plan is at least as good on every metric and better on one.
What is a purpose of multi-objective query optimization?Answer: To consider trade-offs among metrics such as execution time and monetary cost.
Based on the Wikipedia article Query optimization, licensed CC BY-SA 4.0. Adapted into a summary, flashcards and quizzes.