In PostgreSQL, the DROP INDEX statement to remove an existing index.
Syntax: DROP INDEX [ CONCURRENTLY] [ IF EXISTS ] index_name [ CASCADE | RESTRICT ];
Let’s analyze the above syntax:
- index_name : This is used to specify the name of the index that you want to remove after the DROP INDEX clause.
- IF EXISTS: 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.
- CASCADE: 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: The RESTRICT option instructs PostgreSQL to refuse to drop the index if any objects depend on it. The DROP INDEX uses RESTRICT by default.
- CONCURRENTLY: When you execute the DROP INDEX statement, PostgreSQL acquires an exclusive lock on the table and block 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 CONCURRENTLY option.
For the purpose of 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 actor table:
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 ‘John’:
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 statement:
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;
- PostgreSQL - DROP DATABASE
- PostgreSQL - DROP COLUMN
- PostgreSQL - DROP TABLE
- PostgreSQL - DROP SCHEMA
- PostgreSQL - Drop Procedure
- PostgreSQL - Drop Function
- PostgreSQL - DROP TRIGGER
- PostgreSQL - DROP ROLE
- PostgreSQL - DROP TABLESPACE
- PostgreSQL - Connect To PostgreSQL Database Server in Python
- PostgreSQL - CREATE INDEX
- PostgreSQL - Index Types
- PostgreSQL - UNIQUE Index
- PostgreSQL - Partial Index
- PostgreSQL - Index On Expression
- What is PostgreSQL - Introduction
- Install PostgreSQL on Windows
- Install PostgreSQL on Mac
- PostgreSQL - Loading a Database
- PostgreSQL - DISTINCT ON expression
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.