On Wed, Sep 13, 2006 at 10:47:09AM -0400, Tom Lane wrote: > Stefan Kaltenbrunner <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
