Re: Forcing order of Joins etc

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