Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 1CF204758F6 for ; Wed, 22 May 2002 19:46:40 -0400 (EDT) Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id AC9104758DD for ; Wed, 22 May 2002 19:46:33 -0400 (EDT) Received: from [209.122.114.82] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.5.9) with ESMTP id 1468076; Wed, 22 May 2002 16:46:35 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Christoph Haller , pgsql-sql@postgresql.org Subject: Re: Speeding up SELECT MAX(),... GROUP BY ... ? Date: Wed, 22 May 2002 16:48:39 -0700 X-Mailer: KMail [version 1.4] References: <200205221525.RAA26917@rodos> In-Reply-To: <200205221525.RAA26917@rodos> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200205221648.39951.josh@agliodbs.com> X-Archive-Number: 200205/248 X-Sequence-Number: 7883 Chris, > EXPLAIN=20 > select max(timepoint),lid,mid,sid from onfvalue=20 > where timepoint <=3D '2002-05-01 08:00:00' and sid in (1,3,5,7) group= by=20 sid,lid,mid;=20 Having indexes on all four columns will help. Beyond that, there's not a l= ot=20 you can do; aggregate queries are slow. Rely on raw hardware power=20 (multi-channel SCSI, fast processor, lots of RAM and a very high sort_mem= =20 setting). BTW, if those four columns are the only data in the table, a Seq Scan is=20 actually the fastest method.=20=20=20 --=20 -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco