[Bug]: Catastrophic planning times for Realtime Continuous Aggregates (issues with _timescaledb_functions.cagg_watermark)

[martinhale]

What type of bug is this?

Performance issue

What subsystems and features are affected?

Continuous aggregate, Query planner

What happened?

We're facing incredibly slow planning times when querying realtime continuous aggregates over large periods of time.

---

Here’s an example query and EXPLAIN against a CAgg:

EXPLAIN ANALYZE SELECT * FROM crossings_v4_one_hour_v2
WHERE image_space_countline_id IN (15000,14716,14717,14998)
AND bucket > '2023-01-01 00:00:00'
AND bucket < '2023-11-13 23:59:59';

And the same query / EXPLAIN against the underlying hypertable:

EXPLAIN ANALYZE SELECT * FROM _timescaledb_internal._materialized_hypertable_166
WHERE image_space_countline_id IN (15000,14716,14717,14998)
AND bucket > '2023-01-01 00:00:00'
AND bucket < '2023-11-13 23:59:59';

The execution times are slightly different (which might be expected as these are one-off queries etc), but the planning differences are vast (and repeatable). 28,101 ms vs 27 ms (1000x slower!) Note - in the above example the cagg_watermark is later than 2023-11-13 23:59:59 so the result sets are identical.

---

To dig a little deeper, I recreated the realtime functionality with a UNION between the underlying CAgg hypertable and the raw hypertable myself, like this:

SELECT
	*
FROM _timescaledb_internal._materialized_hypertable_166
WHERE image_space_countline_id IN (15000,14716,14717,14998)
AND bucket > $1
AND bucket < $2
AND bucket < _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(166))
UNION
SELECT
	time_bucket('1 hour', crossing_time) AS bucket,
	image_space_countline_id, 
	detection_class_v2_id,
	COUNT(*) FILTER (WHERE clockwise) clockwise_count,
	COUNT(*) FILTER (WHERE NOT clockwise) anticlockwise_count
FROM crossings_v4
WHERE image_space_countline_id IN (15000,14716,14717,14998)
AND crossing_time > _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(166))
AND crossing_time > $1
AND crossing_time < $2
GROUP BY bucket, image_space_countline_id, detection_class_v2_id;

This also has the terrible planning performance.

However, if I replace the _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(166)) call with its result (acquired in a previous statement), then the query absolutely flies:

SELECT
	*
FROM _timescaledb_internal._materialized_hypertable_166
WHERE image_space_countline_id IN (15000,14716,14717,14998)
AND bucket > $1
AND bucket < $2
AND bucket < '2023-11-16 14:00:00+00'
UNION
SELECT
	time_bucket('1 hour', crossing_time) AS bucket,
	image_space_countline_id, 
	detection_class_v2_id,
	COUNT(*) FILTER (WHERE clockwise) clockwise_count,
	COUNT(*) FILTER (WHERE NOT clockwise) anticlockwise_count
FROM crossings_v4
WHERE image_space_countline_id IN (15000,14716,14717,14998)
AND crossing_time > '2023-11-16 14:00:00+00'
AND crossing_time > $1
AND crossing_time < $2
GROUP BY bucket, image_space_countline_id, detection_class_v2_id;

---

So, it seems like the calls to the watermark function are not being constified (or something along those lines).

Are we doing something wrong? Is there something we can do to improve this performance beyond abandoning realtime aggregates / rolling our own?

Many thanks!

---

Slack thread with similar info.

TimescaleDB version affected

2.12.1

PostgreSQL version used

13.13

What operating system did you use?

Managed Service for Timescale (Aiven)

What installation method did you use?

Not applicable

What platform did you run on?

Managed Service for TimescaleDB (MST/Aiven)

Relevant log output and stack trace

No response

How can we reproduce the bug?

I think you'd need a very sizeable hypertable (both temporarily and raw size), create a real time continuous aggregate, and then query.

It's possible that there may be something specific about our data or the shape of our aggregate in which case I'd be happy to share more specifics (although it feels like we've got a very vanilla use case here).

[martinhale]

A few more bits of info that might be useful:

• the above is taken from our production environment. The same issue is present in our staging environment
• the issue first became apparent when running older versions of Timescale (2.11.x), and has persisted following an update to 2.12.2
• our staging environment is running PG14 vs PG13 in production, so that doesn't seem to be having an impact
• we've tried running a manual VACUUM ANALYZE on the underlying hypertable on the off-chance that could help change the planning behaviour, but no luck

[jnidzwetzki]

Hello @martinhale,

Thanks for reaching out and making us aware of these high planning times. I was able to reproduce the problem in my local environment using TimescaleDB 2.13.0-dev and PostgreSQL 15. For reproduction, I used a hypertable with 8000 chunks:

CREATE TABLE ht_chunk_8k(time timestamptz, device int, value float);
SELECT create_hypertable('ht_chunk_8k','time',chunk_time_interval:='1d'::interval);
INSERT INTO ht_chunk_8k SELECT '1900-01-01'::timestamptz + (interval '1 day' * i), 1, 0.5 FROM generate_series(1,8000) g(i);

CREATE MATERIALIZED VIEW continuous_aggregate_hourly
WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', time) AS bucket, device, avg(value) FROM ht_chunk_8k GROUP BY bucket, device;

ALTER MATERIALIZED VIEW continuous_aggregate_hourly set (timescaledb.materialized_only = false);

CALL refresh_continuous_aggregate('continuous_aggregate_hourly', '1900-01-01', '2021-06-01');

After activating the real-time functionality of the continuous aggregate, I also observed high planning times when the continuous aggregate is accessed.

[jnidzwetzki]

#6105 seems to be related

[martinhale]

Hey @jnidzwetzki - thanks so much for reproducing.

#6105 seems to be related

Yeah, I thought so too, although I wanted to ensure cagg_watermark was explicitly called out, as I believe now() has special optimisations.

[alex-tate]

It will be interesting to see if this issue can be traced to a change made in a recent Timescale update or whether it was present all along. I'm sure I've seen a performance reduction in certain Grafana visualisations targetting real-time CAGGs but it wasn't so bad that it made me look into it further. I replicated the queries at the top of the issue on a number of our CAGGs and saw planning time increases of around x20. This is nowhere near the x1000 example given but our CAGGs have far fewer chunks.

[martinhale]

Hey @jnidzwetzki!

I noticed that you've got a PR open that addresses this issue (with some 🌶️ benchmark number improvements - congratulations!)

Any idea when we might be able to expect the fix to be merged and hit a release?

Many thanks!

[jnidzwetzki]

Hello @martinhale,

The PR is currently in an early prototype state. But it shows very promising numbers. We are currently testing the correctness of the change.

At the moment, I cannot say anything definite about merging these changes. However, if the test goes smoothly, we will soon work on a production-ready version of these changes.

[martinhale]

@jnidzwetzki @fabriziomello - I don't suppose you have any further updates on this at the moment? It's causing us quite a lot of issues in our production environments and we're trying to decide how much effort we should put into mitigation ourselves vs waiting for the fix. Thanks! 🙂

[jnidzwetzki]

Hello @martinhale,

The PR is currently under review and we plan to include this improvement in TimescaleDB 2.14.0.

[martinhale]

Amazing, thanks so much for the update!

And thanks for the huge effort involved in implementing the fix - I appreciate this hasn't been a trivial thing to resolve!