Re: Forcing order of Joins etc

Tom,
I don't think the increase in due to an analyse.
The database is static - I have a live server with the database on and I
take a daily copy to my laptop. So my laptop version is static during
the day - and I have been running the development tests of the script
against the laptop copy. So the increase in performance was against
static data.
Stupidly, I didn't save a copy of the explain plan before I changed the
'join_...' setting, so I don't know why its running so much faster.

I did then try running the script on the live server, but it seriously
affected performance, so I had to abort it. But I ran an 'explain'
version on the live and laptop and I can only see one major difference -
that is on how it reads the invoice header.

The explains are attached if they shows anything.

PS the live server is 8.0.8 and the laptop is 8.1.0

PPS Is there any chance that I'm seeing an illusion with the
'join_collate...' setting and that the real impact was caused by taking
the server down and bringing it back up again? That could then explain
the speed up and why it has remained faster.

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