We can classify indexes for several categories; not that these categories can be mixed up, which enables us to model very complex logic. For example, we could have a unique partial index, to make sure that only part of the data was unique, based on a certain condition. The index categories are as follows:
- Partial indexes: A partial index only indexes a subset of the table data that meets a certain predicate; the WHERE clause is used with the index. The idea behind a partial index is to decrease the index size, making it more maintainable and faster to access.
- Covering indexes: These were introduced in PostgreSQL 11. They allow some queries to be executed only using an index-only scan. Currently, this is only supported by ...
