An index is a separate data structure that PostgreSQL maintains alongside a table, built specifically to make certain lookups fast. Without one, finding a row that matches a WHERE condition means scanning every row in the table in order — a sequential scan. With the right index, PostgreSQL can jump almost directly to the matching rows instead. This page covers what an index actually costs, how to create the simplest kind, and a concrete before/after look at the difference one makes.

An index is not free. Every INSERT, UPDATE, or DELETE that touches an indexed column has to also update the index, not just the table row — so more indexes means slower writes. Indexes also take up disk space, sometimes a substantial amount for a large table. The trade-off is: faster reads, slower writes, more storage. That is exactly why PostgreSQL does not simply index every column automatically — for a column that is rarely filtered or sorted on, an index would add write overhead for no real read benefit.

This tells PostgreSQL to maintain a sorted structure over orders.customer_id, so any query filtering or joining on that column can use it instead of scanning the whole table.

EXPLAIN shows the query plan PostgreSQL intends to use, without actually running the query. Here is the same query against an orders table with a few hundred thousand rows, before and after adding an index on customer_id.

The Seq Scan is gone, replaced by an Index Scan, and the estimated cost drops from thousands down to single digits. On a small table PostgreSQL might reasonably choose a sequential scan anyway — reading a few hundred rows directly can be cheaper than the overhead of consulting an index — but as a table grows into the thousands or millions of rows, that gap keeps widening in the index's favor.