Re: Speeding up aggregates

Tom,

We have a winner on simple aggregates:

Version 7.2.3:
explain analyze select client_id, count(*) from case_clients group by
client_id;
NOTICE: QUERY PLAN:

Aggregate (cost=11892.51..12435.75 rows=10865 width=4) (actual
time=1162.27..1569.40 rows=436 loops=1)
-> Group (cost=11892.51..12164.13 rows=108648 width=4) (actual
time=1162.24..1477.70 rows=108648 loops=1)
-> Sort (cost=11892.51..11892.51 rows=108648 width=4) (actual
time=1162.22..1280.64 rows=108648 loops=1)
-> Seq Scan on case_clients (cost=0.00..2804.48 rows=108648
width=4) (actual time=0.07..283.14 rows=108648 loops=1)
Total runtime: 2387.87 msec

Versus Version 7.4devel:
explain analyze select client_id, count(*) from case_clients group by
client_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=3289.72..3289.84 rows=46 width=4) (actual
time=447.80..448.71 rows=436 loops=1)
-> Seq Scan on case_clients (cost=0.00..2746.48 rows=108648 width=4)
(actual time=0.08..267.45 rows=108648 loops=1)
Total runtime: 473.77 msec
(3 rows)

However, more complex queries involving aggregates seem to be unable to make
use of the hashaggregate. I'll get back to you when I know what the
breakpoint is.

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco