Manually create and drop indexes

You can use a regular Postgres statement to create or drop an index on a continuous aggregate.

For example, to create an index on avg_temp for a materialized hypertable named weather_daily:

Indexes are created under the _timescaledb_internal schema, where the continuous aggregate data is stored. To drop the index, specify the schema. For example, to drop the index avg_temp_idx, run:

Limitations on created indexes

In TimescaleDB v2.7 and later, you can create an index on any column in the materialized view. This includes aggregated columns, such as those storing sums and averages. In earlier versions of TimescaleDB, you can't create an index on an aggregated column.

You can't create unique indexes on a continuous aggregate, in any of the TimescaleDB versions.

===== PAGE: https://docs.tigerdata.com/use-timescale/continuous-aggregates/about-continuous-aggregates/ =====

Examples:

Example 1 (sql):

CREATE MATERIALIZED VIEW conditions_daily
  WITH (timescaledb.continuous, timescaledb.create_group_indexes=false)
  AS
  ...

Example 2 (sql):

CREATE INDEX avg_temp_idx ON weather_daily (avg_temp);

Example 3 (sql):

DROP INDEX _timescaledb_internal.avg_temp_idx