Date: Wed, 6 Jul 2005 23:19:46 +0200
To: pgsql-performance@postgresql.org
Subject: CURSOR slowes down a WHERE clause 100 times?
Message-ID: <20050706211946.GA3460@rigacci.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.9i
From: Niccolo Rigacci <niccolo@rigacci.org>

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!