Re: weired behavior... after pg_resetxlog-> dump->initdb-->reload.

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