In PostgreSQL databases, indexes are vital to locate and fetch required data from a huge amount of data efficiently. The query has to scan the complete table to find a single value if there were no indexes created on the table. Indexes enable the query optimizer to directly fetch data using the index of the table or column.

This guide will explain how to drop an index in PostgreSQL.

How to Drop an INDEX in PostgreSQL?

Indexes are immensely important for fetching data quickly from tables created on the database but in some cases, they lose their significance. Query optimizer finds it easy to locate data by scanning the complete table rather than looking for the index and using it. In that case, PostgreSQL allows the user to drop that index which simply doesn't serve any purpose.

Syntax

The following is the syntax for dropping an index in the PostgreSQL database:

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

Here:

- The DROP INDEX clause is used to drop an index from the PostgreSQL table.
- The use of the CONCURRENTLY option blocks access to the table while dropping the index.
- After that, type the name of the index with the IF EXISTS clause which checks the existence of the index in the table.
- The CASCADE clause is used to drop the dependent objects of the index and the RESTRICT clause refuses to drop the index in case any dependent object is found.

Syntax to Drop Multiple Indexes

To drop multiple indexes in the PostgreSQL database simply separate indexes with commas as depicted in the following syntax:

DROP INDEX   index_name, index_name2,... ;

Example 1: DROP INDEX From the Table

Let’s first create an index on the country column of the table person using the following query:

CREATE INDEX idx_person_country 
 ON person(country);

Use the following query to fetch a record from the indexed column:

SELECT * FROM person
 WHERE country = 'UK';

Running the above code will display the record from the person table containing the desired data:

Use the following query to check if the query used the index or not for fetching the table’s data:

EXPLAIN SELECT * FROM person
 WHERE country = 'UK';

The query optimizer has scanned the complete table which means that the index we created earlier has no purpose here:

Use the following code to drop the index created earlier as it is not being used by the query optimizer:

DROP INDEX   idx_person_country;

Running the above query has dropped the index successfully:

That’s all about dropping an INDEX in the PostgreSQL table.

Conclusion

In PostgreSQL, indexes are used to optimize the performance of the database as it is used by the query to fetch data quickly. Without them, the query simply has to scan the complete table to fetch a single row which can be a lot more time-consuming. Sometimes the query scans the complete table in the presence of the index as it deems it more effective. In these cases, it is better to drop indexes rather than keeping them in the database as this guide has explained with examples.