[already sent a variant of that yesterday but it doesn't look like it
made it to the list]
Tom Lane wrote:
> Stefan Kaltenbrunner <stefan@kaltenbrunner.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.
took me a while due to hardware issues on my testbox - but there are new
results(with 6GB for effective_cache_size) up at:
http://www.kaltenbrunner.cc/files/5/
there are still a few issues with the validity of the run like the rf
tests not actually being done right - but lowering effective_cache_size
gave a dramtic speedup on Q5,Q7 and Q8.
that is the explain for the 4h+ Q9:
http://www.kaltenbrunner.cc/files/analyze_q9.txt
increasing the the statistic_target up to 1000 does not seem to change
the plan btw.
disabling nested loop leads to the following (4 times faster) plan:
http://www.kaltenbrunner.cc/files/analyze_q9_no_nest.txt
since the hash-joins in there look rather slow (inappropriate hashtable
set up due to the wrong estimates?) I disabled hash_joins too:
http://www.kaltenbrunner.cc/files/analyze_q9_no_nest_no_hashjoin.txt
and amazingly this plan is by far the fastest one in runtime (15min vs
4,5h ...) except that the planner thinks it is 20 times more expensive ...
Stefan
