Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TMWLr-00070Y-CD for pgsql-performance@postgresql.org; Fri, 12 Oct 2012 03:55:55 +0000 Received: from forward9.mail.yandex.net ([2a02:6b8:0:202::4]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TMWLp-0004MQ-7Z for pgsql-performance@postgresql.org; Fri, 12 Oct 2012 03:55:54 +0000 Received: from web25e.yandex.ru (web25e.yandex.ru [77.88.61.5]) by forward9.mail.yandex.net (Yandex) with ESMTP id 8D4E2CE1160; Fri, 12 Oct 2012 07:55:51 +0400 (MSK) Received: from 127.0.0.1 (localhost.localdomain [127.0.0.1]) by web25e.yandex.ru (Yandex) with ESMTP id 36923168800C; Fri, 12 Oct 2012 07:55:51 +0400 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex.ru; s=mail; t=1350014151; bh=28pnJL0iWP/sMZFZ7JufyPXCJTYQqoYLPVY42Wg/Cdk=; h=From:To:Cc:In-Reply-To:References:Subject:MIME-Version:Message-Id: Date:Content-Transfer-Encoding:Content-Type; b=pLD7FQC8oUwJD1ZwfxgqVp90ejcjqho07+VB74SXwse5uiPNZiUGacZva2xwMrYZp BbnUl/CSncllgFI0wSfmtXjSIylYtOq4FwUcQnqDqzxAy+TgN6ElQBJDNGFKjBXzuA pYTzbiteYOscgHSP9dQr295RaTAUWjeA9OJ08lV8= Received: from [188.168.10.0] ([188.168.10.0]) by web25e.yandex.ru with HTTP; Fri, 12 Oct 2012 07:55:51 +0400 From: Korisk To: Sergey Konoplev Cc: Craig Ringer , "pgsql-performance@postgresql.org" In-Reply-To: References: <118871349885342@web16f.yandex.ru> <323831349928808@web5g.yandex.ru> <50764D55.2060401@ringerc.id.au> <53981349968513@web6f.yandex.ru> Subject: Re: hash aggregation MIME-Version: 1.0 Message-Id: <246351350014151@web25e.yandex.ru> Date: Fri, 12 Oct 2012 07:55:51 +0400 X-Mailer: Yamail [ http://yandex.ru ] 5.0 Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.8 (-) X-Archive-Number: 201210/157 X-Sequence-Number: 48116 Again the same cost. hashes=3D# SELECT name, setting, reset_val FROM pg_settings WHERE setting= <> reset_val; name | setting | reset_val=20 -------------------------+----------------+----------- archive_command | (disabled) |=20 enable_bitmapscan | off | on enable_indexscan | off | on enable_seqscan | off | on log_file_mode | 0600 | 384 random_page_cost | 1 | 4 transaction_isolation | read committed | default unix_socket_permissions | 0777 | 511 (8 rows) hashes=3D# explain analyse verbose select name, count(name) as cnt from = hashcheck group by name order by name desc; = QUERY PLAN =20 =20 -------------------------------------------------------------------------= ----------------------------------------------------------------- ------------------------------------------------ GroupAggregate (cost=3D10000000000.00..10000596612.97 rows=3D200 width=3D= 32) (actual time=3D0.136..7272.240 rows=3D4001 loops=3D1) Output: name, count(name) -> Index Only Scan using hashcheck_name_rev_idx on public.hashcheck = (cost=3D10000000000.00..10000466660.96 rows=3D25990002 width=3D32) (act ual time=3D0.121..3624.624 rows=3D25990002 loops=3D1) Output: name Heap Fetches: 0 Total runtime: 7272.735 ms (6 rows) 11.10.2012, 21:55, "Sergey Konoplev" : > On Thu, Oct 11, 2012 at 8:15 AM, Korisk wrote: > >> =9AWhat's your seq_page_cost and random_page_cost? >> =9Ahashes=3D# SELECT name, setting, reset_val FROM pg_settings WHERE s= etting <> reset_val; >> =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A=9Aname =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A| = =9A=9A=9Asetting =9A=9A=9A=9A| reset_val >> =9A-------------------------+----------------+----------- >> =9A=9Aarchive_command =9A=9A=9A=9A=9A=9A=9A=9A| (disabled) =9A=9A=9A=9A= | >> =9A=9Aenable_bitmapscan =9A=9A=9A=9A=9A=9A| off =9A=9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A| on >> =9A=9Aenable_indexscan =9A=9A=9A=9A=9A=9A=9A| off =9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A| on >> =9A=9Aenable_seqscan =9A=9A=9A=9A=9A=9A=9A=9A=9A| off =9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A| on >> =9A=9Alog_file_mode =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A| 0600 =9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A| 384 >> =9A=9Arandom_page_cost =9A=9A=9A=9A=9A=9A=9A| 0.1 =9A=9A=9A=9A=9A=9A=9A= =9A=9A=9A=9A| 4 >> =9A=9Aseq_page_cost =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A| 0.1 =9A=9A=9A=9A=9A= =9A=9A=9A=9A=9A=9A| 1 >> =9A=9Atransaction_isolation =9A=9A| read committed | default >> =9A=9Aunix_socket_permissions | 0777 =9A=9A=9A=9A=9A=9A=9A=9A=9A=9A| 5= 11 > > Could you please try to set *_page_cost to 1 and then EXPLAIN ANALYZE i= t again? > >> =9A=9A=9A=9A-> =9AIndex Only Scan Backward using hashcheck_name_idx on= public.hashcheck >> =9A=9A(cost=3D10000000000.00..10000398674.92 rows=3D25986792 width=3D3= 2) >> =9A=9A(actual time=3D0.104..3785.767 rows=3D25990002 loops=3D1) > > I am just guessing but it might probably be some kind of a precision > bug, and I would like to check this. > >> =9A(9 rows) >> >> =9APostgresql 9.2.1 was configured and built with default settings. >> >> =9AThank you. > > -- > Sergey Konoplev > > a database and software architect > http://www.linkedin.com/in/grayhemp > > Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +14158679984