PostgreSQL Drop Index

In this section, we are going to understand the working of the PostgreSQL Drop Index and the examples of the Drop index command.

Why we use the PostgreSQL Drop index command?

In PostgreSQL, the Drop index command is used to remove the existing index. And if we drop an Index, it will improve the performance.

Syntax of PostgreSQL Drop Index command

The syntax for dropping an Index command is as follows:

DROP INDEX  [ CONCURRENTLY]  

[ IF EXISTS ]  index_name   

[ CASCADE | RESTRICT ];

In the above syntax, we have used the following parameters, as shown in the below table:

Parameters	Description
index_name	It is used to define the name of the index, which we want to delete it. And it should be written after the DROP INDEX command.
IF EXISTS	If we try to drop a non-existent index, it will raise an error in the output. Therefore, we can use the IF EXISTS option to resolve this error.
CASCADE	The CASCADE option is used in the index, which contains the dependent objects. And the CASCADE option will automatically remove these objects and all the objects, which rely on those objects.
RESTRICT	The RESTRICT option inform PostgreSQL to drop the index if any objects rely on it. By default, the DROP INDEX command uses the RESTRICT option.

CONCURRENTLY

If we implement the DROP INDEX command, PostgreSQL obtains an exclusive lock on the table and block other accesses until the index deletion completes.

In such a case, we can use the CONCURRENTLY option to forcefully allow the statement to wait until the contradictory transaction concludes before eliminating the index.

The DROP INDEX CONCURRENTLY contains some boundaries:

• The CASCADE option is not supported while using the DROP INDEX CONCURRENTLY.
• Implementing in a transaction block is also not supported if we are using the DROP INDEX CONCURRENTLY.

Note: We can separate the indexes using commas (,) and drop multiple indexes simultaneously, as shown in the below illustration:

DROP INDEX index_name, index_name2,... ;  

Example of PostgreSQL DROP INDEX

Let us see an example to understand the working of the PostgreSQL DROP Index command.

For this, we are taking the Employee table, which we created in the earlier section of the PostgreSQL tutorial.

And the Employee table contains various columns such as emp_id, employee_name, phone, and address.

Once we execute the below SELECT command, we can see the data, which exist in the Employee table as shown in the below screenshot

SELECT * FROM Employee;  

Output

After executing the above command, we will get the following output:

Now, we create an index for the Address column of the Employee table, as shown in the following command:

CREATE INDEX idex_emp_address   

ON Employee (address);

Output

After executing the above command, we will get the below message window, which displays that the Idex_emp_address has been created successfully.

Note: Occasionally, the command optimizer does not use the index.

As we can in the following example, the below command is used to identify the employee whose address is Boston:

SELECT * FROM Employee  

WHERE address = 'Boston';

Output

On executing the above command, we will get the following output, where we successfully identify those employees whose address is Boston.

In the above command, we did not use the idex_emp_address index, which was specified in the earlier section of the PostgreSQL Create index from the PostgreSQL index tutorial, as shown in the below EXPLAIN command:

EXPLAIN SELECT *  

FROM Employee  

WHERE address = 'Boston';

Output

After implementing the above command, we will get the following output, which shows the Query plan of the Employee table.

This is happening because the query developer thinks it is ideal for scanning the complete table to locate the row.

Therefore, in such case, the idex_emp_address is not beneficial to use; that’s why we are deleting it with the help of below command:

DROP INDEX idex_emp_address;  

Output

On executing the above command, we will get the following message, which displays that the idex_emp_address index has been removed successfully.

Overview

In the PostgreSQL Drop Index section, we have learned the following topics:

• We understand the PostgreSQL drop Indexcommand which is used to delete the particular table’s Index.