[Bug]: Batch sorted merge is chosen over vectorized aggregation, 6–18× slower, on the recommended segmentby layout

What type of bug is this?

Performance issue

What subsystems and features are affected?

Query planner, Query executor, Compression

What happened?

[Bug]: Batch sorted merge is chosen over vectorized aggregation, 6–18× slower, on the recommended segmentby layout

What type of bug is this?

Performance regression

What subsystems and features are affected?

Query planner / query executor, columnstore (enable_decompression_sorted_merge, VectorAgg)

What happened?

On a columnstore hypertable using the standard recommended layout (segmentby = 'device_id', orderby = 'ts DESC'), a plain hourly aggregate

SELECT time_bucket(INTERVAL '1 hour', ts), avg(value) FROM t GROUP BY 1;

is planned as a batch-sorted-merge GroupAggregate instead of VectorAgg + HashAggregate, and runs 6.7× slower on a 1M-row table and 17.9× slower on a 10M-row table.

The planner's own costs point the other way:

The vectorized path is both cheaper by the planner's own estimate and 6.7× faster, yet it is not selected while batch sorted merge is enabled.

The trigger is ANALYZE on the uncompressed hypertable — i.e. the state every real deployment is in, because autovacuum analyzes tables as they are loaded, before the compression policy runs. Without those statistics the fast plan is chosen and the bug is invisible. This makes it easy to miss in synthetic benchmarks that only analyze after compressing.

Contributing factor: time_bucket() group-count estimation

The planner estimates the number of groups produced by time_bucket(INTERVAL '1 hour', ts) as n_distinct(ts) — it does not account for time_bucket being a many-to-one mapping:

 attname | n_distinct      planner estimate: rows=98966
 ts      |      98966      actual group count:    240

(n_distinct is sometimes reported as the equivalent negative ratio, e.g. -0.0989.)

A ~410× group overestimate makes hash aggregation look expensive — every plan below carries Planned Partitions: 4, i.e. a predicted spill to disk that never happens (Batches: 1).

This is a contributing factor, not the discriminator. The same overestimate (~99,000 vs 240 actual) is present in all three cases below, including the two fast ones — so it cannot by itself explain the plan choice. What separates the cases is whether the batch-sorted-merge path is available:

Note case 3: the planner costs its chosen plan at 129,554 — five times case 2's estimate — and it runs in the same ~32 ms. Estimated cost and real runtime are poorly correlated across all three. The reliable observation is behavioural: whenever the batch-sorted-merge path is available it is chosen, and when it is chosen the query is 6–18× slower.

One caveat on the headline cost comparison: cases 1 and 2 come from different GUC settings, so their costs are not strictly comparable — disabling the GUC may change which ColumnarScan paths get generated, not merely which is picked.

How can we reproduce the bug?

Self-contained, ~15 s. Full script also attached as REPRO.sql.

DROP TABLE IF EXISTS bsm_repro;
CREATE TABLE bsm_repro(ts timestamptz NOT NULL, device_id int NOT NULL, value double precision NOT NULL);
SELECT create_hypertable('bsm_repro', 'ts', chunk_time_interval => INTERVAL '1 day');

-- 10 chunks x 10 devices x 10,000 rows/device/chunk = 1,000,000 rows
INSERT INTO bsm_repro
SELECT TIMESTAMPTZ '2026-01-01' + (d * INTERVAL '1 day') + (i * INTERVAL '8.64 seconds'),
       dev,
       50 + 20*sin(i/100.0) + (dev % 13)
FROM generate_series(0,9) d, generate_series(1,10) dev, generate_series(0,9999) i;

ANALYZE bsm_repro;   -- <<< REQUIRED. Stats on the uncompressed hypertable.
                     --     Remove this line and the bug disappears.

ALTER TABLE bsm_repro SET (timescaledb.enable_columnstore = true,
                           timescaledb.segmentby = 'device_id',
                           timescaledb.orderby   = 'ts DESC');
SELECT compress_chunk(c) FROM show_chunks('bsm_repro') c;
ANALYZE bsm_repro;

-- 1. as the planner chooses
EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY ON)
SELECT time_bucket(INTERVAL '1 hour', ts), avg(value) FROM bsm_repro GROUP BY 1;

-- 2. workaround
SET timescaledb.enable_decompression_sorted_merge = off;
EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY ON)
SELECT time_bucket(INTERVAL '1 hour', ts), avg(value) FROM bsm_repro GROUP BY 1;

Observed output

===== 1. AS THE PLANNER CHOOSES (batch sorted merge) =====
 Finalize GroupAggregate  (cost=63.64..46450.15 rows=100228) (actual rows=240)
   ->  Merge Append
         Sort Key: (time_bucket('01:00:00'::interval, bsm_repro.ts))
         ->  Partial GroupAggregate                      (actual rows=24)
               ->  Custom Scan (ColumnarScan)            (actual rows=100000)
                     ->  Sort  Sort Key: _ts_meta_v2_last_ts
                           ->  Seq Scan on ..._compressed (actual rows=100)
 Execution Time: 207.374 ms

===== 2. WORKAROUND: enable_decompression_sorted_merge = off =====
 Finalize HashAggregate  (cost=22407.65..25449.28 rows=98571)
   Planned Partitions: 4
   ->  Append
         ->  Custom Scan (VectorAgg)
               ->  Custom Scan (ColumnarScan)
                     ->  Seq Scan on ..._compressed
 Execution Time: 31.161 ms

===== 3. CONTROL: stats removed (ALTER COLUMN ts SET STATISTICS 0) =====
 Finalize HashAggregate  (cost=121704.46..129554.09 rows=98966)
 Execution Time: 32.149 ms

