Home / Databases / PostgreSQL

DatabasesHow-toBeginner

PostgreSQL index basics: B-tree indexes, reading EXPLAIN, and why your index is not being used

What an index does, how to create one without locking a busy table, how to read a simple EXPLAIN plan, multi-column index order, and the common reasons PostgreSQL ignores an index you expected it to use.

Deepak Bhardwaj4 min read

Applies toPostgreSQL 13-17 (self-managed and managed services)

An index is a sorted structure that lets PostgreSQL find rows without reading the whole table. The right index turns a 10-second query into a 2-millisecond one. The wrong ones slow every write and use disk for nothing.

Create an index

CREATE INDEX idx_orders_customer ON sales.orders (customer_id);

On a table that is in use, build it without blocking writes:

CREATE INDEX CONCURRENTLY idx_orders_customer ON sales.orders (customer_id);

Warning

CONCURRENTLY takes longer and cannot run inside a transaction block. If it fails, it leaves an INVALID index behind; drop it (DROP INDEX CONCURRENTLY idx_orders_customer;) and try again.

The default type is B-tree, which handles =, <, >, BETWEEN, IN, ORDER BY, and LIKE 'abc%' (prefix, with the right collation or text_pattern_ops).

Read EXPLAIN

EXPLAIN ANALYZE
SELECT * FROM sales.orders WHERE customer_id = 1042;

Without the index:

Seq Scan on orders  (cost=0.00..18334.00 rows=52 width=48) (actual time=0.03..85.21 rows=48 loops=1)
  Filter: (customer_id = 1042)
  Rows Removed by Filter: 999952

With it:

Index Scan using idx_orders_customer on orders  (cost=0.42..12.45 rows=52 width=48) (actual time=0.02..0.09 rows=48 loops=1)
  Index Cond: (customer_id = 1042)

What to look at:

Item Meaning
Seq Scan Reads the whole table
Index Scan / Index Only Scan Uses the index (Index Only = did not need the table)
Bitmap Heap Scan Index used to collect many matching rows, then reads them in bulk
rows= estimate vs actual rows Big difference = stale statistics
Rows Removed by Filter Rows read and thrown away

EXPLAIN ANALYZE actually runs the query. For an UPDATE or DELETE, wrap it in BEGIN; ... ROLLBACK;.

Multi-column indexes: order matters

CREATE INDEX CONCURRENTLY idx_orders_cust_date ON sales.orders (customer_id, created_at);

This index helps:

  • WHERE customer_id = 1042
  • WHERE customer_id = 1042 AND created_at >= '2026-10-01'
  • WHERE customer_id = 1042 ORDER BY created_at DESC LIMIT 10

It does not help much with WHERE created_at >= '2026-10-01' alone, because the index is sorted by customer_id first. Put equality columns first, range or sort columns last.

Why PostgreSQL is not using your index

Reason Example Fix
Query returns a large share of the table WHERE status = 'shipped' matching 70% Seq scan is genuinely faster; fine
Function on the column WHERE lower(email) = 'x' Expression index: CREATE INDEX ON users (lower(email));
Type mismatch Comparing bigint to a text parameter Cast the parameter, not the column
Leading column not filtered Index on (a, b), query filters b Add an index on b or reorder
LIKE '%abc' Leading wildcard Trigram index (pg_trgm)
Stale statistics Estimates far off ANALYZE sales.orders;
Small table A few hundred rows Seq scan is fine
OR across different columns WHERE a = 1 OR b = 2 Separate indexes allow a BitmapOr; or rewrite with UNION

Expression index example:

CREATE INDEX CONCURRENTLY idx_users_email_lower ON app.users (lower(email));
SELECT * FROM app.users WHERE lower(email) = 'alice@example.com';

Partial indexes

Index only the rows you query:

CREATE INDEX CONCURRENTLY idx_orders_open ON sales.orders (created_at) WHERE status = 'open';

Small, fast, and only used when the query's WHERE clause implies status = 'open'.

Find unused and duplicate indexes

SELECT schemaname, relname AS table, indexrelname AS index, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 20;

Indexes with idx_scan = 0 over a long period (and not backing a primary key or unique constraint) are candidates to drop. Statistics reset on some events, so check the time since pg_stat_reset or server start, and check replicas too, since queries there count separately.

Costs of indexes

  • Every INSERT, UPDATE (of indexed columns) and DELETE maintains every index.
  • Indexes use disk and memory.
  • Too many indexes on a write-heavy table slows it down.

Prevent it next time

  • Review EXPLAIN ANALYZE for new queries on big tables before release.
  • Use pg_stat_statements to find the queries worth indexing for.
  • Always create indexes CONCURRENTLY on production tables.

Related fixes