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

Marc Cousin wrote:
> Le Thursday 16 July 2009 22:07:25, Kevin Grittner a écrit :
>> Marc Cousin <cousinmarc@gmail.com> wrote:
>>> the hot parts of these 2 tables are extremely likely to be in the
>>> database or linux cache (buffer hit rate was 97% in the example
>>> provided). Moreover, the first two queries of the insert procedure
>>> fill the cache for us...
>
> Ok, so to sum it up, should I keep these values (I hate doing this :) ) ?
> Would there be a way to approximately evaluate them regarding to the expected
> buffer hit ratio of the query ?

cached_buffer_cost = 0.01
effective_page_cost =
   ((1 - expected_cache_hit_ratio) * standard_page_cost)
+ (expected_cache_hit_ratio       * cached_buffer_cost)

If your assumption is only about these queries in particular, rather
than applicable across the board, you should set the page_costs just for
this query and reset them or close the connection after.

--
-Devin