Re: Best way to get the latest revision from a table

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Shaun's example is a bit off

> As for speed, either one might be faster in a particular
> situation.

After fixing a mistake in my testing and learning from Tom's example
I generated queries against the OP's test data which produce
identical results, and I'm finding no significant difference between
run times for the two versions.  The OP should definitely try both
against the real tables.

Here are the queries which run against the test set:

DROP TABLE IF EXISTS request;
CREATE TEMPORARY TABLE request (a INTEGER NOT NULL);
INSERT INTO request SELECT a FROM generate_series(2, 200) AS t(a);
ANALYZE request;
SELECT y.*
  from (select a, max(revision) as revision
          from test join request using (a)
          group by a) x
  join test y using (a, revision)
  order by a, revision DESC;

DROP TABLE IF EXISTS request;
CREATE TEMPORARY TABLE request (a INTEGER NOT NULL);
INSERT INTO request SELECT a FROM generate_series(2, 200) AS t(a);
ANALYZE request;
SELECT DISTINCT ON (a, b, c) revision, a, b, c
   FROM test join request using (a)
   ORDER BY a, b, c, revision DESC;

Sorry for not sorting it out better initially.

-Kevin