[Bug]: Slow performance on hypertable when query hits two or more chunks

[tulupov]

What type of bug is this?

Performance issue

What subsystems and features are affected?

Query executor

What happened?

Here is the financial data table structure

      Column        |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
---------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 candle_base_id      | smallint                 |           | not null |         | plain    |             |              | 
 candle_quote_id     | smallint                 |           | not null |         | plain    |             |              | 
 candle_start_time   | timestamp with time zone |           | not null |         | plain    |             |              | 
 candle_open_price   | numeric                  |           | not null | 0       | main     |             |              | 
 candle_close_price  | numeric                  |           | not null | 0       | main     |             |              | 
 candle_low_price    | numeric                  |           | not null | 0       | main     |             |              | 
 candle_high_price   | numeric                  |           | not null | 0       | main     |             |              | 
 candle_volume       | numeric                  |           | not null | 0       | main     |             |              | 
 created_at          | timestamp with time zone |           |          | now()   | plain    |             |              | 
Indexes:
    "candles_market_spot_0_1m_pkey" PRIMARY KEY, btree (candle_base_id, candle_quote_id, candle_start_time)
    "candles_market_spot_0_1m_time_index" btree (candle_start_time DESC)
Triggers:
    ts_insert_blocker BEFORE INSERT ON candles_market_spot_0_1m FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.insert_blocker()
Child tables: _timescaledb_internal._hyper_61437_24243_chunk,
              _timescaledb_internal._hyper_61437_24244_chunk,
              _timescaledb_internal._hyper_61437_24245_chunk,
              _timescaledb_internal._hyper_61437_24246_chunk,
              _timescaledb_internal._hyper_61437_24247_chunk,
              _timescaledb_internal._hyper_61437_24248_chunk,
              _timescaledb_internal._hyper_61437_24249_chunk,
              ........

Table is not compressed
Chunk duration - 10 days

If aggregation query hits two or more chunks query very slow

explain (analyze,buffers) select candle_base_id, candle_quote_id, candle_start_time, sum(candle_volume) 
from candles_market_spot_0_1m 
where candle_start_time > '2025-01-01' and candle_start_time < '2025-01-03' 
group by candle_start_time, candle_base_id, candle_quote_id 
order by candle_start_time desc limit 100;

Limit  (cost=40962.34..40962.59 rows=100 width=44) (actual time=2199.184..2199.199 rows=100 loops=1)
   Buffers: shared hit=14680
   ->  Sort  (cost=40962.34..41123.52 rows=64472 width=44) (actual time=2199.182..2199.190 rows=100 loops=1)
         Sort Key: candles_market_spot_0_1m.candle_start_time DESC
         Sort Method: top-N heapsort  Memory: 36kB
         Buffers: shared hit=14680
         ->  Finalize HashAggregate  (cost=37692.36..38498.26 rows=64472 width=44) (actual time=1879.421..2110.991 rows=630501 loops=1)
               Group Key: candles_market_spot_0_1m.candle_start_time, candles_market_spot_0_1m.candle_base_id, candles_market_spot_0_1m.candle_quote_id
               Batches: 1  Memory Usage: 253969kB
               Buffers: shared hit=14680
               ->  Custom Scan (ChunkAppend) on candles_market_spot_0_1m  (cost=0.43..36080.56 rows=128944 width=44) (actual time=517.038..1192.325 rows=630501 loops=1)
                     Order: candles_market_spot_0_1m.candle_start_time DESC
                     Buffers: shared hit=14680
                     ->  Sort  (cost=0.00..0.00 rows=0 width=0) (actual time=517.036..576.503 rows=315360 loops=1)
                           Sort Key: _hyper_61437_24309_chunk.candle_start_time DESC
                           Sort Method: quicksort  Memory: 44529kB
                           Buffers: shared hit=7290
                           ->  Partial HashAggregate  (cost=19709.85..20515.75 rows=64472 width=44) (actual time=301.116..424.205 rows=315360 loops=1)
                                 Group Key: _hyper_61437_24309_chunk.candle_start_time, _hyper_61437_24309_chunk.candle_base_id, _hyper_61437_24309_chunk.candle_quote_id
                                 Batches: 1  Memory Usage: 126993kB
                                 Buffers: shared hit=7290
                                 ->  Index Scan using _hyper_61437_24309_chunk_candles_market_spot_0_1m_time_index on _hyper_61437_24309_chunk  (cost=0.43..16462.53 rows=324732 width=15) (actual time=0.036..77.036 rows=315360 loops=1)
                                       Index Cond: ((candle_start_time > '2025-01-01 00:00:00+00'::timestamp with time zone) AND (candle_start_time < '2025-01-03 00:00:00+00'::timestamp with time zone))
                                       Buffers: shared hit=7290
                     ->  Sort  (cost=0.00..0.00 rows=0 width=0) (actual time=510.777..568.151 rows=315141 loops=1)
                           Sort Key: _hyper_61437_24311_chunk.candle_start_time DESC
                           Sort Method: quicksort  Memory: 44462kB
                           Buffers: shared hit=7390
                           ->  Partial HashAggregate  (cost=14758.92..15564.82 rows=64472 width=44) (actual time=294.056..419.480 rows=315141 loops=1)
                                 Group Key: _hyper_61437_24311_chunk.candle_start_time, _hyper_61437_24311_chunk.candle_base_id, _hyper_61437_24311_chunk.candle_quote_id
                                 Batches: 1  Memory Usage: 126993kB
                                 Buffers: shared hit=7390
                                 ->  Index Scan using _hyper_61437_24311_chunk_candles_market_spot_0_1m_time_index on _hyper_61437_24311_chunk  (cost=0.43..11559.01 rows=319991 width=15) (actual time=0.047..76.213 rows=315141 loops=1)
                                       Index Cond: ((candle_start_time > '2025-01-01 00:00:00+00'::timestamp with time zone) AND (candle_start_time < '2025-01-03 00:00:00+00'::timestamp with time zone))
                                       Buffers: shared hit=7390
 Planning:
   Buffers: shared hit=50
 Planning Time: 0.710 ms
 Execution Time: 2272.118 ms
(39 rows)

If change dates that query fits single chunk it works fast as expected

explain (analyze,buffers) select candle_base_id, candle_quote_id, candle_start_time, sum(candle_volume) 
from candles_market_spot_0_1m 
where candle_start_time > '2025-01-05' and candle_start_time < '2025-01-07' 
group by candle_start_time, candle_base_id, candle_quote_id 
order by candle_start_time desc limit 100;

Limit  (cost=1.46..89.25 rows=100 width=44) (actual time=0.216..0.285 rows=100 loops=1)
   Buffers: shared hit=79
   ->  GroupAggregate  (cost=1.46..55695.38 rows=63442 width=44) (actual time=0.215..0.277 rows=100 loops=1)
         Group Key: _hyper_61437_24309_chunk.candle_start_time, _hyper_61437_24309_chunk.candle_base_id, _hyper_61437_24309_chunk.candle_quote_id
         Buffers: shared hit=79
         ->  Incremental Sort  (cost=1.46..48558.11 rows=634425 width=15) (actual time=0.208..0.213 rows=101 loops=1)
               Sort Key: _hyper_61437_24309_chunk.candle_start_time DESC, _hyper_61437_24309_chunk.candle_base_id, _hyper_61437_24309_chunk.candle_quote_id
               Presorted Key: _hyper_61437_24309_chunk.candle_start_time
               Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB
               Pre-sorted Groups: 1  Sort Method: quicksort  Average Memory: 35kB  Peak Memory: 35kB
               Buffers: shared hit=79
               ->  Index Scan using _hyper_61437_24309_chunk_candles_market_spot_0_1m_time_index on _hyper_61437_24309_chunk  (cost=0.43..27059.04 rows=634425 width=15) (actual time=0.022..0.146 rows=220 loops=1)
                     Index Cond: ((candle_start_time > '2025-01-05 00:00:00+00'::timestamp with time zone) AND (candle_start_time < '2025-01-07 00:00:00+00'::timestamp with time zone))
                     Buffers: shared hit=79
 Planning:
   Buffers: shared hit=36
 Planning Time: 0.879 ms
 Execution Time: 0.354 ms
(18 rows)

TimescaleDB version affected

2.16.1

PostgreSQL version used

16.3

What operating system did you use?

Ubuntu 21.04 X64

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

How can we reproduce the bug?

1. Create table

CREATE TABLE IF NOT EXISTS candles_market_spot_0_1m (candle_base_id smallint NOT NULL,
    candle_quote_id smallint NOT NULL,
    candle_start_time timestamptz NOT NULL,
    candle_open_price numeric NOT NULL DEFAULT 0,
    candle_close_price numeric NOT NULL DEFAULT 0,
    candle_low_price numeric NOT NULL DEFAULT 0,
    candle_high_price numeric NOT NULL DEFAULT 0,
    candle_volume numeric NOT NULL DEFAULT 0,
    created_at timestamptz default now(),
    PRIMARY KEY (candle_base_id, candle_quote_id, candle_start_time));
CREATE INDEX IF NOT EXISTS candles_market_spot_0_1m_time_index on candles_market_spot_0_1m (candle_start_time desc);

2. Copy data from the old database
3. Create hypertable
SELECT create_hypertable('candles_market_spot_0_1m', by_range('candle_start_time', INTERVAL '10 day'), migrate_data => true);

4. Run queries