From: Cédric Villemain <cedric.villemain.debian@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Eger, Patrick <peger@automotive.com>
Cc: Christian Brink <cbrink@r-stream.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Forcing index scan on query produces 16x faster
Date: Sun, 28 Mar 2010 23:04:24 +0200
Message-ID: <e94e14cd1003281404y631617fdu1c9dd363747a8f@mail.gmail.com> (raw)
In-Reply-To: <603c8f071003241746i4713bc8fgc55357486d3f0bd3@mail.gmail.com>
References: <4BA148CF.5080701@r-stream.com>
<1CFD7891521AAB4E8201FB7A78C9D36F05453FD7@mail-001.corp.automotive.com>
<603c8f071003241746i4713bc8fgc55357486d3f0bd3@mail.gmail.com>
2010/3/25 Robert Haas <robertmhaas@gmail.com>:
> On Wed, Mar 17, 2010 at 9:01 PM, Eger, Patrick <peger@automotive.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Cédric Villemain
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cedric.villemain.debian@gmail.com, robertmhaas@gmail.com, peger@automotive.com, cbrink@r-stream.com
Subject: Re: Forcing index scan on query produces 16x faster
In-Reply-To: <e94e14cd1003281404y631617fdu1c9dd363747a8f@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox