What is a partial index?

A partial index is an index that is created not on the entire table, but only on a subset of rows that satisfy a certain condition (filter). This allows reducing the size of the index and improving the performance of queries that use this condition.

For example, in PostgreSQL, you can create a partial index as follows:

CREATE INDEX idx_active_users ON users (last_login)
WHERE is_active = true;

This index will contain only records with is_active = true, which speeds up queries filtering by this attribute.

Partial indexes are useful when queries with a specific filter are frequently executed, and most of the data is not involved in these queries.