From: Korisk <Korisk@yandex.ru>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: Craig Ringer <ringerc@ringerc.id.au>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: hash aggregation
Date: Fri, 12 Oct 2012 08:14:38 +0400
Message-ID: <270381350015278@web27h.yandex.ru> (raw)
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>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Korisk@yandex.ru, gray.ru@gmail.com, ringerc@ringerc.id.au
Subject: Re: hash aggregation
In-Reply-To: <270381350015278@web27h.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox