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

Marc Cousin wrote:
>
>> Your effective_cache_size is really small for the system you seem to have -
>> its the size of IO caching your os is doing and uses no resources itself.
>> And 800MB of that on a system with that amount of data seems a bit unlikely
>> ;-)
>>
>> Using `free` you can see the amount of io caching your OS is doing atm. in
>> the 'cached' column.
>>
>> That possibly might tip some plans in a direction you prefer.
>>
>> What kind of machine are you running this on?
>
> I played with this parameter too, and it didn't influence the plan. Anyway, the
> doc says it's the OS cache available for one query,

No they don't. I'm guessing you're getting mixed up with work_mem.

> and there may be a lot of
> insert queries at the same time, so I chose to be conservative with this
> value. I tried it with 8GB too, the plans were the same.
>
> The OS cache is around 8-10GB by the way.

That's what you need to set effective_cache_size to then.

--
Richard Huxton
Archonet Ltd