> ## 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.
# Accelerate queries using indexes
> Add indexes to speed up queries on your hypertables. Learn how indexing works, which indexes are created by default, and best practices for creating them manually
export const PG = 'Postgres';
export const CHUNK = 'chunk';
export const COLUMNSTORE = 'columnstore';
export const TIMESCALE_DB = 'TimescaleDB';
export const HYPERTABLE = 'hypertable';
Because looking up data can take a long time, especially if you have a lot of data in your {HYPERTABLE}, you can use
an index to speed up read operations from non-compressed {CHUNK}s in the rowstore (which use their
[own columnar indexes][compression]).
{TIMESCALE_DB} supports all table objects supported within {PG}, including data types, indexes, and triggers. 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).
## How indexing works
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
[{PG} timestamp][postgresql-timestamp].
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:
```sql theme={"dark"}
garage-0940
garage-0930
garage-0920
garage-0910
office-0930
office-0920
office-0910
```
An index on `(time DESC, location)` is organized like this:
```sql theme={"dark"}
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`.
### Example: index device metrics
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:
```sql theme={"dark"}
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:
```sql theme={"dark"}
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:
```sql theme={"dark"}
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.
```sql theme={"dark"}
SELECT * FROM devices WHERE store_id = x, 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.
```sql theme={"dark"}
SELECT * FROM devices WHERE device_id = M, 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`.
```sql theme={"dark"}
SELECT * FROM devices WHERE store_id = M, device_id = M, time > 10
```
This is an accurate query for this index. It narrows down the list to a very specific portion.
## 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:
```sql theme={"dark"}
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`][create-index]
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.
You can also use the [{PG} `WITH` clause][pg-with-clause] to perform indexing transactions on an individual {CHUNK}.
## 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:
```sql theme={"dark"}
CREATE INDEX ON conditions (location, time DESC);
```
You can run this command before or after you convert a regular {PG} 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`][hypertable-create-table], a time index is created on
your data. If you want to manually create a time index, you can use this command:
```sql theme={"dark"}
CREATE INDEX ON conditions (time DESC);
```
You can also create an additional index on another column and time. For example:
```sql theme={"dark"}
CREATE INDEX ON conditions (location, time DESC);
```
{TIMESCALE_DB} also creates sparse indexes per compressed {CHUNK} for optimization. You can manually set up those
indexes when you call [`CREATE TABLE`][hypertable-create-table] or [`ALTER_TABLE`][alter_table_hypercore].
If you do not want to create default indexes, you can set `create_default_indexes` to `false` when you create a
{HYPERTABLE}. For example:
```sql theme={"dark"}
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 ...`][hypertable-create-table], the default partitioning
column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a
[columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an
interval equal to the value of the [`chunk_interval`][create_table_arguments], defined through `compress_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} {CHUNK}s are compressed by
up to 98%, and organized for efficient, large-scale queries.
You can customize this policy later using [`alter_job`][alter_job_samples]. However, to change `after` or
`created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must
[remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy].
You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}.
[add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy
[alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples
[convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore
[create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments
[hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table
[remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy
[alter_table_hypercore]: /api-reference/timescaledb/hypercore/alter_table
[compression]: /manage-data/capabilities/compression/about-compression
[create-index]: /api-reference/timescaledb/hypertables/create_index
[hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table
[pg-with-clause]: https://www.postgresql.org/docs/current/queries-with.html
[postgresql-timestamp]: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29