Skip site navigation (1) Skip section navigation (2)

Re: Forcing index scan on query produces 16x faster

From: Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: "Eger, Patrick" <peger(at)automotive(dot)com>, Christian Brink <cbrink(at)r-stream(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Forcing index scan on query produces 16x faster
Date: 2010-03-28 21:04:24
Message-ID: e94e14cd1003281404y631617fdu1c9dd363747a8f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2010-03-29 04:12:53
Subject: Re: [PERFORM] Database size growing over time and leads to performance impact
Previous:From: Tom LaneDate: 2010-03-28 17:37:34
Subject: Re: Optimizer showing wrong rows in plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group