Quick Links

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
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-bugs

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
Your name              
:       Isabelle Therrien
 Your email address     
:       therriei(at)cirano(dot)qc(dot)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
  
  
  
  </html>

Attachment	Content-Type	Size
unknown_filename	text/html	9.8 KB

Responses

Re: important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case at 2001-03-19 23:26:10 from Tom Lane

Browse pgsql-bugs by date

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