Materialize Logo

DROP INDEX

DROP INDEX removes an index from a materialized view. (Non-materialized views do not have any indexes.)

Syntax

DROP INDEX IF EXISTS index_name CASCADE RESTRICT
Field Use
IF EXISTS Do not return an error if the named index doesn’t exist.
index_name The name of the index you want to remove.
CASCADE Remove the index and its dependent objects.
RESTRICT Remove the index. (Default.)

Note: Since indexes cannot currently have dependent objects, DROP INDEX, DROP INDEX RESTRICT, and DROP INDEX CASCADE all do the same thing.

Details

Indexes and materialized views

Primary indexes

By default, materialized views only have one index, which we call the “primary index”, and which stores the result set of the materialized view’s embedded SELECT statement. You can identify the primary index by its name, which follows the format <view name>_primary_idx.

This primary index doesn’t follow the constraints for typical for primary indexes in traditional databases; it is simply the first index created on a view. Please keep in mind the following constraints:

If you remove the primary index and want to recreate it:

  1. Retrieve the view’s embedded SELECT statement with SHOW CREATE VIEW.
  2. Re-create the view as a materialized view with CREATE OR REPLACE MATERIALIZED VIEW.

Alternatively, you can also CREATE INDEX to materialize any view.

Examples

Remove an index

SHOW VIEWS;
+-----------------------------------+
| VIEWS                             |
|-----------------------------------|
| ...                               |
| q01                               |
+-----------------------------------+
SHOW INDEXES FROM q01;
+------------------------+--------------------------------+-----
| View                   | Key_name                       | ...
|------------------------+--------------------------------+----
| materialize.public.q01 | materialize.public.q01_geo_idx | ...
+------------------------+--------------------------------+-----

You can use the unqualified index name (q01_geo_idx) rather the fully qualified name (materialize.public.q01_geo_idx).

You can remove an index with any of the following commands:

Do not issue an error if attempting to remove a nonexistent index

DROP INDEX IF EXISTS q01_geo_idx;
Did this info help?
Yes No