From: Richard Huxton <dev@archonet.com>
To: Niccolo Rigacci <niccolo@rigacci.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: CURSOR slowes down a WHERE clause 100 times?
Date: Thu, 07 Jul 2005 10:14:50 +0100
Message-ID: <42CCF28A.3010403@archonet.com> (raw)
In-Reply-To: <20050707081327.GA17054@rigacci.org>
References: <20050706211946.GA3460@rigacci.org>
<20050707081327.GA17054@rigacci.org>
Niccolo Rigacci wrote:
>>
>>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?
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.
So - if you ask for a cursor, PG weights things to give you the first
few rows as soon as possible, at the expense of fetching *all* rows
quickly. If you're only going to fetch e.g. the first 20 rows this is
exactly what you want. 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.
So - the ORDER BY means PG has to sort all the results before returning
the first row anyway. That probably means the plans with/without cursor
are identical.
Of course, all this assumes that your configuration settings are good
and statistics adequate. To test that, try fetching just the first row
from your cursor with/without the ORDER BY. Without should be quicker.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, niccolo@rigacci.org
Subject: Re: CURSOR slowes down a WHERE clause 100 times?
In-Reply-To: <42CCF28A.3010403@archonet.com>
* 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