X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D3A5ED1B170 for ; Wed, 16 Jun 2004 12:53:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86965-08 for ; Wed, 16 Jun 2004 15:53:09 +0000 (GMT) Received: from trade-india.com (unknown [61.16.154.82]) by svr1.postgresql.org (Postfix) with SMTP id AD778D1B8C4 for ; Wed, 16 Jun 2004 12:52:58 -0300 (ADT) Received: (qmail 31492 invoked from network); 16 Jun 2004 15:36:34 -0000 Received: from valium.trade-india-local.com (HELO ?192.168.0.100?) (192.168.0.100) by system66.trade-india-local.com with SMTP; 16 Jun 2004 15:36:34 -0000 Message-ID: <40D06D1E.5000803@trade-india.com> Date: Wed, 16 Jun 2004 21:24:06 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: weired behavior... after pg_resetxlog-> dump->initdb-->reload. References: <40CFFC68.5090704@trade-india.com> <21524.1087394109@sss.pgh.pa.us> <40D06096.90200@trade-india.com> <22477.1087400110@sss.pgh.pa.us> In-Reply-To: <22477.1087400110@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------050007050100090405020303" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: * X-Archive-Number: 200406/205 X-Sequence-Number: 13865 This is a multi-part message in MIME format. --------------050007050100090405020303 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Dear Tom, It does. rt2=# explain SELECT name from users_sample group by name having count(*) > 1 ; +-----------------------------------------------------------------------+ | QUERY PLAN | +-----------------------------------------------------------------------+ | HashAggregate (cost=25.00..26.00 rows=200 width=78) | | Filter: (count(*) > 1) | | -> Seq Scan on users_sample (cost=0.00..20.00 rows=1000 width=78) | +-----------------------------------------------------------------------+ (3 rows) Time: 375.619 ms rt2=# explain analyze SELECT name from users_sample group by name having count(*) > 1 ; +------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +------------------------------------------------------------------------------------------------------------------------+ | HashAggregate (cost=25.00..26.00 rows=200 width=78) (actual time=567.981..567.981 rows=0 loops=1) | | Filter: (count(*) > 1) | | -> Seq Scan on users_sample (cost=0.00..20.00 rows=1000 width=78) (actual time=0.046..160.706 rows=100489 loops=1) | | Total runtime: 577.212 ms | +------------------------------------------------------------------------------------------------------------------------+ (4 rows) Tom Lane wrote: >Rajesh Kumar Mallah writes: > > >>here is something which was the original problem >>and is replicable. >> >> > >Hm. Does EXPLAIN show that the GROUP BY query is using hash >aggregation? Does its behavior change if you turn off enable_hashagg? > > regards, tom lane > > > --------------050007050100090405020303 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
Dear Tom,

It does.


rt2=# explain SELECT name from users_sample group by name having count(*) > 1 ;
+-----------------------------------------------------------------------+
|                              QUERY PLAN                               |
+-----------------------------------------------------------------------+
| HashAggregate  (cost=25.00..26.00 rows=200 width=78)                  |
|   Filter: (count(*) > 1)                                              |
|   ->  Seq Scan on users_sample  (cost=0.00..20.00 rows=1000 width=78) |
+-----------------------------------------------------------------------+
(3 rows)

Time: 375.619 ms
rt2=# explain analyze SELECT name from users_sample group by name having count(*) > 1 ;
+------------------------------------------------------------------------------------------------------------------------+
|                                                       QUERY PLAN                                                       |
+------------------------------------------------------------------------------------------------------------------------+
| HashAggregate  (cost=25.00..26.00 rows=200 width=78) (actual time=567.981..567.981 rows=0 loops=1)                     |
|   Filter: (count(*) > 1)                                                                                               |
|   ->  Seq Scan on users_sample  (cost=0.00..20.00 rows=1000 width=78) (actual time=0.046..160.706 rows=100489 loops=1) |
| Total runtime: 577.212 ms                                                                                              |
+------------------------------------------------------------------------------------------------------------------------+
(4 rows)





Tom Lane wrote:
Rajesh Kumar Mallah <mallah@trade-india.com> writes:
  
here is something which was the original problem
and is replicable.
    

Hm.  Does EXPLAIN show that the GROUP BY query is using hash
aggregation?  Does its behavior change if you turn off enable_hashagg?

			regards, tom lane

  

--------------050007050100090405020303--