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; 
CREATE INDEX

Let's query the table so as to return the items that have a cost between 5 and 10:

benchmarksql=# EXPLAIN SELECT * FROM bmsql_item WHERE i_price BETWEEN 5 AND 10; 
                                   QUERY PLAN                      ...