By the end of this lesson you can explain the journey of a query from text to result (parse, rewrite, plan, execute), tell the logical order of a query apart from its physical plan, and estimate how much data a query has to read.
Why it matters: Every slow report starts with a query whose plan reads far more data than the answer needs. Before you can fix that, you need a clear picture of who decides how a query runs and what that decision is based on.
Dataset: Sooq Analytics event store. Examples run in the editor on the right.
SQL is a declarative language: you describe what result you want, not how to compute it. SELECT email FROM users WHERE user_id = 42 says nothing about which file to open, which pages to read or in what order. Those decisions belong to the database, and making them well is the job of a component called the planner (also called the optimizer).
This track works on Sooq Analytics, a fictional product-analytics company in Jeddah. Its event store is large on purpose: 20,000 users, 300,000 tracking events and 100,000 orders. Apart from the primary keys, nothing is indexed, so you will see slow plans first and fix them yourself.
The four stages
- Parser. Checks the grammar and turns the text into a tree. A typo such as
SELECfails here, before any data is touched. - Rewriter. Expands views and applies rules. If you query a view, this is where its definition is pasted in.
- Planner. Builds many possible ways to run the query (scan the whole table or use an index? which table first in a join? hash or sort to group?), estimates the cost of each one from statistics about the data, and keeps the cheapest. The result is the plan: a tree of steps called nodes.
- Executor. Runs the plan. Rows flow upward through the tree: the leaves read tables, the nodes above filter, join, group and sort, and the top node hands rows to you.
Only the executor touches your data. The planner works from estimates, which is why a plan can be wrong when the statistics are wrong (you will meet that in lesson 3).
SELECT city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY city
HAVING COUNT(*) >= 2
ORDER BY revenue DESC
LIMIT 1;FROM: Read the table: all rows.
| city | status | amount |
|---|---|---|
| Riyadh | delivered | 120 |
| Jeddah | cancelled | 90 |
| Riyadh | delivered | 60 |
| Dammam | delivered | 200 |
| Jeddah | delivered | 75 |
| Riyadh | delivered | 40 |
| Dammam | delivered | 30 |
You write SELECT first, but PostgreSQL logically evaluates it after FROM, WHERE, GROUP BY and HAVING. That's why WHERE can't use a SELECT alias, while ORDER BY can.
Logical order versus physical plan
You learned the logical order of a query earlier in the course: FROM, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY and finally LIMIT. That order defines the meaning of a query. It's why you can't use a SELECT alias inside WHERE: logically, WHERE runs before SELECT has named anything.
The physical plan is free to do things in a different order, as long as the answer is the same. It can apply the WHERE condition while it reads each row, instead of first building the whole table. It can read rows already sorted from an index, so ORDER BY costs nothing. It can stop reading as soon as LIMIT has enough rows. Keep the two ideas apart: the logical order tells you what the answer is, the plan tells you how much work it took.
EXPLAIN
SELECT event_id, occurred_at
FROM events
ORDER BY event_id
LIMIT 5;Logically this sorts all 300,000 events and then keeps 5. The plan shows a Limit node on top of an Index Scan using events_pkey: PostgreSQL walks the primary-key index, which is already in event_id order, and stops after 5 rows. There is no Sort node at all. Same meaning, a tiny fraction of the work.
Pages: the unit of work
PostgreSQL stores each table as a file of pages (blocks of 8 kB), each holding many rows. A sequential scan (Seq Scan) reads every page of the table from start to end and tests every row. That is cheap per page, because reading consecutive pages is fast, but its total cost grows with the size of the table, not with the size of the answer. Finding one user by email with a sequential scan means testing all 20,000 users.
The planner knows roughly how big each table is from the system catalog pg_class: reltuples is its estimate of the number of rows and relpages the number of pages. ANALYZE refreshes these numbers (the Sooq data was analyzed when it loaded).
SELECT relname AS table_name,
reltuples::bigint AS row_estimate,
relpages AS pages,
pg_size_pretty(pg_relation_size(oid)) AS size_on_disk
FROM pg_class
WHERE relname IN ('users', 'events', 'orders', 'products')
ORDER BY relpages DESC;pg_class holds one row per table and index. events is by far the largest table, then orders, users and the tiny products table. Every sequential scan on events reads all of its pages, which is why the event store is where slow queries live.
EXPLAIN
SELECT user_id, full_name
FROM users
WHERE email = 'user12345@example.com';EXPLAIN shows the plan without running the query. There is one node, a Seq Scan on users with a Filter line: PostgreSQL will read every page of users and test each row's email. The estimate rows=1 says the planner expects one match. With no index on email, a full scan is the only option. You'll read every part of this line in the next lesson.