From: Korisk <Korisk@yandex.ru>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAL_0b1tHHHGz=umGWm=FXkSkt+yCJh-Cb10BJLoD-i3mwvNeaQ@mail.gmail.com>
References: <118871349885342@web16f.yandex.ru>
	<CAL_0b1tHHHGz=umGWm=FXkSkt+yCJh-Cb10BJLoD-i3mwvNeaQ@mail.gmail.com>
Subject: Re: hash aggregation
MIME-Version: 1.0
Message-Id: <323831349928808@web5g.yandex.ru>
Date: Thu, 11 Oct 2012 08:13:28 +0400
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: quoted-printable

Thanx for the advice, but increment table is not acceptable because it sh=
ould be a plenty of them.=20
Nevertheless in the investigations was achieved some progress (7.4 sec vs=
 19.6 sec).
But using IOS scan you can see that there is an abnormal cost calculation=
s it make me suspicious  of little bugs.

Thanks for your answer.


hashes=3D# \d hashcheck;
                              Table "public.hashcheck"
 Column |       Type        |                       Modifiers            =
           =20
--------+-------------------+--------------------------------------------=
------------
 id     | integer           | not null default nextval('hashcheck_id_seq'=
::regclass)
 name   | character varying |=20
 value  | character varying |=20
Indexes:
    "hashcheck_name_idx" btree (name)

hashes=3D# vacuum hashcheck;
VACUUM
hashes=3D# set random_page_cost=3D0.1;
SET
hashes=3D# set seq_page_cost=3D0.1;
SET

hashes=3D# explain analyse verbose select name, count(name) as cnt from  =
hashcheck group by name order by name desc;
                                                                 QUERY PL=
AN                                                              =20
-------------------------------------------------------------------------=
--------------------------------------------------------------------
 Sort  (cost=3D407366.72..407367.22 rows=3D200 width=3D32) (actual time=3D=
10712.505..10712.765 rows=3D4001 loops=3D1)
   Output: name, (count(name))
   Sort Key: hashcheck.name
   Sort Method: quicksort  Memory: 315kB
   ->  HashAggregate  (cost=3D407357.08..407359.08 rows=3D200 width=3D32)=
 (actual time=3D10702.285..10703.054 rows=3D4001 loops=3D1)
         Output: name, count(name)
         ->  Seq Scan on public.hashcheck  (cost=3D0.00..277423.12 rows=3D=
25986792 width=3D32) (actual time=3D0.054..2877.100 rows=3D25990002 loops=
=3D1)
               Output: id, name, value
 Total runtime: 10712.989 ms
(9 rows)

hashes=3D#  set enable_seqscan =3D off;
SET
hashes=3D# explain analyse verbose select name, count(name) as cnt from  =
hashcheck group by name order by name desc;
                                                                         =
                 QUERY PLAN                                     =20
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
---------------------------------------------
 GroupAggregate  (cost=3D10000000000.00..10000528610.88 rows=3D200 width=3D=
32) (actual time=3D0.116..7452.005 rows=3D4001 loops=3D1)
   Output: name, count(name)
   ->  Index Only Scan Backward using hashcheck_name_idx on public.hashch=
eck  (cost=3D10000000000.00..10000398674.92 rows=3D25986792 width=3D32)
 (actual time=3D0.104..3785.767 rows=3D25990002 loops=3D1)
         Output: name
         Heap Fetches: 0
 Total runtime: 7452.509 ms
(6 rows)

=E2=CC=C1=C7=CF=C4=C1=D2=D1 =DB=C1=CD=C1=CE=D3=D4=D7=C1=CD =CE=C1:
http://www.sql.ru/forum/actualthread.aspx?tid=3D974484

11.10.2012, 01:30, "Sergey Konoplev" <gray.ru@gmail.com>:
> On Wed, Oct 10, 2012 at 9:09 AM, Korisk <Korisk@yandex.ru> wrote:
>
>> =9AHello! Is it possible to speed up the plan?
>> =9A=9ASort =9A(cost=3D573977.88..573978.38 rows=3D200 width=3D32) (act=
ual time=3D10351.280..10351.551 rows=3D4000 loops=3D1)
>> =9A=9A=9A=9AOutput: name, (count(name))
>> =9A=9A=9A=9ASort Key: hashcheck.name
>> =9A=9A=9A=9ASort Method: quicksort =9AMemory: 315kB
>> =9A=9A=9A=9A-> =9AHashAggregate =9A(cost=3D573968.24..573970.24 rows=3D=
200 width=3D32) (actual time=3D10340.507..10341.288 rows=3D4000 loops=3D1=
)
>> =9A=9A=9A=9A=9A=9A=9A=9A=9A=9AOutput: name, count(name)
>> =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A-> =9ASeq Scan on public.hashcheck =9A(c=
ost=3D0.00..447669.16 rows=3D25259816 width=3D32) (actual time=3D0.019..2=
798.058 rows=3D25259817 loops=3D1)
>> =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9AOutput: id, name, valu=
e
>> =9A=9ATotal runtime: 10351.989 ms
>
> AFAIU there are no query optimization solution for this.
>
> It may be worth to create a table hashcheck_stat (name, cnt) and
> increment/decrement the cnt values with triggers if you need to get
> counts fast.
>
> --
> Sergey Konoplev
>
> a database and software architect
> http://www.linkedin.com/in/grayhemp
>
> Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +14158679984