PostgreSQL – Partial Index

In PostgreSQL, the partial index is used to improve the performance of the query while reducing the index size. 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 which use constant values as follows:

Syntax:
SELECT * 
FROM table_name
WHERE column_name = constant_value;

For the purpose of demonstration, we will work with the customer table of the sample database, ie, dvdrental.

Example:

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;

This will lead to the following:

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;

Output:

For the above if one needs to create the above partial index use the following Syntax:

Syntax:
CREATE INDEX index_name
ON table_name(column_list)
WHERE condition;