pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSpeeding up SELECT MAX(),... GROUP BY ... ?
2+ messages / 2 participants
[nested] [flat]
* Speeding up SELECT MAX(),... GROUP BY ... ?
@ 1970-01-01 00:00 Christoph Haller <ch@rodos.fzk.de>
2002-05-22 23:48 ` Re: Speeding up SELECT MAX(),... GROUP BY ... ? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 2+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Speeding up SELECT MAX(),... GROUP BY ... ?
1970-01-01 00:00 Speeding up SELECT MAX(),... GROUP BY ... ? Christoph Haller <ch@rodos.fzk.de>
@ 2002-05-22 23:48 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2002-05-22 23:48 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-05-22 23:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 Speeding up SELECT MAX(),... GROUP BY ... ? Christoph Haller <ch@rodos.fzk.de>
2002-05-22 23:48 ` Josh Berkus <josh@agliodbs.com>
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