To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: SELECT very slow
Date: Thu, 16 Jun 2005 00:08:18 +0200
Lines: 46
Message-ID: <d8q8iq$pfg$1@sea.gmane.org>
References: <d87ood$8vq$1@sea.gmane.org> <d8pqbi$a5q$1@sea.gmane.org>
	<op.ssc0t7wrth1vuj@localhost> <op.ssfgo6xath1vuj@localhost>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; de-AT;
	rv:1.7.6) Gecko/20050319 Mnenhy/0.6.0.104
In-Reply-To: <op.ssfgo6xath1vuj@localhost>

PFC wrote on 15.06.2005 22:04:

> 
>> It's not the program or Java. The same program takes about 20 seconds  
>> with Firebird and the exactly same data.
> 
> 
>     Hm, that's still very slow (it should do it in a couple seconds like 
> my  PC does... maybe the problem is common to postgres and firebird ?)
> 
>     Try eliminating disk IO by writing a set returning function which 
> returns  1000000 rows, something simple like just a sequence number and 
> a text  value... if this is slow too... i don't know... 

 > do you have an antivirus  or zonealarm or something ?
Wouldn't that affect all DB access not only PG? And as I said, all other

The 20 seconds are ok. This includes processing of the data in the 
application. If I simply loop over the result set and get each column's 
value without further processing it takes 4 seconds with Firebird.

Basically I'm doing the following:

rs = stmt.executeQuery("select * from foo");
while (rs.next())
{
   for (int i=0; i < 4; i++)
   {
     Object o = rs.getObject(i+1);
   }
}

As I said in my other post, the behaviour/performance in PG is dependent on 
the autocommit setting for the connection.

With autocommit set to false the above code takes about 3 seconds in PG 
but wit autocommit set to true, PG takes 3 minutes! It seems that it also 
is very dependent on the fetchsize (apparently the number of rows that are 
cached by the driver). Anything above 100 seems to slow down the overall 
process.


Regards
Thomas