SELECT Execution: A Lifecycle Tour

Why a SELECT needs stages at all

A SELECT statement arrives at the server as plain text. Somewhere between that text and a result set, ClickHouse has to answer several very different questions: Is this even grammatically valid SQL? Do the tables and columns it mentions actually exist? Given that they exist, what's the smartest order to do the work in? And then, mechanically, how do you actually pull the bytes off disk and turn them into an answer?

Those are four different kinds of question, and they depend on each other in a strict order. You can't check whether country is a real column until you know the query is even shaped like valid SQL. You can't decide the smartest execution order until you know which columns and tables you're actually working with. And you can't start reading data until you've decided what to read. So ClickHouse — like effectively every SQL engine — resolves these questions as four distinct stages, each one handing its output to the next:

1. Parse
2. Analyze
3. Plan
4. Execute

Each stage only knows what the previous one figured out. That separation is the whole reason the stages behave differently when something is wrong with your query — and it's worth walking through with one running example.

Take this query against the events table from the previous lesson (user_id, event_type, event_time):

SELECT event_type, count() AS occurrences
FROM events
WHERE event_time >= '2024-01-15 00:00:00'
GROUP BY event_type
ORDER BY occurrences DESC
LIMIT 5;

Stage 1 — Parse: is this grammatically valid SQL?

Parsing turns the raw SQL text into an AST (abstract syntax tree) — a structured representation of what the query says, with no idea yet whether any of it is true. The parser recognizes that this text has a SELECT list, a FROM, a WHERE, a GROUP BY, an ORDER BY, and a LIMIT, and builds a tree capturing that shape. It does not check whether events exists, whether event_type is a real column, or whether count() makes sense here. Parsing is pure grammar — the same job a programming language's parser does with source code, long before anything is run.

If you write SELECT FORM events (typo on FROM), parsing is where that fails — the text doesn't match the grammar of a SELECT statement at all. That failure has nothing to do with whether events exists.

Stage 2 — Analyze: is any of this actually true?

Analysis takes the AST and checks it against the server's actual schema metadata — the same underlying catalog information system.tables, from the previous lesson, surfaces as query results. Does events exist in the current database? Does it have a column called event_type? Does count() accept the arguments it's given? Analysis also resolves things the parser couldn't: expanding a * into the table's actual column list, matching the alias occurrences to the expression it names, and figuring out concrete data types for every expression in the query.

This is the stage that throws "Unknown table" or "Unknown identifier" errors. SELECT nonexistent_column FROM events is perfectly valid grammar — the parser has no complaint — so it sails through stage 1. It fails in stage 2, once ClickHouse actually checks nonexistent_column against the table's real columns and finds nothing.

Stage 3 — Plan: what's the smartest order to do this in?

Once the query is validated, ClickHouse builds a query plan: a tree of logical steps describing what work needs to happen — read from events, filter by event_time, group by event_type, sort by occurrences, keep the top 5 — and in what order. This stage is also where ClickHouse applies optimizations before touching any data: pushing the WHERE filter as early as possible, figuring out that only event_type and event_time need to be read at all (not user_id, since nothing in this query touches it), and recognizing that aggregation has to run before the final sort, since occurrences doesn't exist as a value until aggregation produces it.

The plan is a description of intended work. Nothing has been read from disk yet.

Stage 4 — Execute: actually doing it

The plan is compiled into a running pipeline, and only now does ClickHouse touch the table's data. It reads column data from disk, applies the filter, aggregates, sorts, and limits — and it does all of that in blocks: chunks of many rows' worth of column values processed together, not one row at a time and not the entire table at once. Filtering a block means checking event_time against the cutoff for a few thousand values in one operation; aggregating a block means folding a few thousand event_type values into running counts in one pass. That block-at-a-time processing is what "vectorized execution" concretely means — the term isn't describing a vague performance philosophy, it's describing this specific unit of work.

EXPLAIN: watching a stage without running the query

EXPLAIN lets you see the output of a stage directly, instead of inferring it from behavior. Run it in front of any SELECT, and instead of executing that query, ClickHouse shows you what one of the earlier stages produced. Used on its own, with no extra keyword, EXPLAIN shows the query plan — the output of stage 3, before any data has been touched.

EXPLAIN
SELECT event_type, count() AS occurrences
FROM events
WHERE event_time >= '2024-01-15 00:00:00'
GROUP BY event_type
ORDER BY occurrences DESC
LIMIT 5;

┌─explain──────────────────────────────────────────┐
│ Expression (Projection)                           │
│   Limit (preliminary LIMIT (without OFFSET))      │
│     Sorting (Sorting for ORDER BY)                │
│       Expression (Before ORDER BY)                │
│         Aggregating                               │
│           Expression (Before GROUP BY)            │
│             Filter (WHERE)                        │
│               ReadFromMergeTree (events)          │
└────────────────────────────────────────────────────┘

Read from the bottom up, this is the shape of the query plan built in stage 3: read from events (a MergeTree-family table — the engine this course spends all of Module 3 on), filter by the WHERE clause, compute the pre-aggregation expression, aggregate by event_type, compute the pre-sort expression, sort by occurrences, and finally limit to 5 rows before projecting the output columns. Every line is a plan step, not a record of anything that has actually happened — EXPLAIN stops at the end of stage 3. There are other EXPLAIN variants that show earlier stages too (EXPLAIN AST shows the parse tree from stage 1, for instance), and later in this course you'll use EXPLAIN variants that show what execution itself looks like — but for now, the default is your window into the plan.