> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Create indexes on continuous aggregates

> Add indexes to your continuous aggregate to make your analytical queries faster

export const PG = 'Postgres';

export const CAGG_CAP = 'Continuous aggregate';

export const CAGG = 'continuous aggregate';

export const TIMESCALE_DB = 'TimescaleDB';

By default, some indexes are automatically created when you create a {CAGG}. You can change this behavior.
You can also manually create and drop indexes.

## Automatically created indexes

When you create a {CAGG}, an index is automatically created for each `GROUP BY` column. The index is a
composite index, combining the `GROUP BY` column with the `time_bucket` column.

For example, if you define a {CAGG} view with `GROUP BY device, location, bucket`, two composite indexes
are created: one on `{device, bucket}` and one on `{location, bucket}`.

### Turn off automatic index creation

To turn off automatic index creation, set `timescaledb.create_group_indexes` to `false` when you create the
{CAGG}.

For example:

```sql theme={"dark"}
CREATE MATERIALIZED VIEW conditions_daily
  WITH (timescaledb.continuous, timescaledb.create_group_indexes=false)
  AS
  ...
```

## Manually create and drop indexes

You can use a regular {PG} statement to create or drop an index on a {CAGG}.

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

```sql theme={"dark"}
CREATE INDEX avg_temp_idx ON weather_daily (avg_temp);
```

Indexes are created under the `_timescaledb_internal` schema, where the {CAGG} data is stored. To drop the
index, specify the schema. For example, to drop the index `avg_temp_idx`, run:

```sql theme={"dark"}
DROP INDEX _timescaledb_internal.avg_temp_idx;
```

### Limitations on created indexes

In {TIMESCALE_DB} 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
{TIMESCALE_DB}, you can't create an index on an aggregated column.

You can't create unique indexes on a {CAGG}, in any of the {TIMESCALE_DB} versions.