DROP INDEX

Use the DROP INDEX command to remove an index from a table or a materialized view.

Syntax

DROP INDEX [ IF EXISTS ] [ schema_name.]index_name [ CASCADE ];

Parameters

Parameter	Description
IF EXISTS clause	Do not return an error if the specified index does not exist.
schema_name	The schema of the index that you want to remove. You can use SHOW SCHEMAS to get a list of all available schemas. If you don’t specify a schema, the specified index in the default schema public will be removed.
index_name	The name of the index to remove. You can use DESCRIBE to show the indexes of a table.
CASCADE option	If this option is specified, all objects that depend on the index, and in turn all objects that depend on those objects will be dropped.

Examples

This statement removes the id_index index from the taxi_trips table in the default schema (public):

DROP INDEX id_index;

This statement removes the ad_id_index index from the ad_ctr_5min materialized view in the rw_schema schema:

DROP INDEX rw_schema.id_index;

Hanging or long running DROP INDEX

If your DROP INDEX command takes a long time to run, it could be due to high barrier latency. DROP commands need to be synchronized with barriers currently. To let DROP take effect immediately, you can use the RECOVER command in a separate session.

See also

CREATE INDEX

Construct an index on a table or a materialized view to speed up queries