3

In PostgreSQL 10.0, I have a big table with two columns:

CREATE TABLE xs (
  ts timestamp with time zone NOT NULL,
  x integer NOT NULL
)

Now, if I have 100 given time points, how do I return exactly 100 x-es that are right before (<=) these time points (for each of those points)?

For just a single one this would be very easy:

SELECT x FROM xs WHERE ts <= '2018-03-13 11:41:47.167973+00' LIMIT 1

But how to do this efficiently for a 100? Something like:

SELECT x FROM xs 
  WHERE ts FIRST_LESS_THAN_EQUAL_IN
    ('2018-03-13 11:41:47.167973+00', 
     '2018-03-13 11:41:47.198564+00', 
     '2018-03-13 11:41:47.555668+00',
      ...
     '2018-03-13 11:41:57.766888+00')

Obviously, there is an index on ts.

  • 2
    Do you have any unique constraints on the table? Also, your query for a single value doesn't have order by, so it can return any x where ts is less or equal to given, not the right before. – a1ex07 Mar 13 '18 at 16:36
  • Oops, sorry about no ORDER BY! ts is not unique, but the I can select an arbitrary value (xs for the same ts will be the same or very close). – Michal Rus Mar 14 '18 at 11:13
  • 1
    Just remember that repeated calls may return different values if the result is not deterministic. Might cause confusion. So the safe approach is to make it deterministic to begin with. – Erwin Brandstetter Mar 14 '18 at 14:17
2

I suggest a LATERAL subquery, best with a LEFT [OUTER] JOIN to preserve all input rows (displaying a NULL value in case there should be no match at all):

SELECT v.reference_ts, t.most_recent_x
FROM  (
   VALUES
      (timestamptz '2018-03-13 11:41:47.167973+00') -- type cast in first row
    , ('2018-03-13 11:41:47.198564+00') 
    , ('2018-03-13 11:41:47.555668+00')
    , ('2018-03-13 11:41:57.766888+00')
   ) AS v(reference_ts)
LEFT JOIN LATERAL (
   SELECT x AS most_recent_x
   FROM   xs
   WHERE  ts <= v.reference_ts  -- ts is unique (?!); see below
   ORDER  BY ts DESC
   LIMIT  1
   ) AS t ON true;

A VALUES expression is one way to put your 100 timestamps into a derived table we can work with. The free-standing VALUES expression may require an explicit type cast in the first row. See:

There are others ways to pass your values, like an array (or an array literal cast to timestamptz[]) and unnest() in the query:

About the LATERAL subquery:

Don't forget the ORDER BY or you get arbitrary results - like @a1ex07 commented.

If ts is not unique, it's undefined which of the duplicates is returned, unless you add more ORDER BY items to make it deterministic. Like: ORDER BY ts DESC, x DESC to get the row with the biggest x from any set of peers. And make the index match.

An index on (ts) (like you mentioned) works wonders on performance.
Or, ideally, on (ts DESC, x) if preconditions for index-only scans are met.

dbfiddle here

  • 1
    Thank you very much! I thought this would be inefficient, but EXPLAIN says it’s not! Wonderful! – Michal Rus Mar 14 '18 at 11:12

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.