[Bug]: Using ORDER BY + LIMIT cause severe speed impact

[Tobbeman22]

What type of bug is this?

Performance issue

What subsystems and features are affected?

Query executor

What happened?

Hello!

This is my first ticket, please let me know if I should change anything.

I have a database with a table called "raw_data" containing minute level measurements. Typically we get around 100M rows per day. Below is a dump of hopefully relevant information:

                        Table "public.raw_data"
  Column   |           Type           | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------
 stream_id | integer                  |           | not null |
 time      | timestamp with time zone |           | not null |
 value     | real                     |           | not null |
Indexes:
    "raw_data_pkey" PRIMARY KEY, btree (stream_id, "time")
    "raw_data_time_idx" btree ("time" DESC)
Number of child tables: 498 (Use \d+ to list them.)

 hypertable_schema | hypertable_name | owner  | num_dimensions | num_chunks | compression_enabled | tablespaces | primary_dimension |  primary_dimension_type
-------------------+-----------------+--------+----------------+------------+---------------------+-------------+-------------------+--------------------------
 public            | raw_data        | cwuser |              1 |        498 | t                   |             | time              | timestamp with time zone
(1 row)

 hypertable_schema | hypertable_name | dimension_number | column_name |       column_type        | dimension_type | time_interval | integer_interval | integer_now_func | num_partitions
-------------------+-----------------+------------------+-------------+--------------------------+----------------+---------------+------------------+------------------+----------------
(1 row)

 interval |   first_range_start    | chunk_count
----------+------------------------+-------------
 12:00:00 | 2026-07-02 00:00:00+00 |          13
 1 day    | 2026-05-14 00:00:00+00 |          49
 7 days   | 2018-01-04 00:00:00+00 |         436
(3 rows)

Recently I wanted to find all "bad values" for different streams. This work led me to write code that would walk stream by stream, and select all values outside a certain span. Writing this code, I felt it would be smart to limit the size of each fetch to manage memory which lead me to queries like this:

select time, value from raw_data where stream_id = 355532 and time >= '2025-01-01' and time < '2026-06-01' and (value < 0 or value > 100) order by time desc limit 1000
The idea was, if we find anything we can select again with a new time span. If we find nothing, there is no data and we can move on to the next stream.

I did some short experiments, and while it took much longer than I expected ( 2-3 seconds vs 70ms for a count(*) with the same terms ) it was good enough for me.
Running the code however, it soon crashed with a timeout. This led me to some more experiments and the reason for this ticket.

I've run these queries and found something interesting. Note that all the queries result in 0 results.

psql timeseries -c "explain analyze select time, value from raw_data where stream_id = 355532 and time >= '2025-01-02' and time < '2025-01-23' and (value < 0 or value > 100) order by time desc" > brief_no_limit.txt
psql timeseries -c "explain analyze select time, value from raw_data where stream_id = 355532 and time >= '2025-01-02' and time < '2025-01-23' and (value < 0 or value > 100) order by time desc limit 100" > brief_limit.txt
psql timeseries -c "explain analyze select time, value from raw_data where stream_id = 355532 and time >= '2025-01-01' and time < '2026-06-01' and (value < 0 or value > 100) order by time desc limit 100" > limit.txt
psql timeseries -c "explain analyze select time, value from raw_data where stream_id = 355532 and time >= '2025-01-01' and time < '2026-06-01' and (value < 0 or value > 100) order by time desc" > no_limit.txt
psql timeseries -c "explain analyze select time, value from raw_data where stream_id = 355532 and time >= '2025-01-01' and time < '2026-05-14' and (value < 0 or value > 100) order by time desc limit 100" > not_slow.txt
psql timeseries -c "explain analyze select time, value from raw_data where stream_id = 355532 and time >= '2025-01-01' and time < '2026-05-15' and (value < 0 or value > 100) order by time desc limit 100" > slow.txt

slow.txt
brief_limit.txt
limit.txt
brief_no_limit.txt
no_limit.txt
not_slow.txt

It looks to me like when adding ORDER BY + LIMIT we get severe slow down. What is very interesting, is that it seems to be connected to if the query spans chunks with different interval? As we can see that "not_slow.txt" is only hitting 1 week chunks while "slow.txt" is hitting 1 week chunk as well as 1 day chunk and the time is very very different.

I'm no expert in reading "explain" output, but looks to me like "limit" and "no_limit" has the same plan except the inclusion of Limit at the top. The buffers output is very different though:
limit: Buffers: shared hit=1915100 read=932434 dirtied=2
no_limit: Buffers: shared hit=470

I did rerun both today to ensure it is not just buffers, but the limit case continue to perform much worse ( though better than the text file, ending up at 3 seconds instead of 21 )

Overall, it feels like when using LIMIT we get strange behavior. I speculate that limit force it to run sequentially instead of parallel, but I still find these finding very strange ( Why much slower when including 1 day chunk compared to only 7 days chunks )

Hopefully this information is helpful and perhaps there is a bug, until it is resolved however I guess we will do paging using only time frames.

TimescaleDB version affected

2.26.3

PostgreSQL version used

18.3

What operating system did you use?

Ubuntu 24.04.4 LTS

What installation method did you use?

Deb/Apt

What platform did you run on?

Microsoft Azure Cloud

Relevant log output and stack trace

How can we reproduce the bug?

Not sure if it is easy without a lot of data.
If this ticket end up being interesting I could take another look.

[akuzm]

I think I fixed this on 2.29.0 here: #9809
Please try when you have a chance.