PostgreSQL indexes explained: types, order and cost

An index is a second, sorted copy of part of your table, kept up to date on every write, that lets PostgreSQL find rows without reading all of them. That sentence contains both halves of the trade: reads get faster, writes get slower, and disk gets used. Most databases that are slow are missing two or three indexes; most databases that are slow to write have eight indexes on a table that needed three.

The practical summary, if you only take one thing: for a query like WHERE tenant_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 20, the index you want is one composite B-tree on (tenant_id, status, created_at DESC), not three separate indexes on three columns. Column order in a composite index is not cosmetic; it decides whether the index can be used at all.

What an index is, and what B-tree can answer#

The default index type, and the right answer perhaps nine times out of ten, is a B-tree. It stores the indexed values in sorted order in a balanced tree, so PostgreSQL can walk from the root to a leaf in a few page reads and then scan sideways through the matching entries.

Because the entries are sorted, one B-tree answers a wide set of questions on the same column:

• Equality: WHERE email = 'a@example.com'
• Ranges: WHERE created_at >= now() - interval '7 days'
• BETWEEN and IN lists
• IS NULL and IS NOT NULL - PostgreSQL does index nulls
• Sorting: ORDER BY created_at DESC with a LIMIT, read backwards
• Prefix matching with LIKE 'inv-2026%', but only if the database collation is C or the index is declared with text_pattern_ops

That last one catches people in every locale but C. If prefix searches are important, add the operator class explicitly:

CREATE INDEX orders_ref_prefix ON app.orders (reference text_pattern_ops);

What a B-tree cannot help with is a leading wildcard (LIKE '%carrot%'), a function applied to the column in the query, or a comparison whose type does not match the index. The second is the most common by far:

-- Does not use an index on created_at: the column is wrapped in a castWHERE created_at::date = '2026-09-01'-- Does, because the column is left aloneWHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'

Any time a query is slow and the column "obviously has an index", check whether the query applies a function or a cast to that column. If it must, index the expression instead, which is covered below.

Composite indexes and the order of the columns#

A composite index is sorted by the first column, then by the second within that, and so on - the same way a phone book is sorted by surname then first name. That gives it the leftmost-prefix rule: an index on (a, b, c) can serve queries filtering on a, on a and b, or on all three. It is close to useless for a query that filters only on b.

Two rules cover most real cases:

1. Equality columns first, range or sort column last. With an index on (tenant_id, created_at), a query filtering tenant_id = 7 and sorting by created_at walks straight to the right slice and reads it in order. Reverse the columns and it cannot.
2. One composite beats several single-column indexes when the columns appear together. PostgreSQL can combine two indexes with a bitmap scan, but that costs an extra step and rechecks rows in the heap; a single index that matches the query is always cheaper.

The worked example:

-- The query the application runs a thousand times an hourSELECT id, total, created_atFROM app.ordersWHERE tenant_id = $1 AND status = 'open'ORDER BY created_at DESCLIMIT 20;-- The index it wantsCREATE INDEX orders_tenant_status_created  ON app.orders (tenant_id, status, created_at DESC);

With that index the plan is an index scan that stops after twenty rows. Without it, PostgreSQL reads every order for that tenant, sorts them all, and throws away everything past the twentieth - work that grows with the table while the result stays the same size. The DESC in the definition is optional for a single-column sort (a B-tree can be read backwards) but it matters when you mix directions across columns.

The index types, and when each is right#

The two non-default types worth learning are GIN and BRIN.

GIN indexes the values inside a column rather than the column as a whole. It is how you make containment queries on jsonb fast, how full-text search works, and - with the pg_trgm extension - how you make a leading-wildcard LIKE usable:

CREATE EXTENSION IF NOT EXISTS pg_trgm;CREATE INDEX customers_name_trgm ON app.customers USING gin (name gin_trgm_ops);-- now this can use an indexSELECT * FROM app.customers WHERE name ILIKE '%anders%';-- jsonb containmentCREATE INDEX events_payload ON app.events USING gin (payload jsonb_path_ops);SELECT * FROM app.events WHERE payload @> '{"type":"signup"}';

jsonb_path_ops builds a smaller index than the default jsonb_ops and supports the containment operator @> only, which is usually the operator you wanted. GIN indexes are slower to update than B-trees and can be several times larger, so put them where they earn it.

BRIN stores nothing but a summary per block range - the minimum and maximum value in each chunk of the table. That makes it tiny (kilobytes where a B-tree would be gigabytes) and only useful when the physical order of the table matches the indexed column, which in practice means an append-only table indexed on its timestamp. On a table where rows arrive in random order it is worse than nothing.

Partial and expression indexes#

Two features that turn a large index into a small one.

A partial index covers only the rows matching a WHERE clause. The classic case is a status column where you only ever query one value:

CREATE INDEX orders_open ON app.orders (tenant_id, created_at)  WHERE status = 'open';

If 2% of orders are open, that index is 2% of the size, fits in cache, and is faster to maintain. The planner uses it only when it can prove the query's conditions imply the index's WHERE clause, so the predicate has to be written in a form it recognises - keep it simple and literal.

An expression index indexes the result of a function, which is how you make a case-insensitive lookup fast:

CREATE UNIQUE INDEX customers_email_lower ON app.customers (lower(email));SELECT * FROM app.customers WHERE lower(email) = lower($1);

The query has to use exactly the same expression as the index. lower(email) in the index and email ILIKE $1 in the query do not match. The function must also be marked immutable, which rules out anything involving the current time or the session's time zone.

Both can be combined with uniqueness, which gives you constraints the type system cannot express: one active subscription per customer, one primary address per account.

CREATE UNIQUE INDEX one_active_sub ON app.subscriptions (customer_id)  WHERE status = 'active';

The indexes you get free, and the one you do not#

A primary key creates a unique B-tree index. So does any UNIQUE constraint. That is why looking a row up by its id is fast without you doing anything.

Foreign keys do not. orders.customer_id REFERENCES customers(id) creates an index on customers.id (it is the primary key) and nothing at all on orders.customer_id. Two things then go wrong on a growing table: SELECT ... WHERE customer_id = $1 scans the whole table, and deleting a customer scans orders to check the constraint, holding a lock while it does. Index your foreign key columns unless you know the child table will stay small.

Two details on uniqueness. Nulls are distinct from each other by default, so a unique column can hold many nulls; PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT if you want the other behaviour. And a foreign key needs a plain unique index on the referenced columns - a partial unique index does not qualify, which surprises people who built one for the constraint trick above.

Finally, INCLUDE (PostgreSQL 11 and later) adds payload columns to a B-tree without making them part of the sort key. It exists to enable index-only scans:

CREATE INDEX orders_lookup ON app.orders (tenant_id, created_at) INCLUDE (total, status);

Reading a plan: is it actually using the index?#

EXPLAIN (ANALYZE, BUFFERS) is the only way to know. The node names tell you what happened:

• Seq Scan - every page of the table was read. Correct for small tables and for queries that need most of the rows.
• Index Scan - the index was walked and each matching row fetched from the table.
• Bitmap Index Scan followed by Bitmap Heap Scan - many matches, so PostgreSQL collected them and read the table in physical order. Often the right plan, and a hint that a more selective index might exist.
• Index Only Scan - everything the query needed was in the index. The fastest of the three, and the one that depends on vacuum.

That last dependency is the non-obvious one. An index does not record whether a row version is visible to your transaction, so an index-only scan still has to check the table unless the visibility map says the whole page is visible to everyone. Vacuum is what sets those bits. A table that autovacuum cannot keep up with quietly loses its index-only scans, which is one of several ways vacuum and bloat turn into a query-speed problem. If the plan says Heap Fetches: 84213, that is what you are looking at.

A seq scan is not automatically a bug. On a table of 500 rows it is faster than any index, and on a query returning 40% of a table the index would mean more random reads than a straight pass. The planner decides with cost constants and statistics, so if it is choosing badly, check that ANALYZE has run recently and that random_page_cost reflects your storage - both are in PostgreSQL tuning for small servers. Reading the plan itself is a skill of its own: EXPLAIN ANALYZE on a slow query goes line by line.

When an index hurts#

The costs are real, and on a small server they are visible.

Writes. Every INSERT and DELETE updates every index on the table. An UPDATE does too, unless PostgreSQL can use a heap-only tuple update - which it can only do when no indexed column changed and there is free space on the page. Adding an index to a column your application updates constantly removes that optimisation and multiplies the write cost.

Disk and cache. Indexes count against the storage on your plan, and more importantly against memory: an index nobody uses still competes for the same cache as the one you need.

