Index categories

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 ...

Get Learning PostgreSQL 11 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.