Mapserver 4.4.1 /POSTGIS 0.8/PostgreSQL 7.4.3 query performance puzzler

[Bill Binko]

On Wed, 6 Apr 2005, John Novak wrote:
> 
> A trace from PostgreSQL shows the following SQL block executing at the server:
> 
> begin; DECLARE mycursor CURSOR FOR SELECT
> asbinary(force_collection(force_2d(mapdata2.the_geom)),'NDR'),OID::text from
> (select mc.the_geom, mc.oid as oid from
> products_components as pc, mapdata as mc where pc.products_id > 0 and
> pc.roll = mc.roll and pc.frame = mc.frame) as mapdata2
> WHERE mapcat2.the_geom && setSRID('BOX3D(-84.6 35.4625,-69.6
> 46.7125)'::BOX3D, 4326 ); ; FETCH ALL in mycursor; commit
> 
...
> 
> Running just the SELECT asbinary 
  in PgAdmin III shows a 516ms response
> including data transfer, so something related to the FETCH ALL is causing
> the problem.  I suspect I have the server badly tuned as most parameters are
> still at installation default values.  Running the entire block replicates
> the very sad behavior.

I am no PostGIS expert, but I had a similar problem recently.  It seems 
that Postgresql uses different query optimization routines for the two 
types of queries.  I solved my problem (with help from a stray comment on 
postGIS's site) by changing this parameter in postgresql.conf:

random_page_cost = 2  
(It was 4).

This made it use the GIST index, where it was doing a full table scan.  

You can tell which one it's using by running your two queries with EXPLAIN 
ANALYZE before them.  I would bet that one uses the index and the other 
doesn't.

Bill