PostgreSQL - DROP INDEX

The DROP INDEX statement in PostgreSQL is used to remove an existing index from a database. Dropping an index frees storage space and reduces the overhead of maintaining indexes during INSERT, UPDATE and DELETE operations.

• Free storage occupied by indexes.
• Reduce index maintenance overhead.
• Improve write performance when an index is no longer needed.

Syntax

DROP INDEX index_name;

Where:

• index_name: The name of the index to remove.

Working

Consider the following Classes table for the examples below:

Example 1: Create an Index

The following statement creates an index on the Instructor column.

Query:

CREATE INDEX idx_instructor
ON Classes (Instructor);

Output:

Example 2: Drop an Index

The following statement removes the idx_instructor index.

Query:

DROP INDEX idx_instructor;

Output:

Example 3: Drop an Index If It Exists

The following statement removes the index only if it exists.

Query:

DROP INDEX IF EXISTS idx_instructor;

Output: