The problem

The orders table of a shop has grown, and the page "my orders" has become slow. The query is simple:

SELECT id, total, created_atFROM ordersWHERE customer_id = 42ORDER BY created_at DESCLIMIT 20;

Nobody changed the query. The data simply outgrew the way the database finds rows. Without a suitable index, PostgreSQL has only one option: read every row of the table and test the condition. That is a sequential scan, and its cost grows with the table, not with the answer.

The idea of a B-tree index

The default index type in PostgreSQL is a B-tree. Think of it as a sorted copy of one or more columns, organised as a shallow tree. Each entry holds the indexed value and a pointer to the row in the table. To find customer_id = 42 the database walks from the root down a few levels and lands on the matching entries, without looking at unrelated rows. Because entries are sorted, the same structure answers range conditions (created_at >= ...), ORDER BY and min/max cheaply.

Creating one is a single statement:

CREATE INDEX idx_orders_customer ON orders (customer_id);

On a table that receives traffic, CREATE INDEX CONCURRENTLY avoids blocking writes during the build, at the price of a slower build that cannot run inside a transaction block.

Reading EXPLAIN

EXPLAIN shows the plan the planner chose; EXPLAIN (ANALYZE, BUFFERS) also executes the query and reports what really happened. Before the index, the plan looks like this (numbers omitted on purpose):

Limit
  -> Sort  (Sort Key: created_at DESC)
       -> Seq Scan on orders
            Filter: (customer_id = 42)
            Rows Removed by Filter: (very many)

The important lines are the node names and Rows Removed by Filter. A Seq Scan that throws away most rows is the typical signal that an index is missing. After creating the index on customer_id, the plan changes to something like:

Limit
  -> Sort  (Sort Key: created_at DESC)
       -> Bitmap Heap Scan on orders
            Recheck Cond: (customer_id = 42)
            -> Bitmap Index Scan on idx_orders_customer

A Bitmap Index Scan collects matching row locations, then Bitmap Heap Scan visits table pages in physical order. For very selective conditions you will see a plain Index Scan. The planner decides using table statistics, so run ANALYZE (autovacuum usually does it) if estimates look wrong. Compare the estimated rows= with the actual rows in ANALYZE output; a large gap points to stale statistics or correlated columns.

Composite indexes and column order

Our query filters on customer_id and sorts by created_at. One index can serve both:

CREATE INDEX idx_orders_customer_created    ON orders (customer_id, created_at DESC);

Now the database descends to customer_id = 42 and reads entries already sorted by created_at, so the Sort node disappears and LIMIT 20 can stop after twenty entries. The plan becomes an Index Scan directly under Limit.

Column order is not cosmetic. A composite index is sorted by the first column, then by the second inside each first-column value. The rule of thumb:

  1. Put columns compared with equality first.
  2. Then the column used for range conditions or ordering.
  3. The index helps queries that use a leftmost prefix of its columns. An index on (customer_id, created_at) helps WHERE customer_id = ?, but does not help WHERE created_at > ? alone.

Covering indexes

If every column the query needs is inside the index, PostgreSQL can answer from the index alone with an Index Only Scan, skipping the table. Use INCLUDE to add payload columns that are not part of the search key:

CREATE INDEX idx_orders_customer_covering    ON orders (customer_id, created_at DESC) INCLUDE (total);

Index-only scans still consult the visibility map, so they work best on tables that are vacuumed regularly. Do not include wide columns just because you can; the index becomes large and slower to maintain.

When indexes hurt

Every index is extra data that must be updated on each INSERT, on DELETE, and on UPDATE of an indexed column. More indexes mean slower writes, more disk and memory, and more work for vacuum. Other cases where an index does not help:

  • Low selectivity: a boolean column where half the rows match. The planner will correctly prefer a sequential scan.
  • Functions on the column: WHERE lower(email) = ... cannot use an index on email. Create an expression index on lower(email) instead.
  • Implicit casts and leading wildcards such as LIKE '%abc'.
  • Tiny tables, where reading everything is already cheap.

A partial index can keep the cost down when only a subset matters, for example CREATE INDEX ... ON orders (customer_id) WHERE status = 'OPEN'.

Common mistakes

  • Adding an index for every column "just in case".
  • Creating a single-column index on each column of a filter instead of one composite index that matches the query.
  • Reading the plan on an empty development database; the planner chooses differently for tiny tables.
  • Forgetting that ANALYZE executes the statement: wrap UPDATE or DELETE in a transaction you roll back.
  • Ignoring unused indexes. The pg_stat_user_indexes view shows how often each one is scanned.

Verifying an index

Creating an index is not the end of the job. Run the same query again with EXPLAIN (ANALYZE, BUFFERS) and check three things: did the node type change (Seq Scan to Index Scan or Bitmap Heap Scan), did the Sort node disappear, and did the number of buffers read go down. If the planner ignores the index, the usual reasons are low selectivity, a function applied to the column, or stale statistics.

You also need realistic data volume to test. On a table with a hundred rows the planner will always choose a sequential scan, and you will not see the benefit. Check against a staging database or generated data. Measure the write side as well: make sure inserts and updates on the same table did not become unacceptably slower after the new index.

A real-life way of thinking

Suppose orders with status = 'OPEN' are a small part of the table and the operations panel shows only them. Instead of a large index covering every row, the partial index shown above is both small and fast: it stores only open orders, and an order that is closed leaves the index. This kind of design fits the index to the real need of the query. So think of an index as a property of the queries, not of the table: first list the most important queries, then serve them with the fewest indexes.

Once more: an index is a tool for speeding up a query, not a goal. Measure first, change second, measure again. These three steps move many arguments about indexes from guesses to facts and keep decisions traceable in the team.

Checklist

  • Start from the slow query and its EXPLAIN (ANALYZE, BUFFERS), not from guesses.
  • Design the composite index for equality columns first, then range or sort.
  • Verify the plan changed, and that writes on the table remain acceptable.
  • Review unused and duplicate indexes periodically.
  • Keep migrations safe: CONCURRENTLY for large live tables.

Where to go next

Many "slow queries" come from the ORM, not the index; see the N+1 problem in JPA. When a query is correct and indexed but still hot, caching strategies is the next lever. To see where your SQL knowledge stands, take the skills assessment.