Thread: important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case
important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case
From
Isabelle Therrien
Date:
Your name
: Isabelle Therrien
Your email address
: therriei@cirano.qc.ca
System Configuration
---------------------
Architecture
: Intel Pentium
Operating System
: Linux
PostgreSQL version
: PostgreSQL-7.1 Beta (but I use JDBC drivers v7.0.2)
I'm using Weblogic Server 5.1
Please enter a FULL description of
your problem:
------------------------------------------------
I observed an important decrease of
performance using the BETA version of PostgreSQL in one particular case:
I have a big query, reported below,
that is called several times in my application.
At least 4 active connections call
it at the same time.
Normally, this query is executed in
about 30-50 milliseconds.
But after a while (depending on how
many connections are used, and how often the query is called),
the query is executed in 1000ms, then
2000ms, and it continues to grow exponentially. I've already seen it reaching
80 seconds.
While having these time reports, I
tried to query the database directly using "psql", and I got the same result
(around 80 sec)
I compared this to an older version
(7.0.2) of PostgreSQL, in the same circumstances, and the worst
I had was a 4000ms, without going
any further. That's why I suppose the problem comes from the BETA version.
----------
The query: (the ? are replaced by
'GPK', GPK being the key we want to look for)
----------
SELECT quotes.xmldocument, prodrefs.xmldocument,
orders.filteredorder, responses.xmldocument
FROM quotes,prodrefs,responses,orders
WHERE quotes.negotiationGPK
= ?
AND quotes.ctime = (SELECT MAX(ctime) FROM quotes WHERE negotiationgpk
= ?)
AND prodrefs.negotiationGPK = ?
AND orders.productreferenceGPK = prodrefs.gpk
AND owner=u1 AND overridden=FALSE
AND responses.orderGPK = orders.gpk
AND responses.ctime = (SELECT MAX(ctime) FROM responses WHERE ordergpk=orders.gpk)
UNION
SELECT quotes.xmldocument,prodrefs.xmldocument,(''),('')
FROM quotes,prodrefs
WHERE quotes.negotiationGPK
= ?
AND quotes.ctime = (SELECT MAX(ctime) FROM quotes WHERE negotiationgpk
= ?)
AND prodrefs.negotiationGPK = ?
-----------
The tables:
-----------
Table "quotes"
Attribute
| Type | Modifier
----------------+-----------+----------
gpk
| bigint | not null
xmldocument
| text | not null
negotiationgpk | bigint
| not null
ctime
| timestamp | not null
Index: quotes_pkey
Table "prodrefs"
Attribute
| Type | Modifier
----------------+-----------+----------
gpk
| bigint | not null
ctime
| timestamp | not null
xmldocument
| text | not null
negotiationgpk | bigint
| not null
Index: prodrefs_pkey
Table "orders"
Attribute
| Type | Modifier
---------------------+-----------+----------
gpk
| bigint | not null
ctime
| timestamp | not null
productreferencegpk | bigint
|
owner
| text | not null
overridden
| boolean |
overridingordergpk | bigint
|
rawdata
| text | not null
filteredorder
| text | not null
previousquotegpk
| bigint |
Index: orders_pkey
Table "responses"
Attribute |
Type | Modifier
-------------+-----------+----------
gpk
| bigint | not null
ctime
| timestamp | not null
ordergpk |
bigint |
xmldocument | text
| not null
quotegpk |
bigint |
Index: responses_pkey
Re: important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case
From
Tom Lane
Date:
Isabelle Therrien <therriei@LUB.UMontreal.CA> writes:
> <!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@cirano.qc.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>
Please don't send HTML to mailing lists.
I'd be willing to look at your issue if you'd submit it in a form
I could read with less pain.
regards, tom lane