How a Database Query Executes

SQL → parser → planner → cost-based optimizer → executor — and why the planner picks index scan over full scan

1 / 18

steps

Stage 1 of 8·The Query

step 1

query.sql

SELECT name, city
FROM users
WHERE city = 'Paris'
ORDER BY name
LIMIT 20;

Speednormal

We will follow one SQL query from text to result. SQL is declarative: you describe WHAT you want — the 20 alphabetically-first users in Paris — not HOW to get it. The database is free to choose any execution strategy that produces the correct answer. Turning this "what" into an efficient "how" is the whole job of the query engine.

Query lifecycle

SQL textdeclarative

Parsertokens → AST

Logical planrelational algebra

Optimizercost-based choice

Full scanO(n) — rejected

Index scanO(log n) — chosen

Executoriterator model

Result set20 rows

Active Recall

Question 1 / 3

A query has an index on the filtered column, yet the optimizer uses a full table scan. Why might that be correct?

Under the Hood

Loading visualizer…

DNSresolving host

TCPconnecting

TLShandshake

HTTPrequesting

DSAverse

How a Database Query Executes

How a Database Query Executes