PostgreSQL - Partial Index
Last Updated :
18 Jul, 2024
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;
Similar Reads
PostgreSQL - List Indexes Indexes in PostgreSQL are crucial for optimizing query performance, helping speed up data retrieval by allowing faster access to rows in a table. PostgreSQL does not provide a direct SHOW INDEXES command like some other databases; however, you can use the pg_indexes view and the psql command line to
4 min read
PostgreSQL - Multicolumn Indexes In PostgreSQL, multicolumn indexes, also known as composite indexes, combined indexes, or concatenated indexes, are indexes defined on more than one column of a table. These indexes can significantly improve query performance by allowing the database to quickly locate rows based on multiple column v
3 min read
PostgreSQL - IN operator The IN operator in PostgreSQL is a powerful and efficient tool used to filter records based on a predefined set of values. When used with the WHERE clause, it simplifies SQL queries and enhances readability, making it a key component of SQL query optimization for data retrieval and database manipula
4 min read
PostgreSQL - DROP INDEX In PostgreSQL, indexes are essential for improving query performance but sometimes we may need to remove them when they are no longer effective or necessary. This is where the DROP INDEX statement comes in. It allows us to delete an existing index from the database, ensuring that our PostgreSQL envi
5 min read
PostgreSQL - LIKE operator In PostgreSQL, the LIKE operator is an essential tool for pattern matching in SQL queries. Whether we're dealing with large datasets or searching for specific string patterns, this operator provides a powerful way to filter and retrieve data based on partial matches. By Using wildcard search techniq
5 min read
PostgreSQL - Index Types Indexes are essential tools in PostgreSQL, allowing you to speed up data retrieval and enhance the performance of the queries. This article will explore the various index types available in PostgreSQL, understand their unique characteristics, and learn how to use them effectively to optimize your da
3 min read
PostgreSQL - Alias PostgreSQL aliases are powerful tools that allow you to assign temporary names to tables or columns within your queries. These aliases only exist during the execution of the query, making your SQL code more readable and efficient.What is a PostgreSQL Alias?An alias in PostgreSQL is a temporary name
2 min read
PostgreSQL - CREATE INDEX The PostgreSQL CREATE INDEX statement is essential for improving database performance, allowing faster data retrieval by creating indexes on specified columns. Indexes in PostgreSQL act like pointers, significantly reducing the time required for query processing, especially on large tables. In this
5 min read
PostgreSQL - UNIQUE Index In PostgreSQL, a UNIQUE index is used to ensure that the values in one or more columns are unique across the rows in a table. This is essential for maintaining data integrity and avoiding duplicate entries.This article will provide a detailed overview of UNIQUE indexes, including syntax, examples, a
4 min read
PostgreSQL - Size of Indexes In PostgreSQL, index management is essential for optimizing query performance and ensuring efficient database storage. One important function for assessing the storage requirements of table indexes is the pg_indexes_size() function. In this article, we will explain the pg_indexes_size() function, it
4 min read