PostgreSQL High Performance Cookbook

Partial indexes

In this recipe, we will be discussing how to create an index for the required data sample set.

Getting ready

Using partial indexes, we can reduce the size of an index by adding a predicate in the index definition. That is, only the entries that match the predicate will only be indexed instead of all of them. This partial index will be utilized when the index predicate satisfies the submitted SQL predicate.

How to do it...

For example, let's say that our application does a frequent query on bmsql_item as to list all the items that have a price between $5 to $10, then it is a candidate predicate to create a partial index as follows:

benchmarksql=# CREATE INDEX CONCURRENTLY part_idx ON bmsql_item(i_price) WHERE i_price BETWEEN 5 AND 10; ...