PostgreSQL Indexes Explained: How They Actually Improve Query Performance

"Add an index" is the most common piece of database advice and the least often explained. It is also wrong often enough to be dangerous — I have watched an index make a query slower, and watched a four-column index sit unused for a year because its columns were in the wrong order.

This is what is actually happening underneath, and how to tell whether an index is helping.

The problem an index solves#

Without an index, finding rows matching a condition means reading every row. PostgreSQL calls this a sequential scan:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..18334.00 rows=12 width=84)
                    (actual time=0.412..84.203 rows=14 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999986
Planning Time: 0.089 ms
Execution Time: 84.230 ms

Rows Removed by Filter: 999986 is the whole story. PostgreSQL read a million rows off disk to return fourteen. It had no way of knowing where those fourteen were.

What a B-tree index actually is#

The default index type in PostgreSQL is a B-tree: a balanced, sorted tree, where each node holds a range of key values and pointers to child nodes. Leaf nodes hold the indexed value plus a pointer to the physical row location on disk.

flowchart TB
    Root["root: 1 … 1,000,000"]
    Root --> A["1 … 333,333"]
    Root --> B["333,334 … 666,666"]
    Root --> C["666,667 … 1,000,000"]
    B --> L1["leaf: 400,001 … 400,200<br/>value → row location"]
    B --> L2["leaf: 400,201 … 400,400<br/>value → row location"]

Finding a value means descending the tree, comparing at each level. Because the tree is balanced and each node has a high fan-out, a million-row table needs roughly three or four node reads instead of a million row reads. That is why the improvement is not 10× — it is orders of magnitude, and it grows as the table does.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Index Scan using idx_orders_customer_id on orders
    (cost=0.42..37.18 rows=12 width=84)
    (actual time=0.031..0.048 rows=14 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.142 ms
Execution Time: 0.071 ms

84 ms to 0.07 ms. Note what changed in the plan: Filter became Index Cond. That distinction is the thing to look for.

Reading EXPLAIN ANALYZE#

Three habits make plans readable.

Use the right invocation. EXPLAIN alone shows estimates. EXPLAIN ANALYZE actually runs the query and shows reality. Add BUFFERS to see how much I/O happened:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

Compare estimated to actual rows. In (cost=... rows=12) (actual ... rows=14), the planner estimated 12 and found 14 — good. When the estimate is off by orders of magnitude, the planner is choosing strategies based on bad information, and the fix is usually statistics (ANALYZE orders;) or an extended statistics object, not an index.

Read the innermost node first. Costs are cumulative, so the top line's time includes everything below it. The node where time jumps is the one that matters.

Composite indexes and why order decides everything#

For a query filtering on several columns, one index over several columns beats several single-column indexes:

CREATE INDEX idx_orders_customer_status_created
    ON orders (customer_id, status, created_at);

A composite B-tree is sorted by the first column, then within equal first values by the second, and so on — like a phone book sorted by surname, then first name.

This produces the leftmost prefix rule: the index can serve a query only if the query constrains a leading prefix of its columns.

That last row is the one that catches people. The index still helps, but created_at cannot be used for a seek because status sits between them and is unconstrained.

You can see the sort disappear in the plan: a query with a matching index shows no Sort node, while one without it shows Sort Method: quicksort Memory: 4096kB or, worse, external merge Disk: 51200kB.

Index-only scans#

If every column a query needs is in the index, PostgreSQL can answer entirely from the index and never touch the table:

CREATE INDEX idx_orders_customer_total
    ON orders (customer_id) INCLUDE (total_amount);

SELECT total_amount FROM orders WHERE customer_id = 42;

Index Only Scan using idx_orders_customer_total on orders
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

INCLUDE adds a column to the index leaves without making it part of the sort key, so the index stays small while covering the query. Heap Fetches: 0 confirms the table was never read.

Partial indexes#

When queries only ever care about a subset of rows, index only that subset:

CREATE INDEX idx_orders_pending
    ON orders (created_at)
    WHERE status = 'pending';

On a table where 2% of orders are pending, this index is roughly 2% the size of the full one. It fits in memory, is faster to scan, and costs far less to maintain on write. PostgreSQL will use it for any query whose WHERE clause implies the index predicate.

This is one of the highest-leverage index types and one of the least used. Soft-deleted rows (WHERE deleted_at IS NULL), unprocessed jobs, active subscriptions — all are natural partial indexes.

When an index makes things worse#

An index is not free. It costs disk, it costs write throughput, and it costs planning time.

Every write maintains every index. An INSERT into a table with eight indexes performs nine write operations. On a write-heavy table, dropping unused indexes is a legitimate performance optimisation.

The planner may correctly ignore it. If a query matches a large fraction of the table, a sequential scan genuinely is faster — random I/O per row costs more than reading the whole table in order. An index on a boolean column where 60% of rows are true will be ignored, and the planner is right to ignore it.

Low-cardinality columns rarely benefit. An index on a status column with three distinct values across a million rows is not selective enough to help on its own. It may still be valuable as the leading column of a composite index, or as a partial index predicate.

Find indexes nothing is using:

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan AS scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
      SELECT conindid FROM pg_constraint WHERE contype IN ('p', 'u')
  )
ORDER BY pg_relation_size(indexrelid) DESC;

Things that quietly disable an index#

A function on the indexed column. This cannot use an index on email:

SELECT * FROM users WHERE lower(email) = 'a@example.com';

Index the expression instead:

CREATE INDEX idx_users_email_lower ON users (lower(email));

A leading wildcard. LIKE 'prefix%' can use a B-tree; LIKE '%suffix' cannot. For substring search, use a trigram index:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_articles_title_trgm
    ON articles USING gin (title gin_trgm_ops);

A type mismatch. Comparing a bigint column to a value PostgreSQL treats as numeric can prevent index use. This is common when an ORM or a driver passes a parameter with an unexpected type — the plan will show a cast in the Filter line.

OR across different columns. WHERE a = 1 OR b = 2 often cannot use either single-column index efficiently. Rewriting as a UNION of two indexed queries is frequently much faster.

Building indexes without locking the table#

CREATE INDEX takes a lock that blocks writes for the duration of the build. On a large production table, that is an outage.

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

CONCURRENTLY builds without blocking writes. The trade-offs: it takes roughly twice as long, cannot run inside a transaction block, and can leave an INVALID index behind if it fails. Check for those afterwards:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Drop and rebuild any it finds.

A method that works#

1. Find the slow query — pg_stat_statements ordered by total_exec_time, not by mean_exec_time. A 5 ms query run a million times matters more than a 3-second query run twice.
2. Run EXPLAIN (ANALYZE, BUFFERS) on it with realistic parameters.
3. Find the node where time accumulates. Look for Seq Scan with a large Rows Removed by Filter, or a Sort doing an external merge.
4. Add the narrowest index that turns that Filter into an Index Cond — equality columns first, range or sort column last, WHERE predicate if the query only touches a subset.
5. Re-run EXPLAIN ANALYZE and confirm the plan actually changed. The planner is not obliged to use your index.
6. Come back in a week and check idx_scan in pg_stat_user_indexes. An index nothing uses is pure cost.

Step 5 is the one people skip, and it is the one that tells you whether any of this worked.