Timescaledb: time_bucket_gapfill is skipping some buckets

[pehlert]

Relevant system information:

• OS: TimescaleDB Docker image
• PostgreSQL version (output of postgres --version): 12
• TimescaleDB version (output of \dx in psql): 2.1.0
• Installation method: Docker

Describe the bug
time_bucket_gapfill is not filling gaps properly anymore.

Additional information
We upgraded from 1.7.1 to 2.1.0 recently on this node. It was working before the upgrade.

When not using any aggregate functions in the SELECT, it appears to work fine.

[svenklemm]

Can you post EXPLAIN VERBOSE for the query

[pehlert]

Hi @svenklemm,

thanks for the response. Please find the EXPLAIN VERBOSE output below:

Sort  (cost=2491.62..2492.39 rows=306 width=16)
"  Output: (time_bucket_gapfill('1 day'::interval, _hyper_1_3179_chunk.""timestamp"", NULL::timestamp without time zone, NULL::timestamp without time zone)), (COALESCE(sum(_hyper_1_3179_chunk.running_time_delta), '0'::bigint))"
"  Sort Key: (time_bucket_gapfill('1 day'::interval, _hyper_1_3179_chunk.""timestamp"", NULL::timestamp without time zone, NULL::timestamp without time zone))"
  ->  HashAggregate  (cost=2475.17..2478.99 rows=306 width=16)
"        Output: (time_bucket_gapfill('1 day'::interval, _hyper_1_3179_chunk.""timestamp"", NULL::timestamp without time zone, NULL::timestamp without time zone)), COALESCE(sum(_hyper_1_3179_chunk.running_time_delta), '0'::bigint)"
"        Group Key: time_bucket_gapfill('1 day'::interval, _hyper_1_3179_chunk.""timestamp"", NULL::timestamp without time zone, NULL::timestamp without time zone)"
        ->  Result  (cost=0.29..2387.46 rows=17542 width=12)
"              Output: time_bucket_gapfill('1 day'::interval, _hyper_1_3179_chunk.""timestamp"", NULL::timestamp without time zone, NULL::timestamp without time zone), _hyper_1_3179_chunk.running_time_delta"
              ->  Append  (cost=0.29..2168.18 rows=17542 width=12)
                    ->  Index Scan Backward using _hyper_1_3179_chunk_events_timestamp_idx on _timescaledb_internal._hyper_1_3179_chunk  (cost=0.29..533.36 rows=1501 width=12)
"                          Output: _hyper_1_3179_chunk.""timestamp"", _hyper_1_3179_chunk.running_time_delta"
"                          Index Cond: ((_hyper_1_3179_chunk.""timestamp"" > '2021-02-01 00:00:00'::timestamp without time zone) AND (_hyper_1_3179_chunk.""timestamp"" <= '2021-02-28 23:59:59.999'::timestamp without time zone))"
"                          Filter: ((_hyper_1_3179_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)"
                    ->  Bitmap Heap Scan on _timescaledb_internal._hyper_1_3180_chunk  (cost=56.37..453.37 rows=1543 width=12)
"                          Output: _hyper_1_3180_chunk.""timestamp"", _hyper_1_3180_chunk.running_time_delta"
"                          Recheck Cond: ((_hyper_1_3180_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)"
"                          Filter: ((_hyper_1_3180_chunk.""timestamp"" > '2021-02-01 00:00:00'::timestamp without time zone) AND (_hyper_1_3180_chunk.""timestamp"" <= '2021-02-28 23:59:59.999'::timestamp without time zone))"
                          ->  Bitmap Index Scan on _hyper_1_3180_chunk_events_guid_idx  (cost=0.00..55.98 rows=1543 width=0)
"                                Index Cond: ((_hyper_1_3180_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)"
                    ->  Bitmap Heap Scan on _timescaledb_internal._hyper_1_3181_chunk  (cost=163.19..583.01 rows=4504 width=12)
"                          Output: _hyper_1_3181_chunk.""timestamp"", _hyper_1_3181_chunk.running_time_delta"
"                          Recheck Cond: ((_hyper_1_3181_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)"
"                          Filter: ((_hyper_1_3181_chunk.""timestamp"" > '2021-02-01 00:00:00'::timestamp without time zone) AND (_hyper_1_3181_chunk.""timestamp"" <= '2021-02-28 23:59:59.999'::timestamp without time zone))"
                          ->  Bitmap Index Scan on _hyper_1_3181_chunk_events_guid_idx  (cost=0.00..162.07 rows=4504 width=0)
"                                Index Cond: ((_hyper_1_3181_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)"
                    ->  Seq Scan on _timescaledb_internal._hyper_1_3182_chunk  (cost=0.00..291.56 rows=7539 width=12)
"                          Output: _hyper_1_3182_chunk.""timestamp"", _hyper_1_3182_chunk.running_time_delta"
"                          Filter: ((_hyper_1_3182_chunk.""timestamp"" > '2021-02-01 00:00:00'::timestamp without time zone) AND (_hyper_1_3182_chunk.""timestamp"" <= '2021-02-28 23:59:59.999'::timestamp without time zone) AND ((_hyper_1_3182_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text))"
                    ->  Index Scan Backward using _hyper_1_3183_chunk_events_timestamp_idx on _timescaledb_internal._hyper_1_3183_chunk  (cost=0.28..219.17 rows=2455 width=12)
"                          Output: _hyper_1_3183_chunk.""timestamp"", _hyper_1_3183_chunk.running_time_delta"
"                          Index Cond: ((_hyper_1_3183_chunk.""timestamp"" > '2021-02-01 00:00:00'::timestamp without time zone) AND (_hyper_1_3183_chunk.""timestamp"" <= '2021-02-28 23:59:59.999'::timestamp without time zone))"
"                          Filter: ((_hyper_1_3183_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)"

[majozv]

We have the same issue, using TSDB release 2.0.1:

We have time series data, every 1 hour one record.
Data starts at 2018-02-17 05:00:00
If I query interval 2018-12-01 - 2018-12-03

 SELECT time_bucket_gapfill('1 day', l_time) AS time_int,
        avg(sensor_value) avg_sensor_value
   FROM sensor_table
  WHERE l_time BETWEEN '2018-12-01T07:00:00'::timestamp AND '2018-12-03T06:59:59'::timestamp
  GROUP BY time_int
  ORDER BY time_int;

result is OK

If I query interval 2018-12-01 - 2018-12-31
result is not ok, blank days are not included among query results

here is a picture how it responded with 1.7.X

[svenklemm]

OK for some reason your plan is missing the gapfill node. Can you post EXPLAIN (verbose,settings) for the query

[pehlert]

