Quick Links

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

From:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To:	Isabelle Therrien <therriei(at)LUB(dot)UMontreal(dot)CA>
Cc:	pgsql-bugs(at)postgresql(dot)org, rgl(at)cirano(dot)qc(dot)ca
Subject:	Re: important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case
Date:	2001-03-19 23:26:10
Message-ID:	7956.985044370@sss.pgh.pa.us
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-bugs

Isabelle Therrien <therriei(at)LUB(dot)UMontreal(dot)CA> writes:
> <!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>

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

In response to

important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case at 2001-03-19 22:54:44 from Isabelle Therrien

Browse pgsql-bugs by date

	From	Date	Subject
Next Message	Isabelle Therrien	2001-03-19 23:30:13	important decrease of performance using the BETA version in one particular case
Previous Message	Isabelle Therrien	2001-03-19 22:54:44	important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case