On Thu, Feb 24, 2011 at 4:38 PM, Dave Crooke <dcro...@gmail.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
