9. SQL and the optimizer

Every optimiser answers the same question: of the many correct ways to run this query, which is cheapest? What makes this one different is that “cheapest” includes where the data is, and getting it wrong does not cost you a slow disk seek — it costs you a continent.

Two layers of planning

   ① LOGICAL / PHYSICAL PLANNING     the cost-based
      optimiser rewrites the query and chooses access
      paths, join order, join algorithms and indexes

   ② DISTRIBUTED PLANNING            the chosen plan is
      cut into pieces and scheduled onto the nodes that
      hold the data — "DistSQL"

Almost every surprising plan is decided in ①, and almost every surprising latency is decided in ②.

The cost-based optimiser

Cockroach Labs wrote a Cascades-style optimiser: transformation rules applied to an expression tree, each alternative costed, the cheapest kept. The familiar rewrites are all there — predicate pushdown, projection pruning, constant folding, join reordering, decorrelating subqueries, limit pushdown.

What it costs with is statistics, collected automatically:

SHOW STATISTICS FOR TABLE users;
CREATE STATISTICS ON email FROM users;          -- force a refresh
ANALYZE users;                                  -- Postgres-compatible spelling

   row count · distinct values · null count · histogram
   per column, refreshed automatically as tables change

Access paths

For each table the optimiser picks one way in:

   PRIMARY INDEX SCAN     the table itself (ch 3)
   SECONDARY INDEX SCAN   the index, then an INDEX JOIN
                          back to the primary index for
                          columns the index lacks
   COVERING INDEX SCAN    the index alone — no join,
                          because STORING has the columns
   CONSTRAINED SCAN       only the key span the WHERE
                          clause implies
   FULL SCAN              every row. on a big table this
                          is a cluster-wide event.

The index join is the one worth watching, because it is the same structure chapter 3 traced through the key space, and it costs one extra lookup per row:

-- index on (email) only: scan the index, then join back
SELECT id, email, region FROM users WHERE email = 'alice@example.com';

-- index on (email) STORING (region): no join at all
CREATE INDEX ON users (email) STORING (region);

Distributed execution

Once a plan exists, DistSQL decides where each operator runs. The rule is simple and worth internalising: push computation to the data, not data to the computation.

   SELECT count(*) FROM orders WHERE status = 'paid';

   n3 (gateway)
     ├── TableReader on n1  → filter → local count = 412
     ├── TableReader on n5  → filter → local count = 388
     └── TableReader on n2  → filter → local count = 501
                                          ▼
                              final aggregation on n3 = 1301

   Three integers cross the network. Not three million rows.

EXPLAIN tells you which mode was chosen:

   distribution: local   everything runs on the gateway;
                         KV requests still go out, but
                         all processing is at home

   distribution: full    operators are scheduled onto
                         the nodes holding the ranges

Neither is automatically better. Distributing a query that touches a handful of rows adds setup overhead for nothing; keeping a hundred-million-row aggregation local floods the gateway.

Join algorithms

   HASH JOIN     build a hash table from the smaller side,
                 probe with the larger. the default for
                 two large inputs.

   MERGE JOIN    both inputs already ordered on the join
                 key — stream them together, no memory.
                 free when the join key prefixes both
                 primary keys.

   LOOKUP JOIN   for each row on the left, look the key
                 up in an index on the right. excellent
                 when the left side is SMALL, catastrophic
                 when the optimiser was wrong about that.

   INVERTED JOIN for JSONB and spatial predicates, via an
                 inverted index.

In a distributed setting the interesting question about a hash join is where the build happens. If both sides are partitioned the same way, each node joins its own slice and nothing crosses the network. If not, rows must be shuffled by hash of the join key first — which is the distributed equivalent of a sort spill, and shows as a large network figure in EXPLAIN ANALYZE.

Reading a plan

EXPLAIN SELECT u.email, o.total
        FROM users u JOIN orders o ON o.user_id = u.id
        WHERE u.region = 'eu-west';

   • distribution: full
   • vectorized: true
   │
   └── hash join
       │ equality: (id) = (user_id)
       ├── scan users@users_region_idx
       │     spans: [/'eu-west' - /'eu-west']     ◄ constrained
       │     estimated row count: 12,400
       └── scan orders@primary
             spans: FULL SCAN                     ◄ the problem
             estimated row count: 4,100,000

Four things to look at, in order:

   1. FULL SCAN on a large table        → missing index
   2. estimated vs actual row counts    → stale statistics
   3. the join algorithm chosen         → and why
   4. distribution and network bytes    → where the time went

And with real numbers:

EXPLAIN ANALYZE (DIST, VERBOSE) SELECT …;

