[postgis-users] Using VIEW with MapServer is very slow

[Niccolo Rigacci]

Hi to all.

I'm at my first experiences with PostGIS, Mapserver and QGIS.

I had some trouble using a VIEW as a data source for MapServer or
QGIS; empirically I discovered that I had to include an OID field
into the VIEW to have it working.

I also added a record to geometry_columns by hand (with an
INSERT) instead of AddGeometryColumn():

CREATE VIEW wpt_comuni AS
    SELECT istat_wpt.oid, istat_wpt.id,
        istat_wpt.toponimo, residenti, wpt
    FROM istat_comuni
    JOIN istat_comuni2wpt
        USING (idprovincia, idcomune)
    JOIN istat_wpt
        ON (idwpt = id);

INSERT INTO geometry_columns
    VALUES('', 'public', 'wpt_comuni', 'wpt', 2, 4326, 'POINT');


Please, can someone confirm that this make sense?

But the real problem is that the query on the VIEW is very slow!
Using debug from Postgres I see that the query is:

BEGIN;
DECLARE mycursor BINARY CURSOR FOR
    SELECT
        toponimo::text,
        asbinary(force_collection(force_2d(wpt)),'NDR'),
        OID::text
        FROM wpt_comuni
            WHERE (residenti > 20000)
            AND (
                wpt &&
                setSRID('BOX3D(4.83 36,20.16 47.5)'::BOX3D,
                    find_srid('','wpt_comuni','wpt'))
            );
FETCH ALL IN mycursor;

The query is extremely slow from the command line too (2 min). 
But if I do only the SELECT (without using the CURSOR), the 
answer is near immediate.

The CURSOR is executed immediately instead if I omit the AND
clause (the overlap check between points and the BOX3D).

Should I have to create some INDEXes? Are VIEWs not suitables for
MapServer? Any hints will be apreciated!

-- 
Niccolo Rigacci
Firenze - Italy

War against Iraq? Not in my name!