---
title: Accelerate queries using indexes | Tiger Data Docs
description: Add indexes to speed up queries and DML on your hypertables — different mechanisms apply to the rowstore and the columnstore
---

Indexes shrink scan work for point lookups and selective filters. TimescaleDB uses two complementary mechanisms:

- **Regular PostgreSQL indexes on the rowstore** — B-tree indexes on recent, non-columnstore chunks, configured the same way as on a regular PostgreSQL table.
- **Sparse indexes on the columnstore** — `bloom` and `minmax` summaries per columnstore batch that let the engine skip batches that can’t match a query. See [Sparse indexes on the columnstore](#sparse-indexes-on-the-columnstore).

For background on how the columnstore stores compressed data, see [Compression methods](/docs/learn/columnar-storage/compression-methods/index.md).

## Indexes on the rowstore

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

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.

### Choose effective index columns

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`.

### Composite indexes

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 effective query for this index. It narrows down the list to a very specific portion.

### Create 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);
```

Tips

Monitor index creation progress on a hypertable through PostgreSQL‘s [`pg_stat_progress_create_index`](https://www.postgresql.org/docs/current/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING) view. Since 2.27.0, TimescaleDB reports per-chunk progress so you can track long-running builds.

If you do not want to lock the entire hypertable for the duration of the build, use [`CREATE INDEX … WITH (timescaledb.transaction_per_chunk)`](/docs/reference/timescaledb/hypertables/create_index/index.md), which creates the index in a separate transaction per chunk. Other operations on the hypertable can run concurrently between chunk transactions.

### Default indexes

When you create a hypertable with [`CREATE TABLE`](/docs/reference/timescaledb/hypertables/create_table/index.md), TimescaleDB creates a time index automatically. If you want to create the time index manually, run:

```
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);
```

To opt out of default index creation, set `tsdb.create_default_indexes` to `false` when you create the hypertable:

```
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

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;
```

Time-series data uses `UNIQUE` indexes more rarely than relational data. When you do need one, the index must include the partitioning column. For example, on a hypertable partitioned only on `time`, a `UNIQUE` index on `(time, location)` is valid; a `UNIQUE` index on `(location)` alone is not.

## Sparse indexes on the columnstore

Once chunks are converted to the columnstore, regular B-tree indexes don’t apply. Instead, TimescaleDB maintains *sparse indexes* — small per-batch summaries that let the engine skip batches that can’t match the query before reading or moving them back to the rowstore.

Two index types are available:

- **`bloom(<column>, ...)`** — probabilistic filter for equality predicates. Lets the engine answer “could this batch contain `column = X`?” without inspecting the batch’s compressed data. Accepts one or more columns; a [composite bloom](#composite-bloom-filters) tests combined equality predicates across the listed columns.
- **`minmax(<column>)`** — stores per-batch min and max for the column. Lets the engine answer range and equality predicates by comparing the predicate against the batch’s bounds.

Sparse indexes accelerate `SELECT`, `UPSERT`, `UPDATE`, and `DELETE` on columnstore data. For DML, when a batch can’t contain matching rows, TimescaleDB skips it without moving it back to the rowstore for checking — this is what delivers the headline speedups (more than 2× for upserts, up to 160× for `UPDATE`/`DELETE` with selective bloom matches).

### Default sparse indexes

By default ([`timescaledb.auto_sparse_indexes`](/docs/reference/timescaledb/configuration/gucs/index.md)), TimescaleDB picks suitable sparse indexes automatically based on the hypertable‘s existing B-tree indexes and column data types. Setting `tsdb.orderby` always creates an implicit `minmax` index on the orderby column.

### Configure sparse indexes

Configure sparse indexes when you [`CREATE TABLE`](/docs/reference/timescaledb/hypertables/create_table#arguments/index.md), and change them at any time with [`ALTER TABLE`](/docs/reference/timescaledb/hypercore/alter_table#arguments/index.md). Use `tsdb.sparse_index` with a comma-separated list:

```
CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id INT NOT NULL,
  region TEXT,
  cpu DOUBLE PRECISION,
  PRIMARY KEY (time, device_id)
) WITH (
  tsdb.hypertable,
  tsdb.orderby = 'time DESC',
  tsdb.segmentby = 'region',
  tsdb.sparse_index = 'bloom(device_id), minmax(cpu)'
);
```

### Restrictions

- **Bloom on `tsdb.segmentby` columns**: not allowed, single-column or composite. A bloom whose column list includes any segmentby column is rejected.
- **Single-column bloom on a `tsdb.orderby` column**: not allowed. The orderby column gets an implicit `minmax` automatically; use that for range filters. A composite `bloom(orderby_col, other_col)` that includes the orderby column **is** allowed.
- **Minmax on `tsdb.segmentby` columns**: not allowed.
- **Minmax on a `tsdb.orderby` column**: redundant — an implicit `minmax` is already added; configuring one explicitly has the same effect.
- **One single-column sparse index per column**: you cannot have two indexes on the same single column (`bloom(region), minmax(region)` is rejected). Composite indexes are not affected by this rule (`bloom(region, dev), bloom(dev)` is valid).

### Composite bloom filters

When a query filters on multiple columns that have `bloom` indexes, TimescaleDB automatically combines the per-column bloom checks at query time — no extra configuration needed.

For known multi-column query patterns, you can also configure an explicit composite bloom by listing the columns inside a single `bloom()` call. This stores one combined bloom per batch, which can be tighter than separate per-column blooms:

```
ALTER TABLE metrics SET (
  timescaledb.sparse_index = 'bloom(region, device)'
);
```

You can mix single-column and composite bloom indexes on the same hypertable. When multiple bloom indexes apply to a query, the planner picks the most-selective one.

### Verify with EXPLAIN

`EXPLAIN ANALYZE` exposes counters for sparse-index pruning. For `SELECT`, `UPDATE`, and `DELETE`:

- **Compressed batches filtered** — batches skipped by sparse indexes without moving them back to the rowstore.
- **Batches filtered after decompression** — batches that had to be processed before being filtered out.

For `UPSERT`, additional bloom-specific counters:

- **Batches checked by bloom**, **Batches pruned by bloom**, **Batches without bloom**, **Batches bloom false positives**.

Use these to confirm bloom and minmax indexes are doing useful work for your workload. If `Compressed batches filtered` stays at zero on selective queries, your sparse indexes are not on the right columns.