8. The optimizer

The optimiser is eleven milliseconds that decide whether your query takes a second or a minute. This chapter is what it does, in the order it does it, and what to do when it gets it wrong.

The passes, in order

   LOGICAL PLAN from the binder
     ↓
   1  EXPRESSION REWRITER
        constant folding, arithmetic
        simplification, CASE collapsing,
        IN → OR or hash set
     ↓
   2  FILTER PULLUP / PUSHDOWN
        move filters as far down as they
        can legally go
     ↓
   3  REGEX / LIKE optimisation
        'abc%'  → a range predicate on a
        prefix, which zone maps can use
     ↓
   4  JOIN ORDER OPTIMISER
        cardinality estimation, then dynamic
        programming over join orders
     ↓
   5  DELIMINATOR / subquery flattening
        correlated subqueries → joins
     ↓
   6  UNUSED COLUMN REMOVAL
        (projection pushdown)
     ↓
   7  COMMON SUBEXPRESSION ELIMINATION
     ↓
   8  STATISTICS PROPAGATION
        push min/max through the plan; prune
        impossible branches entirely
     ↓
   9  TOP-N, LIMIT pushdown, IN-clause
        rewriting, sampling pushdown
     ↓
   PHYSICAL PLAN

Two of these deserve detail because they cause nearly all the problems.

Cardinality estimation

Every join-order decision rests on one question: how many rows will come out of this? DuckDB estimates it from statistics.

   WHERE IT GETS THE NUMBERS

   ─ base table row counts: exact, from the
     catalog or the Parquet footer
   ─ per-column min/max: exact, from segment
     zone maps
   ─ distinct counts: approximate, HyperLogLog
     sketches maintained per column
   ─ NULL counts: exact
   ─ NO histograms. There is no per-column
     value distribution.

The absence of histograms is the important line. It means selectivity for a range predicate is estimated by assuming a uniform distribution between min and max.

   WHERE amount BETWEEN 100 AND 200
   column min 0, max 10,000

   estimate: (200-100) / (10,000-0)
           = 1% of rows

   REALITY: if 80% of your amounts are
   between 100 and 200, the estimate is
   off by 80×.

For equality predicates it uses the distinct count:

   WHERE country = 'DE'
   approx_distinct(country) = 190

   estimate: 1/190 ≈ 0.5% of rows

   REALITY: if 60% of your rows are 'DE',
   the estimate is off by 120×.

Skew is the enemy of cardinality estimation, and it is the reason the plan you got is occasionally absurd. The same limitation exists in every optimiser here — PostgreSQL’s planner mitigates it with histograms and most-common-value lists, Trino with table statistics you have to ANALYZE for, Spark with adaptive query execution that re-plans mid-flight.

Join ordering

Given estimated cardinalities, DuckDB searches for a good join order using dynamic programming — the classic approach, restricted so it does not explode.

   FROM a JOIN b JOIN c JOIN d

   The DP builds up the best plan for every
   SUBSET of relations:

     {a,b} {a,c} {a,d} {b,c} {b,d} {c,d}
     {a,b,c} {a,b,d} {a,c,d} {b,c,d}
     {a,b,c,d}

   cost of a subset = min over all the ways
   of splitting it into two already-solved
   halves.

   Complexity is exponential in the number
   of relations. Beyond a threshold
   (around 10–12 joins) DuckDB falls back
   to a greedy heuristic.

Two decisions come out of this, and they are different:

   1. THE ORDER. Which joins happen first.
      Getting this wrong means materialising
      a huge intermediate result.

   2. THE SIDE. For each hash join, which
      input builds the hash table.
      → ALWAYS the smaller one.
      → getting this wrong means building a
        41-million-row hash table to probe
        it with 480,000 rows.

The side matters more often and is easier to diagnose. In an EXPLAIN ANALYZE the build side is the left child of the HASH_JOIN node; if its row count is the larger of the two, the estimate was wrong.

Reading `EXPLAIN` and `EXPLAIN ANALYZE`

EXPLAIN SELECT ...;          -- the plan, no execution
EXPLAIN ANALYZE SELECT ...;  -- run it, annotate with
                             -- real timings and row counts

EXPLAIN ANALYZE is the one that matters, because the gap between estimated and actual is the diagnosis.

   ┌───────────────────────────┐
   │        HASH_JOIN          │
   │      INNER  a.id = b.id   │
   │   ─────────────────────   │
   │   EC: 12,000              │  ← ESTIMATED
   │   ─────────────────────   │
   │   8,412,993 rows          │  ← ACTUAL
   │        (11.42s)           │
   └───────────────────────────┘

   Estimated 12 thousand. Got 8.4 million.
   → 700× wrong.
   → every decision above this node was made
     on a false premise.

A large EC-versus-actual gap is the single highest-signal thing in a DuckDB profile. Find the lowest node where the gap first appears — everything above it inherits the error.

The four things that actually go wrong

