Re: Forcing index scan on query produces 16x faster

2010/3/25 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Wed, Mar 17, 2010 at 9:01 PM, Eger, Patrick <peger(at)automotive(dot)com> wrote:
>> I'm running 8.4.2 and have noticed a similar heavy preference for
>> sequential scans and hash joins over index scans and nested loops.  Our
>> database is can basically fit in cache 100% so this may not be
>> applicable to your situation, but the following params seemed to help
>> us:
>>
>> seq_page_cost = 1.0
>> random_page_cost = 1.01
>> cpu_tuple_cost = 0.0001
>> cpu_index_tuple_cost = 0.00005
>> cpu_operator_cost = 0.000025
>> effective_cache_size = 1000MB
>> shared_buffers = 1000MB
>>
>>
>> Might I suggest the Postgres developers reconsider these defaults for
>> 9.0 release, or perhaps provide a few sets of tuning params for
>> different workloads in the default install/docs? The cpu_*_cost in
>> particular seem to be way off afaict. I may be dead wrong though, fwiw
>> =)
>
> The default assume that the database is not cached in RAM.  If it is,
> you want to lower seq_page_cost and random_page_cost to something much
> smaller, and typically make them equal.  I often recommend 0.005, but
> I know others have had success with higher values.
>
> Ultimately it would be nice to have a better model of how data gets
> cached in shared_buffers and the OS buffer cache, but that is not so
> easy.

I have some work on this point with pgfincore project. Getting the
information from the OS is actualy a bit slow but possible. I try to
find time to finish my patch in order to get the info in the
pg_statio_* views :)

>
> ...Robert
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Cédric Villemain