Javatpoint Logo
Javatpoint Logo

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:

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:

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:

PostgreSQL Drop Index

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.

PostgreSQL Drop Index

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.

PostgreSQL Drop Index

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.

PostgreSQL Drop Index

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.

PostgreSQL Drop Index

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.





Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA