[Bug]: Very high planning time for queries on real-time continous aggregates with many chunks

[raymalt]

What type of bug is this?

Performance issue

What subsystems and features are affected?

Query planner

What happened?

A simple query on a continuous aggregate extracting the last timestamp takes unexpectedly long time due to the time spent planning the query. It seems to be very dependent on the number of chunks in the raw table. Setting materialized_only = true however gives a super-fast response.

It is expected to take more time on a real-time CAGG than if only working on the materialized view, but not to spend this much time query planning - especially considering that the query filters out all data in the WHERE clause.

Query in reproduction steps below (Table creation courtesy of @jnidzwetzki) ;)

TimescaleDB version affected

2.11.2

PostgreSQL version used

15.4

What operating system did you use?

Timescale Cloud

What installation method did you use?

Not applicable

What platform did you run on?

Timescale Cloud

Relevant log output and stack trace

# Output from `EXPLAIN (ANALYZE, VERBOSE, BUFFERS)`

## With `materialized_only = false`


Limit  (cost=2977.05..2977.05 rows=1 width=8) (actual time=0.239..0.241 rows=0 loops=1)
  ->  Sort  (cost=2977.05..2978.01 rows=386 width=8) (actual time=0.237..0.240 rows=0 loops=1)
        Sort Key: "*SELECT* 1"."time" DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Append  (cost=0.16..2975.12 rows=386 width=8) (actual time=0.233..0.235 rows=0 loops=1)
              ->  Subquery Scan on "*SELECT* 1"  (cost=0.16..424.35 rows=177 width=8) (actual time=0.019..0.020 rows=0 loops=1)
                    ->  Result  (cost=0.16..422.58 rows=177 width=28) (actual time=0.018..0.019 rows=0 loops=1)
                          ->  Custom Scan (ChunkAppend) on _materialized_hypertable_236  (cost=0.16..420.81 rows=177 width=12) (actual time=0.017..0.018 rows=0 loops=1)
                                Chunks excluded during startup: 176
                                ->  Index Scan using _hyper_236_27119_chunk__materialized_hypertable_236_time_idx on _hyper_236_27119_chunk  (cost=0.15..2.37 rows=1 width=12) (actual time=0.016..0.016 rows=0 loops=1)
                                      Index Cond: (("time" < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(236)), '-infinity'::timestamp with time zone)) AND ("time" > now()))
              ->  Subquery Scan on "*SELECT* 2"  (cost=2544.14..2548.84 rows=209 width=8) (actual time=0.213..0.214 rows=0 loops=1)
                    ->  HashAggregate  (cost=2544.14..2546.75 rows=209 width=28) (actual time=0.213..0.213 rows=0 loops=1)
                          Group Key: time_bucket('7 days'::interval, sensor_data."time"), sensor_data.sensor_id
                          Batches: 1  Memory Usage: 40kB
                          ->  Result  (cost=0.15..2533.68 rows=2091 width=12) (actual time=0.210..0.211 rows=0 loops=1)
                                ->  Custom Scan (ChunkAppend) on sensor_data  (cost=0.15..2507.54 rows=2091 width=12) (actual time=0.210..0.210 rows=0 loops=1)
                                      Chunks excluded during startup: 1758
                                      ->  Index Scan using _hyper_235_26941_chunk_sensor_data_time_idx on _hyper_235_26941_chunk  (cost=0.28..14.18 rows=100 width=12) (actual time=0.081..0.081 rows=0 loops=1)
                                            Index Cond: ("time" >= COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(236)), '-infinity'::timestamp with time zone))
                                            Filter: (time_bucket('7 days'::interval, "time") > now())
                                            Rows Removed by Filter: 300
                                      ->  Seq Scan on _hyper_235_26942_chunk  (cost=0.00..23.50 rows=233 width=12) (actual time=0.128..0.129 rows=0 loops=1)
                                            Filter: (("time" >= COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(236)), '-infinity'::timestamp with time zone)) AND (time_bucket('7 days'::interval, "time") > now()))
                                            Rows Removed by Filter: 700
Planning Time: 465.181 ms
Execution Time: 26.968 ms

With materialized_only = true

Limit  (cost=0.14..1.27 rows=1 width=8) (actual time=0.010..0.011 rows=0 loops=1)
  ->  Custom Scan (ChunkAppend) on _materialized_hypertable_236  (cost=0.14..1.27 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1)
        Order: _materialized_hypertable_236."time" DESC
        Chunks excluded during startup: 0
        ->  Index Only Scan using _hyper_236_27119_chunk__materialized_hypertable_236_time_idx on _hyper_236_27119_chunk  (cost=0.14..1.27 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1)
              Index Cond: ("time" > now())
              Heap Fetches: 0
Planning Time: 0.778 ms
Execution Time: 0.104 ms

### How can we reproduce the bug?

```bash
CREATE TABLE sensor_data(
time timestamp with time zone NOT NULL,
sensor_id integer NOT NULL,
cpu double precision,
temperature double precision);

SELECT FROM create_hypertable(relation=>'sensor_data', time_column_name=> 'time');

INSERT INTO sensor_data
SELECT time + (INTERVAL '1 minute' * random()) AS time,
sensor_id, 
random() AS cpu, 
random()* 100 AS temperature
FROM
generate_series('1990-01-01'::date, '2023-09-21', INTERVAL '1 day') AS g1(time),
generate_series(1, 100, 1 ) AS g2(sensor_id)
ORDER BY time;

create materialized view cagg
with (timescaledb.continuous) as
select
	time_bucket(interval '1 week',
	time) as time,
	sensor_id,
	max(cpu) max_cpu,
	max(temperature) max_temp
from
	sensor_data
group by
	1, 2
with no data;

select add_continuous_aggregate_policy('cagg', 
                               start_offset => NULL, 
                               end_offset => '1 week', 
                               schedule_interval => INTERVAL '1 week');
         

explain analyze
select time 
from cagg
where time > now()
order by time desc
limit 1;
                             
alter materialized view cagg set (timescaledb.materialized_only = true);

explain analyze
select time 
from cagg
where time > now()
order by time desc
limit 1;
                             

[jnidzwetzki]

Hello @raymalt,

Thanks for the detailed steps to reproduce the issue. I was able to reproduce it locally using TimescaleDB 2.12.0-dev.

The hypertable sensor_data consists of ~1750 chunks. When the real-time aggregation is turned on (timescaledb.materialized_only = false), the CAGG has to determine which chunks contain the real-time data. Although most chunks are excluded (see Chunks excluded during startup: 1758 in the query plan), this check takes some time, which leads to an increased planning time.

Flamegraphs

materialized_only = true

materialized_only = false

Diff

[raymalt]

Hi @jnidzwetzki,

Thanks for your response. It seems like the WHERE-clause is not propagated when excluding chunks in the raw table. For example, these queries on the raw table have about the same planning times as for the queries on the CAGGs above.

Query 1, time > now() - interval '1w', planning time: 0.6 ms

select time 
from sensor_data
where time > now() - interval '1w'
order by time desc
limit 1;

Query 2, time > now() - interval '40y', planning time: 541 ms

select time 
from sensor_data
where time > now() - interval '40y'
order by time desc
limit 1;

(Same result if removing WHERE clause altogether.)

[raymalt]

Hello,

I just wanted to add that this problem is very dependent on the use of the now() function. Replacing now() with a fixed timestamp leads to very fast planning times, e.g.

select time 
from cagg
where time > '2023-10-10'
order by time desc
limit 1;```