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

On Fri, Jan 14, 2011 at 8:50 PM, Nikolas Everett <nik9000(at)gmail(dot)com> wrote:
>
>
> On Fri, Jan 14, 2011 at 7:59 PM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>>
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.
>>
> <snip>
>>
>> -Kevin
>
> After trying both against the real tables DISTINCT ON seems to be about two
> orders of magnitude faster than the other options.

What I've often done in these situations is add a Boolean to the table
that defaults to true, and an ON INSERT trigger that flips the Boolean
for any existing row with the same key to false. Then you can just do
something like "SELECT * FROM tab WHERE latest". And you can create
partial indexes etc: CREATE INDEX foo ON tab (a) WHERE latest.

Although if using DISTINCT ON is working, no reason to do anything
more complicated.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company