Tell me about composite indexes in PostgreSQL. How did you come to use them and on which tables?

Composite indexes in PostgreSQL are indexes that are created on multiple columns of a table simultaneously. They are useful when queries filter or sort data based on a combination of these fields.

I started using composite indexes when I noticed that queries with conditions on multiple columns (for example, WHERE user_id = ? AND created_at > ?) were running slowly, despite having individual indexes on user_id and created_at. Creating a composite index on (user_id, created_at) significantly sped up such queries.

Example of creating a composite index:

CREATE INDEX idx_user_created ON orders (user_id, created_at);

I used such indexes on tables with large volumes of data, where filtering and sorting on multiple fields simultaneously was common, for example, in orders, logs, and transactions tables.