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