which adds actual row count, KV time, contention time, network usage and per-node timings. contention time is the direct link back to chapter 5: a slow query that spends its life waiting on another transaction’s intents is not an optimiser problem at all.

Vectorised execution

Below the plan sits a columnar, batch-at-a-time execution engine — the same idea as the one the DuckDB guide explains at length, adopted here because interpreting one row at a time wastes most of a modern CPU. vectorized: true in a plan means the whole plan could be executed that way; occasionally an operator has no vectorised implementation and the plan falls back.

You rarely tune this. It matters mostly as an explanation for why analytical queries are respectable rather than terrible, and why memory limits (sql.distsql.temp_storage.workmem) exist — a hash table that exceeds them spills to disk.

Prepared statements and the plan cache

Planning is not free, and on a simple point lookup it can be a meaningful share of the total. CockroachDB caches plans per session for prepared statements, so a driver that prepares once and executes many times avoids repeating the work.

PREPARE get_user AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user (4718);

Most drivers do this for you when you use parameterised queries — which you should be doing anyway, for the same injection reasons as everywhere else. String-interpolated SQL costs you a fresh plan every time as well as a security hole.

   parameterised   one plan, reused, and the optimiser
                   can still specialise on the values
                   through histogram estimates

   interpolated    a new fingerprint per literal, so the
                   cache never hits, and your statement
                   statistics become thousands of rows
                   instead of one

That second effect is worth knowing on its own: interpolated literals make the Statements page useless, because the query you are trying to find is smeared across ten thousand fingerprints.

Subqueries, CTEs and the shapes that go wrong

-- correlated subquery: the optimiser will try to
-- decorrelate it into a join. usually it succeeds.
SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- a CTE is not a fence, but it is an optimisation
-- barrier when it is referenced more than once
WITH recent AS (SELECT * FROM orders WHERE ts > now() - INTERVAL '1d')
SELECT … FROM recent r1 JOIN recent r2 ON …;

Two shapes cause most of the bad plans people report:

   ① OR across different columns
        WHERE email = $1 OR phone = $2
      neither index constrains the scan on its own.
      the optimiser may union two scans, or may give
      up and scan everything. UNION the two queries
      explicitly if it matters.

   ② functions on the indexed column
        WHERE lower(email) = $1
      the index on email cannot be used. build an
      EXPRESSION INDEX on lower(email) instead.

Neither is CockroachDB-specific — PostgreSQL behaves the same way — but the penalty is larger here, because a full scan is not a local disk read, it is a cluster-wide fan-out.

Hints, and when to use them

-- force an index
SELECT * FROM users@users_email_idx WHERE email = 'a@b.c';

-- force a join algorithm
SELECT … FROM a INNER HASH JOIN b ON …;
SELECT … FROM a INNER LOOKUP JOIN b ON …;

-- refuse to run a full scan rather than silently do one
SET disallow_full_table_scans = on;

That last one belongs in your application’s session defaults on any large OLTP schema. A plan regression that turns into a full scan is one of the few ways a healthy CockroachDB cluster falls over, and failing the query is nearly always better.

Where it differs from PostgreSQL

   • the planner is Cascades-style, not PostgreSQL's,
     so plans for the same schema can legitimately differ
   • costs include NETWORK and RANGE LOCALITY
   • no PostgreSQL extensions, so no pg_hint_plan,
     no pg_stat_statements — the equivalents are
     crdb_internal tables and the DB Console
   • some features arrived late or are absent:
     check the release notes, not your instincts

Chapter 15 gives the honest list.

What to take from this chapter

Planning happens in two stages: a cost-based (Cascades-style) optimiser choosing access paths and joins, then DistSQL scheduling the plan onto the nodes that hold the data.
Costs are driven by automatic statistics. After a bulk load or restore, ANALYZE first — stale statistics are the usual cause of an absurd plan.
The access path to watch is the index join; remove it with a covering index (STORING).
DistSQL’s rule is push computation to the data: distribution: full returns aggregates rather than rows. Neither local nor full is always right.
Joins are hash, merge (free when both inputs are already ordered on the key), lookup (great with a small left side, catastrophic when the estimate was wrong) and inverted.
Read a plan in this order: full scans, estimated versus actual rows, join algorithm, distribution and network. EXPLAIN ANALYZE also exposes contention time, which points back at chapter 5.
Execution is vectorised, in the DuckDB mould; memory limits make large hash joins spill to disk.
Set disallow_full_table_scans on large OLTP schemas, and prefer statistics and covering indexes over hints.

Next: schema — primary keys as physical decisions, index design, hash sharding, and online schema changes that do not lock.

Written by Sepuri Sai Krishna