Execution Plan

What is a Covering Index in SQL?

A covering index is an index that includes every column a query needs — both the filter/sort columns and the selected columns — so the database engine can answ…

What Are Index Selectivity and Cardinality?

Cardinality is the number of distinct values in a column, and selectivity is the ratio of distinct values to total rows — a high-selectivity column (close to 1…

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…

CTE vs Subquery: Which Performs Better?

Neither a CTE nor a subquery is inherently faster; on most modern optimizers, including PostgreSQL 12+ and SQL Server, a non-recursive CTE is inlined into the…

What Does It Mean for a CTE to be Materialized?

A materialized CTE is one the database engine fully computes and holds in a temporary work area exactly once, before the outer query runs against it, as oppose…

What is a Reliable Methodology for Tuning a Slow SQL Query?

A reliable query-tuning methodology starts with measuring, not guessing: capture the query's actual execution plan, identify the single most expensive operatio…

How Do Prepared Statements Improve Database Performance?

A prepared statement is parsed, validated, and compiled into an execution plan once, and then executed repeatedly with different parameter values, so the datab…

How Do You Detect a Query Performance Regression?

You detect a query performance regression by continuously capturing query execution plans and timing statistics, comparing them against a historical baseline,…

What are Query Plan Hints and When Should You Force Index Usage?

A query plan hint is an explicit instruction embedded in a SQL statement that overrides the optimizer’s chosen access path, such as forcing use of a specific i…

What are the Limitations of a Cost-Based Query Optimizer?

A cost-based optimizer’s limitations stem from the fact that it estimates a query’s cheapest plan using statistics and heuristics rather than executing anythin…

What is Adaptive Query Execution?

Adaptive query execution is the ability of a query engine to change its execution plan mid-run based on actual runtime statistics observed during execution, ra…

How Does Parallel Query Execution Work in a Database?

Parallel query execution splits a single query’s work — such as a table scan, sort, or join — across multiple CPU cores or worker processes that run concurrent…

What is Vectorized Query Execution?

Vectorized query execution processes data in batches of many values at once, using CPU SIMD instructions and cache-friendly columnar layouts, instead of the tr…