Skip site navigation (1) Skip section navigation (2)

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

From: Isabelle Therrien <therriei(at)LUB(dot)UMontreal(dot)CA>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: important decrease of performance using the BETA version in one particular case
Date: 2001-03-20 15:49:27
Message-ID: 3AB77C07.3726DB50@lub.umontreal.ca (view raw or flat)
Thread:
Lists: pgsql-bugs
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(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

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2001-03-20 21:09:13
Subject: Re: Doucumentation bug in libpq > Asynchronous Query Processing
Previous:From: Tom LaneDate: 2001-03-20 15:35:39
Subject: Re: important decrease of performance using the BETA version in one particular case

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group