Message-ID: <42CC4D1C.9040606@arbash-meinel.com>
Date: Wed, 06 Jul 2005 16:29:00 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Macintosh/20050317)
MIME-Version: 1.0
To: Niccolo Rigacci <niccolo@rigacci.org>,
	Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: CURSOR slowes down a WHERE clause 100 times?
References: <20050706211946.GA3460@rigacci.org>
In-Reply-To: <20050706211946.GA3460@rigacci.org>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig0BDC10F2B83E07547A560931"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig0BDC10F2B83E07547A560931
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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


--------------enig0BDC10F2B83E07547A560931
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCzE0fJdeBCYSNAAMRAk+eAJ96Sz+yQMX94j1ba77ojk021iA6IQCfYRvm
ynbwpZw6yU9j96fu225RJvA=
=DWez
-----END PGP SIGNATURE-----

--------------enig0BDC10F2B83E07547A560931--