"explain analyze SELECT * FROM bbb WHERE time >= 1577327100118ms and time <=1577327237287ms order by time asc limit 1000 "
├── execution_time: 4.102769ms
├── planning_time: 21.350743ms
├── total_time: 25.453512ms
but with ORDER BY time DESC LIMIT 1000
├── execution_time: 139.067401ms
├── planning_time: 537.022162ms
├── total_time: 676.089563ms
Why such difference in total time ?
My "guess’ is that the blocks within the database can only be read serially, from start to finish. Meaning that
ORDER BY DESC means reading the data in ascending order and reversing.
However, this is a guess.
Let me ping someone on our storage and query team for a more educated answer
To me, it looks weird. If there is an index, what difference does it read in asc or desc order?
InfluxDB doesn’t index on time, it shards on time. It means it can find blocks with timestamps quickly, but it still needs to sort those; and if your time range is across many blocks - this adds time.
But what would make a sort in ascending order and a sort in descending order
so different in duration?