In this recipe, we will be discussing how to create an index for the required data sample set.
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.
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; ...