1. The build side is the big one

   SYMPTOM   memory climbing, then a spill,
             on a join where one side is
             obviously small.

   CAUSE     the estimate for the "small"
             side was wrong, usually because
             a filter's selectivity was
             misjudged.

   FIX       materialise the small side first:

             CREATE TEMP TABLE small AS
               SELECT ... WHERE ...;
             -- now the row count is exact
             SELECT ... FROM big JOIN small ...

Materialising an intermediate result replaces an estimate with a fact, and it is the most reliable optimiser fix available. It costs a write and it usually pays for itself many times over.

2. A filter that cannot push down

   ✗ WHERE lower(country) = 'de'
        the function wraps the column.
        Zone maps on `country` are useless.

   ✓ WHERE country ILIKE 'de'
        or store it normalised.

   ✗ WHERE date_trunc('month', ts) = '2026-09-01'
        same problem, on the column that
        actually correlates with insertion
        order — so this is expensive.

   ✓ WHERE ts >= '2026-09-01'
       AND ts <  '2026-10-01'
        a range on the raw column.
        Zone maps work. Row groups skip.

Wrapping a column in a function is the commonest self-inflicted slowdown in any columnar engine, and it is the same advice ClickHouse and every warehouse give. The rule: keep the column bare on one side of the comparison.

3. Correlated subqueries that did not flatten

DuckDB is unusually good at this — the “unnesting arbitrary subqueries” work from the HyPer line of research means most correlated subqueries become joins automatically. But not all.

   CHECK THE PLAN for a DELIM_JOIN.

   A DELIM_JOIN is DuckDB's mechanism for a
   subquery it could not fully flatten. It
   is not always slow — but if one sits
   under a large scan, that is where to look.

4. The estimate was fine and the data is skewed

   SELECT ... FROM events JOIN users
     ON events.user_id = users.id

   If 40% of events have user_id = 0
   (a sentinel for "anonymous"), one hash
   bucket holds 40% of the rows and one
   thread does 40% of the work.

Hash-join skew is not an optimiser problem and no plan change fixes it. The answers are the usual ones: filter the sentinel out and handle it separately, or salt the key. This is the same phenomenon Spark’s chapter on skew describes at cluster scale; it happens on one machine too, it just looks like “one core busy, fifteen idle.”

The knobs

There are few, deliberately.

-- turn the whole optimiser off, to see what it was doing
PRAGMA disable_optimizer;
PRAGMA enable_optimizer;

-- disable one pass by name (for debugging)
SET disabled_optimizers = 'filter_pushdown';

-- the join-order DP threshold
SET threads = 16;   -- affects the parallelism, not
                    -- the plan

There are no join hints. No /*+ BROADCAST */, no FORCE INDEX, no way to pin an order. This is a deliberate position: the fix for a bad plan is better statistics, and the way to get better statistics is to materialise an intermediate result.

Some people find that frustrating. It is also the reason DuckDB queries do not accumulate a decade of hint comments that stopped being true three schema changes ago.

Making the optimiser’s job easier

Five habits, in rough order of value:

1. STORE PARQUET, NOT CSV. Footers carry
   row counts and per-chunk statistics.
   A CSV has neither, so every estimate
   is a guess.

2. KEEP COLUMNS BARE in predicates.
   `ts >= x AND ts < y`, never
   `date_trunc(ts) = x`.

3. INSERT IN THE ORDER YOU FILTER IN.
   Chapter 3's point: zone maps only skip
   when values cluster.

4. MATERIALISE when an estimate is clearly
   wrong. A CREATE TEMP TABLE turns a guess
   into a count.

5. FILTER EARLY IN CTEs. DuckDB inlines
   CTEs by default, so a filter in the outer
   query usually pushes into the CTE — but
   writing it in the CTE guarantees it.

And the meta-habit: run EXPLAIN ANALYZE before optimising anything. The number of DuckDB queries “optimised” by rewriting a join that was taking four milliseconds is large.

What to take from this chapter

Nine passes, in a fixed order. The two that matter are cardinality estimation and join ordering; the rest are cheap and almost always right.
There are no histograms. Statistics are exact row counts, exact min/max, approximate distinct counts (HyperLogLog) and null counts. Range selectivity assumes a uniform distribution, so skew is what breaks estimates.
Join ordering is dynamic programming over subsets, falling back to a greedy heuristic past roughly 10–12 relations.
Two separate decisions: the order (avoid huge intermediates) and the side (always build on the smaller input). The side is wrong more often and is easier to spot.
EXPLAIN ANALYZE’s highest-signal number is EC versus actual. Find the lowest node where the gap appears — everything above inherited the error.
Four common failures: the wrong build side, a filter wrapped in a function so it cannot push down, a DELIM_JOIN from an unflattened subquery, and hash-join skew, which no plan change fixes.
There are no join hints, deliberately. The supported fix for a bad plan is materialising an intermediate result, which replaces an estimate with a fact.
Five habits: Parquet not CSV, bare columns in predicates, insert in filter order, materialise when the estimate is clearly wrong, and filter inside CTEs.
Run EXPLAIN ANALYZE before optimising anything.

Next: extensions — httpfs, Iceberg, Delta, the database scanners, and how far a predicate travels once it leaves DuckDB.

Written by Sepuri Sai Krishna