How do indexes work in PostgreSQL? When does an index not work?

Indexes in PostgreSQL are data structures (usually B-trees) that speed up the search for rows in tables based on certain columns. When you make a query with a WHERE condition on an indexed field, the DBMS can quickly find the needed records without scanning the entire table.

An index does not work or is used inefficiently in the following cases:

• The query does not use columns that have an index.
• Using functions or expressions on indexed columns (e.g., WHERE LOWER(name) = 'abc') if there is no corresponding functional index.
• Very small tables where a full scan is faster.
• Queries with conditions that return most of the table — then PostgreSQL may choose sequential scanning.
• Indexes are not suitable for LIKE patterns starting with a wildcard ('%abc').

Example of creating an index:

CREATE INDEX idx_users_email ON users(email);

And using it in a query:

SELECT * FROM users WHERE email = 'user@example.com';

In this case, the index will help quickly find the user by email.