Skip site navigation (1) Skip section navigation (2)

Re: hash aggregation

From: Korisk <Korisk(at)yandex(dot)ru>
To: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
Cc: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>,"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: hash aggregation
Date: 2012-10-12 04:14:38
Message-ID: 270381350015278@web27h.yandex.ru (view raw or flat)
Thread:
Lists: 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=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)







In response to

Responses

pgsql-performance by date

Next:From: Ondrej IvaničDate: 2012-10-12 04:32:53
Subject: Re: hash aggregation
Previous:From: Sergey KonoplevDate: 2012-10-12 04:01:21
Subject: Re: hash aggregation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group