From: Peter Childs <peterachilds@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: optimize query with a maximum(date) extraction
Date: Wed, 5 Sep 2007 13:43:24 +0100
Message-ID: <a2de01dd0709050543j57de8774s9e9e0a576cc21bd1@mail.gmail.com> (raw)
In-Reply-To: <87642pqpue.fsf@oxford.xeocode.com>
References: <302443.65402.qm@web26209.mail.ukl.yahoo.com>
<87abs1qrhu.fsf@oxford.xeocode.com>
<87642pqpue.fsf@oxford.xeocode.com>
On 05/09/07, Gregory Stark <stark@enterprisedb.com> wrote:
>
> "Gregory Stark" <stark@enterprisedb.com> writes:
>
> > "JS Ubei" <jsubei@yahoo.fr> writes:
> >
> >> I need to improve a query like :
> >>
> >> SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
> >...
> > I don't think you'll find anything much faster for this particular
> query. You
> > could profile running these two (non-standard) queries:
> >
> > SELECT DISTINCT ON (id) id, the_date AS min_date FROM my_table ORDER BY
> id, the_date ASC
> > SELECT DISTINCT ON (id) id, the_date AS max_date FROM my_table ORDER BY
> id, the_date DESC
>
> Something else you might try:
>
> select id,
> (select min(the_date) from my_table where id=x.id) as min_date,
> (select max(the_date) from my_table where id=x.id) as max_date
> from (select distinct id from my_table)
>
> Recent versions of Postgres do know how to use the index for a simple
> ungrouped min() or max() like these subqueries.
>
> This would be even better if you have a better source for the list of
> distinct
> ids you're interested in than my_table. If you have a source that just has
> one
> record for each id then you won't need an extra step to eliminate
> duplicates.
>
>
My personal reaction is why are you using distinct at all?
why not
select id,
min(the_date) as min_date,
max(the_date) as max_date
from my_table group by id;
Since 8.0 or was it earlier this will use an index should a reasonable one
exist.
Peter.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: peterachilds@gmail.com
Subject: Re: optimize query with a maximum(date) extraction
In-Reply-To: <a2de01dd0709050543j57de8774s9e9e0a576cc21bd1@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox