O'Reilly logo

PostgreSQL High Performance Cookbook by Dinesh Kumar, Chitij Chauhan

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required