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