How to Drop an Index in PostgreSQL

Introduction

When you use indexes to optimize query performance in PostgreSQL, there will be times when you may want to remove an index from the system. PostgreSQL makes it easy to accomplish this with the help of the DROP INDEX statement. This tutorial will walk you through a series of examples that demonstrate how to create an index and then drop the index.

Prerequisites

If you’re planning to follow along with this tutorial and create your own sample index to drop, you’ll need to make sure that PostgreSQL server has been properly installed and configured. The PostgreSQL server also needs to be running in the background.

If you’re using the Linux or Windows operating systems on your machine, you can download PostgreSQL here.

What is the DROP INDEX statement in PostgreSQL?

The PostgreSQL DROP INDEX statement is used to remove a specified existing index within the PostgreSQL database.

The statement uses the following syntax:

Let’s take a closer look at the syntax shown above:

• As the name implies, we specify the name of the index that we want to remove in the_index_name. This phrase comes immediately after the clause DROP INDEX.

• The clause IF EXISTS serves as a conditional option where the specified index name will only be deleted if it exists within the database system. If we tried to remove an index that doesn’t exist while using the IF EXISTS clause, PostgreSQL will return a notification instead of an error.

• Using the CONCURRENTLY clause will cause PostgreSQL to wait for the conflicting transactions to finish before removing the index.

• The CASCADE option will drop the index itself as well as all objects that depend on it.

• The RESTRICT option will not allow an index to be dropped if PostgreSQL detects that the index has one or more dependent objects. This option is set by default in the DROP INDEX statement.

Sample Data Set

Before we can demonstrate how to use the PostgreSQL DROP INDEX statement, we’ll need to create a sample table. We can create a table named leader_tbl using the following statement:

Then, we’ll use the statement shown below to insert a few records into the leader_tbl table:

Creating an Index

In this example, we’ll be creating an index on the leader_fname column of the leader_tbl table. We’ll use the following statement:

NOTE: To learn more about the PostgreSQL CREATE INDEX command, consult this article: PostgreSQL CREATE INDEX.

Now, let’s try a basic SELECT query on the leader_tbl table that we created earlier. Our query will look like this:

The statement shown above simply returns a row that has a value of oda in the column leader_fname.

In some cases, PostgreSQL’s built-in optimizer will not use an index if PostgreSQL finds it more efficient to just browse the whole table. We can see an example of this in action with the following query:

You’ll get results that look something like this:

We can see that the index idx_leader_first_name isn’t very useful at this moment; therefore, we’ll delete it using the DROP INDEX command:

We know that the delete operation was successful as we received a notification from PostgreSQL stating “DROP INDEX”.

CONCLUSION

Although indexes can go a long way toward optimizing queries and helping your database applications operate at peak performance, there are times when an index simply isn’t needed. In those cases, it’s important to know how to remove an index from the system. This tutorial explained how to use the DROP INDEX statement to remove an index. With the examples we provided as a guide, you’ll be able to delete indexes when needed in your own database system.

Starting PostgreSQL Server

Let’s look at the different steps needed to start up PostgreSQL on Linux and Windows machines:

• To start the PostgreSQL service in a Linux environment, use the following command:

• To check whether the service is running, use the command shown below:

• The output will look like this:

• The instructions needed to start, stop and restart PostgreSQL in a Windows environment are shown below:

1. First, open Control Panel
2. Then, open Administrative Tools
3. Open Services
4. Find the PostgreSQL Server service
5. Start, Stop or Restart the service