Indexes, Sargability & Reading a Plan

You can write the SQL. A senior engineer also asks: why is this query slow, what does the plan say, and what does it cost?

A plan is the list of steps the database chooses to run your query. The part that chooses is the planner. An index is one tool the planner can pick.

This chapter uses PostgreSQL and the shop. The shop has six orders, so the planner would just read them all.

To see index behaviour, imagine orders has 10 million rows. That is an assumption, not a measurement.

Every plan in this chapter is simplified. It shows the shape of the plan, not real costs or times. Three questions guide the chapter:

1. Can this query use an index? That is sargability.
2. Is the index worth having? That depends on selectivity.
3. What does the index cost? Every write pays for it.

Indexes belong to row stores such as PostgreSQL, which keep each row together. Columnar warehouses keep each column together and work differently. The section "Warehouses have no B-tree indexes" says how.

An index is a sorted copy of some columns. Each entry also holds a pointer: the place of the full row in the table.

By default, PostgreSQL keeps the copy in a B-tree. A B-tree is a tree of sorted blocks.

The database starts at the top and picks one branch at each level. It reaches an entry in a few steps, even for millions of rows.

order_id is the primary key of orders, so PostgreSQL already built an index on it. Here is that index for the shop. It is sorted by order_id, and each entry points to a row place:

-- Look up one order by its id.
SELECT *
FROM orders
WHERE order_id = 104;

Because the copy is sorted, it does three jobs well:

• Find one value: order_id = 104.
• Read a range: order_date between two dates. Find the start, then walk forward.
• Read in order: with an index on order_date, ORDER BY order_date LIMIT 3 needs no sort step.

EXPLAIN shows the plan without running the query. Put it in front of the order lookup:

EXPLAIN
SELECT *
FROM orders
WHERE order_id = 104;

Now imagine the same query with no index on order_id. The planner has only one choice:

EXPLAIN
SELECT *
FROM orders
WHERE order_id = 104;

These are the four main scan types. This table says when each one fits:

EXPLAIN ANALYZE runs the query for real. It adds what really happened next to what the planner guessed:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE order_id = 104;

Selectivity is how few rows a value matches. A value that matches few rows is selective. An index helps most when it narrows the search a lot.

Count the orders for each status in the shop:

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY status;

Now imagine 10 million rows and an index on status. About 9 in 10 orders are paid, and about 1 in 1,000 is pending. These shares are assumptions.

The planner ignores the index for paid because an index scan jumps around the table. Reading nine million rows by jumping costs more than one straight read.

The planner decides from statistics about each column, such as how common each value is. The command ANALYZE refreshes them.

A background process called autovacuum usually runs ANALYZE for you. Old statistics make estimated rows differ from actual rows.

A predicate is a test in WHERE. A predicate is sargable when the database can use an index to jump to the matching rows. The name comes from "search argument able".

The index stores the column alone, sorted. We call this the bare column: no function and no math on it.

If you change the column inside the test, the stored values no longer match. So keep the bare column on one side, and do the work on the other side.

A function on the column

You want all orders from January 2026. The first version wraps order_date in date_trunc, which cuts a date back to the first day of its month. The second asks for a range:

-- Before: the function hides the column.
SELECT order_id
FROM orders
WHERE date_trunc('month', order_date) = '2026-01-01';

-- After: a range on the bare column.
SELECT order_id
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2026-02-01';

A wildcard at the start of LIKE

Imagine customers also has 10 million rows and an index on name. A B-tree is sorted from the first letter.

A pattern that starts with a letter can use it. A pattern that starts with % cannot:

-- Leading wildcard: no sorted start to jump to.
SELECT name FROM customers WHERE name LIKE '%a';

-- Trailing wildcard: jump to 'A', then walk.
SELECT name FROM customers WHERE name LIKE 'A%';

A type that does not match

order_id is an integer. A literal is a value you type in the query, and 104.0 is a numeric literal. PostgreSQL then converts order_id to numeric on every row, and the integer index cannot help:

-- Before: 104.0 is numeric, the column is integer.
SELECT order_id FROM orders WHERE order_id = 104.0;

-- After: the literal has the column's type.
SELECT order_id FROM orders WHERE order_id = 104;

OR across two columns

A test like customer_id = 3 OR status = 'refunded' needs an index on each column. If only customer_id has one, the planner reads the whole table. A UNION rewrite does not fix that.

Say both columns have an index. Here are the OR form and the UNION form. Both return the same rows:

-- One OR across two columns.
SELECT order_id FROM orders
WHERE customer_id = 3 OR status = 'refunded';

-- The same question as two queries.
SELECT order_id FROM orders WHERE customer_id = 3
UNION
SELECT order_id FROM orders WHERE status = 'refunded';

Select a key such as order_id. UNION removes duplicate output rows, so without a key it can drop real rows.

In PostgreSQL the OR form is often fine. The UNION rewrite helps when the OR spans two joined tables, such as c.country = 'IN' OR o.status = 'refunded'. One index cannot serve both tables.

-- Before: the OR mixes customers and orders.
SELECT o.order_id
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.country = 'IN' OR o.status = 'refunded';

