From: Josh Berkus <josh@agliodbs.com>
To: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Subject: Re: Speeding up SELECT MAX(),... GROUP BY ... ?
Date: Wed, 22 May 2002 16:48:39 -0700
Message-ID: <200205221648.39951.josh@agliodbs.com> (raw)
In-Reply-To: <200205221525.RAA26917@rodos>
References: <200205221525.RAA26917@rodos>
Chris,
> EXPLAIN
> select max(timepoint),lid,mid,sid from onfvalue
> where timepoint <= '2002-05-01 08:00:00' and sid in (1,3,5,7) group by
sid,lid,mid;
Having indexes on all four columns will help. Beyond that, there's not a lot
you can do; aggregate queries are slow. Rely on raw hardware power
(multi-channel SCSI, fast processor, lots of RAM and a very high sort_mem
setting).
BTW, if those four columns are the only data in the table, a Seq Scan is
actually the fastest method.
--
-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
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-sql@postgresql.org
Cc: josh@agliodbs.com, ch@rodos.fzk.de
Subject: Re: Speeding up SELECT MAX(),... GROUP BY ... ?
In-Reply-To: <200205221648.39951.josh@agliodbs.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