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:

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

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:

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:

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:

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:

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:

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:

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.