August 2, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL
DROP INDEX statement to remove an existing index.
Introduction to PostgreSQL DROP INDEX statement
Sometimes, you may want to remove an existing index from the database system. To do it, you use the
DROP INDEX statement as follows:
DROP INDEX [ CONCURRENTLY] [ IF EXISTS ] index_name [ CASCADE | RESTRICT ];
In this syntax:
You specify the name of the index that you want to remove after the
DROP INDEX clause.
Attempting to remove a non-existent index will result in an error. To avoid this, you can use the
IF EXISTS option. In case you remove a non-existent index with
IF EXISTS, PostgreSQL issues a notice instead.
If the index has dependent objects, you use the
CASCADE option to automatically drop these objects and all objects that depend on those objects.
RESTRICT option instructs PostgreSQL to refuse to drop the index if any objects depend on it. The
DROP INDEX uses
RESTRICT by default.
Note that you can drop multiple indexes at a time by separating the indexes by commas (,):
DROP INDEX index_name, index_name2,... ;
When you execute the
DROP INDEX statement, PostgreSQL acquires an exclusive lock on the table and blocks other accesses until the index removal completes.
To force the command waits until the conflicting transaction completes before removing the index, you can use the
DROP INDEX CONCURRENTLY has some limitations:
- First, the
CASCADEoption is not supported.
- Second, executing in a transaction block is also not supported.
PostgreSQL DROP INDEX example
We will use the
actor table from the sample database for the demonstration.
The following statement creates an index for the
first_name column of the
CREATE INDEX idx_actor_first_name ON actor (first_name);
Sometimes, the query optimizer does not use the index. For example, the following statement finds the actor with the name
SELECT * FROM actor WHERE first_name = 'John';
The query did not use the
idx_actor_first_name index defined earlier as explained in the following
EXPLAIN SELECT * FROM actor WHERE first_name = 'John';
This is because the query optimizer thinks that it is more optimal to just scan the whole table to locate the row. Hence, the
idx_actor_first_name is not useful in this case and we need to remove it:
DROP INDEX idx_actor_first_name;
The statement removed the index as expected.
- Use the PostgreSQL
DROP INDEXstatement to delete an index.