Query Optimizer

What is a Query Execution Plan?

A query execution plan is the step-by-step, low-level operation tree the database engine builds to actually run a SQL statement — which access paths, join orde…

What Does EXPLAIN ANALYZE Do?

EXPLAIN ANALYZE actually runs the query and returns the real execution plan annotated with true measured timings and row counts for every operator, unlike plai…

Cost-Based vs Rule-Based Query Optimization: What is the Difference?

A rule-based optimizer picks an execution plan by applying a fixed priority ranking of access methods regardless of the actual data, while a cost-based optimiz…

How Does the Hash Join Algorithm Work?

A hash join builds an in-memory hash table from the smaller input table keyed on the join column, then probes that table with each row of the larger input, mat…

How Does the Merge Join (Sort-Merge Join) Algorithm Work?

A merge join, also called a sort-merge join, works by taking two inputs that are already sorted on the join key (or sorting them first), then walking both sort…

What is Query Plan Caching in a Database?

Query plan caching is the database engine's practice of storing a compiled execution plan for a query the first time it is optimized, then reusing that same pl…

What is the Parameter Sniffing Problem in SQL?

Parameter sniffing is the situation where a database compiles and caches a query plan optimized for the specific parameter values passed on the first execution…

What Role Do Statistics and Histograms Play in Query Planning?

Statistics and histograms are metadata the database keeps about the distribution of values in each column, and the query optimizer uses them to estimate how ma…