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

Isabelle Therrien <therriei(at)LUB(dot)UMontreal(dot)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