Re: hash aggregation

From: Korisk <Korisk(at)yandex(dot)ru>
To: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
Cc: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: hash aggregation
Date: 2012-10-12 03:55:51
Message-ID: 246351350014151@web25e.yandex.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(dot)ru(at)gmail(dot)com>:
> On Thu, Oct 11, 2012 at 8:15 AM, Korisk <Korisk(at)yandex(dot)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(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +14158679984

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Sergey Konoplev 2012-10-12 04:01:21 Re: hash aggregation
Previous Message Sergey Konoplev 2012-10-11 22:31:13 Re: Drawbacks of create index where is not null ?