DROP INDEX to Remove Indexes

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

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.