Message-ID: <50764D55.2060401@ringerc.id.au>
Date: Thu, 11 Oct 2012 12:38:45 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Korisk <Korisk@yandex.ru>
CC: Sergey Konoplev <gray.ru@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: hash aggregation
References: <118871349885342@web16f.yandex.ru>
	<CAL_0b1tHHHGz=umGWm=FXkSkt+yCJh-Cb10BJLoD-i3mwvNeaQ@mail.gmail.com>
	<323831349928808@web5g.yandex.ru>
In-Reply-To: <323831349928808@web5g.yandex.ru>
Content-Type: text/plain; charset=KOI8-R; format=flowed
Content-Transfer-Encoding: 7bit

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