Having covered expression indexes, I want to cover another index feature
partial indexes. Partial indexes allow you to create an index on
part of a table. Why would you want to do that?

  • index only frequently-accessed values, allowing smaller index files
  • avoid indexing common values, which are unlikely to be useful in an index
  • smaller index files allow faster index traversal and improve index caching
  • avoid index insertion overhead for non-indexed values
  • constrain uniqueness to a subset of table rows

People usually don’t use expression or partial indexes initially, but there are certainly some use-cases that can only be solved with
these index features.

View or Post Comments