From: Korisk <Korisk@yandex.ru>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: Craig Ringer <ringerc@ringerc.id.au>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: hash aggregation
Date: Fri, 12 Oct 2012 07:55:51 +0400
Message-ID: <246351350014151@web25e.yandex.ru> (raw)
In-Reply-To: <CAL_0b1tap1pSQ78t482ynYN_2yBqpYQnmJAXNC3zS023+=Xqqg@mail.gmail.com>
References: <118871349885342@web16f.yandex.ru>
<CAL_0b1tHHHGz=umGWm=FXkSkt+yCJh-Cb10BJLoD-i3mwvNeaQ@mail.gmail.com>
<323831349928808@web5g.yandex.ru>
<50764D55.2060401@ringerc.id.au>
<53981349968513@web6f.yandex.ru>
<CAL_0b1tap1pSQ78t482ynYN_2yBqpYQnmJAXNC3zS023+=Xqqg@mail.gmail.com>
Again the same cost.
hashes=# SELECT name, setting, reset_val FROM pg_settings WHERE setting <> reset_val;
name | setting | reset_val
-------------------------+----------------+-----------
archive_command | (disabled) |
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=# explain analyse verbose select name, count(name) as cnt from hashcheck group by name order by name desc;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------
GroupAggregate (cost=10000000000.00..10000596612.97 rows=200 width=32) (actual time=0.136..7272.240 rows=4001 loops=1)
Output: name, count(name)
-> Index Only Scan using hashcheck_name_rev_idx on public.hashcheck (cost=10000000000.00..10000466660.96 rows=25990002 width=32) (act
ual time=0.121..3624.624 rows=25990002 loops=1)
Output: name
Heap Fetches: 0
Total runtime: 7272.735 ms
(6 rows)
11.10.2012, 21:55, "Sergey Konoplev" <gray.ru@gmail.com>:
> On Thu, Oct 11, 2012 at 8:15 AM, Korisk <Korisk@yandex.ru> wrote:
>
>> What's your seq_page_cost and random_page_cost?
>> hashes=# SELECT name, setting, reset_val FROM pg_settings WHERE setting <> reset_val;
>> name | setting | reset_val
>> -------------------------+----------------+-----------
>> archive_command | (disabled) |
>> enable_bitmapscan | off | on
>> enable_indexscan | off | on
>> enable_seqscan | off | on
>> log_file_mode | 0600 | 384
>> random_page_cost | 0.1 | 4
>> seq_page_cost | 0.1 | 1
>> transaction_isolation | read committed | default
>> unix_socket_permissions | 0777 | 511
>
> Could you please try to set *_page_cost to 1 and then EXPLAIN ANALYZE it again?
>
>> -> Index Only Scan Backward using hashcheck_name_idx on public.hashcheck
>> (cost=10000000000.00..10000398674.92 rows=25986792 width=32)
>> (actual time=0.104..3785.767 rows=25990002 loops=1)
>
> I am just guessing but it might probably be some kind of a precision
> bug, and I would like to check this.
>
>> (9 rows)
>>
>> Postgresql 9.2.1 was configured and built with default settings.
>>
>> Thank you.
>
> --
> Sergey Konoplev
>
> a database and software architect
> http://www.linkedin.com/in/grayhemp
>
> Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +14158679984
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: Korisk@yandex.ru, gray.ru@gmail.com, ringerc@ringerc.id.au
Subject: Re: hash aggregation
In-Reply-To: <246351350014151@web25e.yandex.ru>
* 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