---
title: Accelerate queries using indexes | Tiger Data Docs
description: Add indexes to speed up queries on your hypertables and learn best practices for creating them
---

Indexes shrink scan work for point lookups and selective filters on recent, non-compressed chunks in the rowstore (compressed chunks rely on [columnar indexing](/docs/learn/columnar-storage/compression-methods/index.md) instead).

You can create an index on any combination of columns. To define an index as a `UNIQUE` or `PRIMARY KEY` index, it must include the partitioning column (this is usually the time column).

Which column you choose to create your index on depends on what kind of data you have stored. When you create a hypertable, set the datatype for the `time` column as `timestamptz` and not `timestamp`. For more information, see [PostgreSQL timestamp best practices](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29).

Note

While it is possible to add an index that does not include the `time` column, doing so results in very slow ingest speeds. For time-series data, indexing on the time column allows one index to be created per chunk.

Consider a simple example with temperatures collected from two locations named `office` and `garage`:

An index on `(location, time DESC)` is organized like this:

```
garage-0940
garage-0930
garage-0920
garage-0910
office-0930
office-0920
office-0910
```

An index on `(time DESC, location)` is organized like this:

```
0940-garage
0930-garage
0930-office
0920-garage
0920-office
0910-garage
0910-office
```

A good rule of thumb with indexes is to think in layers. Start by choosing the columns that you typically want to run equality operators on, such as `location = garage`. Then finish by choosing columns you want to use range operators on, such as `time > 0930`.

As a more complex example, imagine you have a number of devices tracking 1,000 different retail stores. You have 100 devices per store, and 5 different types of devices. All of these devices report metrics as `float` values, and you decide to store all the metrics in the same table, like this:

```
CREATE TABLE devices (
     time timestamptz,
     device_id int,
     device_type int,
     store_id int,
     value float
);
```

When you create this table, an index is automatically generated on the time column, making it faster to query your data based on time.

If you want to query your data on something other than time, you can create different indexes. For example, you might want to query data from the last month for just a given `device_id`. Or you could query all data for a single `store_id` for the last three months.

You want to keep the index on time so that you can quickly filter for a given time range, and add another index on `device_id` and `store_id`. This creates a composite index. A composite index on `(store_id, device_id, time)` orders by `store_id` first. Each unique `store_id`, will then be sorted by `device_id` in order. And each entry with the same `store_id` and `device_id` are then ordered by `time`. To create this index, use this command:

```
CREATE INDEX ON devices (store_id, device_id, time DESC);
```

When you have this composite index on your hypertable, you can run a range of different queries. Here are some examples:

```
SELECT * FROM devices WHERE store_id = x
```

This queries the portion of the list with a specific `store_id`. The index is effective for this query, but could be a bit bloated; an index on just `store_id` would probably be more efficient.

```
SELECT * FROM devices WHERE store_id = x AND time > 10
```

This query is not effective, because it would need to scan multiple sections of the list. This is because the part of the list that contains data for `time > 10` for one device would be located in a different section than for a different device. In this case, consider building an index on `(store_id, time)` instead.

```
SELECT * FROM devices WHERE device_id = M AND time > 10
```

The index in the example is useless for this query, because the data for `device M` is located in a completely different section of the list for each `store_id`.

```
SELECT * FROM devices WHERE store_id = M AND device_id = M AND time > 10
```

This is an accurate query for this index. It narrows down the list to a very specific portion.

## Creating indexes

You can create an index using the `CREATE INDEX` command. For example, to create an index that sorts first by `location`, then by `time`, in descending order:

```
CREATE INDEX ON conditions (location, time DESC);
```

You can run this command before or after you convert a regular PostgreSQL table to a hypertable.

### Default indexes

Some indexes are created by default when you perform certain actions on your database.

When you create a hypertable with a call to [`CREATE TABLE`](/docs/reference/timescaledb/hypertables/create_table/index.md), a time index is created on your data. If you want to manually create a time index, you can use this command:

```
CREATE INDEX ON conditions (time DESC);
```

You can also create an additional index on another column and time. For example:

```
CREATE INDEX ON conditions (location, time DESC);
```

TimescaleDB also creates sparse indexes per columnstore chunk for optimization, including bloom indexes for equality filters and min/max indexes for range filters. You can manually configure these indexes when you call [`CREATE TABLE`](/docs/reference/timescaledb/hypertables/create_table/index.md) or [`ALTER TABLE`](/docs/reference/timescaledb/hypercore/alter_table/index.md).

If you do not want to create default indexes, you can set `create_default_indexes` to `false` when you create a hypertable. For example:

```
CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  device      TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
) WITH (
  tsdb.hypertable,
  tsdb.create_default_indexes=false
);
```

When you create a hypertable using [CREATE TABLE … WITH …](/docs/reference/timescaledb/hypertables/create_table/index.md), the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a [columnstore policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) that automatically converts your data to the columnstore, after an interval equal to the value of the [chunk\_interval](/docs/reference/timescaledb/hypertables/set_chunk_time_interval/index.md), defined through `after` in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries.

You can customize this policy later using [alter\_job](/docs/reference/timescaledb/jobs-automation/alter_job/index.md). However, to change `after` or `created_before`, the compression settings, or the hypertable the policy is acting on, you must [remove the columnstore policy](/docs/reference/timescaledb/hypercore/remove_columnstore_policy/index.md) and [add a new one](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md).

You can also manually [convert chunks](/docs/reference/timescaledb/hypercore/convert_to_columnstore/index.md) in a hypertable to the columnstore.

### Best practices for indexing

If you have sparse data, with columns that are often NULL, you can add a clause to the index, saying `WHERE column IS NOT NULL`. This prevents the index from indexing NULL data, which can lead to a more compact and efficient index. For example:

```
CREATE INDEX ON conditions (time DESC, humidity)
  WHERE humidity IS NOT NULL;
```

To define an index as a `UNIQUE` or `PRIMARY KEY` index, the index must include the time column and the partitioning column, if you are using one. For example, a unique index must include at least the `(time, location)` columns, in addition to any other columns you want to use. Generally, time-series data uses `UNIQUE` indexes more rarely than relational data.

If you do not want to create an index in a single transaction, you can use the [`CREATE_INDEX`](/docs/reference/timescaledb/hypertables/create_index/index.md) function. This uses a separate function to create an index on each chunk, instead of a single transaction for the entire hypertable. This means that you can perform other actions on the table while the index is being created, rather than having to wait until index creation is complete.

Note

You can also use the [PostgreSQL `WITH` clause](https://www.postgresql.org/docs/current/queries-with.html) to perform indexing transactions on an individual chunk.