Indexes, Sargability & Reading a Plan

An index is a sorted copy of some columns with a pointer back to the row. That one sentence explains almost everything an index can and cannot do: it can find rows by a prefix of its sorted key, and it cannot help with anything that is not a prefix.

This chapter is about the transactional side of SQL — the database behind an application, where a query looks up a handful of rows out of millions. It is the half of performance work a data engineer meets when they touch the source system rather than the warehouse.

Four ideas carry most of the value: selectivity decides whether an index is worth having, sargability decides whether a query can use one, the leftmost-prefix rule decides what a composite index covers, and every index has a write cost that nobody sees on a dashboard.

selectivity

Distinct values as a share of rows. High selectivity means a lookup narrows the search a lot; a column with six values narrows almost nothing.

sargable

A predicate the engine can satisfy by seeking the index — from "Search ARGument ABLE". Wrapping the column in a function usually destroys it.

leftmost prefix

A composite index on (a, b, c) can serve queries on (a), (a, b) and (a, b, c) — and not on (b) or (c) alone.

covering index

An index that contains every column a query needs, so the engine never has to visit the table row at all.

A B-tree holds the key columns in sorted order in a shallow tree — typically three or four levels for millions of rows, so a lookup is a handful of page reads rather than a scan.

Because it is sorted, it supports exactly three access patterns: find one key, find a contiguous range of keys, and read the keys in order without a sort. Everything an index is good for is one of those three.

The last one is easy to forget. An index on (created_at) serves `ORDER BY created_at LIMIT 20` without sorting anything, which is why a query with an ORDER BY and a small LIMIT can be nearly free.

-- 1. Point lookup: seek straight to the key
select * from orders where order_id = 84213;

-- 2. Range scan: seek to the start, walk until the end
select * from orders
where created_at >= '2026-03-01' and created_at < '2026-04-01';

-- 3. Ordered read: the index is already in order, so no sort step
select order_id, created_at from orders
order by created_at desc
limit 20;

An index earns its place by narrowing the search. Selectivity — distinct values as a share of rows — is the first-pass measure of how much it narrows.

A primary key is 100% selective: one value, one row. A status column with six values across two million rows is effectively 0%: the most common value points at hundreds of thousands of rows, and reading those through an index is slower than scanning the table.

The rule of thumb is that below roughly 1% selectivity an index is unlikely to be chosen. Like all rules of thumb it has a real exception, and it is worth knowing which one.

select
  column_name,
  round(100.0 * distinct_values / total_rows, 2) as selectivity_pct,
  case
    when 100.0 * distinct_values / total_rows >= 1 then 'worth indexing'
    else 'too few distinct values'
  end as verdict
from column_stats
where table_name = 'orders'
order by selectivity_pct desc, column_name;

A predicate is sargable when the engine can turn it into a seek on the index. The most common way to destroy sargability is to wrap the column in a function — the index stores the column, not the function of the column.

The symptom is always the same and always quiet: the query works, returns the right rows, and reads the entire table to do it. Nothing errors and the index still shows in the schema.

Every fix is the same shape: move the transformation off the column and onto the constant, or index the expression instead.

-- NOT sargable: the index on created_at cannot be used
select * from orders
where strftime('%Y-%m', created_at) = '2026-03';

-- sargable: a half-open range on the bare column
select * from orders
where created_at >= '2026-03-01'
  and created_at <  '2026-04-01';

-- each left-hand form is unusable; each right-hand form is a seek
where lower(email) = 'ada@acme.com'      -->  index on lower(email), or store it lowered
where email like '%@acme.com'            -->  no fix: a B-tree has no suffix order
where sku like 'SKU-A%'                  -->  already sargable: a known prefix seeks
where cast(order_id as text) = '84213'   -->  where order_id = 84213
where amount + 0 > 100                   -->  where amount > 100
where date(created_at) = '2026-03-04'    -->  half-open range over that day

A composite index on (a, b) is sorted by a, and within each a by b. That single fact determines everything it can serve.

It answers queries on a, and on a and b together. It cannot answer a query on b alone, any more than a phone book sorted by surname can find everyone whose first name is John.

The practical consequences are two: column order is a design decision, and a single-column index on the first key is redundant the moment the composite exists.

-- served: seek on the first key
where customer_id = 4021

-- served: seek on the first key, then range on the second within it
where customer_id = 4021 and created_at >= '2026-03-01'

-- served: the index is already in this order
where customer_id = 4021 order by created_at desc limit 10

-- NOT served: created_at is the second key, and there is no order on it alone
where created_at >= '2026-03-01'

select
  narrow.index_name  as redundant_index,
  wide.index_name    as covered_by,
  narrow.size_mb     as reclaimable_mb
from index_catalog as narrow
join index_catalog as wide
  on  wide.table_name = narrow.table_name
  and wide.index_name <> narrow.index_name
  and wide.key_columns like narrow.key_columns || ', %'
order by reclaimable_mb desc;

Every index has to be maintained. An insert writes the row and then writes into every index on the table; an update that touches an indexed column rewrites those index entries too.

That cost never appears on a query dashboard, because it is spread across every write rather than concentrated in one slow statement. It shows up as inserts that got gradually slower over two years.

The other half of the accounting is the index that is never read. It has all of the cost and none of the benefit.

select index_name, table_name, key_columns, size_mb, writes_last_30d
from index_catalog
where scans_last_30d < 100
order by size_mb desc, index_name;

Snowflake, BigQuery and Redshift have no B-tree indexes. Not "they are discouraged" — there is nothing to create.

The reason is the workload. A transactional database finds a handful of rows out of millions, which is what a sorted index is for. A warehouse scans millions of rows to produce a summary, and the way to make that cheaper is to read fewer bytes, not to find rows faster.

The vocabulary changes accordingly: partitioning replaces the range seek, clustering replaces the sort order, and column pruning replaces the covering index.

Four problems on the three catalogue tables this chapter uses: column statistics, a query log, and the index list. There is no EXPLAIN in the workspace — and index decisions are made from statistics and query history far more often than from a single plan.

Work them in order. The selectivity audit and the unused-index report answer the same question from opposite ends, and seeing that they agree is the point.