On 2/24/11 3:38 PM, Dave Crooke 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
But wait, there's more! You haven't tested the Windowing Function solution. I'll bet it's even faster. SELECT id_key, time_stamp, value FROM ( SELECT id_key, time_stamp, value, row_number() OVER ( PARTITION BY id_key ORDER BY time_stamp DESC) as ranking FROM thetable ) as filtered_table WHERE ranking = 1 -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance