PostgreSQL - Partial Index

In PostgreSQL, partial indexes are a powerful feature designed to improve query performance while minimizing index size. By allowing you to specify the rows of a table that should be indexed, partial indexes can significantly speed up queries that use common WHERE conditions with constant values.

Let us better understand the Partial Index in PostgreSQL from this article.

What is the Partial Index?

A partial index is an index built over a subset of a table, defined by a condition in the WHERE clause. This targeted indexing approach is especially useful when you frequently query specific subsets of data, as it reduces the overhead of indexing and scanning irrelevant rows.

Syntax:

SELECT * 
FROM table_name
WHERE column_name = constant_value;

For demonstration, we will work with the 'customer' table of the sample database, ie, DVD Rental.

PostgreSQL Partial Index Example

The following query finds all inactive customers.

Query:

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.

Query:

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:

CREATE INDEX index_name
ON table_name(column_list)
WHERE condition;