Re: hash aggregation - Mailing list pgsql-performance

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=25990002width=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)
(actualtime=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=25990002loop 
s=1)
               Output: id, name, value
 Total runtime: 21747.356 ms
(9 rows)