Database Pipelines — Part 1/5
When you write SQL, you are not telling the database how to run. You are telling it what result you want. The engine turns that statement into an executable plan, then decides whether to scan a table or use an index and which join algorithm to pick. That step is query planning. A bad plan makes even fast hardware look slow; a good plan can make a mid-sized dataset feel snappy.
This is Part 1 of 5 in the Database Pipelines series. The spine below is engine-generic; concrete operator names and commands use PostgreSQL so you can check them against official docs. There are no fabricated millisecond timings here. Planner cost is a relative unit, not wall-clock time.
The running example
Assume two tables:
CREATE TABLE customers (
customer_id bigint PRIMARY KEY,
email text NOT NULL UNIQUE,
city text NOT NULL,
created_at timestamptz NOT NULL
);
CREATE TABLE orders (
order_id bigint PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers (customer_id),
status text NOT NULL,
total_cents integer NOT NULL,
created_at timestamptz NOT NULL
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_status_created_idx ON orders (status, created_at);
CREATE INDEX customers_city_idx ON customers (city);
The query we keep coming back to:
SELECT c.email, o.order_id, o.total_cents
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE c.city = 'Fairfax'
AND o.status = 'paid'
AND o.created_at >= TIMESTAMP '2026-01-01';
The meaning is simple: paid orders on or after 2026-01-01 for customers in Fairfax. The interesting part is how the engine walks from this text to concrete operators.
From text to a plan: one pipeline
Most relational engines follow a similar spine (names vary by product):
- Parse. Lexical and syntactic analysis produce a parse tree. Parentheses, keywords, and literals are handled here. Tables and columns are not guaranteed to exist yet.
-
Bind / semantic analysis. Names resolve to objects: tables, columns, types, visibility, and privileges. Which table owns
city, and what typeTIMESTAMP '2026-01-01'is, get fixed here. Failures usually show up as “relation does not exist” or “column does not exist,” not as a surprise at execution time. - Logical plan. A tree close to relational algebra: scan, select (σ / filter), project (π), join (⋈), and so on. There is still no “Hash Join” or “Index Scan.”
- Rewrite and cost-based optimize. Apply equivalence transforms (for example, push safe predicates closer to scans), enumerate candidate physical plans, estimate row counts and cost from statistics, and pick a plan that looks cheapest while staying semantically equivalent.
-
Physical plan. Concrete access paths and algorithms:
Seq Scan/Index Scan, Nested Loop / Hash / Merge Join, whether to sort, whether to materialize intermediates. - Execute. Pull rows, join, filter, and return the result set according to the physical plan.
Engine-generic vs PostgreSQL.
Parse → bind → logical → optimize → physical → execute is the shared skeleton. PostgreSQL’s Planner/Optimizer chapter describes generating scan plans for each base relation, then considering join strategies (nested loop, merge, hash) and assembling a full plan tree. Other engines (SQL Server, MySQL optimizers, and so on) carve the stages similarly, but operator names, cost models, and knobs differ.
What a logical plan looks like
For the running query, a simplified logical tree can look like this:
Project (c.email, o.order_id, o.total_cents)
└─ Join (o.customer_id = c.customer_id)
├─ Filter (c.city = 'Fairfax')
│ └─ Scan customers
└─ Filter (o.status = 'paid' AND o.created_at >= '2026-01-01')
└─ Scan orders
A logical plan answers what to compute, not how to compute it fastest. The optimizer may:
- Swap join inputs when the join is commutative;
- Push filters closer to the data source (predicate pushdown);
- Turn
Scan customersinto an index scan or a sequential scan in the physical stage.
Predicate pushdown: correctness first
Predicate pushdown means moving WHERE / ON conditions closer to base-table scans when semantics allow, so later operators see fewer rows.
For an inner join (JOIN / INNER JOIN), pushing c.city = 'Fairfax' onto the customers side is usually safe: every result row must satisfy both sides’ conditions anyway.
Outer joins need care. With customers c LEFT JOIN orders o ON ..., you cannot casually move a right-table-only filter into WHERE, or push it incorrectly before the right scan, without turning “preserve unmatched left rows” into inner-join semantics. Rules of thumb:
-
ONdecides how to match; -
WHEREfilters after the join result is formed; - For outer joins, the optimizer may push only predicates it can prove leave the result unchanged.
This article’s example is an inner join, so pushdown has more room. With LEFT JOIN, decide whether null-extended rows must survive before you chase performance.
Statistics, selectivity, and cardinality
Optimizers rarely “go by feel.” They use statistics on tables and indexes to estimate:
- Cardinality: how many rows a step is expected to produce;
-
Selectivity: what fraction of rows a predicate keeps.
city = 'Fairfax'may be rare;status = 'paid'may be common.
In PostgreSQL, ANALYZE (and autovacuum-triggered analysis) collects those stats for the planner. Stale stats often show up as a plan that says rows=100 while reality returns hundreds of thousands—and join choice and memory estimates go wrong with them.
Cost is not wall-clock time. PostgreSQL cost is a planner-internal relative unit (conventionally related to abstract work such as page I/O). When you compare two plans, you compare estimated cost—not milliseconds invented by a plain EXPLAIN. Milliseconds only appear after a real execution (see EXPLAIN ANALYZE below).
Physical plans: choosing an access path
For customers and orders, the planner generates several access path candidates and compares estimated cost:
| Path (PostgreSQL terms) | Plain meaning |
|---|---|
| Seq Scan | Read the whole table (or most of its pages) in order |
| Index Scan | Locate via an index, then fetch heap rows |
| Index Only Scan | Index covers needed columns; fewer heap fetches when visibility allows |
| Bitmap Index Scan + Bitmap Heap Scan | Collect many tids from indexes, then fetch heap pages in batches |
For the running query, plausible shapes include:
- Use
customers_city_idxto find Fairfax customers, then nested-loop probeorders_customer_id_idxpercustomer_id, filteringstatus/created_aton the orders side; - Use
orders_status_created_idxto pull “paid and recent enough” orders, then join back to customers and filter city; - Filter both sides, then Hash Join.
Having an index does not mean it will be used. If city = 'Fairfax' matches almost the whole table, a sequential scan can be cheaper: random heap fetches from an index can cost more than an ordered read. The planner compares estimated total cost, not “is there an index?”
Column order in composite indexes
orders_status_created_idx (status, created_at) fits predicates like:
o.status = 'paid' AND o.created_at >= TIMESTAMP '2026-01-01'
Equality on the leading column status comes first; then a range on created_at. A query with only a created_at predicate and no usable status constraint often gets little or no help from that index—the leftmost prefix decides whether the index can be used efficiently. That is engine-generic B-tree intuition; whether the planner picks it still depends on cost and stats.
Three join algorithms
For o.customer_id = c.customer_id, mainstream engines usually choose among these strategies (PostgreSQL’s docs list exactly these three):
1. Nested Loop Join
For each outer row, look up matches on the inner side. If the inner side can probe an index on the join key (for example, orders_customer_id_idx per customer), a small outer input plus a selective index works well. A large outer input with a heavy inner scan each time is painful.
2. Hash Join
Usually build a hash table on the smaller input, then probe with the other. Equality joins (=) fit; inequality joins generally do not use hash joins.
Memory pressure causes spilling. When the hash table or its partitions do not fit in a limit such as PostgreSQL’s work_mem, the engine writes partitions to temporary files and processes them in batches. Semantics stay the same; I/O rises, and runtime can diverge far from the estimate. That is a common reason a plan “looks fine” but runs badly.
3. Merge Join
Both inputs must be ordered on the join key (from an index order or an explicit Sort), then merged with a two-pointer scan. Merge Join is attractive when both sides are already ordered, or when the result needs that order anyway. If both sides need a large sort first, total cost can beat Hash Join—or lose to it.
There is no universally fastest algorithm. Data size, selectivity, existing order, equality vs inequality, and memory budget all change the winner.
EXPLAIN for the plan, EXPLAIN ANALYZE for reality
Using PostgreSQL as the concrete tool (other engines have analogs under different names):
EXPLAIN
SELECT c.email, o.order_id, o.total_cents
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE c.city = 'Fairfax'
AND o.status = 'paid'
AND o.created_at >= TIMESTAMP '2026-01-01';
EXPLAIN plans without executing (for an ordinary SELECT). You see the operator tree, estimated cost, and estimated rows—what the optimizer intends to do.
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
EXPLAIN ANALYZE actually executes the statement, then reports real row counts and timings per node. The official docs are explicit: the ANALYZE option runs the statement, not only the planner. So:
- For
SELECT: it reads data and spends CPU/I/O; the result set is discarded, but functions with side effects may still run; - For
INSERT/UPDATE/DELETE: it modifies data—use an isolated writable test environment instead of a read replica.
When you read a plan, check first whether estimated rows and actual rows differ by orders of magnitude. If they do, refresh stats (ANALYZE), check whether predicates are indexable, and only then start forcing a particular join shape.
Here is an illustrative plan shape (structure only—not a real measurement):
Hash Join
Hash Cond: (o.customer_id = c.customer_id)
-> Bitmap Heap Scan on orders o
Recheck Cond: ((status = 'paid') AND (created_at >= ...))
-> Bitmap Index Scan on orders_status_created_idx
-> Hash
-> Index Scan using customers_city_idx on customers c
Index Cond: (city = 'Fairfax')
With different statistics, the same SQL can become a Nested Loop. Plans depend on data, not only on syntax.
What to do with this
- Get the semantics right first (especially outer joins), then chase speed.
- Keep statistics reasonably fresh before you trust
EXPLAIN. - When comparing access paths and join algorithms, ask whether estimated row counts are credible—not only whether an index exists.
- When estimates look wrong, use
EXPLAIN ANALYZEin a safe environment; remember it executes. - Design composite indexes with the leftmost prefix in mind: equality columns before range columns when that matches your predicates.
Limits and a note on the rest of the series
This article deliberately stays narrow:
- No deep dive into parallel query, partition pruning, materialized views, or hint-syntax differences across engines;
- No tour of the optimizer search space (for example, PostgreSQL’s genetic query optimizer past a join-count threshold);
- No reproducible benchmark numbers—plans shift with data distribution, so copying someone else’s
EXPLAINoutput is not useful; - Examples lean on PostgreSQL terminology; for another engine, read that product’s official “execution plan” docs.
The Database Pipelines series will continue into later stages of how data moves through an engine and how plans become durable engineering practice. Exact later titles will be set when those pieces are written; this part does not pre-claim them. Get comfortable with text → logical → physical → execute, and denser plans get easier to read.
References
- PostgreSQL Documentation: Planner/Optimizer — official scan paths and the three join strategies
-
PostgreSQL Documentation: Using EXPLAIN — reading plan trees and
EXPLAIN ANALYZE -
PostgreSQL Documentation: EXPLAIN —
ANALYZEactually executes the statement - PostgreSQL Documentation: ANALYZE — how statistics feed the planner
About the author
I write about Applied AI, MCP, Azure AI Search, and RAG. Follow me on Dev.to and Hashnode for more.



Top comments (0)