What is Query Optimization

Definition

Query optimization is the process by which a database engine, or an engineer, rewrites and plans a query to execute it as efficiently as possible, choosing access paths, join orders, and indexes that minimise the time and resources needed to return correct results.

« Back to Glossary Index

Cuts query runtime and compute cost by choosing the most efficient execution plan
Reduces data scanned through index use, partition pruning, and predicate pushdown
Improves concurrency by freeing resources sooner per query
Makes interactive analytics feasible on large datasets

Real World Example

An analyst rewrites a slow report query by adding a partition filter and selecting only needed columns, cutting a scan of billions of rows to millions and dropping runtime from minutes to seconds.

FAQs

What does a query optimizer do?

It evaluates possible execution plans for a query and selects the one expected to run fastest, considering indexes, join orders, and statistics.

How can engineers help query performance?

By filtering early, selecting only needed columns, using partitions and indexes, and avoiding patterns that prevent the optimizer from pruning data.

Why do statistics matter for optimization?

The optimizer relies on table statistics to estimate costs, so stale statistics can lead it to choose poor plans.