Online College Courses for Credit

DROP INDEX to Remove Indexes
of 0 possible points
DROP INDEX to Remove Indexes

DROP INDEX to Remove Indexes

Author: Sophia Tutorial

Identify a correctly composed DROP INDEX statement to remove an existing index.

See More

what's covered
This tutorial explores the use of the DROP INDEX command to remove indexes in two parts:
  1. Index Creation
  2. Dropping an Index

1. Index Creation

We previously looked at the creation of indexes through the use of primary keys and foreign keys. Both of those constraints automatically create the indexes. As a recap, the CREATE INDEX statement will look like this:

<tablename> (<columnname>)  [USING method];

For example, if we wanted to create a unique constraint and index on the email address in the customer table, we could do:

CREATE UNIQUE INDEX idx_customer_email
ON customer(email);

However, we could not create a UNIQUE constraint and index on the country as the country does repeat by customer:

CREATE UNIQUE INDEX idx_customer_country
ON customer(country);

Unique Index Error Message

You could however create an index on the country in general:

CREATE INDEX idx_customer_country
ON customer(country);

We could add the type of index that we would want to add by adding the USING method. By default, the b-tree option is selected. However, you can pass in a hash, gist, or gin. Not all versions of PostgreSQL support this.

CREATE INDEX idx_customer_country USING hash
ON customer(country);

2. Dropping an Index

Once we have created an index, removing the index is quite simple with a few options. We simply have the following:


You should find that many of the options are quite similar to some of the other DROP statements off of other objects. Typically, we want to remove any unused indexes so that we consider the performance of the database.

IF EXISTS will help avoid the database returning an error and will only remove an index if it exists.

DROP INDEX IF EXISTS idx_customer_country;

CASCADE will automatically drop any objects that depend on the index that we are dropping.

DROP INDEX idx_customer_country CASCADE;

RESTRICT is there by default and will not drop the index if we have any objects that depend on it.

DROP INDEX idx_customer_country RESTRICT;

When we run the DROP INDEX statement, PostgreSQL will get a lock on the table and block any other access to the table until the dropping of the index is complete. If there is a conflicting transaction that is running on the table, we can add CONCURRENTLY to the command to wait until any conflicting transactions are done before we remove the index. Note that if we do use the DROP INDEX CONCURRENTLY option, we cannot use the CASCADE option.

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

Dropping an index is important to run if we do not use an index.

Source: Authored by Vincent Tran