2

(This is a PostgreSQL-specific question, using version 9.1, but could be upgraded to 9.3.)

I have a number of tables that represent a series of changes. These tables all have the same structure:

CREATE TABLE ... (
    event_ts TIMESTAMPTZ PRIMARY KEY,
    val REAL
)

Each row represents a change. Let's say table_a has these values:

event_ts                  val
---------------------------------
2013-07-31 23:57:06       1.0
2013-08-01 00:04:13       1.3
2013-08-01 00:06:27       1.1
2013-08-01 00:13:59       2.0

Conceptually, the "value" of A at 2013-08-01 00:05:00 would be 1.3, since it last changed at 2013-08-01 00:04:13, to take the value 1.3.

When I want to know the values during a date range, I not only need to select the rows within that interval, but I also need to get the last row just before.

For example:

SELECT event_ts, val FROM table_a
    WHERE event_ts >= (SELECT MAX(event_ts) FROM table_a WHERE event_ts <= '2013-08-01 00:00+00'::timestamptz)
      AND event_ts < '2013-12-01 00:00+00'::timestamptz

When I do such a query, the time-consuming part seems to be finding that previous row, just before the range starts (see explain analyze result).

SELECT MAX(event_ts) FROM table_a WHERE event_ts <= '2013-08-01 00:00'::timestamptz causes "Index Scan Backward using table_a_pkey on table_a (cost=0.00..8046.32 rows=220852 width=8) (actual time=53.974..53.974 rows=1 loops=1)".

It's not too bad at that stage, but it becomes an issue when I try to join two tables for all the timestamps they cover within that range. Whenever there is a change is either table_a or table_b, I want to know what the current value at that point in time for both series is:

event_ts                A       B
------------------------------------
2013-07-31 23:57:06     1.0     5.1        Coming from table_a
2013-07-31 23:59:55     1.0     5.0        Coming from table_b
2013-08-01 00:04:13     1.3     5.0        Coming from table_a
2013-08-01 00:06:27     1.1     5.0        Coming from table_a
2013-08-01 00:11:25     1.1     1.0        Coming from table_b
2013-08-01 00:13:59     2.0     1.0        Coming from table_a
2013-08-01 00:18:39     2.0     2.0        Coming from table_b
2013-08-01 00:20:34     2.1     2.0        Coming from table_a

This is obtained from this query, which essentially find the union of both set of timestamps, and then finds the latest value for each series for each of these timestamps:

WITH all_ts AS (
    SELECT event_ts FROM table_a
            WHERE event_ts >= (SELECT MAX(event_ts) FROM table_a WHERE event_ts <= '2013-08-01 00:00+00'::timestamptz)
              AND event_ts < '2013-12-01 00:00+00'::timestamptz
    UNION 
    SELECT event_ts FROM table_b
            WHERE event_ts >= (SELECT MAX(event_ts) FROM table_b WHERE event_ts <= '2013-08-01 00:00+00'::timestamptz)
              AND event_ts < '2013-12-01 00:00+00'::timestamptz

)
SELECT ats.event_ts,
    (SELECT t.val FROM table_a t
        WHERE t.event_ts=(SELECT MAX(m.event_ts) FROM table_a m WHERE m.event_ts <= ats.event_ts)), 
    (SELECT t.val FROM table_b t
        WHERE t.event_ts=(SELECT MAX(m.event_ts) FROM table_b m WHERE m.event_ts <= ats.event_ts))
FROM all_ts ats
    ORDER BY ats.event_ts

Here, the performance hit due to (SELECT MAX(m.event_ts) FROM table_X m WHERE m.event_ts <= ats.event_ts)) is more noticeable (see explain analyze result).

Is there a better way (in terms of speed) to get these results?

I've tried to use the btree_gist extension, created additional GiST indexes on event_ts and replaced (SELECT MAX(m.event_ts) ... with this:

(SELECT event_ts FROM
    (SELECT event_ts, event_ts <-> '2013-08-01 00:00+00'::timestamptz AS dist
        FROM table_b WHERE event_ts <= '2013-08-01 00:00+00'::timestamptz ORDER BY dist LIMIT 1) x)

I was hoping that the GiST index would improve search (since it's a one-sided nearest neighbour search), but it doesn't really (although the GiST indexes are used, when using <->).

I was wondering if a PL/pgSQL function could help, by iterating through two tables at once, somehow, but I'm not sure if that's possible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.