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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Ondrej Ivanič | 2012-10-12 04:32:53 | Re: hash aggregation |
Previous Message | Sergey Konoplev | 2012-10-12 04:01:21 | Re: hash aggregation |