From: Korisk <Korisk@yandex.ru>
To: pgsql-performance@postgresql.org
Subject: hash aggregation speedup
Date: Sun, 07 Oct 2012 21:33:19 +0400
Message-ID: <739641349631199@web11d.yandex.ru> (raw)
I have table:
create table hashcheck(id serial, name varchar, value varchar);
and query:
hashaggr=# explain analyse verbose select name, count(name) as cnt from hashcheck group by name order by name desc;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=471979.50..471985.40 rows=2362 width=9) (actual time=19642.938..19643.184 rows=4001 loops=1)
Output: name, (count(name))
Sort Key: hashcheck.name
Sort Method: quicksort Memory: 343kB
-> HashAggregate (cost=471823.53..471847.15 rows=2362 width=9) (actual time=19632.256..19632.995 rows=4001 loops=1)
Output: name, count(name)
-> Seq Scan on public.hashcheck (cost=0.00..363494.69 rows=21665769 width=9) (actual time=49.552..11674.170 rows=23103672 loops=1)
Output: id, name, value
Total runtime: 19643.497 ms
(9 rows)
without indexes.
Indexes don't speedup the query much
For hash Total runtime: 17678.225 ms
For btree Total runtime: 14188.484 ms
I'm don't know how to use Gin and gist this way.
So the question is there any way to speed up the "group by" query? Or may be there does exists any other way to count histogram?
Thank you.
PS:
hashaggr=# select version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.7.2 20120921 (Red Hat 4.7.2-2), 64-bit
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
Subject: Re: hash aggregation speedup
In-Reply-To: <739641349631199@web11d.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