TopolearnAn index is the difference between a 2 ms and a 20 second query once a table holds millions of rows.
Without an index, finding orders WHERE customer_id = 2 means reading every row: a sequential scan. An index is a separate, sorted data structure that maps column values to row locations, like the index at the back of a book. The default kind, a B-tree, finds a value in a few page reads even among billions of rows, and also serves range conditions (<, BETWEEN, LIKE 'abc%' in suitable collations) and ORDER BY on the indexed column.
CREATE INDEX orders_customer_id_idx ON orders (customer_id);Primary keys and UNIQUE constraints create indexes automatically. Foreign-key columns do not, and they are usually the first indexes you should add.
A multicolumn index on (customer_id, ordered_at) is sorted by customer_id, then by ordered_at within each customer. It serves WHERE customer_id = 2, and WHERE customer_id = 2 AND ordered_at > ..., and WHERE customer_id = 2 ORDER BY ordered_at DESC LIMIT 10. It is not efficient for WHERE ordered_at > ... alone, because the leading column is unconstrained. Put equality columns first and range or sort columns last.
Indexes are not free. Every insert, update and delete must also update every index, and each index takes disk space and memory. Add the ones your queries need; drop the ones nothing uses.
An index cannot help when the query hides the column inside an expression: WHERE lower(email) = ... or WHERE ordered_at::date = ... cannot use a plain index on that column. Also, if a condition matches a large fraction of the table, the planner will rightly choose a sequential scan instead.
On a busy production table, use CREATE INDEX CONCURRENTLY. It takes longer, but does not block writes while it builds.
Example
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
-- Serves equality on customer_id plus ordering by ordered_at
CREATE INDEX orders_customer_time_idx ON orders (customer_id, ordered_at);
-- Uses orders_customer_time_idx: no sort needed, stops after 10 rows
SELECT id, total FROM orders
WHERE customer_id = 2
ORDER BY ordered_at DESC
LIMIT 10;
-- Cannot use the index well: leading column is unconstrained
SELECT id FROM orders WHERE ordered_at > now() - interval '7 days';
-- Production-safe build that does not block writes
CREATE INDEX CONCURRENTLY customers_email_idx ON customers (email);
-- Which indexes exist? In psql: \diRemember
- A B-tree index serves equality, ranges and ORDER BY on its columns
- PRIMARY KEY and UNIQUE create indexes; foreign keys do not
- Multicolumn indexes: equality columns first, range/sort columns last
- Each index slows writes; wrapping a column in a function bypasses it
Watch out for
- Creating an index on (a, b) and expecting it to speed up queries that filter only on b.