Re: CURSOR slowes down a WHERE clause 100 times?

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
=:->