TopolearnMost slow queries are missing an index, and most bloated databases have indexes nobody uses.
Without an index, finding WHERE email = 'a@b.com' means reading every row: a sequential scan, fine for a thousand rows and painful for a hundred million. An index is a separate data structure, kept in sync with the table, that maps column values to row locations so the database can jump straight to matching rows.
An index is a trade-off:
- Reads that filter, join or sort on the indexed columns get much faster.
- Every
INSERT,DELETE, and everyUPDATEof indexed columns must also update the index, so writes get slower. - Indexes take disk space and memory (cache).
What to index, in practice:
- Primary keys and unique constraints (databases index these automatically).
- Foreign-key columns used in joins and in
ON DELETEchecks — many databases do not index these automatically. - Columns in frequent, selective
WHEREclauses.
Selectivity matters: an index on a column where 90% of rows share one value rarely helps, because reading most of the table through an index is slower than scanning it. The query planner decides whether to use an index based on statistics, so an index existing doesn't mean it will be used.
Add indexes for real queries you have measured, and periodically drop ones that are never used.
Example
CREATE TABLE event (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id int NOT NULL, kind text NOT NULL);
INSERT INTO event (user_id, kind)
SELECT g % 10000, 'click' FROM generate_series(1, 200000) g;
ANALYZE event;
EXPLAIN SELECT * FROM event WHERE user_id = 42;
-- Seq Scan on event (filter: user_id = 42)
CREATE INDEX event_user_id_idx ON event (user_id);
EXPLAIN SELECT * FROM event WHERE user_id = 42;
-- Bitmap Heap Scan on event ... -> Bitmap Index Scan on event_user_id_idxRemember
- Indexes speed up reads on their columns and slow down writes
- Index foreign keys and selective, frequently filtered columns
- The planner may ignore an index when a scan is cheaper
Watch out for
- Indexing every column just in case, which slows every write and wastes cache.