Mapserver 4.4.1 /POSTGIS 0.8/PostgreSQL 7.4.3 query performance puzzler

[John Novak]

All,



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



Nothing special here, except that this form requires 55000msec (!) to return
results to the client.  I have seen similar behavior on an earlier Mapserver
release.



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.



Any enlightenment is greatly appreciated.



John Novak

Novacell Technologies