Re: Very big insert/join performance problem (bacula)

Marc Cousin <cousinmarc@gmail.com> wrote:

> to sum it up, should I keep these values (I hate doing this :) ) ?

Many people need to set the random_page_cost and/or seq_page_cost to
reflect the overall affect of caching on the active portion of the
data.  We set our fully-cached databases to 0.1 for both.  Databases
with less caching usually wind up at 2 and 1.  We have one database
which does best at 0.5 and 0.3.  My advice is to experiment and try to
find a pair of settings which works well for most or all of your
queries.  If you have a few which need a different setting, you can
set a special value right before running the query, but I've always
been able to avoid that (thankfully).

> Would there be a way to approximately evaluate them regarding to
> the expected buffer hit ratio of the query ?

Nothing query-specific except setting them on the connection right
before the query (and setting them back or discarding the connection
afterward).  Well, that and making sure that effective_cache_size
reflects reality.

-Kevin