Skip site navigation (1) Skip section navigation (2)

important decrease of performance using the BETA version in one particular case

From: Isabelle Therrien <therriei(at)LUB(dot)UMontreal(dot)CA>
To: pgsql-bugs(at)postgresql(dot)org
Subject: important decrease of performance using the BETA version in one particular case
Date: 2001-03-19 23:30:13
Message-ID: Pine.LNX.4.21.0103191827330.26512-100000@dev11.lub.umontreal.ca (view raw or flat)
Thread:
Lists: pgsql-bugs

(sorry for previous 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 


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-03-19 23:44:43
Subject: Re: important decrease of performance using the BETA version in one particular case
Previous:From: Tom LaneDate: 2001-03-19 23:26:10
Subject: Re: important decrease of performance using the 7.1 BETA version of PostgreSQL in one particular case

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group