PostgreSQL indexes that help

A PostgreSQL index earns its writes when it covers the columns the query filters or sorts on, in the right order. On 300,000 rows the same query went from 15.471 ms to 0.024 ms with one index, and to 0.008 ms with a composite index that also covers the sort.

EXPLAIN first, index second

An index picked by intuition solves the wrong case. Run EXPLAIN (ANALYZE, BUFFERS) on the real query, with real parameters and real data: look for a sequential scan on a large table, a row estimate far from the actual count, or a sort that spills to a temp file. If the estimates are wrong, the first remedy is ANALYZE, not another index.

Three indexes that pay for themselves

• Foreign key: PostgreSQL does not index the column of a foreign key for you. If you join on it or delete the parent row, the lookup is a scan without an index.
• Composite on filter plus sort: (customer_id, created_at DESC) answers the filter and the ORDER BY in one pass, as in the test above.
• Partial on a hot subset: WHERE status = 'open' indexes a few rows instead of the whole table, and stays small as the table grows.

-- the query you actually run, with real parameters
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 20;

-- one index for the filter and the sort
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

-- a partial index for the hot subset only
CREATE INDEX CONCURRENTLY idx_orders_open
  ON orders (created_at DESC)
  WHERE status = 'open';

CREATE INDEX CONCURRENTLY cannot run inside a transaction block and can leave an invalid index behind if it fails: drop and rebuild it, because the planner will not use it. The clause does not block writes, but it scans the table twice, so on a large table it takes longer than a normal build.

The write cost

In the control test 5,000 inserts took 5 ms with no indexes and 85 ms with three. PostgreSQL writes to the heap, to every btree and to the WAL for each row, so the shape is the same; the ratio is not linear and depends on cache, but the direction does not change. An index nobody reads is pure slowdown.

Finding indexes that do no work

After a full traffic cycle, pg_stat_user_indexes reports how often each index was used. An idx_scan stuck at zero marks an index to drop, unless it backs a constraint. The query below lists the candidates with the space they take.

SELECT relname AS table_name,
       indexrelname AS index_name,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

The EneUI catalogue

Showing what an index does needs dense tables: the catalogue holds table components, table blocks and a verified record, Origin UI's table, with an MIT licence declared upstream. Checked on 1 November 2026 against 111,985 components.

The limitation

The numbers above come from SQLite, not PostgreSQL: they show the shape of the gain, not the latency of your database. PostgreSQL has a cost model, statistics, bloat and transaction visibility that SQLite does not, and an index can stay unused because of a bad estimate even when it is correct. The measurement that counts is always EXPLAIN (ANALYZE, BUFFERS) on your own instance.

Where to continue

The index is half the problem: the other half is the shape of the query and the contract, described in designing an API. If latency comes from caching, read the four cache layers in Next.js. The comparison of API styles, with the costs of each, is in MCP and REST.