<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<font face="Courier New,Courier">Your name
: Isabelle Therrien</font>
<br><font face="Courier New,Courier">Your email address
: therriei(at)cirano(dot)qc(dot)ca</font>
<br><font face="Courier New,Courier"></font> <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"> Architecture
: Intel Pentium</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier"> Operating System
: Linux</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier"> PostgreSQL version
: 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> <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"> FROM quotes,prodrefs,responses,orders</font>
<br><font face="Courier New,Courier"> WHERE quotes.negotiationGPK
= ?</font>
<br><font face="Courier New,Courier">
AND quotes.ctime = (SELECT MAX(ctime) FROM quotes WHERE negotiationgpk
= ?)</font>
<br><font face="Courier New,Courier">
AND prodrefs.negotiationGPK = ?</font>
<br><font face="Courier New,Courier">
AND orders.productreferenceGPK = prodrefs.gpk</font>
<br><font face="Courier New,Courier">
AND owner=u1 AND overridden=FALSE</font>
<br><font face="Courier New,Courier">
AND responses.orderGPK = orders.gpk</font>
<br><font face="Courier New,Courier">
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"> FROM quotes,prodrefs</font>
<br><font face="Courier New,Courier"> WHERE quotes.negotiationGPK
= ?</font>
<br><font face="Courier New,Courier">
AND quotes.ctime = (SELECT MAX(ctime) FROM quotes WHERE negotiationgpk
= ?)</font>
<br><font face="Courier New,Courier">
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">
Table "quotes"</font>
<br><font face="Courier New,Courier"> Attribute
| Type | Modifier</font>
<br><font face="Courier New,Courier">----------------+-----------+----------</font>
<br><font face="Courier New,Courier"> gpk
| bigint | not null</font>
<br><font face="Courier New,Courier"> xmldocument
| text | not null</font>
<br><font face="Courier New,Courier"> negotiationgpk | bigint
| not null</font>
<br><font face="Courier New,Courier"> ctime
| 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">
Table "prodrefs"</font>
<br><font face="Courier New,Courier"> Attribute
| Type | Modifier</font>
<br><font face="Courier New,Courier">----------------+-----------+----------</font>
<br><font face="Courier New,Courier"> gpk
| bigint | not null</font>
<br><font face="Courier New,Courier"> ctime
| timestamp | not null</font>
<br><font face="Courier New,Courier"> xmldocument
| text | not null</font>
<br><font face="Courier New,Courier"> negotiationgpk | bigint
| not null</font>
<br><font face="Courier New,Courier">Index: prodrefs_pkey</font>
<br><font face="Courier New,Courier"> </font>
<br><font face="Courier New,Courier">
Table "orders"</font>
<br><font face="Courier New,Courier"> Attribute
| Type | Modifier</font>
<br><font face="Courier New,Courier">---------------------+-----------+----------</font>
<br><font face="Courier New,Courier"> gpk
| bigint | not null</font>
<br><font face="Courier New,Courier"> ctime
| timestamp | not null</font>
<br><font face="Courier New,Courier"> productreferencegpk | bigint
|</font>
<br><font face="Courier New,Courier"> owner
| text | not null</font>
<br><font face="Courier New,Courier"> overridden
| boolean |</font>
<br><font face="Courier New,Courier"> overridingordergpk | bigint
|</font>
<br><font face="Courier New,Courier"> rawdata
| text | not null</font>
<br><font face="Courier New,Courier"> filteredorder
| text | not null</font>
<br><font face="Courier New,Courier"> previousquotegpk
| bigint |</font>
<br><font face="Courier New,Courier">Index: orders_pkey</font><font face="Courier New,Courier"></font>
<p><font face="Courier New,Courier">
Table "responses"</font>
<br><font face="Courier New,Courier"> Attribute |
Type | Modifier</font>
<br><font face="Courier New,Courier">-------------+-----------+----------</font>
<br><font face="Courier New,Courier"> gpk
| bigint | not null</font>
<br><font face="Courier New,Courier"> ctime
| timestamp | not null</font>
<br><font face="Courier New,Courier"> ordergpk |
bigint |</font>
<br><font face="Courier New,Courier"> xmldocument | text
| not null</font>
<br><font face="Courier New,Courier"> quotegpk |
bigint |</font>
<br><font face="Courier New,Courier">Index: responses_pkey</font>
<br><font face="Courier New,Courier"></font>
<br><font face="Courier New,Courier"></font>
<br><font face="Courier New,Courier"></font>
<br><font face="Courier New,Courier"></font> </html>