Timings are stable across repeated runs of the script: 207.4 / 31.2 / 31.8 ms and 207.8 / 31.3 / 32.1 ms on two consecutive executions.

Affected surface

Swept by total rows, segmentby cardinality, and chunk count. chosen is the planner's plan; merge off is the same query with enable_decompression_sorted_merge = off.

Two conditions must hold together:

1. ≳10,000 rows per device per chunk (≈10+ compressed batches per device per chunk). At 5,000 and below the fast plan is chosen.
2. segmentby cardinality roughly 2–10. At 1 device the sorted path is genuinely faster (0.4×) and correctly chosen — that is the case the optimization exists for. At ≥20 devices the planner avoids it correctly. The bug lives in the band between.

Also required: more than one chunk. A single-chunk hypertable never produced the regression in any configuration tested (no VectorAgg path is generated at all there).

Layout control. Recompressing the same repro table with full ts statistics but segmentby = '', orderby = 'device_id, ts DESC' keeps VectorAgg and runs in 33.0 ms versus 207.8 ms for segmentby = 'device_id' — identical data, identical statistics, only the columnstore layout differs. The same holds at 10M rows (~116 ms, with or without ANALYZE).

This matters because segmentby = '<id column>', orderby = '<time> DESC' is the layout the documentation recommends, so the affected configuration is the default advice rather than an unusual one.

Workaround

SET timescaledb.enable_decompression_sorted_merge = off;

Restores VectorAgg and full speed in every affected case tested. enable_vectorized_aggregation and enable_chunkwise_aggregation do not help (2041 ms and 2938 ms respectively — both leave the sorted-merge path in place).

TimescaleDB version affected

2.29.2

PostgreSQL version used

18.4 (Ubuntu 18.4-1.pgdg22.04+1), aarch64

What operating system did you use?

Ubuntu 22.04 aarch64 — Tiger Cloud managed service, 2 vCPU / 8 GB

What installation method did you use?

Tiger Cloud (managed)

Relevant log output and stack trace

Not applicable — no error, incorrect plan selection only.

Additional context

Not tested on PostgreSQL 16 or 17, nor on TimescaleDB versions before 2.29.2, so the introducing version is unknown. All measurements are best-of-3 after an untimed warm-up, max_parallel_workers_per_gather = 2, on an otherwise idle instance.

Found while benchmarking columnstore segmentby layouts across device cardinality; the sweep harness and raw results are available if useful.

TimescaleDB version affected

2.29.2

PostgreSQL version used

18.4

What operating system did you use?

Tiger Cloud

What installation method did you use?

Not applicable

What platform did you run on?

Timescale Cloud

Relevant log output and stack trace

How can we reproduce the bug?

# Reproducible SQL

-- Batch sorted merge chosen over vectorized aggregation -> 6x slowdown
-- Self-contained. ~15s on a 2 vCPU instance. TimescaleDB 2.29.2 / PostgreSQL 18.4.
--
-- The trigger is ANALYZE on the UNCOMPRESSED hypertable, which is what autovacuum
-- does in every real deployment. Without those stats the bug does not appear.

DROP TABLE IF EXISTS bsm_repro;
CREATE TABLE bsm_repro(ts timestamptz NOT NULL, device_id int NOT NULL, value double precision NOT NULL);
SELECT create_hypertable('bsm_repro', 'ts', chunk_time_interval => INTERVAL '1 day');

-- 10 chunks x 10 devices x 10,000 rows/device/chunk = 1,000,000 rows
INSERT INTO bsm_repro
SELECT TIMESTAMPTZ '2026-01-01' + (d * INTERVAL '1 day') + (i * INTERVAL '8.64 seconds'),
       dev,
       50 + 20*sin(i/100.0) + (dev % 13)
FROM generate_series(0,9) d, generate_series(1,10) dev, generate_series(0,9999) i;

ANALYZE bsm_repro;   -- <<< stats collected while uncompressed. Remove this line and the bug vanishes.

ALTER TABLE bsm_repro SET (timescaledb.enable_columnstore = true,
                           timescaledb.segmentby = 'device_id',
                           timescaledb.orderby   = 'ts DESC');
SELECT count(*) FROM (SELECT compress_chunk(c) FROM show_chunks('bsm_repro') c) x;
ANALYZE bsm_repro;

-- The bad estimate that drives everything: planner thinks time_bucket() yields as many
-- groups as there are distinct timestamps. Actual group count is 240 (10 days x 24h).
SELECT attname, n_distinct FROM pg_stats
WHERE schemaname='public' AND tablename='bsm_repro' AND attname='ts';

\echo '===== 1. AS THE PLANNER CHOOSES (batch sorted merge) ====='
EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY ON)
SELECT time_bucket(INTERVAL '1 hour', ts), avg(value) FROM bsm_repro GROUP BY 1;

\echo '===== 2. WORKAROUND: disable batch sorted merge ====='
SET timescaledb.enable_decompression_sorted_merge = off;
EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY ON)
SELECT time_bucket(INTERVAL '1 hour', ts), avg(value) FROM bsm_repro GROUP BY 1;
RESET timescaledb.enable_decompression_sorted_merge;

\echo '===== 3. CONTROL: same layout, stats dropped -> planner picks the fast plan ====='
SELECT count(*) FROM (SELECT decompress_chunk(c) FROM show_chunks('bsm_repro') c) x;
ALTER TABLE bsm_repro ALTER COLUMN ts SET STATISTICS 0;
ANALYZE bsm_repro;
SELECT count(*) FROM (SELECT compress_chunk(c) FROM show_chunks('bsm_repro') c) x;
EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY ON)
SELECT time_bucket(INTERVAL '1 hour', ts), avg(value) FROM bsm_repro GROUP BY 1;