How a Query Is Executed

You press run. Something reads your SQL, decides how to get the answer, and gets it.

That middle step — deciding how — is where the difference between 8 milliseconds and 40 seconds lives.

Why & what

SQL says what you want, never how to get it. So something has to invent the "how," and that something makes real choices: use an index or scan the table, join A to B or B to A, sort now or later.

The query processor works in three stages: the parser checks your SQL, the optimiser picks the cheapest plan, and the executor runs it.

How it works

Take SELECT name FROM Student WHERE roll_no = 101.

1. Parser. Is the SQL valid? Does Student exist? Does it have roll_no and name? Errors here are syntax and name errors — you've seen these already.
2. Optimiser. The interesting stage. It considers several plans: scan every block, or use the index on roll_no. It estimates the cost of each using statistics it keeps about the table how many rows, how many distinct values.
3. Cost is measured in block reads, which is exactly the idea from Module 7.1. Full scan of 2 million rows ≈ 25,000 block reads. Index lookup ≈ 4. It picks the index.
4. Executor. Runs the chosen plan and returns rows.
5. See the plan yourself. Put EXPLAIN in front of any query and the database prints what it decided. Seq Scan means full table scan. Index Scan means it used your index. That single word is usually the answer to "why is this slow."

Pause here. EXPLAIN shows Seq Scan on a query with a WHERE on an indexed column. Name two reasons it might have ignored the index.

Either the column is wrapped in a function (so the index doesn't match), or the query returns so much of the table that scanning is genuinely cheaper.

Notice: you write WHAT you want — the optimiser decides HOW.

Common confusion

People think the optimiser is guaranteed to find the best plan. It isn't. It uses estimates from statistics, and stale statistics produce bad plans. That's why databases have a command to refresh them (ANALYZE in PostgreSQL) and why a query can suddenly get slow after a big data load without a single line of SQL changing.

Second: cost numbers in EXPLAIN aren't seconds. They're arbitrary units for comparing plans against each other. A cost of 48,000 doesn't mean 48 seconds — it means "much more work than the plan costing 8."

Interview angle

1. "What does a query optimiser do?" — Considers several execution plans and picks the cheapest by estimated cost, using table statistics. Say cost is measured in block reads.
2. "How would you debug a slow query?" — Run EXPLAIN, look for a sequential scan where an index should apply, check whether a function is disabling the index, and check whether statistics are stale.
3. "Are the numbers in EXPLAIN seconds?" — No, they're relative cost units for comparing plans.

Recap

A query goes parser → optimiser → executor, the optimiser estimates plan costs in block reads using table statistics, and EXPLAIN shows you which plan it chose.