Continuous Aggregates Impact of ORDER BY

aglover · February 13, 2023, 12:52pm

Hi,

I note that CAGGs post v2.7 supports ORDER BY keyword.

Can someone please explain the impact of this? I had assumed that CAGGs would be ordered the same as the HyperTable - primarily by the timestamp column.

If an ORDER BY is not defined in a CAGG, in what order will data be materialized in the CAGG?

Thanks in Advance

jonatasdp · February 13, 2023, 7:33pm

Welcome @aglover ! It impacts working with order by clauses in them, this means things like:

SELECT percentile_cont(price)
 WITHIN GROUP (ORDER BY time)

or

SELECT array_agg(foo ORDER BY time)

If you want to ORDER BY time in your final data, you can use SELECT from the continuous aggregate though: ie

SELECT * FROM mat_view_stocks_real_time
ORDER BY time_bucket

and that should work just fine.

jonatasdp · February 15, 2023, 2:29pm

Hi @aglover, we also have this related PR which brings more details about this:

github.com/timescale/timescaledb

[Enhancement]: Enable ORDER BY clause in Continuous Aggregate definition

opened 06:23PM - 17 Jun 22 UTC

closed 06:52PM - 31 Jul 22 UTC

fabriziomello

ui/ux continuous_aggregate core enhancement ddl

