Re: A questions on planner choices

Il 19/08/11 22:15, Scott Marlowe ha scritto:
> On Fri, Aug 19, 2011 at 1:05 PM, Edoardo Panfili<edoardo@aspix.it>  wrote:
>> [1] Plan for the firts query
>>
-------------------------------------------------------------------------------------------------------------------------------------------------------
>>   Sort  (cost=20.45..20.46 rows=1 width=931) (actual time=4457.775..4457.786
>> rows=76 loops=1)
>>    Sort Key: cartellino.id
>>    Sort Method:  quicksort  Memory: 74kB
>>    ->    Hash Join  (cost=8.32..20.44 rows=1 width=931) (actual
>> time=243.679..4457.658 rows=76 loops=1)
>>          Hash Cond: (cartellino.idspecie = principale.id)
>>          ->    Nested Loop  (cost=0.00..9.81 rows=614 width=886) (actual
>> time=4.094..4439.024 rows=18370 loops=1)
>
> The row estimate here is off by a factor of 30 or so.  In this case a
> different join method would likely work better.   It might be that
> cranking up stats for the columns involved will help, but if that
> doesn't change the estimates then we might need to look elsewhere.
>
> What's your work_mem and random_page_cost?
  work_mem = 1MB
  random_page_cost = 4

I am using an SSD but the production system uses a standard hard disk.

I did a try also with
set default_statistics_target=10000;
vacuum analyze cartellino;
vacuum analyze specie; -- the base table for specienomi
vacuum analyze confini_regioni;

but is always 4617.023 ms

Edoardo