From: Niccolo Rigacci <niccolo@rigacci.org>
To: pgsql-performance@postgresql.org
Subject: CURSOR slowes down a WHERE clause 100 times?
Date: Wed, 6 Jul 2005 23:19:46 +0200
Message-ID: <20050706211946.GA3460@rigacci.org> (raw)
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!
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: <20050706211946.GA3460@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