PostgreSQL Partial Index

In this section, we are going to understand the working of the PostgreSQL Partial Index, which is used to enhance the performance of the command by reducing the index size.

What is PostgreSQL partial index?

Till now, we have understood how to add values of various columns to an index. The Partial index is the next command, which is used in the PostgreSQL index.

It allows us to define the rows of a table, which could be indexed. Therefore, we will use the partial index to speed up the query while dropping the index’s size.

In other words, we can say that the PostgreSQL partial index is an index, which is constructed over a subsection of a table. The predicate of the partial index is well- defined subset by a conditional expression.

And the index is accessed for only those table rows, which satisfy the predicate.

Suppose we have used the WHERE conditions that have a constant value, then in such scenarios, we can go for the PostgreSQL partial index as it is very beneficial.

Note: If the column name contains the constant_value, then we can use the following illustration:

SELECT *   

FROM table_name  

WHERE column_name = constant_value;

Syntax of PostgreSQL Partial Index

The syntax for specifying a partial index is as follows:

CREATE INDEX index_name  

ON table_name(column_list)  

WHERE condition;

In the above illustration, the WHERE clause is used to define those rows, which are added to the index.

Example of PostgreSQL Partial index

Let us see a sample example to understand the working of the PostgreSQL Partial Index.

We are creating one new table as Buyer with the CREATE command’s help and inserting some values using the INSERT command.

To create Clients into an Organization database, we use the CREATE command:

CREATE TABLE Buyer (  

    Buyer_id int Primary key,  

    first_name character varying(35) NOT NULL,  

    last_name character varying(35) NOT NULL,  

    email_address character varying(50),  

   active integer  

);

Output

After implementing the above command, we will get the following message window, which displays that the Buyer table has been created successfully into the Organization table.

After creating the Buyer table successfully, we will enter some values into it with the INSERT command’s help.

INSERT INTO Buyer( Buyer_id, first_name, last_name, email_address, active)VALUES (1001,'Mike', 'Ross','[email protected]',1),  
(1002,'Olivia', 'Smith','[email protected]',0),  
(1003,'Kathy', 'James','[email protected]',0),  
(1004,'Annie', 'Russell','[email protected]',0),  
(1005,'Rachel', 'Barnes','[email protected]',1);    

Output

After implementing the above command, we will get the following message window, which displays that the particular values have been inserted successfully into the Buyer table.

Typically, if we are interested in inactive buyers and frequently do some follow-ups to get them back to purchase more items.

In the below command, we are trying to identify all those buyers who are inactive:

SELECT Buyer_id, first_name, last_name, email_address  

FROM Buyer  

WHERE active = 0;

Output

After successfully implementing the above command, we will get the below result, which displays all the buyer information who are inactive:

In the below command, we are using the EXPLAIN keyword to implement the above command as the query planner needs to scan the Buyer table:

EXPLAIN  

SELECT Buyer_id, first_name, last_name, email_address  

FROM Buyer  

WHERE active = 0;

Output

After implementing the above command, we will get the following output, which shows the Buyer table’s Query plan.

Here, we can also enhance the command by creating an index for the active column, as shown in the following command:

CREATE INDEX idex_buyer_active  

ON Buyer(active);

Output

We will get the following message on executing the above command: the index has idex_buyer_active been created successfully

The above-created index satisfies its purpose, but it also contains several rows, which are never searched, specifically all the active buyers.

In the below command, we are specifying the index, which includes only inactive buyers:

CREATE INDEX idex_buyer_inactive  

ON Buyer(active)  

WHERE active = 0;

Output

After implementing the above command, we will get the following message: the idex_buyer_inactive index has been created successfully.

Hence, PostgreSQL will use partial index when the WHERE clause appears in a command, as shown in the following command:

EXPLAIN  

SELECT Buyer_id, first_name, last_name, email_address  

FROM Buyer  

WHERE active = 0;

Output

We will get the following message on executing the above command, which shows the Buyer table’s Query plan as the PostgreSQL optimizer is using the idex_buyer_inactive index.

Overview

In the PostgreSQL Partial Index section, we have understood the working of PostgreSQL partial index and how to define the rows added to the index.