2

I have a Postgres table where the PK is made up of a UUID (id) and an integer (version). Most queries want to select the latest version of a particular ID. The query I use now looks like this:

select * from dataset
where id = '0faa6a7b-587c-4106-9b1e-3cf155c2ee41' and 
version = (select max(version) from dataset where id = '0faa6a7b-587c-4106-9b1e-3cf155c2ee41')

I am wondering if there is a better, more efficient way of handling this type of query.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
AlexGad
  • 6,612
  • 5
  • 33
  • 45

2 Answers2

4

Which method is the fastest depends on indexes and data distribution. The shortest (and often fastest) in PostgreSQL is:

SELECT DISTINCT ON (id) *
FROM   dataset
WHERE  id = '0faa6a7b-587c-4106-9b1e-3cf155c2ee41'
ORDER  BY id, version DESC;

Can be expanded to fetch multiple rows with the latest version at once. For just a single row, this will do, too:

SELECT *
FROM   dataset
WHERE  id = '0faa6a7b-587c-4106-9b1e-3cf155c2ee41'
ORDER  version DESC
LIMIT  1;

The perfect index for the case would be a multi-column index like:

CREATE INDEX dataset_id_version_idx ON dataset(id, version DESC);

More about DISTINCT ON:

Select first row in each GROUP BY group?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

you can use anti-joins,

here is an example, with returns the latest versions of all

SELECT
    d.*   
FROM
    "dataset" AS d
    LEFT JOIN "dataset" As d_ ON d_."id" = d."id" AND d_."version" > d."version"
WHERE
    d_."id" IS NULL
;

this query also provides you speed, if you have an index on version field too. also you can filter whatever you want.

ncank
  • 946
  • 5
  • 15
  • Interesting, this one would provide more flexibility as it allows other filters. I just tried it on a 2 million row table and using 3 ORs against LEFT JOINed tables, I got results back in 95ms. Not bad. I am going to have to look at this one some more. – AlexGad Sep 04 '12 at 02:10
  • i use this method in all my queries. i don't know what i would do, if there is no such a method :) – ncank Sep 04 '12 at 20:50