[postgis-users] Re: [Mapserver-users] Mapscript drawQuery() on big PostGIS layers doesn't work properly ?

[David Blasby]

Paul Ramsey wrote:
>   Cost analysis. On large enough tables and complex enough queries,  
> cost analysis gets important. A PostGIS installation with the STATS  
> option enabled and stats calculated can do the "right thing" in query  
> planning which a naive installation completely screws up.

Whats happening is that postgresql is using the spatial index (which 
presumably returns the entire layer), then does a sequencial scan to 
find the rows with the "filter" attributes.

The most likely problem is you dont have an index on OID:

CREATE INDEX mybigpolygon_table_oid_idx ON mybigpolygon_table (oid);

You might find that turning on stats will give you speed improvements. 
To do this:
1. make sure your postGIS makefile has "USESTATS=1" set
2. in postgresql, do a "SELECT UPDATE_GEOMETRY_STATS();"
3. in postgresql, do a "VACUUM ANALYSE"

If this doesnt work, I'll need more information.  Turn the layer's DEBUG 
flag to on, and run your script.  You should see the SQL query go by (it 
starts with "DECLARE CURSOR mycursor FOR ...".

If it says "DECLARE CURSOR mycursor FOR SELECT the_geom,oid FROM 
mybigpolygon_table", then go into postgresql and type:

EXPLAIN ANALYSE SELECT the_geom,oid FROM mybigpolygon_table

(I replaced the 'DECLARE CURSOR mycursor FOR' with 'EXPLAIN ANALYSE')

The output will tell you what postgresql is doing and how long its taking.

dave