ASC time index in timescaleDB

user_10551132 0
Viewed 121

I'm building a TimescaleDB local server and I'm creating my first "production" hypertables. The point is that, at the moment, all the future consumers of my DB are going to use the data in ASC order, but by default timescale creates a DESC index in the time column.

My doubt is, does it worth to change the default behaviour and make the index to be ASC?

I don't know if it's DESC by default for a good reason and I'm going to have some penalty. I have also read that indexs in postgresql can be read backward, so a DESC index could be used in an ASC query, but I don't know if there are performance penalties.

In the other hand, it's safe to simple delete the default index and create a new one with different order? Also not sure if deleting it I'm going to screw up some timescale internal functionality.

Thanks for your time,

H25E

timescaledb indexing postgresql
1 Answers

For a single-column index, it does not matter at all if it is created ASC or DESC, because indexes can be read in both directions with the same efficiency.

The only time when you really need to specify DESC in an index is if the index is supposed to support an ORDER BY clause like ORDER BY a, b DESC. Then one of the index columns must be sorted ASC and the other DESC — but again it doesn't matter which one is ASC and which DESC, as the index can be read in both directions.

So, for a single column index, there is no need to build the index again, and there was no good reason to create it DESC in the first place (but it doesn't matter).

user_64643080
Related