Re: hash aggregation

On 10/11/2012 12:13 PM, Korisk wrote:
> Thanx for the advice, but increment table is not acceptable because it should be a plenty of them.
> Nevertheless in the investigations was achieved some progress (7.4 sec vs 19.6 sec).
> But using IOS scan

"IOS scan" ?

Do you mean some kind of I/O monitoring tool?

> you can see that there is an abnormal cost calculations it make me suspicious of little bugs.

Abnormal how?

The cost estimates aren't times, I/Os, or anything you know, they're a
purely relative figure for comparing plan costs.

> hashes=# set enable_seqscan = off;
> SET

What's your seq_page_cost and random_page_cost?

> 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..10000528610.88 rows=200 width=32) (actual time=0.116..7452.005 rows=4001 loops=1)
> Output: name, count(name)
> -> Index Only Scan Backward using hashcheck_name_idx on public.hashcheck
^^^^^^^^^^^^^^^^^^^^^^^^
If you don't mind the increased cost of insert/update/delete try:

CREATE INDEX hashcheck_name_rev_idx
ON public.hashcheck (name DESC);

ie create the index in descending order.

--
Craig Ringer