[Bug]: Excessive chunk locking when using now()

[ivaaaan]

What type of bug is this?

Performance issue

What subsystems and features are affected?

Query planner

What happened?

We have noticed exessive locking on chunks when we use now() function in our queries. I have attached fully reproducible example below. The issue happens only when query using now():

WITH current_window AS (
  SELECT bucket, shop_id, location_id, price
  FROM sales_agg_1m
  WHERE bucket > now() - INTERVAL '24 hours'
),
previous_window AS (
  SELECT bucket, shop_id, location_id, price
  FROM sales_agg_1m
  WHERE bucket between now() - INTERVAL '48 hours' AND now() - INTERVAL '24 hours'
)
SELECT
  cw.bucket,
  cw.shop_id, 
  cw.location_id,
  cw.price,
  pw.price AS previous_price,
  (cw.price - pw.price) / NULLIF(pw.price, 0) AS price_change
FROM current_window cw
JOIN previous_window pw
ON cw.shop_id = pw.shop_id AND cw.location_id = pw.location_id;

If I put literal values in query, it doesn't use too many locks:

WITH current_window AS (
  SELECT bucket, shop_id, location_id, price
  FROM sales_agg_1m
  WHERE bucket >  '2025-02-03 00:00:00'
),
previous_window AS (
  SELECT bucket, shop_id, location_id, price
  FROM sales_agg_1m
  WHERE bucket between '2025-02-02 00:00:00' AND '2025-02-03 00:00:00'
)
SELECT
  cw.bucket,
  cw.shop_id, 
  cw.location_id,
  cw.price,
  pw.price AS previous_price,
  (cw.price - pw.price) / NULLIF(pw.price, 0) AS price_change
FROM current_window cw
JOIN previous_window pw
ON cw.shop_id = pw.shop_id AND cw.location_id = pw.location_id;

TimescaleDB version affected

2.17.2

PostgreSQL version used

17.0

What operating system did you use?

Ubuntu x64

What installation method did you use?

Not applicable

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

How can we reproduce the bug?

CREATE TABLE sales_data (
   timestamp     TIMESTAMPTZ NOT NULL,
   shop_id       INT,
   location_id   INT,
   selling_price NUMERIC
);

SELECT create_hypertable('sales_data', 'timestamp', chunk_time_interval => INTERVAL '12 hour');

INSERT INTO sales_data (timestamp, shop_id, location_id, selling_price)
SELECT gs,
       (floor(random() * 10) + 1)::int AS shop_id,
       (floor(random() * 10) + 1)::int AS location_id,
       random() * 100
FROM generate_series(now() - INTERVAL '3 years', now(), INTERVAL '1 hour') gs;

CREATE MATERIALIZED VIEW sales_agg_1m WITH (timescaledb.continuous, timescaledb.create_group_indexes=false, timescaledb.materialized_only=false) AS
SELECT time_bucket('1 minute', timestamp) AS bucket,
       shop_id,
       location_id,
       last(selling_price, timestamp) AS price
FROM sales_data
GROUP BY bucket, shop_id, location_id
WITH NO DATA;

ALTER MATERIALIZED VIEW sales_agg_1m set (timescaledb.compress = true);

SELECT set_chunk_time_interval(
    (
        select format('%I.%I', materialization_hypertable_schema,
               materialization_hypertable_name)
        from timescaledb_information.continuous_aggregates
        where view_name = 'sales_agg_1m'
    )::regclass, INTERVAL '12 hour'
);

CALL refresh_continuous_aggregate('sales_agg_1m', NULL, now());

BEGIN;
WITH current_window AS (
  SELECT bucket, shop_id, location_id, price
  FROM sales_agg_1m
  WHERE bucket > now() - INTERVAL '24 hours'
),
previous_window AS (
  SELECT bucket, shop_id, location_id, price
  FROM sales_agg_1m
  WHERE bucket between now() - INTERVAL '48 hours' AND now() - INTERVAL '24 hours'
)
SELECT
  cw.bucket,
  cw.shop_id, 
  cw.location_id,
  cw.price,
  pw.price AS previous_price,
  (cw.price - pw.price) / NULLIF(pw.price, 0) AS price_change
FROM current_window cw
JOIN previous_window pw
  ON cw.shop_id = pw.shop_id AND cw.location_id = pw.location_id;

SELECT count(DISTINCT(c.relname)) AS locked_tables
 FROM pg_locks l
 JOIN pg_class c ON l.relation = c.oid
 WHERE l.pid = pg_backend_pid();;

ROLLBACK;