From SQL to an Executable Plan

4
2

Introduction

A SQL query tells the database which data you want. It usually does not specify which index to read, how to combine tables, or whether sorting will be necessary.

The database translates that request into an executable plan. Understanding this process helps explain why the same SQL can run differently as data, indexes, and statistics change.

SQL Describes the Required Result

Consider a query that retrieves recent orders for one customer:

SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 101
ORDER BY created_at DESC
LIMIT 20;

The query specifies the required columns, filtering condition, output order, and maximum number of records.

It leaves several implementation choices open:

  • Scan the table or use an index?

  • Retrieve matching rows before sorting?

  • Read an existing index in the required order?

  • Stop early or examine many records first?

An executable plan resolves these choices using the database’s available methods.

Parsing and Semantic Analysis

The database first checks whether it can interpret the statement.

Parsing checks the SQL grammar and creates an internal representation. A missing comma or incorrectly placed keyword can cause a syntax error here.

Semantic analysis determines what the statement refers to. It resolves table and column names, checks relevant types, and identifies operations such as comparisons and function calls.

For example:

SELECT unknown_column
FROM orders;

This statement may be grammatically valid, but it cannot proceed if unknown_column does not exist.

Permission checks also occur during statement processing. Their exact timing varies across database systems.

Building and Transforming the Logical Request

The database represents the query as operations such as reading a relation, filtering records, selecting columns, sorting, and limiting output.

This logical representation describes the required work without fully deciding how each operation will run.

The database may apply transformations that preserve the query’s meaning. Examples include:

  • Simplifying constant expressions.

  • Expanding views.

  • Moving filters closer to the data source when valid.

  • Rewriting eligible subqueries.

These transformations must respect SQL semantics. Moving a condition across an outer join, for example, can change the result if done incorrectly.

The precise boundaries between rewriting and optimization differ by implementation.

Choosing Physical Operators

A logical operation can have several physical implementations.

Required operation

Possible implementation

Read records

Table scan or index scan

Apply a condition

Filter operator or index condition

Combine tables

Nested-loop, hash, or merge join

Produce ordered output

Sort or suitable ordered access

Restrict output size

Limit or top-N processing

Not every database supports every operator.

For our query, a suitable index on (customer_id, created_at) may support both filtering and ordered access. Without that access path, the database might scan records and perform a separate sort.

From SQL to executable plan

From SQL to executable plan

How the Optimizer Compares Plans

The query optimizer evaluates candidate plans and selects one using its rules and estimates.

A cost-based optimizer considers factors such as:

  • Estimated records matching each condition.

  • Table and index sizes.

  • Available indexes and ordering.

  • Expected page access and CPU work.

  • Memory requirements for sorting or joining.

These estimates depend heavily on statistics describing the data.

If customer 101 has few orders, an index lookup may be attractive. If a condition matches most of a table, scanning it can be cheaper than performing many separate row lookups.

Cost values are estimates used to compare alternatives. They are not necessarily predicted execution times in milliseconds.

The optimizer also has limited planning time, so it does not necessarily explore every mathematically possible plan.

Two Possible Plans for the Same Query

The recent-orders query could use either of these broad strategies:

Stage

Scan-and-sort approach

Suitable-index approach

Locate records

Scan and retain customer 101’s orders

Locate customer 101’s index entries

Establish order

Sort qualifying records, possibly using a top-N method

Traverse entries newest first

Retrieve values

Read required values during processing

Fetch additional row values if needed

Finish

Return up to 20 rows

Stop after enough qualifying rows

Both approaches must produce a valid answer. Their performance depends on the actual data and storage organization.

An index is an option, not an instruction the optimizer must follow.

Two plans, same query

Two plans, same query

Executing and Inspecting the Plan

The selected plan contains physical operators and the information needed to run them. During execution, those operators read data, apply conditions, and pass records or batches through the plan.

Some operators can produce output incrementally. Others, such as certain sorts, need substantial input before producing results.

Many databases provide an EXPLAIN command:

EXPLAIN
SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 101
ORDER BY created_at DESC
LIMIT 20;

Its output format varies, but it can reveal access paths, operator choices, and estimated row counts.

Some tools also report actual execution measurements. Options that collect actual statistics may execute the statement, so distinguish them from estimated-plan inspection.

Compare estimated and actual row counts when available. Large differences can help explain an unsuitable plan.

Summary

A database turns SQL into an executable plan by interpreting the statement, resolving its meaning, transforming the logical request, and selecting physical operators.

The resulting plan reflects available access paths and estimated costs. Inspecting it helps connect SQL statements to the work performed and provides a useful starting point for investigating performance.

CS Core

Read Similar Blogs

Comments0