Date: Thu, 7 Jul 2005 12:06:58 +0200
To: pgsql-performance@postgresql.org
Subject: Re: CURSOR slowes down a WHERE clause 100 times?
Message-ID: <20050707100658.GA19237@rigacci.org>
References: <20050706211946.GA3460@rigacci.org>
	<20050707081327.GA17054@rigacci.org>
	<42CCF28A.3010403@archonet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <42CCF28A.3010403@archonet.com>
User-Agent: Mutt/1.5.9i
From: Niccolo Rigacci <niccolo@rigacci.org>

On Thu, Jul 07, 2005 at 10:14:50AM +0100, Richard Huxton wrote:
> >By trial and error I discovered that adding an "ORDER BY 
> >toponimo" clause to the SELECT, boosts the CURSOR performances 
> >so that they are now equiparable to the SELECT alone.

> I think you're misunderstanding exactly what's happening here. If you 
> ask for a cursor, PG assumes you aren't going to want all the results 
> (or at least not straight away). After all, most people use them to work 
> through results in comparatively small chunks, perhaps only ever 
> fetching 1% of the total results.

This make finally sense!

> In your case, since you're immediately issuing FETCH ALL,
> you're not really using the cursor at all, but PG doesn't know
> that.

In fact, fetching only the first rows from the cursor, is rather
quick! This demonstrates that the PG planner is smart.

Not so smart are the MapServer and QGIS query builders, which use
a CURSOR to FETCH ALL.

I will investigate in this direction now.

Thank you very much, your help was excellent!

-- 
Niccolo Rigacci
Firenze - Italy

War against Iraq? Not in my name!