-- After: each branch filters one table.
SELECT o.order_id
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.country = 'IN'
UNION
SELECT o.order_id
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'refunded';

Read the plan before you rewrite.

Arithmetic on the column

Math on the column has the same problem as a function. The database cannot match amount * 2 to the stored amounts. Move the math to the other side, or remove it:

-- Before: arithmetic on the column.
SELECT order_id FROM orders WHERE amount * 2 > 100;

-- After: the bare column.
SELECT order_id FROM orders WHERE amount > 50;

When you cannot rewrite

Sometimes you really need the function, such as a search that ignores upper and lower case. Then index the expression itself. The query must use the same expression:

CREATE INDEX customers_lower_name_idx
  ON customers (LOWER(name));

SELECT customer_id, name
FROM customers
WHERE LOWER(name) = 'asha';

A composite index has more than one column. Take an index on (customer_id, order_date).

It is sorted by customer_id first. Inside each customer, it is sorted by order_date.

Here is that index for the shop:

CREATE INDEX orders_customer_date_idx
  ON orders (customer_id, order_date);

-- Customer 1, orders from 5 January on.
SELECT order_id, amount
FROM orders
WHERE customer_id = 1
  AND order_date >= '2026-01-05';

This index serves some queries and not others. This is the left-prefix rule: the search must start with the first column.

The third query returns orders 104, 105 and 106 in the shop. With 10 million orders, the planner usually scans the table.

It may read the whole index instead, which is smaller. That is only a small gain.

PostgreSQL 18 added a skip scan. It jumps from one customer_id to the next and searches the dates inside each one.

It helps only when the first column has few different values.

Column order follows two rules:

• Put columns tested with = first.
• Put the range column last.

The order of the tests inside WHERE does not matter.

A normal index scan has two steps: read the index, then read the table row. A covering index holds every column the query needs. Then the database can skip the second step.

INCLUDE adds extra columns to the index entry. They are stored but not sorted:

CREATE INDEX orders_customer_amount_idx
  ON orders (customer_id) INCLUDE (amount);

SELECT amount
FROM orders
WHERE customer_id = 1;

Look at the plan for that query:

EXPLAIN
SELECT amount
FROM orders
WHERE customer_id = 1;

An Index Only Scan still reads the table for rows on recently changed pages. VACUUM, a cleanup job, marks pages with no recent changes in a map called the visibility map.

EXPLAIN ANALYZE counts the other table reads as Heap Fetches.

An index speeds up some reads. It slows down writes. When you INSERT a row, PostgreSQL writes the table row and one entry in every index.

Say orders has three indexes: the primary key, (customer_id, order_date), and status. One new order is then four writes:

INSERT INTO orders (order_id, customer_id, status, amount, order_date)
VALUES (107, 5, 'paid', 30.00, '2026-01-22');

An UPDATE writes a new row version. If an indexed column changes, every index gets a new entry, not only that column's index.

If no indexed column changes and the page has room, PostgreSQL skips the index work. This is called a HOT update (heap-only tuple; a tuple is one row version). The new row version lives only in the table.

The benefit is per query. The cost is per write, and nobody sees it on one slow statement. An index that no query reads has all the cost and none of the benefit.

PostgreSQL counts index use in pg_stat_user_indexes. This query lists indexes that were never scanned. It skips unique indexes, which enforce a rule even when no query reads them:

SELECT s.relname      AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
ORDER BY s.relname, s.indexrelname;

Use the list as a start. Check each index before you drop it. Ask five questions:

• Does it enforce a rule? Unique and primary-key indexes do.
• Is the time window long enough? A month-end report may run once a month.
• Is it the only index for a rare but urgent query? Search the query log for its columns.
• Does a foreign key rely on it? Deletes on the parent table can turn into scans.
• Did you check every server? Counts are kept per server, so a read replica has its own.

A partial index covers only the rows that match a WHERE condition. It is small, and it only changes when a matching row changes.

Most orders become paid. Support often asks for the few that are still pending. Index only those:

CREATE INDEX orders_pending_idx
  ON orders (order_date)
  WHERE status = 'pending';

SELECT order_id, order_date
FROM orders
WHERE status = 'pending'
ORDER BY order_date;

The planner uses a partial index only when it can see that every row your WHERE asks for is in the index. The safe way is to repeat the condition exactly.

This also answers the selectivity problem. A full index on status is ignored for paid. A partial index on the rare value keeps the benefit and skips the cost of the common ones.

Normal Snowflake and BigQuery tables have no B-tree indexes. There is nothing to create. A warehouse reads many rows to build a summary, so the goal is to read fewer bytes, not to find single rows.

They use other tools. Data is split into partitions, and the engine skips the partitions a filter does not need. This skipping is called pruning.

Clustering keeps similar values stored together, so pruning works better.

The ideas are similar, but the tools differ:

Only the sargability habit stays the same: keep the bare column in the filter. Costs, partitions and bytes scanned are taught in the next chapter.

These four problems use system tables (the catalog): column statistics, a query log, and an index list. The workspace has no EXPLAIN. Index decisions often come from statistics and history, not from one plan.

Do the selectivity audit and the unused-index report together. They answer the same question in two ways.