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

On Thu, Jul 16, 2009 at 6:30 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:
> Marc Cousin <cousinmarc@gmail.com> wrote:
>
>> 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).
>
> I'm afraid I'll have to let someone else speak to that; I only have a
> vague sense of its impact.  I've generally gotten good results setting
> that to the available cache space on the machine.  If I'm running
> multiple database clusters on one machine, I tend to hedge a little
> and set it lower to allow for some competition.

It really has very little impact.  It only affects index scans, and
even then only if effective_cache_size is less than the size of the
table.

Essentially, when this kicks in, it models the effect that if you are
index scanning a table much larger than the size of your cache, you
might have to reread some blocks that you previously read in during
*that same index scan*.

...Robert