What is an index and when does it help?

An index is a separate data structure that maps column values to row locations, so PostgreSQL can find matching rows without scanning the whole table. The default type is a B-tree, which supports =, <, >, BETWEEN, IN, ORDER BY and prefix LIKE 'abc%'. Other types: GIN (arrays, JSONB, full-text search), GiST (geometry, ranges), BRIN (huge tables ordered by time), Hash (equality only).

create index orders_customer_date_idx on orders (customer_id, ordered_on);
-- helps: where customer_id = 42 and ordered_on >= '2026-01-01'
-- helps: where customer_id = 42 order by ordered_on
-- does not help much: where ordered_on = '2026-03-01'  (not the leading column)

Indexes help selective filters, joins on foreign keys and sorted reads. They don't help when a query returns a large fraction of the table, when the column is wrapped in a function (where lower(email) = … needs an index on lower(email)), or with a leading wildcard (like '%abc').

Costs: every INSERT, UPDATE and DELETE must also update each index, and indexes take disk space and memory. Note that PostgreSQL indexes primary keys and UNIQUE columns automatically but not foreign-key columns — indexing them is usually worthwhile.