Thread

Re: important decrease of performance using the BETA version in one particular case

Isabelle Therrien <therriei@lub.umontreal.ca> — 2001-03-20T15:49:27Z

Thanks, I will try that.  It is in fact possible that I vacuumed the tables after emptying them. I
couldn't imagine it had so much effect on the optimizer.  But it didn't happen just once, so I
wonder if the problem is only related to that.   
And thanks for the tip for my query, I will surely change it.

Isabelle Therrien




Tom Lane wrote:
> 
> Isabelle Therrien <therriei@LUB.UMontreal.CA> writes:
> > The tables are emptied often.  We don't keep these datas. So there's
> > never more than 50 tuples per table. And with this query, about 3-4
> > tuples are retrieved.
> 
> Well, it would appear that in the 7.1 installation, you last vacuumed
> the tables just after emptying them --- notice how all the cost
> estimates are nearly zero.  The 7.0 optimizer on the other hand is
> working with more reasonable cost values, and is presumably able to
> select a smarter plan because of that.
> 
> I'd suggest making a practice of vacuum analyzing the tables just before
> you empty them, not just after.  This may seem weird but it will leave
> the optimizer with appropriate statistics.
> 
> If you see a decrease in performance even when 7.1 and 7.0 are being
> given equivalent vacuum statistics, then I'd like to know more.
> 
> BTW, this query could be rewritten to be much more efficient by using
> outer joins and SELECT DISTINCT ON ... but that's not really relevant
> to the question of why 7.1 is slower than 7.0 for you ...
> 
>                         regards, tom lane