[Bug] Postgres 17 query planner much slower with hypertable joins

[t-aistleitner]

What type of bug is this?

Performance issue

What subsystems and features are affected?

Query planner

What happened?

We recently upgraded our development environment from Postgres 14 → Postgres 17 and TimescaleDB 2.16 → 2.21.

Since then, we’ve noticed that certain queries involving joins now take 2.5× longer in the planning phase compared to Postgres 14. These queries are performance-critical in our application.

I’m aware that Postgres 17 introduced major changes in the query planner, but I don’t fully understand why planning times got significantly worse for these specific cases.

In particular, we have a query that joins an array of datetimes with data from a hypertable.

• Querying the hypertable directly performs equally well on Postgres 14 and 17.
• However, the planning time of the join query grows with the number of chunks in the hypertable.

Execution plans for reference:

• Postgres 14.13 and TimescaleDB 2.16: https://explain.depesz.com/s/2qgd
• Postgres 17.15 and TimescaleDB 2.21: https://explain.depesz.com/s/VJIk

I executed the queries several times to see if planning time decreases.

Question: Why does Postgres 17’s new planner cause such a large increase in planning time for this type of TimescaleDB query, and is there a recommended way to mitigate this regression?

TimescaleDB version affected

2.21

PostgreSQL version used

17.15

What operating system did you use?

Rocky Linux 9

What installation method did you use?

RPM

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

How can we reproduce the bug?

drop table if exists test_performance cascade;
create table if not exists test_performance (id INT not null,
							date TIMESTAMP not null,
							value INT not null);

create unique index test_performance_idx on test_performance (id, date);

SELECT create_hypertable('test_performance', by_range('date', INTERVAL '1 day'), create_default_indexes => False);
ALTER TABLE test_performance SET (timescaledb.compress, timescaledb.compress_orderby = 'date DESC', timescaledb.compress_segmentby = 'id');

INSERT INTO test_performance (id, date, value)
SELECT
    id_val,
    time_val,
    (random()*100)::INT
FROM
    generate_series('2024-01-01 00:00:00'::timestamp, '2024-10-30 23:59:59'::timestamp, '10 minutes') AS time_val
CROSS JOIN
    generate_series(1, 100) AS id_val;

explain analyse select id, date from unnest(array['2024-10-15 00:10'::timestamp, '2024-10-15 00:20'::timestamp]) as test_dates
left join (select id, date from test_performance where id = 1 and date >= '2024-10-15 00:10' and date <= '2024-10-15 00:20') as tp on (tp.date = test_dates);

[t-aistleitner]

I have also tested this on PostgreSQL 15 and 16 now. The issue persists on PG16, but on PG15, the planning time is comparable to a normal data query.

Postgres 15.14 and TimescaleDB 2.22:

• Query with join: https://explain.depesz.com/s/VFkZ#html
• "Normal" data query: https://explain.depesz.com/s/k4TG

[antekresic]

Interesting observation and I can reproduce the issue. Whats more interesting that by tweaking the join out of the subquery, I can fix the plan time performance regression:

explain analyse select id, date
from unnest(array['2024-10-15 00:10'::timestamp, '2024-10-15 00:20'::timestamp]) as test_dates
left join test_performance tp on  (tp.date = test_dates)
where id = 1 and date >= '2024-10-15 00:10' and date <= '2024-10-15 00:20';
                                                                                   QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..4.42 rows=2 width=12) (actual time=0.069..0.076 rows=2 loops=1)
   Join Filter: (tp.date = test_dates.test_dates)
   Rows Removed by Join Filter: 2
   ->  Function Scan on unnest test_dates  (cost=0.00..0.02 rows=2 width=8) (actual time=0.014..0.015 rows=2 loops=1)
   ->  Materialize  (cost=0.29..4.34 rows=2 width=12) (actual time=0.023..0.024 rows=2 loops=2)
         ->  Index Only Scan using _hyper_1_289_chunk_test_performance_idx on _hyper_1_289_chunk tp  (cost=0.29..4.33 rows=2 width=12) (actual time=0.042..0.043 rows=2 loops=1)
               Index Cond: ((id = 1) AND (date >= '2024-10-15 00:10:00'::timestamp without time zone) AND (date <= '2024-10-15 00:20:00'::timestamp without time zone))
               Heap Fetches: 0
 Planning Time: 0.966 ms
 Execution Time: 0.154 ms
(10 rows)

Time: 3.980 ms

This is on PG17. Still unsure if this a problem with Postgres or TSDB. WIll look at it some more.

[t-aistleitner]

Thanks for taking a look into this!

Interesting that removing the join from the subquery resolves the issue. As a workaround, we created a function that retrieves the data from the hypertable, stores the results in a custom type, and then joins this later with the array.

It seems to be related to TSDB, but I’m not entirely sure. In a quick test using a regular table, the planning time was similar between PostgreSQL 15 and >16. Of course, the execution time was longer without using a hypertable, but the planning behavior appeared consistent.

[injectionsuccesfully]

I have a similar problem, please help.