hash aggregation speedup

Поиск
Список
Период
Сортировка
От Korisk
Тема hash aggregation speedup
Дата
Msg-id 739641349631199@web11d.yandex.ru
обсуждение исходный текст
Список pgsql-performance
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=23103672loops=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


В списке pgsql-performance по дате отправления:

Предыдущее
От: "Anibal David Acosta"
Дата:
Сообщение: Re: how to avoid deadlock on masive update with multiples delete
Следующее
От: Undertaker Rude
Дата:
Сообщение: Re: Same query doing slow then quick