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;