From: | Steve T <steve(at)retsol(dot)co(dot)uk> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostGreSQL <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Forcing order of Joins etc |
Date: | 2008-10-03 14:12:01 |
Message-ID: | 1223043121.3598.77.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)retsol(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Turner | 2008-10-03 17:24:42 | quiet restore |
Previous Message | Steve T | 2008-10-03 12:52:58 | Re: Forcing order of Joins etc |