# DROP INDEX
DROP INDEX removes an index

> For the complete documentation index, see [llms.txt](https://preview.materialize.com/materialize/40269/llms.txt).


`DROP INDEX` removes an index from Materialize.

## Syntax

```mzsql
DROP INDEX [IF EXISTS] <index_name> [CASCADE|RESTRICT];
```

Syntax element | Description
---------------|------------
**IF EXISTS** | Optional. If specified, do not return an error if the specified index does not exist.
`<index_name>` | Index to drop.
**CASCADE** | Optional. Equivalent to `RESTRICT`. `DROP INDEX` never drops the objects that depend on the index. See [Dependent objects](#dependent-objects).
**RESTRICT** | Optional. Do not remove the index if other objects depend on it. _(Default.)_

## Dependent objects

When Materialize creates a materialized view, another index, or a metric sink,
its optimizer may plan the new object to read from an existing index rather than
from the underlying relation. The object then depends on that index, even though
the index does not appear in the object's definition.

`DROP INDEX` fails if any materialized view, index, or metric sink reads from the
index, and the error names the dependent objects. This holds with `CASCADE` too:
because the dependency is invisible in the dependents' definitions, `DROP INDEX`
never drops them. To drop the index, first drop the dependent objects yourself,
then drop the index and recreate the dependents. In production, use a
[blue/green deployment](https://preview.materialize.com/materialize/40269/markdown-docs/developer-tools/dbt/blue-green-deployments/index.md) to replace
them without downtime.

A newer index on a relation can read from an older index on the same relation,
so drop indexes on the same relation newest first.

In-progress `SELECT`, `SUBSCRIBE`, and `COPY TO` statements that read from the
index do not block the drop. The index continues to be maintained until they
finish, and the `DROP INDEX` statement returns a notice saying so.

You can inspect which objects read from an index with
[`mz_internal.mz_compute_dependencies`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/system-catalog/mz_internal/index.md#mz_compute_dependencies).

## Privileges

To execute the `DROP INDEX` statement, you need:

- Ownership of the dropped index.
- `USAGE` privileges on the containing schema.

## Examples

### Remove an index

> **Tip:** In the **Materialize Console**, you can view existing indexes in the [**Database
> object explorer**](https://preview.materialize.com/materialize/40269/markdown-docs/developer-tools/console/data/index.md). Alternatively, you can use the
> [`SHOW INDEXES`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/show-indexes/index.md) command.


Using the  `DROP INDEX` commands, the following example drops an index named `q01_geo_idx`.

```mzsql
DROP INDEX q01_geo_idx;
```

If the index `q01_geo_idx` does not exist, the above operation returns an error.

### Remove an index that other objects read from

If a materialized view was planned to read from the index, `DROP INDEX` returns
an error:

```mzsql
DROP INDEX q01_geo_idx;
```
```nofmt
ERROR:  cannot drop index "q01_geo_idx": still depended upon by materialized view "q01_geo_summary"
DETAIL:  The dependent objects are live dataflows that read from index "q01_geo_idx", so the index cannot be dropped while they exist.
HINT:  Drop the dependent objects first, then drop the index and recreate the dependents. To replace them in production without downtime, use a blue/green deployment.
```

Drop the materialized view first, then the index, and recreate the view:

```mzsql
DROP MATERIALIZED VIEW q01_geo_summary;
DROP INDEX q01_geo_idx;
CREATE MATERIALIZED VIEW q01_geo_summary AS ...;
```

### Remove an index without erroring if the index does not exist

You can specify the `IF EXISTS` option so that the `DROP INDEX` command does
not return an error if the index to drop does not exist.

```mzsql
DROP INDEX IF EXISTS q01_geo_idx;
```

## Related pages

- [`CREATE INDEX`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-index/index.md)
- [`SHOW VIEWS`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/show-views/index.md)
- [`SHOW INDEXES`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/show-indexes/index.md)
- [`DROP OWNED`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/drop-owned/index.md)