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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Isabelle Therrien <therriei(at)LUB(dot)UMontreal(dot)CA>
Cc: 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:35:39
Message-ID: 10842.985102539@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Isabelle TherrienDate: 2001-03-20 15:49:27
Subject: Re: important decrease of performance using the BETA version in one particular case
Previous:From: Isabelle TherrienDate: 2001-03-20 14:53:45
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