Re: Picking out the most recent row using a time stamp column

On Thu, Feb 24, 2011 at 4:38 PM, Dave Crooke <dcrooke(at)gmail(dot)com> wrote:

> Thanks to all .... I had a tickling feeling at the back of my mind that
> there was a neater answer here. For the record, times (all from in-memory
> cached data, averaged over a bunch of runs):
>
> Dependent subquery = 117.9 seconds
> Join to temp table = 2.7 sec
> DISTINCT ON = 2.7 sec
>
> So the DISTINCT ON may not be quicker, but it sure is tidier.
>
> Cheers
> Dave

I'm using 8.3.3 and I have a similar sort of setup and just thought I'd add
another point of reference, here's the timing from doing the same sort of
queries on my dataset of ~700,000 records with ~10,000 unique "id_key"s.

I also added a 4th version that uses a permanent table that's auto-populated
by a trigger with the rid of the most recent entry from the main table, so
it's a simple join to get the latest entries.

Dependent subquery = (killed it after it ran for over 10 minutes)
Join on temp table = 1.5 seconds
DISTINCT ON = 2.9 seconds
Join on auto-populated table = 0.8 seconds

Dave