### What type of enhancement is this? Performance, User experience ### Wha…t subsystems and features will be improved? Continuous aggregate ### What does the enhancement do? ## Background Users often execute TopN like queries over Continuous Aggregates and now with the release 2.7 such queries are even faster because we remove the re-aggregation and don't store partials anymore and it give us the ability to create indexes on aggregated columns for performance improvements. But there are a considerable performance difference between `Materialized-Only` (really faster) and `Realt-Time` Continuous Aggregates. Have a look in the following example: ### Example Setup ```sql CREATE EXTENSION timescaledb; CREATE TABLE conditions ( time TIMESTAMPTZ NOT NULL, device_id INTEGER, temperature NUMERIC ); CREATE INDEX conditions_temperature ON conditions(temperature); SELECT create_hypertable('conditions', 'time'); INSERT INTO conditions SELECT generate_series(now() - interval '1 year', now(), '10 seconds'), (random()*4)+1, random()*100; CREATE MATERIALIZED VIEW conditions_summary_by_5minutes WITH (timescaledb.continuous, timescaledb.materialized_only=true) AS SELECT time_bucket(INTERVAL '5 minutes', time) AS bucket, device_id, MIN(temperature), MAX(temperature), AVG(temperature), SUM(temperature) FROM conditions GROUP BY bucket, device_id; CREATE INDEX conditions_summary_by_5minutes_sum_desc ON conditions_summary_by_5minutes (sum DESC); VACUUM (ANALYZE); -- make sure all statistics are updated ``` ### Tests 1. Materialized-Only Continuous Aggregate: ```sql fabrizio=# EXPLAIN (ANALYZE) SELECT * FROM conditions_summary_by_5minutes ORDER BY sum DESC LIMIT 5; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=2.46..2.92 rows=5 width=132) (actual time=0.117..0.128 rows=5 loops=1) -> Merge Append (cost=2.46..49011.50 rows=521827 width=132) (actual time=0.115..0.126 rows=5 loops=1) Sort Key: _hyper_2_54_chunk.sum DESC -> Index Scan using _hyper_2_54_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_54_chunk (cost=0.29..3712.82 rows=48836 width=60) (actual time=0.022..0.022 rows=1 loops=1) -> Index Scan using _hyper_2_55_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_55_chunk (cost=0.42..5526.02 rows=72640 width=140) (actual time=0.017..0.017 rows=1 loops=1) -> Index Scan using _hyper_2_56_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_56_chunk (cost=0.42..7605.72 rows=100087 width=140) (actual time=0.018..0.018 rows=1 loops=1) -> Index Scan using _hyper_2_57_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_57_chunk (cost=0.42..7606.08 rows=100111 width=140) (actual time=0.019..0.019 rows=1 loops=1) -> Index Scan using _hyper_2_58_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_58_chunk (cost=0.42..7605.86 rows=100096 width=140) (actual time=0.019..0.022 rows=3 loops=1) -> Index Scan using _hyper_2_59_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_59_chunk (cost=0.42..7601.27 rows=100057 width=140) (actual time=0.016..0.019 rows=3 loops=1) Planning Time: 0.609 ms Execution Time: 0.177 ms (11 rows) Time: 1.854 ms ``` 2. Change the Continous Aggregate to Real-Time: ```sql fabrizio=# ALTER MATERIALIZED VIEW conditions_summary_by_5minutes SET (timescaledb.materialized_only=false); ``` 3. Real-Time Continuous Aggregate: ```sql fabrizio=# EXPLAIN (ANALYZE) SELECT * FROM conditions_summary_by_5minutes ORDER BY sum DESC LIMIT 5; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=31792.34..31792.35 rows=5 width=60) (actual time=370.166..370.171 rows=5 loops=1) -> Sort (cost=31792.34..33097.04 rows=521880 width=60) (actual time=370.164..370.169 rows=5 loops=1) Sort Key: _materialized_hypertable_2.sum DESC Sort Method: top-N heapsort Memory: 26kB -> Append (cost=0.00..23124.10 rows=521880 width=60) (actual time=0.008..180.228 rows=521827 loops=1) -> Custom Scan (ChunkAppend) on _materialized_hypertable_2 (cost=0.00..15063.97 rows=521827 width=60) (actual time=0.008..146.200 rows=521827 loops=1) Chunks excluded during startup: 0 -> Seq Scan on _hyper_2_54_chunk (cost=0.00..1409.63 rows=48836 width=60) (actual time=0.007..10.602 rows=48836 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Seq Scan on _hyper_2_55_chunk (cost=0.00..2097.20 rows=72640 width=60) (actual time=0.008..15.943 rows=72640 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Seq Scan on _hyper_2_56_chunk (cost=0.00..2889.52 rows=100087 width=60) (actual time=0.009..21.098 rows=100087 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Seq Scan on _hyper_2_57_chunk (cost=0.00..2889.94 rows=100111 width=60) (actual time=0.007..21.218 rows=100111 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Seq Scan on _hyper_2_58_chunk (cost=0.00..2889.68 rows=100096 width=60) (actual time=0.009..21.244 rows=100096 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Seq Scan on _hyper_2_59_chunk (cost=0.00..2888.00 rows=100057 width=60) (actual time=0.009..21.158 rows=100057 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> GroupAggregate (cost=230.21..231.93 rows=53 width=140) (actual time=0.036..0.037 rows=0 loops=1) Group Key: (time_bucket('00:05:00'::interval, conditions."time")), conditions.device_id -> Sort (cost=230.21..230.34 rows=53 width=24) (actual time=0.034..0.034 rows=0 loops=1) Sort Key: (time_bucket('00:05:00'::interval, conditions."time")), conditions.device_id Sort Method: quicksort Memory: 25kB -> Custom Scan (ChunkAppend) on conditions (cost=0.29..228.69 rows=53 width=24) (actual time=0.021..0.022 rows=0 loops=1) Chunks excluded during startup: 52 -> Index Scan using _hyper_1_53_chunk_conditions_time_idx on _hyper_1_53_chunk (cost=0.29..4.31 rows=1 width=24) (actual time=0.020..0.021 rows=0 loops=1) Index Cond: ("time" >= COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) Planning Time: 7.103 ms Execution Time: 371.202 ms (30 rows) Time: 380.380 ms ``` ## Proposal Enable `ORDER BY` clause in the Continuous Aggregate definition in order to: 1. improve user experience by removing the restriction of use `ORDER BY` clause in the Continuous Aggregate definition 2. improve performance for TopN queries on Real-time Continuous Aggregates because producing ordered datasets we can take advantage of the planner MergeAppend node. Let's have a look into the Continuous Aggregate definition of the example above: ```sql fabrizio=# \d+ conditions_summary_by_5minutes View "public.conditions_summary_by_5minutes" Column | Type | Collation | Nullable | Default | Storage | Description -----------+--------------------------+-----------+----------+---------+---------+------------- bucket | timestamp with time zone | | | | plain | device_id | integer | | | | plain | min | numeric | | | | main | max | numeric | | | | main | avg | numeric | | | | main | sum | numeric | | | | main | View definition: SELECT _materialized_hypertable_2.bucket, _materialized_hypertable_2.device_id, _materialized_hypertable_2.min, _materialized_hypertable_2.max, _materialized_hypertable_2.avg, _materialized_hypertable_2.sum FROM _timescaledb_internal._materialized_hypertable_2 WHERE _materialized_hypertable_2.bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone) UNION ALL SELECT time_bucket('00:05:00'::interval, conditions."time") AS bucket, conditions.device_id, min(conditions.temperature) AS min, max(conditions.temperature) AS max, avg(conditions.temperature) AS avg, sum(conditions.temperature) AS sum FROM conditions WHERE conditions."time" >= COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone) GROUP BY (time_bucket('00:05:00'::interval, conditions."time")), conditions.device_id; ``` Enabling an `ORDER BY` clause in the Continuous Aggregate definition we can produce a view definition as the following example and get benefit of performance improvement my having a chance to the planner use MergeAppend node because we're producing ordered datasets in each side of the `UNION ALL` set operation. 1. Continuous Aggregate definition using `ORDER BY` ```sql CREATE MATERIALIZED VIEW conditions_summary_by_5minutes WITH (timescaledb.continuous, timescaledb.materialized_only=true) AS SELECT time_bucket(INTERVAL '5 minutes', time) AS bucket, device_id, MIN(temperature), MAX(temperature), AVG(temperature), SUM(temperature) FROM conditions GROUP BY bucket, device_id ORDER BY SUM(temperature) DESC; ``` 2. Proposed view definition for the Continuous Aggregate using `ORDER BY` clause 2.1 Real-Time Continuous Aggregate user view definition ```sql ( SELECT _materialized_hypertable_2.bucket, _materialized_hypertable_2.device_id, _materialized_hypertable_2.min, _materialized_hypertable_2.max, _materialized_hypertable_2.avg, _materialized_hypertable_2.sum FROM _timescaledb_internal._materialized_hypertable_2 WHERE _materialized_hypertable_2.bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(3)), '-infinity'::timestamp with time zone) ORDER BY _materialized_hypertable_2.sum DESC) UNION ALL ( SELECT time_bucket('00:05:00'::interval, conditions."time") AS bucket, conditions.device_id, min(conditions.temperature) AS min, max(conditions.temperature) AS max, avg(conditions.temperature) AS avg, sum(conditions.temperature) AS sum FROM conditions WHERE conditions."time" >= COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(3)), '-infinity'::timestamp with time zone) GROUP BY (time_bucket('00:05:00'::interval, conditions."time")), conditions.device_id ORDER BY (sum(conditions.temperature)) DESC) ORDER BY 6 DESC; ``` 2.2 Materialized-Only Continuous Aggregate user view definition ```sql SELECT _materialized_hypertable_2.bucket, _materialized_hypertable_2.device_id, _materialized_hypertable_2.min, _materialized_hypertable_2.max, _materialized_hypertable_2.avg, _materialized_hypertable_2.sum FROM _timescaledb_internal._materialized_hypertable_2 ORDER BY _materialized_hypertable_2.sum DESC; ``` Simulation of a query for Real-Time Continuous Aggregate with `ORDER BY` enabled: ```sql fabrizio=# EXPLAIN (ANALYZE) (SELECT _materialized_hypertable_2.bucket, _materialized_hypertable_2.device_id, _materialized_hypertable_2.min, _materialized_hypertable_2.max, _materialized_hypertable_2.avg, _materialized_hypertable_2.sum FROM _timescaledb_internal._materialized_hypertable_2 WHERE _materialized_hypertable_2.bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone) ORDER BY _materialized_hypertable_2.sum DESC) UNION ALL (SELECT time_bucket('00:05:00'::interval, conditions."time") AS bucket, conditions.device_id, min(conditions.temperature) AS min, max(conditions.temperature) AS max, avg(conditions.temperature) AS avg, sum(conditions.temperature) AS sum FROM conditions WHERE conditions."time" >= COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone) GROUP BY (time_bucket('00:05:00'::interval, conditions."time")), conditions.device_id ORDER BY sum(conditions.temperature) DESC) ORDER BY 6 DESC LIMIT 5; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=235.91..236.52 rows=5 width=60) (actual time=0.085..0.100 rows=5 loops=1) -> Merge Append (cost=235.91..63589.20 rows=521832 width=60) (actual time=0.085..0.098 rows=5 loops=1) Sort Key: _materialized_hypertable_2.sum DESC -> Custom Scan (ConstraintAwareAppend) (cost=2.46..52918.97 rows=521779 width=60) (actual time=0.070..0.083 rows=5 loops=1) Hypertable: _materialized_hypertable_2 Chunks excluded during startup: 0 -> Merge Append (cost=2.46..52918.97 rows=521779 width=60) (actual time=0.070..0.081 rows=5 loops=1) Sort Key: _hyper_2_54_chunk.sum DESC -> Index Scan using _hyper_2_54_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_54_chunk (cost=0.29..4074.42 rows=48807 width=60) (actual time=0.014..0.014 rows=1 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Index Scan using _hyper_2_55_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_55_chunk (cost=0.42..8356.71 rows=100102 width=60) (actual time=0.011..0.011 rows=1 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Index Scan using _hyper_2_56_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_56_chunk (cost=0.42..8351.01 rows=100031 width=60) (actual time=0.010..0.015 rows=4 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Index Scan using _hyper_2_57_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_57_chunk (cost=0.42..8351.57 rows=100056 width=60) (actual time=0.010..0.012 rows=2 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Index Scan using _hyper_2_58_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_58_chunk (cost=0.42..8355.97 rows=100069 width=60) (actual time=0.010..0.010 rows=1 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Index Scan using _hyper_2_59_chunk_conditions_summary_by_5minutes_sum_desc on _hyper_2_59_chunk (cost=0.42..6076.42 rows=72714 width=60) (actual time=0.010..0.010 rows=1 loops=1) Filter: (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) -> Sort (cost=233.45..233.58 rows=53 width=140) (actual time=0.014..0.014 rows=0 loops=1) Sort Key: (sum(conditions.temperature)) DESC Sort Method: quicksort Memory: 25kB -> GroupAggregate (cost=230.21..231.93 rows=53 width=140) (actual time=0.011..0.011 rows=0 loops=1) Group Key: (time_bucket('00:05:00'::interval, conditions."time")), conditions.device_id -> Sort (cost=230.21..230.34 rows=53 width=24) (actual time=0.010..0.011 rows=0 loops=1) Sort Key: (time_bucket('00:05:00'::interval, conditions."time")), conditions.device_id Sort Method: quicksort Memory: 25kB -> Custom Scan (ChunkAppend) on conditions (cost=0.29..228.69 rows=53 width=24) (actual time=0.007..0.008 rows=0 loops=1) Chunks excluded during startup: 52 -> Index Scan using _hyper_1_53_chunk_conditions_time_idx on _hyper_1_53_chunk (cost=0.29..4.31 rows=1 width=24) (actual time=0.007..0.007 rows=0 loops=1) Index Cond: ("time" >= COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) Planning Time: 7.227 ms Execution Time: 1.160 ms (34 rows) Time: 10.398 ms ``` ## Summary Enabling `ORDER BY` in Continuous Aggregates besides improve the UX also can improve a lot the performance for simple and frequented used TopN queries. | | Current | Proposal | Improvement | |-------------------|------------|-----------|-------------| | Materialized-Only | 1.854 ms | 1.854 ms | - | | Real-Time | 380.380 ms | 10.398 ms | ~38x |