Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id D16C9476660
	for <pgsql-sql@postgresql.org>; Wed, 22 May 2002 11:27:28 -0400 (EDT)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by postgresql.org (Postfix) with SMTP id 13F67475923
	for <pgsql-sql@postgresql.org>; Wed, 22 May 2002 11:25:52 -0400 (EDT)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Wed May 22 17:25:51 2002 +0200
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id RAA27125 for <pgsql-sql@postgresql.org>; Wed, 22 May 2002 17:25:51 +0200 (METDST)
From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200205221525.RAA26917@rodos>
Subject: Speeding up SELECT MAX(),... GROUP BY ... ?
To: pgsql-sql@postgresql.org
Date: Wed, 22 May 2002 17:25:50 METDST
X-Mailer: Elm [revision: 212.4]
X-Archive-Number: 200205/246
X-Sequence-Number: 7881

Hi, 

I am having select statements which are running too slow. 
It's PostgreSQL 7.2.1 and a VACUUM ANALYZE was done. 
There are various where clauses restricting the output 
to timepoints and/or ids related to sources,locations,...
One statement looks like: 

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; 

NOTICE:  QUERY PLAN:

Aggregate  (cost=21044.07..22383.53 rows=13395 width=20)
  ->  Group  (cost=21044.07..22048.66 rows=133946 width=20)
        ->  Sort  (cost=21044.07..21044.07 rows=133946 width=20)
              ->  Seq Scan on onfvalue  (cost=0.00..5530.24 rows=133946 width=20)

Any ideas/alternatives to speed this up? 
The table 'onfvalue' is supposed to keep some million rows. 

Regards, Christoph