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@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
|
|