Re: hash aggregation

On Thu, Oct 11, 2012 at 8:55 PM, Korisk <Korisk@yandex.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=25990002width=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



--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +14158679984