Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CFE0F9FA161 for ; Wed, 5 Sep 2007 09:43:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 19492-05 for ; Wed, 5 Sep 2007 09:43:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id C041D9FA0C6 for ; Wed, 5 Sep 2007 09:43:26 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id 30so1767056nfu for ; Wed, 05 Sep 2007 05:43:25 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=puv2IayUNmQ9PSifa1eGVx0czoV8MiXZdDmYBgwLIwzWObyLjFmBHw3e0l0BcOeWXsXxekX5jhHjzv4tCFvZSAEJu9ME/Ix5fFBv9wiN6v1eLWOlcfQIbda8+0rJRBY7DP1ylxxqs6DwJx2i27kHraifSA0CcR8V2EPwN5PgoHE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=QQioRq9lbQsykk/EVR2dBulsEWUsjO80rE5Ktnymo4mzrl+BlNpEsxZEMl68ZDV2YbTyHV5jG1cKT45r36nHICg+GuhxgRFGJWnyXnEJ5XTxoIzBM128DxHg1TvL9ePXnoEk+Bul5Nl5AkqpdzLCvmc8MLilC3rvisXPVS9NiI0= Received: by 10.78.134.2 with SMTP id h2mr5158889hud.1188996204165; Wed, 05 Sep 2007 05:43:24 -0700 (PDT) Received: by 10.78.146.6 with HTTP; Wed, 5 Sep 2007 05:43:23 -0700 (PDT) Message-ID: Date: Wed, 5 Sep 2007 13:43:24 +0100 From: "Peter Childs" Subject: Re: optimize query with a maximum(date) extraction Cc: pgsql-performance@postgresql.org In-Reply-To: <87642pqpue.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_46643_4902174.1188996204094" References: <302443.65402.qm@web26209.mail.ukl.yahoo.com> <87abs1qrhu.fsf@oxford.xeocode.com> <87642pqpue.fsf@oxford.xeocode.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/41 X-Sequence-Number: 26586 ------=_Part_46643_4902174.1188996204094 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 05/09/07, Gregory Stark wrote: > > "Gregory Stark" writes: > > > "JS Ubei" 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. ------=_Part_46643_4902174.1188996204094 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

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.



------=_Part_46643_4902174.1188996204094--