From: Korisk <Korisk@yandex.ru>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: Craig Ringer <ringerc@ringerc.id.au>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAL_0b1v1yGzkLsnCS=8Cxh8awPa22F5OZYM4NcpvpYijFcABLA@mail.gmail.com>
References: <118871349885342@web16f.yandex.ru>
	<CAL_0b1tHHHGz=umGWm=FXkSkt+yCJh-Cb10BJLoD-i3mwvNeaQ@mail.gmail.com>
	<323831349928808@web5g.yandex.ru> <50764D55.2060401@ringerc.id.au>
	<53981349968513@web6f.yandex.ru>
	<CAL_0b1tap1pSQ78t482ynYN_2yBqpYQnmJAXNC3zS023+=Xqqg@mail.gmail.com>
	<246351350014151@web25e.yandex.ru>
	<CAL_0b1v1yGzkLsnCS=8Cxh8awPa22F5OZYM4NcpvpYijFcABLA@mail.gmail.com>
Subject: Re: hash aggregation
MIME-Version: 1.0
Message-Id: <270381350015278@web27h.yandex.ru>
Date: Fri, 12 Oct 2012 08:14:38 +0400
Content-Transfer-Encoding: 7bit
Content-Type: text/plain

Strange situation.
After indexscan enabling the cost is seriously decreased.

hashes=# set enable_bitmapscan=on;
SET
hashes=# explain  analyse verbose select name, count(name) as cnt from  hashcheck group by name order by name desc;
                                                                                        QUERY PLAN                                        
                                                
------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------
 GroupAggregate  (cost=10000000000.00..10000596612.97 rows=200 width=32) (actual time=0.187..7424.799 rows=4001 loops=1)
   Output: name, count(name)
   ->  Index Only Scan using hashcheck_name_rev_idx on public.hashcheck  (cost=10000000000.00..10000466660.96 rows=25990002 width=32) (act
ual time=0.166..3698.776 rows=25990002 loops=1)
         Output: name
         Heap Fetches: 0
 Total runtime: 7425.403 ms
(6 rows)

hashes=# set enable_indexscan=on;
SET
hashes=# explain  analyse verbose select name, count(name) as cnt from  hashcheck group by name order by name desc;
                                                                                QUERY PLAN                                                
                                 
------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------
 GroupAggregate  (cost=0.00..596612.97 rows=200 width=32) (actual time=0.148..7339.115 rows=4001 loops=1)
   Output: name, count(name)
   ->  Index Only Scan using hashcheck_name_rev_idx on public.hashcheck  (cost=0.00..466660.96 rows=25990002 width=32) (actual time=0.129.
.3653.848 rows=25990002 loops=1)
         Output: name
         Heap Fetches: 0
 Total runtime: 7339.592 ms
(6 rows)

hashes=# set enable_seqscan=on;
SET
hashes=# explain  analyse verbose select name, count(name) as cnt from  hashcheck group by name order by name desc;
                                                                  QUERY PLAN                                                              
     
------------------------------------------------------------------------------------------------------------------------------------------
-----
 Sort  (cost=565411.67..565412.17 rows=200 width=32) (actual time=21746.799..21747.026 rows=4001 loops=1)
   Output: name, (count(name))
   Sort Key: hashcheck.name
   Sort Method: quicksort  Memory: 315kB
   ->  HashAggregate  (cost=565402.03..565404.03 rows=200 width=32) (actual time=21731.551..21733.277 rows=4001 loops=1)
         Output: name, count(name)
         ->  Seq Scan on public.hashcheck  (cost=0.00..435452.02 rows=25990002 width=32) (actual time=29.431..13383.812 rows=25990002 loop
s=1)
               Output: id, name, value
 Total runtime: 21747.356 ms
(9 rows)