Online College Courses for Credit

+
DROP INDEX to Remove Indexes

DROP INDEX to Remove Indexes

Rating:
(0)
Author: Sophia Tutorial
Description:

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

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

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:

CREATE [UNIQUE] INDEX <indexname> <tablename> (<columnname>) [USING];

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);

File:11496-3340-1.png

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:

DROP INDEX [CONCURRENTLY] [IF] <indexname> [CASCADE];

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 use CONCURRENTLY being added 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.

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