Subject: Re: SELECT very slow
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Thomas Kellerer <spam_eater@gmx.net>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <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>
	<d8q8iq$pfg$1@sea.gmane.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1118930439.3382.91.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Thu, 16 Jun 2005 09:00:39 -0500

On Wed, 2005-06-15 at 17:08, Thomas Kellerer wrote:
> 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.

There's got to be more happening than what this is showing us.  A
select, and looping through it, should involve no writes, and therefore
no real performance difference from autocommit versus not.  Is there
some underlying trigger on the view or something like that?  Some kind
of auditing function?