Thread: CURSOR slowes down a WHERE clause 100 times?
Hi to all,
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.
The wpt_comuni_view is a VIEW of a 3 tables JOIN, and the "wpt"
field is a PostGIS geometry column. The "&&" is the PostGIS
"overlaps" operator.
If I CURSOR SELECT from a temp table instead of the JOIN VIEW the
query time 1 second.
If I omit the WHERE clause the CURSOR fetches results in 1
second.
Can the CURSOR on JOIN affects so heavly the WHERE clause? I
suspect that - with the CURSOR - a sequential scan is performed
on the entire data set for each fetched record...
Any idea?
This is the definition of the VIEW:
CREATE VIEW wpt_comuni_view AS
SELECT istat_wpt.oid, istat_wpt.id, istat_wpt.toponimo,
istat_comuni.residenti, istat_wpt.wpt
FROM istat_comuni
JOIN istat_comuni2wpt
USING (idprovincia, idcomune)
JOIN istat_wpt
ON (idwpt = id);
Thank you for any hint.
--
Niccolo Rigacci
Firenze - Italy
War against Iraq? Not in my name!
Niccolo Rigacci wrote:
>Hi to all,
>
>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.
>
>The wpt_comuni_view is a VIEW of a 3 tables JOIN, and the "wpt"
>field is a PostGIS geometry column. The "&&" is the PostGIS
>"overlaps" operator.
>
>If I CURSOR SELECT from a temp table instead of the JOIN VIEW the
>query time 1 second.
>
>If I omit the WHERE clause the CURSOR fetches results in 1
>second.
>
>Can the CURSOR on JOIN affects so heavly the WHERE clause? I
>suspect that - with the CURSOR - a sequential scan is performed
>on the entire data set for each fetched record...
>
>Any idea?
>
>
What does it say if you do "EXPLAIN ANALYZE SELECT..." both with and
without the cursor?
It may not say much for the cursor, but I think you can explain analyze
the fetch statements.
It is my understanding that Cursors generally favor using an
slow-startup style plan, which usually means using an index, because it
expects that you won't actually want all of the data. A seqscan is not
always slower, especially if you need to go through most of the data.
Without an explain analyze it's hard to say what the planner is thinking
and doing.
>This is the definition of the VIEW:
>
> CREATE VIEW wpt_comuni_view AS
> SELECT istat_wpt.oid, istat_wpt.id, istat_wpt.toponimo,
> istat_comuni.residenti, istat_wpt.wpt
> FROM istat_comuni
> JOIN istat_comuni2wpt
> USING (idprovincia, idcomune)
> JOIN istat_wpt
> ON (idwpt = id);
>
>Thank you for any hint.
>
>
>
You might also try comparing your CURSOR to a prepared statement. There
are a few rare cases where preparing is worse than issuing the query
directly, depending on your data layout.
John
=:->
Attachment
> >Can the CURSOR on JOIN affects so heavly the WHERE clause? I
> >suspect that - with the CURSOR - a sequential scan is performed
> >on the entire data set for each fetched record...
> >
> >Any idea?
> >
> >
> What does it say if you do "EXPLAIN ANALYZE SELECT..." both with and
> without the cursor?
> It may not say much for the cursor, but I think you can explain analyze
> the fetch statements.
How can I EXPLAIN ANALYZE a cursor like this?
BEGIN;
DECLARE mycursor BINARY CURSOR FOR
SELECT ...
FETCH ALL IN mycursor;
END;
I tried to put EXPLAIN ANALYZE in front of the SELECT and in
front of the FETCH, but I got two "syntax error"...
Thanks
--
Niccolo Rigacci
Firenze - Italy
War against Iraq? Not in my name!
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!
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
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!
Niccolo Rigacci <niccolo@rigacci.org> writes:
> How can I EXPLAIN ANALYZE a cursor like this?
> BEGIN;
> DECLARE mycursor BINARY CURSOR FOR
> SELECT ...
> FETCH ALL IN mycursor;
> END;
> I tried to put EXPLAIN ANALYZE in front of the SELECT and in
> front of the FETCH, but I got two "syntax error"...
Just FYI, you can't EXPLAIN ANALYZE this, but you can EXPLAIN it:
EXPLAIN DECLARE x CURSOR FOR ...
so you can at least find out what the plan is.
It might be cool to support EXPLAIN ANALYZE FETCH --- not sure what that
would take.
regards, tom lane