To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: SELECT very slow
Date: Thu, 16 Jun 2005 16:45:35 +0200
Lines: 18
Message-ID: <d8s30h$r12$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>
	<1118930439.3382.91.camel@state.g2switchworks.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de-AT;
	rv:1.7.6) Gecko/20050319 Mnenhy/0.7.2.0
In-Reply-To: <1118930439.3382.91.camel@state.g2switchworks.com>

On 16.06.2005 16:00 Scott Marlowe wrote:

> 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?

That's exactly the code that produced the mentioned timings. This is - according
to the JDBC driver's documentation - the expected behaviour. The driver can be
set to use cursor based fetching but *only* if autocommit is false.

If autocommit is on (or fetch size is zero) then the driver will build the whole
result set before returning to the caller.

http://jdbc.postgresql.org/documentation/80/query.html#query-with-cursor

Thomas