What is an index in PostgreSQL and how does it help performance?

Answer

An index is a separate data structure (usually B‑tree) that stores ordered keys and pointers to rows. It lets PostgreSQL find matching rows without scanning the whole table, speeding up lookups and joins at the cost of extra storage and slower writes.

CREATE INDEX idx_users_email ON users(email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';