Re: important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case - Mailing list pgsql-bugs
| From | Tom Lane |
|---|---|
| Subject | Re: important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case |
| Date | |
| Msg-id | 7956.985044370@sss.pgh.pa.us Whole thread Raw |
| In response to | important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case (Isabelle Therrien <therriei@LUB.UMontreal.CA>) |
| List | pgsql-bugs |
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
pgsql-bugs by date: