To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
cc: "Nikolas Everett" <nik9000@gmail.com>, sthomas@peak6.com,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Best way to get the latest revision from a table 
In-reply-to: <4D3088E70200002500039632@gw.wicourts.gov> 
References: <AANLkTikVZG_Us9R9iLdz=GnqqEnHE5D7Go9xkENkdGiQ@mail.gmail.com>
	<4D30D708.3020704@peak6.com>
	<4D3088E70200002500039632@gw.wicourts.gov>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Fri, 14 Jan 2011 17:33:27 -0600"
Date: Fri, 14 Jan 2011 18:40:43 -0500
Message-ID: <14113.1295048443@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Shaun Thomas <sthomas@peak6.com> wrote:
>> This actually looks like a perfect candidate for DISTINCT ON.
>> 
>> SELECT DISTINCT ON (a, b) a, b, revision
>> FROM test
>> ORDER BY a, b DESC;
 
> I wouldn't say perfect.  It runs about eight times slower than what
> I suggested and returns a fairly random value for revision instead
> of the max(revision).

Shaun's example is a bit off: normally, when using DISTINCT ON, you want
an ORDER BY key that uses all the given DISTINCT keys and then some
more.  To get the max revision for each a/b combination it ought to be

SELECT DISTINCT ON (a, b) a, b, revision
   FROM test
   ORDER BY a, b, revision DESC;

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

			regards, tom lane