By default, some indexes are automatically created when you create a continuous aggregate. You can change this behavior. You can also manually create and drop indexes.
When you create a continuous aggregate, an index is automatically created for
GROUP BY column. The index is a composite index, combining the
column with the
For example, if you define a continuous aggregate view with
GROUP BY device,
location, bucket, two composite indexes are created: one on
and one on
To turn off automatic index creation, set
false when you create the continuous aggregate.
CREATE MATERIALIZED VIEW conditions_dailyWITH (timescaledb.continuous, timescaledb.create_group_indexes=false)AS...
You can use a regular PostgreSQL statement to create or drop an index on a continuous aggregate.
For example, to create an index on
avg_temp for a materialized hypertable
CREATE INDEX avg_temp_idx ON weather_daily (avg_temp);
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
DROP INDEX _timescaledb_internal.avg_temp_idx
In Timescale 2.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 Timescale versions.
Found an issue on this page?Report an issue!