Tom,
I thought I'd try it on a backup server as well.
What this highlighted is the effect of caching?
I did the following:
Took an explain
Ran the query (took just over 4m)
Set the join_collapse_limit to 1
Ran the query and timed it (just under 1m)
Reset the join_collapse_limit to 8
Ran the query (just over 1m)
Took an explain
So that looked like the collapse limit wasn't being reset. Then I realised that the cache may be having an impact, so I ran a large query (select * on a table with 1million rows) - then:
Ran the query (took just over 4m)
Ran the query (took 26secs!)
So what I saw as a potential non-reset of the join collapse limit appears to be the effect of effective caching?
On Fri, 2008-10-03 at 08:38 -0400, Tom Lane wrote:
Steve T <steve@retsol.co.uk> writes:
> So in my case, I can now see that the join_collapse_limit has indeed
> been set back to 8 - but I'm still getting the improved query speed.
So that was in fact unrelated to your problem. Maybe it was just that
auto-analyze caught up with changes you'd made to the table contents?
regards, tom lane
Steve Tucknott
ReTSol Ltd
DDI: 01323 488548
|
|