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