>>> On Mon, Sep 10, 2007 at 2:25 PM, in message <fc45mi$1tj9$1(at)news(dot)hub(dot)org>,
"Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca> wrote:
> is the default setting of 4.0 realistic or could it be lower?
Wow, such a simple, innocent question.
As you may have inferred, it can't be answered in isolation. Make sure that
you have reviewed all of your memory settings, then try adjusting this and
seeing what the results are. With accurate effective_cache_size and a fairly
generous work_mem setting, we have found that these settings work best for us
with our actual production loads:
(1) Cache well below database size (for example 6 GB or 12 GB RAM on a box
running a 210 GB database):
#seq_page_cost = 1.0
random_page_cost = 2.0
(2) On a database which is entirely contained within cache:
seq_page_cost = 0.1
random_page_cost = 0.1
(3) Where caching is very significant, but not complete, we have to test
to see where performance is best. One example that significantly beat both
of the above in production on a particular box:
seq_page_cost = 0.3
random_page_cost = 0.5
So, the short answer to your question is that the default might be realistic
in some environments; the best choice will be lower in many environments;
the best choice will be higher in some environments; only testing your
actual applications in your actual environment can tell you which is the
case for you.
My approach is to pick one of the first two, depending on whether the
database will be fully cached, then monitor for performance problems. When
the queries with unacceptable response time have been identified, I look
for ways to improve them. One of the things I may try, where a bad plan
seems to have been chosen, is to adjust the random page cost. If I do
change that in production, then I closely monitor for regression in other
queries.
-Kevin