1

I have a timeseries table where I need to process data in partitions of hours and processing of each individual row depends on the previous row.

Please consider this simple table.

CREATE TABLE IF NOT EXISTS t (
    timestamp timestamptz,
    value float
);


INSERT INTO t VALUES
    (CURRENT_TIMESTAMP + '1 minute'::interval, 1),
    (CURRENT_TIMESTAMP + '2 minute'::interval, 2),
    (CURRENT_TIMESTAMP + '3 minute'::interval, 3),
    (CURRENT_TIMESTAMP + '4 minute'::interval, 4),
    (CURRENT_TIMESTAMP + '5 minute'::interval, 5)
;
WITH
    v AS
    (
        SELECT
            timestamp,
            lag(timestamp) OVER client_window AS prev_timestamp,

            value,
            lag(value) OVER client_window AS prev_value,
            value - lag(value) OVER client_window AS diff
        FROM
            t
        WINDOW client_window AS (ORDER BY timestamp)
        ORDER BY
            timestamp
    )
SELECT
    *
FROM
    v
WHERE
    timestamp BETWEEN CURRENT_TIMESTAMP + '1 minute'::interval AND CURRENT_TIMESTAMP + '4 minute'::interval
;

The above query returns the result I want. Unfortuantely because postgres doesn't optimize across the boundaries of CTE, the following query scans the whole table.

+-------------------------------+-------------------------------+---------+--------------+--------+
| timestamp                     | prev_timestamp                | value   | prev_value   | diff   |
+===============================+===============================+=========+==============+========+
| 2020-10-01 00:54:14.172296+00 | 2020-10-01 00:53:14.172296+00 | 2.0     | 1.0          | 1.0    |
+-------------------------------+-------------------------------+---------+--------------+--------+
| 2020-10-01 00:55:14.172296+00 | 2020-10-01 00:54:14.172296+00 | 3.0     | 2.0          | 1.0    |
+-------------------------------+-------------------------------+---------+--------------+--------+
| 2020-10-01 00:56:14.172296+00 | 2020-10-01 00:55:14.172296+00 | 4.0     | 3.0          | 1.0    |
+-------------------------------+-------------------------------+---------+--------------+--------+

I've also tried the following query

WITH
    v AS
    (
        SELECT
            timestamp,
            lag(timestamp) OVER client_window AS prev_timestamp,

            value,
            lag(value) OVER client_window AS prev_value,
            value - lag(value) OVER client_window AS diff
        FROM
            t
        WHERE
            timestamp BETWEEN CURRENT_TIMESTAMP + '1 minute'::interval AND CURRENT_TIMESTAMP + '4 minute'::interval
        WINDOW client_window AS (ORDER BY timestamp)
            ORDER BY
            timestamp
    )
SELECT
    *
FROM
    v
;

Unfortunately, because of the filtering by where clause first row returns NULL for prev_value.

+-------------------------------+-------------------------------+---------+--------------+--------+
| timestamp                     | prev_timestamp                | value   | prev_value   | diff   |
+===============================+===============================+=========+==============+========+
| 2020-10-01 00:54:14.172296+00 | <null>                        | 2.0     | <null>       | <null> |
+-------------------------------+-------------------------------+---------+--------------+--------+
| 2020-10-01 00:55:14.172296+00 | 2020-10-01 00:54:14.172296+00 | 3.0     | 2.0          | 1.0    |
+-------------------------------+-------------------------------+---------+--------------+--------+
| 2020-10-01 00:56:14.172296+00 | 2020-10-01 00:55:14.172296+00 | 4.0     | 3.0          | 1.0    |
+-------------------------------+-------------------------------+---------+--------------+--------+

Is there any way to get the intended result without scanning the whole table?

2
  • 2
    Are you only dealing with the one value? Defining your primary key as CREATE UNIQUE INDEX pk_yourtable ON your table UNIQUE (timestamp) INCLUDE (value) should keep Postgres from hitting the heap, but it'll probably still read the entire B-tree, which may be fine for your purposes.
    – user212533
    Oct 1, 2020 at 13:02
  • Thanks! I will look into that. However, the amont of data we have hitting the index might not be an option since it's an append-only tme series table.
    – user
    Oct 1, 2020 at 23:30

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.