Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 59F1F9F97F9 for ; Wed, 5 Sep 2007 10:11:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 99275-03 for ; Wed, 5 Sep 2007 10:11:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web26207.mail.ukl.yahoo.com (web26207.mail.ukl.yahoo.com [217.12.10.244]) by postgresql.org (Postfix) with SMTP id 396629F9D6F for ; Wed, 5 Sep 2007 10:11:33 -0300 (ADT) Received: (qmail 4004 invoked by uid 60001); 5 Sep 2007 13:11:31 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.fr; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=2s0egyzau25SUbRobpWo3ntxtBffOnPrrQbvcLxjhic5tq8B+XYjZE0z5wbsOTHpEgnoUGuGAhYp28CrY9W+kFZAzaqt/JqhtgB4KRGhGMb3qfzizzMmv+nHFRGPJNziIUMFaZepMvkxdV/iqogwScSdkK2u6Dpf1+Cfy40N+ug=; X-YMail-OSG: tW9E.BsVM1kSwvNrM8GE42nUz3ZrU2n1Ai_q.GPEEheYYSminsnbPrSuLiMofSZ454.LdsFy2yHqU5ceGobD2XlurBQw_eeAnm7C_UgIbazlcPLZ Received: from [194.254.156.248] by web26207.mail.ukl.yahoo.com via HTTP; Wed, 05 Sep 2007 13:11:31 GMT X-Mailer: YahooMailRC/651.50 YahooMailWebService/0.7.134 Date: Wed, 5 Sep 2007 13:11:31 +0000 (GMT) From: JS Ubei Subject: Re : optimize query with a maximum(date) extraction To: Gregory Stark Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Message-ID: <280073.2123.qm@web26207.mail.ukl.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/43 X-Sequence-Number: 26588 Great idea !=0A=0Awith your second solution, my query seem to use the index= on date. but the global performance is worse :-(=0A=0AI will keep th origi= nal solution !=0A=0ALot of thanks, Gregory=0A=0Ajsubei=0A=0A----- Message d= 'origine ----=0ADe : Gregory Stark =0A=C0 : JS Ubei= =0ACc : pgsql-performance@postgresql.org=0AEnvoy=E9 le : = Mercredi, 5 Septembre 2007, 14h06mn 01s=0AObjet : Re: optimize query with a= maximum(date) extraction=0A=0A"Gregory Stark" wri= tes:=0A=0A> "JS Ubei" writes:=0A>=0A>> I need to improve = a query like :=0A>>=0A>> SELECT id, min(the_date), max(the_date) FROM my_ta= ble GROUP BY id;=0A>...=0A> I don't think you'll find anything much faster = for this particular query. You=0A> could profile running these two (non-sta= ndard) queries:=0A>=0A> SELECT DISTINCT ON (id) id, the_date AS min_date FR= OM my_table ORDER BY id, the_date ASC=0A> SELECT DISTINCT ON (id) id, the_d= ate AS max_date FROM my_table ORDER BY id, the_date DESC=0A=0ASomething els= e you might try:=0A=0Aselect id, =0A (select min(the_date) from my_ta= ble where id=3Dx.id) as min_date,=0A (select max(the_date) from my_ta= ble where id=3Dx.id) as max_date=0A from (select distinct id from my_table= )=0A=0ARecent versions of Postgres do know how to use the index for a simpl= e=0Aungrouped min() or max() like these subqueries.=0A=0AThis would be even= better if you have a better source for the list of distinct=0Aids you're i= nterested in than my_table. If you have a source that just has one=0Arecord= for each id then you won't need an extra step to eliminate duplicates.=0A= =0A-- =0A Gregory Stark=0A EnterpriseDB http://www.enterprisedb.= com=0A=0A=0A=0A=0A=0A ________________________________________________= _____________________________ =0ANe gardez plus qu'une seule adresse mail != Copiez vos mails vers Yahoo! Mail