Skip site navigation (1) Skip section navigation (2)

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

From: Dave Johansen <davejohansen(at)gmail(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Picking out the most recent row using a time stamp column
Date: 2011-02-25 16:50:40
Message-ID: AANLkTin9K_E37QzwEN2ARbh9SHZWv5DwQ6Go7sZpzGL+@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Dave CrookeDate: 2011-02-25 20:45:23
Subject: Re: Picking out the most recent row using a time stamp column
Previous:From: Sam WongDate: 2011-02-25 13:31:26
Subject: Index use difference betweer LIKE, LIKE ANY?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group