> ## 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.
# Improve hypertable and query performance
> How and why to use chunk skipping to optimize hypertable performance and make sure your analytical queries are as fast as they can be.
export const TIMESCALE_DB = 'TimescaleDB';
export const HYPERTABLE = 'hypertable';
export const PG = 'Postgres';
Hypertables are {PG} tables that help you improve insert and query performance by automatically partitioning
your data by time. Each hypertable is made up of child tables called chunks. Each chunk is assigned a range of time,
and only contains data from that range. When you run a query, {TIMESCALE_DB} identifies the correct chunk and runs
the query on it, instead of going through the entire table. This page shows you how to tune hypertables to increase
performance even more.
* [Optimize hypertable chunk intervals][change-chunk-intervals]: choose the optimum chunk size for your data
* [Enable chunk skipping][chunk-skipping]: skip chunks on non-partitioning columns in hypertables when you query your data
* [Analyze your hypertables][analyze-hypertables]: use {PG} `ANALYZE` to create the best query plan
## Optimize hypertable chunk intervals
Adjusting your hypertable chunk interval can improve performance in your database.
1. **Choose an optimum chunk interval**
{PG} builds the index on the fly during ingestion. That means that to build a new entry on the index,
a significant portion of the index needs to be traversed during every row insertion. When the index does not fit
into memory, it is constantly flushed to disk and read back. This wastes IO resources which would otherwise
be used for writing the heap/WAL data to disk.
The default chunk interval is 7 days. However, best practice is to set `chunk_interval` so that prior to processing,
the indexes for chunks currently being ingested into fit within 25% of main memory. For example, on a system with 64
GB of memory, if index growth is approximately 2 GB per day, a 1-week chunk interval is appropriate. If index growth is
around 10 GB per day, use a 1-day interval.
You set `chunk_interval` when you [create a {HYPERTABLE}][hypertable-create-table], or by calling
[`set_chunk_time_interval()`][chunk_interval] on an existing hypertable.
[chunk_interval]: /api-reference/timescaledb/hypertables/set_chunk_time_interval
[hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table
In the following example you create a table called `conditions` that stores time values in the
`time` column and has chunks that store data for a `chunk_interval` of one day:
```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 (
timescaledb.hypertable,
timescaledb.chunk_interval='1 day'
);
```
2. **Check current setting for chunk intervals**
Query the {TIMESCALE_DB} catalog for a {HYPERTABLE}. For example:
```sql theme={"dark"}
SELECT *
FROM timescaledb_information.dimensions
WHERE hypertable_name = 'conditions';
```
The result looks like:
```sql theme={"dark"}
hypertable_schema | hypertable_name | dimension_number | column_name | column_type | dimension_type | time_interval | integer_interval | integer_now_func | num_partitions
-------------------+-----------------+------------------+-------------+--------------------------+----------------+---------------+------------------+------------------+----------------
public | metrics | 1 | recorded | timestamp with time zone | Time | 1 day | | |
```
Time-based interval lengths are reported in microseconds.
3. **Change the chunk interval length on an existing hypertable**
To change the chunk interval on an already existing hypertable, call [`set_chunk_time_interval()`][set_chunk_time_interval].
```sql theme={"dark"}
SELECT set_chunk_time_interval('conditions', INTERVAL '24 hours');
```
The updated chunk interval only applies to new chunks. This means setting an overly long
interval might take a long time to correct. For example, if you set
`chunk_interval` to 1 year and start inserting data, you can no longer
shorten the chunk for that year. If you need to correct this situation, create a
new hypertable and migrate your data.
While chunk turnover does not degrade performance, chunk creation
does take longer lock time than a normal `INSERT` operation into a chunk that has
already been created. This means that if multiple chunks are being created at
the same time, the transactions block each other until the first transaction is
completed.
If you use expensive index types, such as some PostGIS geospatial indexes, take
care to check the total size of the chunk and its index using
[`chunks_detailed_size()`][chunks_detailed_size].
## Enable chunk skipping
Early access Since [2.17.1][tsdb-2.17.1]
One of the key purposes of hypertables is to make your analytical queries run with the lowest latency possible.
When you execute a query on a hypertable, you do not parse the whole table; you only access the chunks necessary
to satisfy the query. This works well when the `WHERE` clause of a query uses the column by which a hypertable is
partitioned. For example, in a hypertable where every day of the year is a separate chunk, a query for September 1
accesses only the chunk for that day.
However, many queries use columns other than the partitioning one. For example, a satellite company might have a
table with two columns: one for when data was gathered by a satellite and one for when it was added to the database.
If you partition by the date of gathering, a query by the date of adding accesses all chunks in the hypertable and
slows the performance.
To improve query performance, TimescaleDB enables you to skip chunks on non-partitioning columns in hypertables.
Chunk skipping only works on chunks converted to the columnstore **after** you `enable_chunk_skipping`.
### How chunk skipping works
You enable chunk skipping on a column in a hypertable. TimescaleDB tracks the minimum and maximum values for that
column in each chunk. These ranges are stored in the start (inclusive) and end (exclusive) format in the `chunk_column_stats`
catalog table. TimescaleDB uses these ranges for dynamic chunk exclusion when the `WHERE` clause of an SQL query
specifies ranges on the column.
![Chunk skipping][chunk-skipping-image]
You can enable chunk skipping on hypertables compressed into the columnstore for `smallint`, `int`, `bigint`, `serial`,
`bigserial`, `date`, `timestamp`, or `timestamptz` type columns.
### When to enable chunk skipping
You can enable chunk skipping on as many columns as you need. However, best practice is to enable it on columns that
are both:
* Correlated, that is, related to the partitioning column in some way.
* Referenced in the `WHERE` clauses of the queries.
In the satellite example, the time of adding data to a database inevitably follows the time of gathering.
Sequential IDs and the creation timestamp for both entities also increase synchronously. This means those two
columns are correlated.
For a more in-depth look on chunk skipping, see [our blog post][our-blog-post].
### Enable chunk skipping
To enable chunk skipping on a column, call [`enable_chunk_skipping()`][enable_chunk_skipping] on a `hypertable` for a `column_name`. For example,
the following query enables chunk skipping on the `order_id` column in the `orders` table:
```sql theme={"dark"}
SELECT enable_chunk_skipping('orders', 'order_id');
```
For more details on how to implement chunk skipping, see the [API Reference][enable_chunk_skipping].
## Analyze your hypertables
You can use the {PG} `ANALYZE` command to query all chunks in your
{HYPERTABLE}. The statistics collected by the `ANALYZE` command are used by the
{PG} planner to create the best query plan. For more information about the
`ANALYZE` command, see the [{PG} documentation][pg-analyze].
[analyze-hypertables]: #analyze-your-hypertables
[change-chunk-intervals]: #optimize-hypertable-chunk-intervals
[chunk-skipping]: #enable-chunk-skipping
[chunk-skipping-image]: https://assets.timescale.com/docs/images/hypertable-with-chunk-skipping.png
[chunks_detailed_size]: /api-reference/timescaledb/hypertables/chunks_detailed_size
[enable_chunk_skipping]: /api-reference/timescaledb/hypertables/enable_chunk_skipping
[our-blog-post]: https://www.tigerdata.com/blog/boost-postgres-performance-by-7x-with-chunk-skipping-indexes
[pg-analyze]: https://www.postgresql.org/docs/current/sql-analyze.html
[set_chunk_time_interval]: /api-reference/timescaledb/hypertables/set_chunk_time_interval
[tsdb-2.17.1]: https://github.com/timescale/timescaledb/releases/tag/2.17.1