[Bug]: Correlation on chunks do not sum up into hypertable statistics about timescaledb HOT 2 OPEN

Hello @igor2x and thanks for the bug report.

It looks like, to my surprise, data in my chunks are really super "fragmented" (non-clustered) and I need to cluster them, to increase performance.

Chunks are not automatically clustered in any way just because they are chunks. You have to run cluster on them explicitly to get the correlation back. If you do a lot of updates on the table, correlation will go down because the new row versions are added last in the data file, which affects correlation.

However, running cluster and then analyze should get the correlation statistics back.

from timescaledb.

Additional info:

1. I see partition index on time is by default in descending order, so expected value on hypertable level should be near -1.
2. For one particular chunk where correlation was near 0,2 I executed: CLUSTER my_chunk USING index_on_time_column;
3. I have rechecked the correlation on time column on now clustered chunk and I have got: -0.9999673. This is very near -1 and this is pretty much expected value.

Conclusions:

1. It looks like on hypertable correlations not updated. Maybe this is the value when first time PostgreSQL table was created and before it was converted to hypertable. Why, I assume value near 1, is not usual for TimescaleDB, it should be near -1, because of descending order of index.
2. It looks like, to my surprise, data in my chunks are really super "fragmented" (non-clustered) and I need to cluster them, to increase performance.

from timescaledb.