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

[Gregory S. Williamson]

Niccolo --

Try running postgres' EXPLAIN ANALYZE command on your sql (typically from the psql tool or some other interface)
EXPLAIN ANALYZE 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'))
            );

And post the results, along with a description of the tables and indexes involved, and the postgres version. Details on RAM, disk, OS and some settings from the psql config file (sort memory, and the like) might be useful.

Also, have you run VACUUM ANALYZE recently on the tables in question? (With recent releases of PostGIS I think this does the geometry stats as well; earlier releases you need to explicitly run the update_geometry_stats(table_name,column_name); to make decent statistics for the analyzer.)

Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From:	postgis-users-bounces at postgis.refractions.net on behalf of Niccolo Rigacci
Sent:	Tue 7/5/2005 1:52 PM
To:	postgis-users at postgis.refractions.net
Cc:	
Subject:	[postgis-users] Using VIEW with MapServer is very slow
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!
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

!DSPAM:42caf312317241542719194!