A query engine is the component that takes a declarative query, decides how to compute it, and executes that decision. Every relational database contains one — Postgres, MySQL and SQLite included.
You never tell it how to get the answer, only what answer you want. Everything between those two things is the engine: parse the text, build a plan, rewrite the plan into a cheaper equivalent, then run it against bytes on disk. PostgreSQL's own manual puts the planner's job plainly — a query can be executed in a wide variety of different ways, each producing the same results, and the optimizer's task is to pick the one expected to be fastest.
So this page starts general. The eight stages below exist in essentially every SQL system built since the 1970s. Only stages 6 to 8 diverge sharply between a transactional database and an analytical one, and that is exactly where the section after next draws the line.
Two acronyms come up constantly and the rest of this page leans on them, so here they are before anything else. They name workloads, not products. One engine is tuned for one of them; the same SQL keywords serve both.
The work an application does while people use it. Read one order, insert one payment, update one profile. Thousands of tiny operations a second, each touching a handful of rows, each needing to be correct and durable immediately.
Shape of a query: WHERE id = 4471. Success is measured in milliseconds per query and transactions per second. Rows are the natural unit, so storage is row-oriented and indexes matter enormously.
The work a report or dashboard does. Summarise a year of orders by region. Few queries, each reading enormous numbers of rows but only a few columns of them, and aggregating the result down to something a human reads.
Shape of a query: SUM(...) GROUP BY ... over millions of rows. Success is measured in bytes scanned and seconds per query. Columns are the natural unit, so storage is columnar and indexes are usually beside the point.
The distinction is old and the terms are loose in practice — systems now advertise themselves as “hybrid”, and Postgres will happily run an aggregation while ClickHouse will happily fetch one row. The reason to keep the two words is that they predict the design trade-offs: the same eight stages, resolved differently at each one. Your work sits on the OLAP side. Postgres docs · DuckDB on the OLAP case
Everything above assumed a query runs alone. In production it never does — OLTP engines exist to serve thousands of small transactions a second against the same rows, and the naive fix, making readers wait for writers and vice versa, would strangle the throughput the previous section just promised. Postgres and MySQL both solve this with MVCC (multi-version concurrency control): instead of locking a row a reader wants, the engine keeps more than one version of it around and hands each transaction the version that was current when its own snapshot began. A writer creates a new version rather than overwriting the old one in place, so a transaction already in flight keeps seeing a consistent picture — nobody blocks anybody just to read.
How much of that consistent picture a transaction gets is a knob: the isolation level. Each step up removes one more way the outside world can leak in mid-transaction.
| Isolation level | What it guarantees | Default in |
|---|---|---|
| Read Committed | Every statement gets a fresh snapshot — no dirty reads of uncommitted data, but two statements in the same transaction can see different, both-committed answers | Postgres |
| Repeatable Read | The whole transaction shares one snapshot taken at its start — a row read twice returns the same value; Postgres and InnoDB both go further than the SQL standard requires and block phantom rows too | MySQL / InnoDB |
| Serializable | The engine detects conflicts between concurrent transactions and aborts one rather than let the result diverge from some serial (one-at-a-time) execution order | Neither — opt-in on both |
T1: BEGIN
T1: SELECT balance FROM accounts WHERE id = 42 → 100 (reads v1, snapshot taken)
T2: BEGIN
T2: UPDATE accounts SET balance = 80
WHERE id = 42 (writes v2, doesn't overwrite v1)
T2: COMMIT
T1: SELECT balance FROM accounts WHERE id = 42 → ? (same transaction, second look)
Repeatable Read: → 100 still v1 — T1's snapshot was fixed at BEGIN, T2's commit is invisible to it
Read Committed: → 80 v2 — T1 takes a fresh snapshot per statement, so it now sees T2's commit
T1: COMMIT
The two databases reach MVCC by different routes, the same split in philosophy the Indexing section found in how they store rows. Postgres keeps every version directly in the table: an UPDATE doesn't rewrite a row in place, it inserts a new tuple stamped with the ID of the transaction that created it and, once superseded, the one that deleted it — a reader's snapshot is just a rule for which stamps it's allowed to see. Nothing is reclaimed automatically; old versions pile up as dead tuples until VACUUM walks the table and frees them, which is why an idle autovacuum or one long-running transaction holding back the oldest visible snapshot is a classic source of Postgres table bloat. InnoDB (MySQL) takes the opposite trade: the table holds only the current version of each row, and older versions are reconstructed on demand from an undo log — a record of how to undo each change. A reader wanting an old version replays undo records backward instead of hunting through the table; cleanup is a background purge thread trimming that log instead of a table-wide sweep.
Analytical engines mostly opt out of this problem rather than solve it. DuckDB does implement MVCC — it needs to, so a long-running scan isn't corrupted by a concurrent write — but only one writer is active at a time even so, which is a fair trade when the workload is bulk loads rather than thousands of small conflicting writers. ClickHouse's default MergeTree tables go further and skip cross-statement isolation entirely: a single INSERT is atomic, but there's no guarantee across separate statements unless the experimental transactions feature is turned on. Neither gap matters for this page's query — it only reads.
These systems are usually discussed as if they were different species. They are not. Each one parses, plans, optimizes and executes. What separates them is two independent things: which workload above they were tuned for, and whether they own their storage.
Those two axes are why there are four families here but only two columns in the table further down. The table compares workload, so families 2, 3 and 4 all sit in its OLAP column — they differ from each other on storage ownership and packaging, not on the decisions the engine makes. Family 1 is the whole OLTP column.
Two columns, by workload — not by family. The analytical, storage-detached and embedded families above all share the OLAP column, because they make the same stage-by-stage decisions; they differ from each other only in packaging and storage ownership. Where they disagree with each other, the stage detail below says which.
A mental picture, if it helps: imagine a kitchen with eight stations, from taking the order to plating the dish. A fast-food counter (transactional) and a banquet caterer (analytical) run the exact same eight stations — but a counter is built to fire one order at a time as fast as possible, while a caterer is built to prep the same dish hundreds of times over in one batch. Same stations, different jobs, so the later stations end up run differently.
| Stage | Postgres / MySQL (transactional) | Analytical and BI engines |
|---|---|---|
| 01 Parse and bind | Same idea: grammar, then name and type resolution | Same, and often skipped — the plan arrives from a UI or a dataframe API |
| 02 Logical plan | Query tree of relational operators | Identical concept, frequently the public API (Spark, Polars lazy frames) |
| 03 Statistics | ANALYZE, histograms in pg_statistic; MySQL histograms since 8.0 | Statistics live in file footers as well as the catalog |
| 04 Optimize | Cost-based, dynamic programming; Postgres switches to a genetic search past geqo_threshold | Same rewrites, plus pushdown into the storage layer and network cost |
| 05 Physical operators | Index scan, sequential scan, nested-loop / hash / merge join | Index scans are rare; hash joins and hash aggregates dominate |
| 06 Execution model | Row at a time — cheap for a single-row lookup | Vectorized batches or compiled code — 10–100× on scans |
| 07 Storage | Row-oriented pages, B-tree indexes | Columnar files, min/max statistics, projection and predicate pushdown |
| 08 Distribution | Single node unless extended (Citus, Vitess) | Partitioned and shuffled across workers by design |
Read the table as one claim: stages 1 to 5 are shared vocabulary you can carry between any two SQL systems. Stages 6 to 8 are where analytics made different trades, and where the rest of this page spends its time — because that is the part your work touches.
SELECT r.region_name,
SUM(s.amount) AS revenue
FROM sales s
JOIN dim_region r ON r.region_id = s.region_id
WHERE s.order_date >= '2026-04-01'
GROUP BY r.region_name
ORDER BY revenue DESC;
Press play and the same query is rewritten, stage by stage, from text into work. Click any stage to read it in full. Every stage carries a split panel naming what Postgres does and what an analytical engine does, so you can always see which side you are looking at.
Keeping the kitchen picture from above: this is one order ticket moving through those eight stations. Click a station and see exactly what happens to this one ticket there, and how a fast-food counter's version of that station differs from a caterer's.
The engine reads your SQL as characters. A lexer chops it into tokens (SELECT, an identifier, a comma), and a parser arranges those tokens into an abstract syntax tree that mirrors the grammar of the language. Nothing has been validated against your data yet — this stage only decides whether the sentence is well formed.
Then comes binding, or analysis: s.amount is resolved to a real column of a real table, SUM to a known aggregate function, and the types are checked. This is where you get "column not found" rather than a wrong answer.
Sort [revenue DESC]
└─ Aggregate [group: r.region_name, agg: SUM(s.amount)]
└─ Filter [s.order_date >= DATE '2026-04-01']
└─ Join [inner, r.region_id = s.region_id]
├─ Table sales AS s
└─ Table dim_region AS r
Where the time goes: almost nowhere. Parsing a query is microseconds. It matters for a different reason — parsed plans are cacheable. A BI tool that fires the same shape of query thousands of times a minute should parse once and re-bind parameters, not re-parse.
In your product: the drag-and-drop UI is effectively a parser already — it builds the tree directly and skips the text. That is why BI engines often have an internal plan representation that SQL is only one entry point to.
The tree is rewritten into relational algebra: a small set of operators that each take relations in and return a relation out. Filter (σ), project (π), join (⋈), aggregate (γ), sort (τ). The plan says what result is wanted and says nothing about how to compute it.
That separation is the whole trick. Because these operators obey algebraic laws, the engine may rewrite the plan freely as long as the result is provably identical — and a rewritten plan can be thousands of times cheaper.
τ revenue DESC
└ γ region_name; SUM(amount)
└ σ order_date >= '2026-04-01'
└ ⋈ region_id
├ sales
└ dim_region
The laws that pay: a filter can move below a join when it only touches one side (filter early, join less). A projection can move down to drop unused columns. Two joins can be reassociated to change their order. An aggregate can sometimes be split into partial and final halves so the partial half runs in parallel.
Read a logical plan top-down for meaning, bottom-up for execution: the leaves produce rows, each parent consumes its children.
df.explain() in Spark or Polars prints it, and lazy evaluation exists precisely so the plan can be optimized before anything runs.Before the engine can choose between plans it has to guess how many rows each step will produce. That guess is cardinality, and it comes from statistics collected about the data: row counts, per-column min and max, number of distinct values, null counts, and histograms of value distribution.
Our filter is a date range. With a histogram over order_date, the engine can say: the last quarter is about 4% of the table, so 1.2B rows in gives roughly 46M rows out. That single number decides everything downstream — which join algorithm, which side builds the hash table, how much memory to reserve.
# estimating a range predicate from an equi-depth histogram
def selectivity(hist, lo):
"""hist: list of buckets, each holding an equal share of rows."""
share = 1.0 / len(hist)
hit = 0.0
for b in hist:
if b.hi < lo: # bucket entirely below the range
continue
if b.lo >= lo: # bucket entirely inside
hit += share
else: # partial: interpolate within the bucket
frac = (b.hi - lo) / (b.hi - b.lo)
hit += share * frac
return hit # → 0.041 ≈ 46M of 1.2B rows
Where it goes wrong: estimation errors compound multiplicatively through joins. A 10× underestimate at a leaf becomes a 1000× underestimate three joins up, and the engine then picks a plan sized for thousands of rows while millions arrive. Stale statistics after a big load are one of the most common causes of a query that was fast yesterday.
In your product: whenever a client's pipeline rewrites a dataset, statistics must be refreshed with it. If your ingest writes files but not stats, the optimizer is planning blind.
ANALYZE or autovacuum. Postgres keeps histograms and most-common-value lists in pg_statistic; MySQL has had histograms since 8.0.The optimizer generates plans that are all logically equivalent, assigns each an estimated cost, and keeps the cheapest. Cost is a made-up unit that models real resources: bytes read, rows processed, hash entries built, bytes shuffled across the network.
Rewrites come in two flavours. Rule-based rewrites are always wins and are applied unconditionally — push the date filter below the join, drop the 38 columns nobody selected, collapse a subquery. Cost-based choices need the numbers from stage 3, because they could go either way: which join order, which algorithm, whether to sort or to hash.
plan A join(sales, dim_region) then filter
1.2B rows joined, cost 41 200
plan B filter(sales) then join ← chosen
46M rows joined, cost 310
plan C as B, but build hash on dim_region (18 rows)
46M probes, 18-entry table, cost 290 ← chosen
Join order and the build side. A hash join builds an in-memory table from one input and streams the other past it. You always want the small side as the build side: 18 region rows make a hash table that fits in L1 cache, and 46M sales rows stream past it. Get it backwards and you build a 46M-entry table, blow out memory, and spill.
Join order is the hardest part of the problem — the number of orderings grows factorially, so engines search a bounded space with dynamic programming and give up on very wide join graphs.
geqo_threshold.Each logical operator is replaced by a concrete algorithm with concrete memory behaviour. This is the plan that runs.
| Logical | Physical choice | Cost shape |
|---|---|---|
| Scan | Full scan / partition-pruned scan / index lookup | bytes read |
| Join | Hash join, sort-merge join, nested loop, broadcast | memory for the build side |
| Aggregate | Hash aggregate, streaming aggregate on sorted input | memory ∝ group count |
| Sort | In-memory quicksort, external merge sort | spills when it exceeds budget |
Sort [revenue DESC] 18 rows
└ HashAggregate [key region_name, SUM(amount)] 18 groups
└ HashJoin [build: dim_region (18), probe: sales] 46M rows
├ Scan dim_region cols: region_id, region_name
└ Scan sales cols: region_id, amount, order_date
filter pushed: order_date >= '2026-04-01'
Spilling to disk. Every blocking operator — hash build, sort, hash aggregate — has a memory budget. Exceed it and the operator must spill partitions to disk and read them back, which typically costs an order of magnitude. Spills show up in query profiles as a sudden jump in one operator's time with low CPU: it is waiting on I/O.
Note that the aggregate here holds only 18 groups. Group cardinality, not input size, sets an aggregate's memory. GROUP BY customer_id over millions of customers is a different machine entirely.
The classic design is the Volcano or iterator model: every operator exposes next(), and the root pulls one row, which pulls one row from its child, and so on down the tree. It is elegant and uses almost no memory. It is also slow, because each row costs several virtual function calls and the CPU never gets to do the same thing twice in a row.
Modern analytical engines are vectorized: next() returns a batch of a few thousand values per column instead of a single row. The per-call overhead is amortised across the batch, the inner loop is a tight pass over a contiguous array, and the CPU can use SIMD instructions to add sixteen numbers at once.
// row-at-a-time: one dynamic dispatch per row
while let Some(row) = child.next_row() { // ~1.2B calls
if row.order_date >= cutoff { sink.push(row) }
}
// vectorized: one dispatch per 2048-value batch
while let Some(batch) = child.next_batch() { // ~600K calls
let dates: &[i32] = batch.column(2).values();
let mask: Vec<bool> =
dates.iter().map(|d| *d >= cutoff).collect(); // auto-vectorized
sink.push(batch.filter(&mask));
}
Rule of thumb: vectorized execution buys 10–100× on analytical scans and aggregations. It buys nothing on a single-row lookup, which is why transactional databases kept the row model. Batch size is a tuning knob — big enough to amortise dispatch, small enough that a batch's columns stay in CPU cache. A few thousand is the usual sweet spot.
Compiled execution is the third option: generate machine code for this specific query. Faster still, at the cost of compile latency — a poor trade for short interactive queries.
Row storage keeps a whole record together, which is right when you fetch one record. Column storage keeps each column's values together, which is right when you scan one column of every record. Analytics is always the second case — and our query touches 3 of the 41 columns in sales.
Three separate wins stack up. Projection pushdown: the engine reads only the three columns, not 93% of the bytes it would otherwise. Compression: a column holds one type and often few distinct values, so dictionary and run-length encoding shrink it several-fold, and the engine can even filter on the compressed codes. Predicate pushdown with min/max statistics: each file and row group records the range of each column, so the scan skips whole chunks whose order_date range cannot match.
file: sales/2026-Q1/part-07.parquet
row group 0 order_date [2026-01-01 .. 2026-01-14] → skip
row group 1 order_date [2026-01-14 .. 2026-01-29] → skip
...
file: sales/2026-Q2/part-01.parquet
row group 0 order_date [2026-04-01 .. 2026-04-16] → read
columns read: region_id, amount, order_date (3 of 41)
scanned 46M of 1.2B rows · 1.4 GB of 210 GB
Late materialization is the fourth win and the subtlest. Rather than reconstructing full rows and then filtering them, the engine evaluates the filter against one column, produces a selection vector of positions that survived, and fetches the other columns only for those positions. Rows are assembled as late as possible — sometimes never, if the query only aggregates.
In your product: your columnar file layer is where most of the achievable speed lives. How data is partitioned and sorted on write determines how much a scan can skip on read — an unsorted column has useless min/max ranges, because every chunk's range covers everything.
When the data does not fit on one machine, the plan is cut into stages at every point where rows must move between workers. Within a stage, each worker runs the same operator pipeline over its own partition, fully in parallel. Between stages, rows are redistributed — a shuffle — and that is the expensive part, because it means serializing data and sending it over a network.
Our query needs one shuffle at most, and with a small dimension table it needs none: dim_region is broadcast to every worker, each worker joins and aggregates its own slice of sales locally, and only 18 partial groups per worker travel to the final aggregate.
stage 1 ×8 workers, one per partition group
Scan sales (pruned) → HashJoin(broadcast dim_region)
→ partial HashAggregate
emits 18 partial groups per worker ~2 KB total
stage 2 ×1
final HashAggregate → Sort → result 18 rows
Partial aggregation before the shuffle is the single most valuable distributed trick: aggregate locally first so you ship 144 rows instead of 46 million. Skew is the usual failure — if one partition holds 40% of the rows, seven workers finish and one runs for minutes, and the stage is as slow as its slowest task.
Partition on a column with even distribution and high cardinality, and prune on a column you filter by. Those are often not the same column, which is the real design tension.
The walkthrough above named an index twice without stopping to explain it: Stage 05 picked an index scan as a physical operator, and Stage 07 called B-tree indexes part of Postgres and MySQL's storage layer. This section is what's underneath both mentions.
An index is a second copy of a column, or a few columns, kept in sorted order with a pointer back to the full row — so the engine can find a match without reading every row on disk. The default shape in both Postgres and MySQL is a B-tree: a balanced structure where every leaf sits at the same depth. It stays only a handful of levels deep even over billions of rows, because each node is a full disk page holding hundreds of keys — the tree is wide, not small, and that's what keeps a lookup cheap.
Where the index points matters as much as its shape. InnoDB (MySQL) clusters the table around its primary key: the leaf level of the primary key's B-tree is the row, so a primary-key lookup needs one structure and nothing else. Every other index on that table is a secondary index — it stores the indexed columns plus the primary key, and fetching anything beyond that requires a second trip back into the clustered index. Postgres has no clustered index by default: every index, primary key included, is a separate structure pointing at a heap tuple, so even a primary-key lookup touches two structures. Either way, every index is rewritten on every INSERT, UPDATE and DELETE that touches its columns — an index is a standing trade of write cost and disk space for read speed, to be used sensibly, not applied to every column.
idx_sales_order_date B-tree, 3 levels, 1.2B entries
root [ 2025-04-01 | 2025-10-01 | 2026-04-01 | 2026-10-01 ]
│
branch [ 2026-01-01 | 2026-02-15 | 2026-04-01 | 2026-05-30 ]
│
leaf … 2026-03-31→tid(881,4) 2026-04-01→tid(881,5) 2026-04-01→tid(902,1) …
└── sibling pointer: keep scanning right ──→
46M matching leaf entries — each one a candidate heap fetch,
unless the index already carries every column the query needs
The one index that changes the arithmetic: index-only scans. If the index carries every column a query needs — in Postgres, using INCLUDE to add non-key payload columns — the engine can answer straight from the index and skip the row fetch entirely, checking a small visibility map instead of the heap. The visibility map is roughly four orders of magnitude smaller than the heap it describes, so that swap turns 46M random heap reads into 46M sequential index reads — the difference between an index paying for itself and an index making things worse.
None of this fires for the query on this page. Stage 05 already noted that an index scan is rare once a filter matches 4% of the table — indexes earn their keep on selective point lookups like WHERE id = 4471, the OLTP shape from the very first section of this page, not this OLAP one. Stage 07 is where this query's actual speed comes from instead.
The clearest way to see why the two families end up so far apart is to compare two indexes by their granularity — how many rows a single entry covers — rather than by which product ships them. A B-tree, the OLTP default, is a phone book: every name gets its own line, kept in sorted order, so "Nguyen, Thi" is one lookup away no matter how many million names came before it — one entry, one row. A sparse index, the OLAP alternative, works more like the thumb-tabs cut into the side of a large dictionary: there's no tab for every word, just one every few hundred pages. A tab doesn't take you to the word, it takes you to roughly the right neighborhood, and everything inside that neighborhood — a whole block of pages — gets read or skipped together. ClickHouse's MergeTree keeps exactly that: about 146,484 tabs for this page's 1.2-billion-row table, one per 8,192-row granule, each one describing a range of values instead of a single row.
DENSE — "phone book" (B-tree, OLTP) SPARSE — "dictionary thumb-tabs" (OLAP)
one entry per row one entry per 8,192-row granule
Aab, J. → row 40,441,880 granule 0 rows 1 – 8,192 skip
Aabel, T. → row 40,441,881 granule 1 rows 8,193 – 16,384 skip
Aachen, K. → row 40,441,882 ...
... granule 14 rows 114,689 – 122,880 READ (order_date overlaps filter)
Zywiec, M. → row 68,220,014 granule 15 rows 122,881 – 131,072 skip
...
1.2B entries — one per row 146,484 entries — one per granule (~8,192× fewer)
find one name: descend the tree, a few hops find one range: binary-search the tabs, then scan the granule
The granule comparison above and Stage 07's row-group statistics are the same idea under two names, but not quite the same structure. ClickHouse's MergeTree keeps two different things that both get called an "index": the sparse primary index shown above, ordered by the table's declared sort key, used to find where a range of rows starts — and separate, optional data skipping indexes (minmax, set, Bloom filter) that store a per-block summary and let a block be skipped without the primary index finding it first. That second kind is the one doing Parquet row-group min/max statistics' job from Stage 07 — a Parquet row group isn't sorted internally and has no primary index at all, only the skipping kind, so "index" there means exactly this min/max metadata and nothing more.
Both kinds of pruning only work if the data is physically arranged to make them narrow. Stage 07 already said as much in passing — an unsorted column has useless min/max ranges, because every chunk's range covers everything. Sort keys and clustering keys exist to fix exactly that. Redshift's SORTKEY stores rows in 1 MB blocks in sorted order and lets the query processor skip blocks whose min/max range misses a filter — enough, in Redshift's own example, to remove up to 98% of blocks from a scan. Snowflake's clustering keys and Databricks' Z-order or newer liquid clustering do the equivalent job for engines with no user-controlled row order at all: left alone, a table is ordered roughly by load time, which rarely matches the column a later query filters on, so every block's range ends up covering the whole table and nothing gets skipped. Reordering rows so a filtered value clusters together is what turns a zone map from decoration into a real speedup.
WHERE region = 'EU' AND status = 'active' resolves as a bitwise AND between two bitmaps before a row is touched. Cheap to store, expensive to update, which is why it's a read-mostly warehouse feature rather than a general-purpose one. Postgres builds the same kind of bitmap too, but only transiently in memory during a scan to combine two B-tree indexes — never as a structure stored on disk.Materialized views are a different move again: instead of narrowing what a scan touches, they precompute and store the query's answer, so a report that would otherwise aggregate a billion rows every run instead reads a small, already-summarized table. Postgres materializes on demand, refreshed by an explicit REFRESH MATERIALIZED VIEW. ClickHouse's materialized views are incremental — an insert trigger updates the stored aggregate as new rows land, so the summary stays current without a full rebuild. Neither is indexing; both trade storage and write-time cost for skipping the scan altogether, which is sometimes the better fix for a slow dashboard tile than any index or sort key.
What to look for when a query is slow, and which stage above to reread.
WHERE YEAR(order_date) = 2026 defeats a min/max skip that order_date >= '2026-01-01' would use.Don't take this page's word for anything. These are the primary sources it was written from: vendor documentation for how a specific engine behaves, and papers for why the designs are what they are. Links checked August 2026; some papers sit behind a publisher paywall, in which case the title is searchable for an open copy.
Where this page states a number without a citation — the 48 seconds, the 46 million rows, the cost figures — it is an illustration written to make a mechanism legible, not a measurement. The mechanisms are sourced; the numbers are mine.