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!