Re: -HEAD planner issue wrt hash_joins on dbt3 ?

On Wed, Sep 13, 2006 at 10:47:09AM -0400, Tom Lane wrote:
> Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> writes:
> > Tom Lane wrote:
> >> Apparently we've made the planner a bit too optimistic about the savings
> >> that can be expected from repeated indexscans occurring on the inside of
> >> a join.
>
> > effective_cache_size was set to 10GB(my fault for copying over the conf
> > from a 16GB box) during the run - lowering it just a few megabytes(!) or
> > to a more realistic 6GB results in the following MUCH better plan:
> > http://www.kaltenbrunner.cc/files/dbt3_explain_analyze2.txt
>
> Interesting. It used to be that effective_cache_size wasn't all
> that critical... what I think this report is showing is that with
> the 8.2 changes to try to account for caching effects in repeated
> indexscans, we've turned that into a pretty significant parameter.
>
> It'd be nice not to have to depend on the DBA to give us a good
> number for this setting. But I don't know of any portable ways to
> find out how much RAM is in the box, let alone what fraction of it
> we should assume is available per-query.

That's fairly straight-forward, if a little crude. We ask the DBA and
provide some tools for estimating and tuning same. :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!