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 C05DCD1B267 for ; Thu, 17 Jun 2004 00:42:48 -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 32642-06 for ; Thu, 17 Jun 2004 03:42:43 +0000 (GMT) Received: from trade-india.com (unknown [61.16.154.82]) by svr1.postgresql.org (Postfix) with SMTP id B3438D1B170 for ; Thu, 17 Jun 2004 00:42:36 -0300 (ADT) Received: (qmail 22127 invoked from network); 17 Jun 2004 03:26:06 -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; 17 Jun 2004 03:26:06 -0000 Message-ID: <40D1136F.3040708@trade-india.com> Date: Thu, 17 Jun 2004 09:13:43 +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> <40D06D1E.5000803@trade-india.com> <23874.1087409853@sss.pgh.pa.us> In-Reply-To: <23874.1087409853@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------030205090706030005030206" 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/214 X-Sequence-Number: 13874 This is a multi-part message in MIME format. --------------030205090706030005030206 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >Rajesh Kumar Mallah writes: > > >>It does. >> >> > >And? > > > Yep the problem of original posting could be replicated on disabling hash aggregates. On disabling i could get the repeated rows. Regds Mallah. rt2=# SET enable_hashagg TO off; SET Time: 329.533 ms rt2=# explain analyze SELECT name from users_sample group by name having count(*) > 1 ; +------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +------------------------------------------------------------------------------------------------------------------------------+ | GroupAggregate (cost=69.83..78.33 rows=200 width=78) (actual time=2411.411..2749.733 rows=4 loops=1) | | Filter: (count(*) > 1) | | -> Sort (cost=69.83..72.33 rows=1000 width=78) (actual time=2411.300..2532.821 rows=100489 loops=1) | | Sort Key: name | | -> Seq Scan on users_sample (cost=0.00..20.00 rows=1000 width=78) (actual time=0.024..170.258 rows=100489 loops=1) | | Total runtime: 2915.439 ms | +------------------------------------------------------------------------------------------------------------------------------+ (6 rows) Time: 3265.102 ms rt2=# SELECT name from users_sample group by name having count(*) > 1 ; +----------------------+ | name | +----------------------+ | ??? | | ????163.com | | ?@hotmail.com | | p? | +----------------------+ (4 rows) Time: 3358.030 ms rt2=# SET enable_hashagg TO on; SET Time: 330.148 ms rt2=# SELECT name from users_sample group by name having count(*) > 1 ; +------+ | name | +------+ +------+ (0 rows) Time: 752.395 ms rt2=# >>Tom Lane wrote: >> >> >>>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 > > > --------------030205090706030005030206 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote:




Rajesh Kumar Mallah <mallah@trade-india.com> writes:
  
It does.
    

And?

  
Yep the problem of original posting could be replicated on
disabling hash aggregates. On disabling i could get the repeated rows.


Regds
Mallah.


rt2=# SET enable_hashagg TO off;
SET
Time: 329.533 ms
rt2=# explain analyze SELECT name from users_sample group by name having count(*) > 1 ;
+------------------------------------------------------------------------------------------------------------------------------+
|                                                          QUERY PLAN                                                          |
+------------------------------------------------------------------------------------------------------------------------------+
| GroupAggregate  (cost=69.83..78.33 rows=200 width=78) (actual time=2411.411..2749.733 rows=4 loops=1)                        |
|   Filter: (count(*) > 1)                                                                                                     |
|   ->  Sort  (cost=69.83..72.33 rows=1000 width=78) (actual time=2411.300..2532.821 rows=100489 loops=1)                      |
|         Sort Key: name                                                                                                       |
|         ->  Seq Scan on users_sample  (cost=0.00..20.00 rows=1000 width=78) (actual time=0.024..170.258 rows=100489 loops=1) |
| Total runtime: 2915.439 ms                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------+
(6 rows)

Time: 3265.102 ms
rt2=# SELECT name from users_sample group by name having count(*) > 1 ;
+----------------------+
|         name         |
+----------------------+
| ���           |
| ����163.com     |
| �@hotmail.com |
| p�                |
+----------------------+
(4 rows)

Time: 3358.030 ms
rt2=# SET enable_hashagg TO on;
SET
Time: 330.148 ms
rt2=# SELECT name from users_sample group by name having count(*) > 1 ;
+------+
| name |
+------+
+------+
(0 rows)

Time: 752.395 ms
rt2=#









  
Tom Lane wrote:
    
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

  

--------------030205090706030005030206--