Introduction to PostgreSQL partial index

When youcreate an index on a column of a table, PostgreSQL uses all the values from that column for building the index.

Sometimes, you may want to include only some values from the indexed column in the index. To do that you can use a partial index.

A partial index is an index built on a subset of data of the indexed columns.

To define a subset of data, you use a predicate, which is a conditional expression, of the partial index. PostgreSQL will build an index for rows that satisfy the predicate.

This partial index can enhance query performance while reducing the index size. It can also improve table updates because PostgreSQL does not need to maintain the index in all cases.

To create a partial index, you use theCREATE INDEX statement with aWHERE clause:

CREATE [IF NOT EXISTS]INDEX index_nameON table_name(column1, column2, ...)WHERE predicate;

In this syntax:

• First, specify the index name in theCREATE INDEX statement. Use theIF NOT EXISTS to prevent an error of creating an index that already exists.
• Second, provide the table name along with indexed columns in theON clause.
• Third, use a predicate in theWHERE clause to specify the condition for rows to be included in the index.

PostgreSQL partial index example

Let’s take a look at thecustomer table from thesample database:

The active column has two values:

• 0: inactive
• 1: active

When querying data from the database, you often work with inactive customers, not active ones. For example, you may want to follow up with inactive customers to get them back to order more films.

To speed up the query that retrieves the inactive customer, you can create a partial index.

First, create a partial index on theactive column of the customer table:

CREATE INDEX customer_activeON customer(active)WHERE active= 0;

Second, show the query plan that retrieves the inactive customers:

EXPLAINSELECT  *FROM  customerWHERE  active= 0;

Output:

QUERY PLAN----------------------------------------------------------------------------------- Index Scan using customer_active on customer  (cost=0.14..16.12rows=15 width=70)

The partial index customer_active improves the query performance while including the only rows that are often searched.

Summary

• Use PostgreSQL partial index to create an index that includes a subset of rows in a table specified by a condition.

Last updated onFebruary 28, 2024

On this page

• Introduction to PostgreSQL partial index
• PostgreSQL partial index example
• Summary