Not sure if you need that from me as well, here is with settings included however:
Sort (cost=2491.62..2492.39 rows=306 width=16) " Output: (time_bucket_gapfill('1 day'::interval, _hyper_1_3179_chunk.""timestamp"", NULL::timestamp without time zone, NULL::timestamp without time zone)), (COALESCE(sum(_hyper_1_3179_chunk.running_time_delta), '0'::bigint))" " Sort Key: (time_bucket_gapfill('1 day'::interval, _hyper_1_3179_chunk.""timestamp"", NULL::timestamp without time zone, NULL::timestamp without time zone))" -> HashAggregate (cost=2475.17..2478.99 rows=306 width=16) " Output: (time_bucket_gapfill('1 day'::interval, _hyper_1_3179_chunk.""timestamp"", NULL::timestamp without time zone, NULL::timestamp without time zone)), COALESCE(sum(_hyper_1_3179_chunk.running_time_delta), '0'::bigint)" " Group Key: time_bucket_gapfill('1 day'::interval, _hyper_1_3179_chunk.""timestamp"", NULL::timestamp without time zone, NULL::timestamp without time zone)" -> Result (cost=0.29..2387.46 rows=17542 width=12) " Output: time_bucket_gapfill('1 day'::interval, _hyper_1_3179_chunk.""timestamp"", NULL::timestamp without time zone, NULL::timestamp without time zone), _hyper_1_3179_chunk.running_time_delta" -> Append (cost=0.29..2168.18 rows=17542 width=12) -> Index Scan Backward using _hyper_1_3179_chunk_events_timestamp_idx on _timescaledb_internal._hyper_1_3179_chunk (cost=0.29..533.36 rows=1501 width=12) " Output: _hyper_1_3179_chunk.""timestamp"", _hyper_1_3179_chunk.running_time_delta" " Index Cond: ((_hyper_1_3179_chunk.""timestamp"" > '2021-02-01 00:00:00'::timestamp without time zone) AND (_hyper_1_3179_chunk.""timestamp"" <= '2021-02-28 23:59:59.999'::timestamp without time zone))" " Filter: ((_hyper_1_3179_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)" -> Bitmap Heap Scan on _timescaledb_internal._hyper_1_3180_chunk (cost=56.37..453.37 rows=1543 width=12) " Output: _hyper_1_3180_chunk.""timestamp"", _hyper_1_3180_chunk.running_time_delta" " Recheck Cond: ((_hyper_1_3180_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)" " Filter: ((_hyper_1_3180_chunk.""timestamp"" > '2021-02-01 00:00:00'::timestamp without time zone) AND (_hyper_1_3180_chunk.""timestamp"" <= '2021-02-28 23:59:59.999'::timestamp without time zone))" -> Bitmap Index Scan on _hyper_1_3180_chunk_events_guid_idx (cost=0.00..55.98 rows=1543 width=0) " Index Cond: ((_hyper_1_3180_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)" -> Bitmap Heap Scan on _timescaledb_internal._hyper_1_3181_chunk (cost=163.19..583.01 rows=4504 width=12) " Output: _hyper_1_3181_chunk.""timestamp"", _hyper_1_3181_chunk.running_time_delta" " Recheck Cond: ((_hyper_1_3181_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)" " Filter: ((_hyper_1_3181_chunk.""timestamp"" > '2021-02-01 00:00:00'::timestamp without time zone) AND (_hyper_1_3181_chunk.""timestamp"" <= '2021-02-28 23:59:59.999'::timestamp without time zone))" -> Bitmap Index Scan on _hyper_1_3181_chunk_events_guid_idx (cost=0.00..162.07 rows=4504 width=0) " Index Cond: ((_hyper_1_3181_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)" -> Seq Scan on _timescaledb_internal._hyper_1_3182_chunk (cost=0.00..291.56 rows=7539 width=12) " Output: _hyper_1_3182_chunk.""timestamp"", _hyper_1_3182_chunk.running_time_delta" " Filter: ((_hyper_1_3182_chunk.""timestamp"" > '2021-02-01 00:00:00'::timestamp without time zone) AND (_hyper_1_3182_chunk.""timestamp"" <= '2021-02-28 23:59:59.999'::timestamp without time zone) AND ((_hyper_1_3182_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text))" -> Index Scan Backward using _hyper_1_3183_chunk_events_timestamp_idx on _timescaledb_internal._hyper_1_3183_chunk (cost=0.28..219.17 rows=2455 width=12) " Output: _hyper_1_3183_chunk.""timestamp"", _hyper_1_3183_chunk.running_time_delta" " Index Cond: ((_hyper_1_3183_chunk.""timestamp"" > '2021-02-01 00:00:00'::timestamp without time zone) AND (_hyper_1_3183_chunk.""timestamp"" <= '2021-02-28 23:59:59.999'::timestamp without time zone))" " Filter: ((_hyper_1_3183_chunk.guid)::text = '4c27047b-fcb3-41ca-919c-c2a23030b39e'::text)"

[svenklemm]

There is some interaction with enable_partitionwise_aggregate do you have that enabled by any chance?

show enable_partitionwise_aggregate;

[pehlert]

I'm afraid we don't, no

[majozv]

the same for me

[svenklemm]

Ok this is some interaction between gapfill and the HashAgg optimization we are doing resulting in the HashAgg optimization producing a cheaper path.
As a workaround you can make the aggregate an ordered aggregate by changing avg(sensor_value) to avg(sensor_value ORDER BY l_time) or you can disable HashAgg with set enable_hashagg to false;.

[majozv]

Hi svenklemm,

avg(sensor_value ORDER BY l_time) works.