Skip site navigation (1) Skip section navigation (2)

Re: Speeding up aggregates

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Speeding up aggregates
Date: 2002-12-07 01:54:43
Message-ID: 200212061754.43566.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: Fred MoyerDate: 2002-12-07 02:16:43
Subject: Query optimization
Previous:From: Josh BerkusDate: 2002-12-07 00:38:18
Subject: Re: query question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group