Re: Forcing order of Joins etc

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

In response to

Browse pgsql-novice by date

  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