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

Le Thursday 16 July 2009 23:54:54, Kevin Grittner a écrit :
> Marc Cousin <cousinmarc(at)gmail(dot)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

OK, thanks a lot.

A last thing :

As mentionned in another mail from the thread (from Richard Huxton), I felt
this message in the documentation a bit misleading :

effective_cache_size (integer)
Sets the planner's assumption about the effective size of the disk cache that
is available to a single query

I don't really know what the 'a single query' means. I interpreted that as
'divide it by the amount of queries typically running in parallel on the
database'. Maybe it should be rephrased ? (I may not be the one
misunderstanding it).