[Bug]: Incorrect results - range predicate on an interval column drops rows when it is the first compress_orderby key

Author: JacobBrejnbjergCreated Aug 21, 2026Updated Sep 16, 2026

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Compression, Query executor

What happened?

After changing compress_orderby so that an interval column is the first orderby key, and recompressing chunks, a range predicate on that interval column in a WHERE clause silently returns only a subset of the matching rows. No error, no warning, no notice.

The same predicate expressed as an aggregate FILTER over the same chunk returns the correct rows, so the discrepancy is in predicate handling, not in the stored data.

On one affected chunk, for the band predicate delay > INTERVAL '45 min' AND delay <= INTERVAL '60 min':

form	rows returned
`count(*) FILTER (WHERE ...)`	331,022 — correct
`WHERE ...`	131,039 — 60.4% of rows missing
`WHERE ...` with `timescaledb.enable_columnar_scan_filter_pushdown = off`	331,022 — correct

I verified that 331,022 is the truth independently of TimescaleDB predicate handling: I dumped all 3,867,052 rows of the chunk filtering only on segmentby columns (no predicate referencing the interval column at all), then evaluated band membership externally using exact integer microsecond arithmetic. Result: 331,022.

Aggregates computed over the truncated row set are wrong but plausible-looking. Comparing a GROUP BY aggregation before and after the orderby change against the externally computed truth (288 groups):

summed quantity understated by 63.9% overall
sum()-derived values wrong in 288/288 groups; worst group reported 15.6 where the true value is 678.7
weighted-average values wrong in 288/288 groups
max()/min() wrong in roughly half the groups — they often survive because the retained subset frequently still contains the extremes, which makes the failure easy to miss on inspection

Expected behaviour: identical results regardless of compress_orderby column ordering, and regardless of enable_columnar_scan_filter_pushdown.

Diagnosis

On the affected chunk the scan prunes using the firstlast sparse index on the leading orderby column:

Index Cond: ((unit_size = '0.25'::double precision)
         AND ("_ts_meta_v2_first_delay" <= '01:00:00'::interval)
         AND ("_ts_meta_v2_last_delay"  >  '00:45:00'::interval))

Before the orderby change — with the same column present as orderby key #2 — the same chunk pruned via _ts_meta_min_2 / _ts_meta_max_2 and returned correct results.

Suspected cause: firstlast sparse-index comparison on the interval type. Values in this column exceed 24 hours (max approximately 1 day 09:44), so the interval representation carries a non-zero days field alongside the microseconds field. A comparison that does not normalise days against time would compute wrong first/last bounds and eliminate batches that do contain matching rows.

Two supporting observations:

A chunk carrying the same interval column as orderby key #2 (with a timestamptz column first) returns correct results with pushdown enabled — even though it also declares a firstlast sparse index on the interval column. Only the leading-orderby case misbehaves.
SET timescaledb.enable_columnarscan = off returns 0 rows for the same query on the affected chunk — a third, different wrong answer. This may be a separate issue.

TimescaleDB version affected

2.29.2

PostgreSQL version used

18.4

What operating system did you use?

Ubuntu 24.04 x64

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

bash

# Nothing is logged. No error, no warning, no notice.
# The query succeeds and returns a silently truncated result set.

How can we reproduce the bug?

bash

# NOTE: The behaviour described above is confirmed on a production instance.
# The script below is my synthetic reduction of it, which I have NOT been able
# to run (no DDL rights on the affected instance), so please treat it as a
# starting point rather than a verified reproducer.
#
# The essential ingredients appear to be:
#   1. an `interval` column as the FIRST compress_orderby key
#   2. values in that column exceeding 24h, so the interval `days` field is set
#   3. enough rows per segment that segments span multiple 1000-row batches
#   4. a range predicate (not equality) on that interval column in WHERE

psql <<'SQL'
CREATE TABLE readings (
  bucket_start timestamptz NOT NULL,
  recorded_at  timestamptz NOT NULL,
  delay        interval GENERATED ALWAYS AS (bucket_start - recorded_at) STORED,
  src          text   NOT NULL,
  dst          text   NOT NULL,
  unit_size    float8 NOT NULL,
  amount       bigint NOT NULL
);

SELECT create_hypertable('readings', 'bucket_start',
                         chunk_time_interval => INTERVAL '3 days');

-- delay spans 0..30h so the interval `days` field is populated for part of it
INSERT INTO readings (bucket_start, recorded_at, src, dst, unit_size, amount)
SELECT b,
       b - make_interval(secs => (g * 53) % 108000),
       'src' || (g % 5),
       'dst' || ((g / 5) % 5),
       0.25,
       (g % 997) + 1
FROM generate_series('2025-01-01'::timestamptz,
                     '2025-01-03 23:45'::timestamptz,
                     INTERVAL '15 min') b,
     generate_series(1, 2000) g;

ALTER TABLE readings SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'src,dst,unit_size',
  timescaledb.compress_orderby   = 'delay ASC, bucket_start ASC'   -- interval FIRST
);
SELECT compress_chunk(c) FROM show_chunks('readings') c;
ANALYZE readings;

-- (A) truth: predicate evaluated as an aggregate FILTER
SELECT count(*) FILTER (WHERE delay > INTERVAL '45 min'
                          AND delay <= INTERVAL '60 min') AS via_filter
FROM readings;

-- (B) same predicate in WHERE -- expected to return fewer rows
SELECT count(*) AS via_where
FROM readings
WHERE delay > INTERVAL '45 min' AND delay <= INTERVAL '60 min';

-- (C) workaround: disabling filter pushdown restores (A)
SET timescaledb.enable_columnar_scan_filter_pushdown = off;
SELECT count(*) AS via_where_no_pushdown
FROM readings
WHERE delay > INTERVAL '45 min' AND delay <= INTERVAL '60 min';
RESET timescaledb.enable_columnar_scan_filter_pushdown;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM readings
WHERE delay > INTERVAL '45 min' AND delay <= INTERVAL '60 min';

-- Contrast: with the timestamptz column first, results are correct
ALTER TABLE readings SET (
  timescaledb.compress_orderby = 'bucket_start ASC, delay ASC'
);
SELECT compress_chunk(c, recompress => true) FROM show_chunks('readings') c;
SELECT count(*) FROM readings
WHERE delay > INTERVAL '45 min' AND delay <= INTERVAL '60 min';   -- matches (A)
SQL

Source: timescale/timescaledb

View original on GitHub View discussion on GitHub