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