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

important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case

From: Isabelle Therrien <therriei(at)LUB(dot)UMontreal(dot)CA>
To: pgsql-bugs(at)postgresql(dot)org
Cc: rgl(at)cirano(dot)qc(dot)ca
Subject: important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case
Date: 2001-03-19 22:54:44
Message-ID: 3AB68E34.10C57DA4@lub.umontreal.ca (view raw or flat)
Thread:
Lists: pgsql-bugs
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<font face="Courier New,Courier">Your name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Isabelle Therrien</font>
<br><font face="Courier New,Courier">Your email address&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; therriei(at)cirano(dot)qc(dot)ca</font>
<br><font face="Courier New,Courier"></font>&nbsp;<font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">System Configuration</font>
<br><font face="Courier New,Courier">---------------------</font>
<br><font face="Courier New,Courier">&nbsp; Architecture&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
: Intel Pentium</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">&nbsp; Operating System&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
: Linux</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">&nbsp; PostgreSQL version&nbsp;&nbsp;&nbsp;
: PostgreSQL-7.1 Beta (but I use JDBC drivers v7.0.2)</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">I'm using Weblogic Server 5.1</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">Please enter a FULL description of
your problem:</font>
<br><font face="Courier New,Courier">------------------------------------------------</font>
<br><font face="Courier New,Courier">I observed an important decrease of
performance using the BETA version of PostgreSQL in one particular case:</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">I have a big query, reported below,
that is called several times in my application.</font>
<br><font face="Courier New,Courier">At least 4 active connections call
it at the same time.</font>
<br><font face="Courier New,Courier">Normally, this query is executed in
about 30-50 milliseconds.</font>
<br><font face="Courier New,Courier">But after a while (depending on how
many connections are used, and how often the query is called),</font>
<br><font face="Courier New,Courier">the query is executed in 1000ms, then
2000ms, and it continues to grow exponentially. I've already seen it reaching
80 seconds.</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">While having these time reports, I
tried to query the database directly using "psql", and I got the same result
(around 80 sec)</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">I compared this to an older version
(7.0.2) of PostgreSQL, in the same circumstances, and the worst</font>
<br><font face="Courier New,Courier">I had was a 4000ms, without going
any further. That's why I suppose the problem comes from the BETA version.</font>
<br><font face="Courier New,Courier"></font>&nbsp;<font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">----------</font>
<br><font face="Courier New,Courier">The query: (the ? are replaced by
'GPK', GPK being the key we want to look for)</font>
<br><font face="Courier New,Courier">----------</font>
<br><font face="Courier New,Courier">SELECT quotes.xmldocument, prodrefs.xmldocument,
orders.filteredorder, responses.xmldocument</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp; FROM quotes,prodrefs,responses,orders</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp; WHERE quotes.negotiationGPK
= ?</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AND quotes.ctime = (SELECT MAX(ctime) FROM quotes WHERE negotiationgpk
= ?)</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AND prodrefs.negotiationGPK = ?</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AND orders.productreferenceGPK = prodrefs.gpk</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AND owner=u1 AND overridden=FALSE</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AND responses.orderGPK = orders.gpk</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AND responses.ctime = (SELECT MAX(ctime) FROM responses WHERE ordergpk=orders.gpk)</font>
<br><font face="Courier New,Courier">UNION</font>
<br><font face="Courier New,Courier">SELECT quotes.xmldocument,prodrefs.xmldocument,(''),('')</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp; FROM quotes,prodrefs</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp; WHERE quotes.negotiationGPK
= ?</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AND quotes.ctime = (SELECT MAX(ctime) FROM quotes WHERE negotiationgpk
= ?)</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AND prodrefs.negotiationGPK = ?</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">-----------</font>
<br><font face="Courier New,Courier">The tables:</font>
<br><font face="Courier New,Courier">-----------</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Table "quotes"</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp; Attribute&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp; | Modifier</font>
<br><font face="Courier New,Courier">----------------+-----------+----------</font>
<br><font face="Courier New,Courier">&nbsp;gpk&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| bigint&nbsp;&nbsp;&nbsp; | not null</font>
<br><font face="Courier New,Courier">&nbsp;xmldocument&nbsp;&nbsp;&nbsp;
| text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null</font>
<br><font face="Courier New,Courier">&nbsp;negotiationgpk | bigint&nbsp;&nbsp;&nbsp;
| not null</font>
<br><font face="Courier New,Courier">&nbsp;ctime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| timestamp | not null</font>
<br><font face="Courier New,Courier">Index: quotes_pkey</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Table "prodrefs"</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp; Attribute&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp; | Modifier</font>
<br><font face="Courier New,Courier">----------------+-----------+----------</font>
<br><font face="Courier New,Courier">&nbsp;gpk&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| bigint&nbsp;&nbsp;&nbsp; | not null</font>
<br><font face="Courier New,Courier">&nbsp;ctime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| timestamp | not null</font>
<br><font face="Courier New,Courier">&nbsp;xmldocument&nbsp;&nbsp;&nbsp;
| text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null</font>
<br><font face="Courier New,Courier">&nbsp;negotiationgpk | bigint&nbsp;&nbsp;&nbsp;
| not null</font>
<br><font face="Courier New,Courier">Index: prodrefs_pkey</font>
<br><font face="Courier New,Courier">&nbsp;</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Table "orders"</font>
<br><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Attribute&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp; | Modifier</font>
<br><font face="Courier New,Courier">---------------------+-----------+----------</font>
<br><font face="Courier New,Courier">&nbsp;gpk&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| bigint&nbsp;&nbsp;&nbsp; | not null</font>
<br><font face="Courier New,Courier">&nbsp;ctime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| timestamp | not null</font>
<br><font face="Courier New,Courier">&nbsp;productreferencegpk | bigint&nbsp;&nbsp;&nbsp;
|</font>
<br><font face="Courier New,Courier">&nbsp;owner&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null</font>
<br><font face="Courier New,Courier">&nbsp;overridden&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| boolean&nbsp;&nbsp; |</font>
<br><font face="Courier New,Courier">&nbsp;overridingordergpk&nbsp; | bigint&nbsp;&nbsp;&nbsp;
|</font>
<br><font face="Courier New,Courier">&nbsp;rawdata&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null</font>
<br><font face="Courier New,Courier">&nbsp;filteredorder&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null</font>
<br><font face="Courier New,Courier">&nbsp;previousquotegpk&nbsp;&nbsp;&nbsp;
| bigint&nbsp;&nbsp;&nbsp; |</font>
<br><font face="Courier New,Courier">Index: orders_pkey</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Table "responses"</font>
<br><font face="Courier New,Courier">&nbsp; Attribute&nbsp; |&nbsp;&nbsp;
Type&nbsp;&nbsp;&nbsp; | Modifier</font>
<br><font face="Courier New,Courier">-------------+-----------+----------</font>
<br><font face="Courier New,Courier">&nbsp;gpk&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| bigint&nbsp;&nbsp;&nbsp; | not null</font>
<br><font face="Courier New,Courier">&nbsp;ctime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| timestamp | not null</font>
<br><font face="Courier New,Courier">&nbsp;ordergpk&nbsp;&nbsp;&nbsp; |
bigint&nbsp;&nbsp;&nbsp; |</font>
<br><font face="Courier New,Courier">&nbsp;xmldocument | text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| not null</font>
<br><font face="Courier New,Courier">&nbsp;quotegpk&nbsp;&nbsp;&nbsp; |
bigint&nbsp;&nbsp;&nbsp; |</font>
<br><font face="Courier New,Courier">Index: responses_pkey</font>
<br><font face="Courier New,Courier"></font>&nbsp;
<br><font face="Courier New,Courier"></font>&nbsp;
<br><font face="Courier New,Courier"></font>&nbsp;
<br><font face="Courier New,Courier"></font>&nbsp;</html>



Attachment: unknown_filename
Description: text/html (9.8 KB)

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-03-19 23:26:10
Subject: Re: important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case
Previous:From: Tom LaneDate: 2001-03-19 20:10:05
Subject: Re: Some regression tests are failed RH7.0

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