PostgreSQL Drop Indexes

This article describes how to drop existing indexes from a table usding DROP INDEX.

In PostgreSQL, you can drop an existing index from a table using the DROP INDEX statement.

PostgreSQL DROP INDEX syntax

Sometimes, you may wish to drop existing indexes from your database system. To do this, you can use the following DROP INDEX statement:

DROP INDEX  [ CONCURRENTLY ]
[ IF EXISTS ]  name
[ CASCADE | RESTRICT ];

Explanation:

  • The name is the name of the index to drop.

  • The IF EXISTS indicates that no error will be given if the specified index does not exist, and that PostgreSQL will issue a notification.

  • The CASCADE indicates PostgreSQL to automatically drop objects that depend on this index.

  • The RESTRICT indicates that the deletion is rejected if there are other objects that depend on this index. This is the default behavior.

  • When dropping an index, PostgreSQLt acquires an exclusive lock on the table and prevents any other access until the index drop is complete by defaul. You can use the CONCURRENTLY option to change this behavior.

    Note that CONCURRENTLY is not supported when using the CASCADE option.

A simple statement to drop an index is as follows:

DROP INDEX name;

You can drop multiple indexes at the same time with one statement, as follows:

DROP INDEX name1, name2,... ;

PostgreSQL DROP INDEX Examples

We will demonstrate using the actor table from PostgreSQL Sakila sample database.

The following statement creates an index on the first_name column of the actor table:

CREATE INDEX idx_actor_first_name
ON actor (first_name);

Sometimes the query optimizer doesn’t use indexes. For example, the following statement finds actors whose name is John:

SELECT * FROM actor
WHERE first_name = 'John';

The query does not use an index idx_actor_first_name defined earlier, as described in the following EXPLAIN statement:

EXPLAIN
SELECT * FROM actor
WHERE first_name = 'John';

                      QUERY PLAN
------------------------------------------------------
 Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
   Filter: ((first_name)::text = 'John'::text)

This is because the query optimizer thinks it is more optimal to just scan the entire table to locate rows. So idx_actor_first_name is not useful in this case, we can drop it:

DROP INDEX idx_actor_first_name;

Conclusion

In PostgreSQL, you can drop an existing index from a table using the DROP INDEX statement.