Re: hash aggregation

On Thu, Oct 11, 2012 at 8:55 PM, Korisk <Korisk(at)yandex(dot)ru> wrote:
> hashes=# explain analyse verbose select name, count(name) as cnt from hashcheck group by name order by name desc;

Now set enable_bitmapscan and enable_indexscan to on an try it again.

Then set enable_seqscan to on and run it one more time.

> 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

--
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