Re: hash aggregation - Mailing list 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=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