Planning. Every extra index is another option the planner evaluates, and a set of overlapping indexes makes bad choices more likely, not less.

Bloat. Indexes bloat like tables do, and a bloated index is a slower index.

The specific cases where an index is the wrong answer: columns with very few distinct values (a boolean flag where half the rows are true - use a partial index on the rare value instead), tables under a few thousand rows, columns that are only ever selected and never filtered on, and a third index whose columns are already the leading columns of an existing one.

Creating and dropping indexes without locking the table#

A plain CREATE INDEX takes a lock that blocks writes to the table for the whole build. On a large table during business hours, that is an outage.

CREATE INDEX CONCURRENTLY orders_tenant_created  ON app.orders (tenant_id, created_at);

CONCURRENTLY does two passes over the table and lets reads and writes continue throughout. The trade-offs are worth knowing before you rely on it: it takes longer, it cannot run inside a transaction block (so migration tools often need a flag to run it outside one), and if it fails it leaves behind an invalid index that is maintained on every write but never used for queries. Find those and drop them:

SELECT indexrelid::regclass AS index, indrelid::regclass AS tableFROM pg_index WHERE NOT indisvalid;DROP INDEX CONCURRENTLY orders_tenant_created;

Rebuilding follows the same pattern with REINDEX INDEX CONCURRENTLY (PostgreSQL 12 and later). Raising maintenance_work_mem for the session makes any of these finish sooner. Adding an index is a schema change like any other, so the ordering advice in schema migrations without downtime applies: add it in its own step, before the code that needs it.

Finding the unused and the missing#

PostgreSQL keeps counters. Use them rather than opinions.

-- Indexes nobody has used, largest firstSELECT s.relname AS table, s.indexrelname AS index, s.idx_scan AS scans,       pg_size_pretty(pg_relation_size(s.indexrelid)) AS sizeFROM pg_stat_user_indexes sJOIN pg_index i ON i.indexrelid = s.indexrelidWHERE s.idx_scan = 0 AND NOT i.indisuniqueORDER BY pg_relation_size(s.indexrelid) DESC;-- Tables being read sequentially the mostSELECT relname, seq_scan, seq_tup_read, idx_scan,       seq_tup_read / GREATEST(seq_scan, 1) AS rows_per_scanFROM pg_stat_user_tablesWHERE seq_scan > 0ORDER BY seq_tup_read DESCLIMIT 10;

Two cautions on the first query. The counters reset when the statistics are reset or the server is rebuilt, so a zero on a server that restarted yesterday means nothing; PostgreSQL 16 and later add last_idx_scan, which is easier to trust. And never drop a unique index on the grounds that it is unused - it is enforcing a constraint whether or not anyone queries it.

The second query finds candidates rather than answers. A table with millions of sequentially-read rows is telling you a query is scanning it repeatedly; pg_stat_statements tells you which query, and EXPLAIN tells you which index would fix it. When the planner's estimates are far off on two correlated columns, extended statistics (CREATE STATISTICS) can fix the estimate without any new index at all.

FAQ#

How many indexes is too many on one table?

There is no fixed number, but past five or six on a table that takes constant writes you should be able to name the query each one serves. If you cannot, check idx_scan and drop the ones nobody uses.

Why is my index not being used?

The three usual reasons: the query wraps the column in a function or cast, the query filters on a column that is not the leading column of a composite index, or the table is small enough that a sequential scan is genuinely cheaper. EXPLAIN settles it in a few seconds.

Should I index every foreign key column?

Index the ones you filter or join on, and the ones whose parent rows get deleted or updated. On a small child table it does not matter; on a large one, an unindexed foreign key turns every parent delete into a full scan under a lock.

Do indexes need maintenance?

They are kept current automatically, but they bloat as rows are updated and deleted. REINDEX INDEX CONCURRENTLY rebuilds one without blocking writes. Most databases never need this; a table with heavy churn does.

What is an index-only scan and why did mine stop happening?

It is a plan where every column the query needs is in the index, so the table is never touched. It requires the visibility map to mark the pages as all-visible, which only vacuum does. If autovacuum has fallen behind, the plan quietly degrades to fetching rows from the heap.

Is a composite index or several single-column indexes better?

A composite index, when the columns are used together in the same query and in the right order. Single-column indexes are better when the columns are used independently by different queries. PostgreSQL can combine two indexes in a bitmap scan, but that is a fallback rather than a plan to aim for.