From: Craig Ringer <ringerc@ringerc.id.au>
To: Korisk <Korisk@yandex.ru>
Cc: Sergey Konoplev <gray.ru@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: hash aggregation
Date: Thu, 11 Oct 2012 12:38:45 +0800
Message-ID: <50764D55.2060401@ringerc.id.au> (raw)
In-Reply-To: <323831349928808@web5g.yandex.ru>
References: <118871349885342@web16f.yandex.ru>
<CAL_0b1tHHHGz=umGWm=FXkSkt+yCJh-Cb10BJLoD-i3mwvNeaQ@mail.gmail.com>
<323831349928808@web5g.yandex.ru>
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
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: ringerc@ringerc.id.au, Korisk@yandex.ru, gray.ru@gmail.com
Subject: Re: hash aggregation
In-Reply-To: <50764D55.2060401@ringerc.id.au>
* 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