PostgreSQL Tutorial: Partial Index

Summary: in this tutorial, you will learn how to use the PostgreSQL partial index to improve the performance of the query while reducing the index size.

So far you have learned how to add values of one or more columns to an index. PostgreSQL partial index even allows you to specify the rows of a table that should be indexed. This partial index helps speed up the query while reducing the size of the index.

The partial index is useful in case you have commonly used WHERE conditions that use constant values as follows:

SELECT * 
FROM table_name
WHERE column_name = constant_value;

Let’s take a look at the customer table from the sample database:

For example, you typically are interested in inactive customers and often do some kinds of follow-ups to get them back to buy more stuff.

The following query finds all inactive customers:

SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM
    customer
WHERE
    active = 0;

To perform this query, the query planner needs to scan the customer table as shown in the following EXPLAIN statement:

EXPLAIN SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM
    customer
WHERE
    active = 0;

Here is the output:

You can optimize this query by creating an index for the active column as follows:

CREATE INDEX idx_customer_active
ON customer(active);

This index fulfills its purpose, however, it includes many rows that are never searched, namely all the active customers.

To define an index that includes only inactive customers, you use the following statement:

CREATE INDEX idx_customer_inactive
ON customer(active)
WHERE active = 0;

From now on, PostgreSQL will consider the partial index whenever the WHERE clause appears in a query:

EXPLAIN SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM
    customer
WHERE
    active = 0;

The following shows the output:

The syntax for defining a partial index is quite straightforward:

CREATE INDEX index_name
ON table_name(column_list)
WHERE condition;

In this syntax, the WHERE clause specifies which rows should be added to the index.

In this tutorial, you have learned about the PostgreSQL partial index and how to use it to specify the rows that should be added to the index.