Thread: Speeding up SELECT MAX(),... GROUP BY ... ?

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=133946width=20)
 

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

Regards, Christoph 

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
informationtechnology     josh@agliodbs.com   and data management solutions     (415) 565-7293  for law firms, small
businesses     fax 621-2533   and non-profit organizations.     San Francisco