From: Niccolo Rigacci <niccolo@rigacci.org>
To: pgsql-performance@postgresql.org
Subject: Re: CURSOR slowes down a WHERE clause 100 times?
Date: Thu, 7 Jul 2005 10:13:27 +0200
Message-ID: <20050707081327.GA17054@rigacci.org> (raw)
In-Reply-To: <20050706211946.GA3460@rigacci.org>
References: <20050706211946.GA3460@rigacci.org>
On Wed, Jul 06, 2005 at 11:19:46PM +0200, Niccolo Rigacci wrote:
>
> I have a performace problem with the following query:
>
> BEGIN;
> DECLARE mycursor BINARY CURSOR FOR
> SELECT
> toponimo,
> wpt
> FROM wpt_comuni_view
> WHERE (
> wpt &&
> setSRID('BOX3D(4.83 36, 20.16 47.5)'::BOX3D, 4326)
> );
> FETCH ALL IN mycursor;
> END;
>
> I get the results in about 108 seconds (8060 rows).
>
> If I issue the SELECT alone (without the CURSOR) I get the
> same results in less than 1 second.
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.
Is there some documentation on how an ORDER can affect the
CURSOR in a different way than the SELECT?
--
Niccolo Rigacci
Firenze - Italy
War against Iraq? Not in my name!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: niccolo@rigacci.org
Subject: Re: CURSOR slowes down a WHERE clause 100 times?
In-Reply-To: <20050707081327.GA17054@rigacci.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox