[mapserver-dev] PostGIS performance

[Bart van den Eijnden]

Hi list,

somebody reported to me that using Mapserver 5.1 dev on PostGIS compared to 5.4.2 the latter was a lot slower then the first one with maps.

Is this to be expected of the binary cursor rewrite Paul did 1 year ago? Or should it be the same performance?

TIA.

FWTools (MS 5.1-dev):
msPOSTGISLayerParseData: unique column = OID, srid='', geom_column_name = the_geom, table_name=bebouwdekom
query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from bebouwdekom WHERE the_geom && setSRID('BOX3D(107000 435852.25375626,175000 481147.74624374)'::BOX3D, find_srid('','bebouwdekom','the_geom') )
msPOSTGISLayerFreeItemInfo called
msPOSTGISLayerClose datastatement: the_geom from bebouwdekom
msPOSTGISLayerClose -- closing query_result

MS 5.4.2:
msPostGISParseData: unique_column=oid, srid=, geom_column_name=the_geom, table_name=bebouwdekom
msPostGISBuildSQL called.
msPostGISBuildSQLItems called.
msPostGISBuildSQLFrom called.
msPostGISBuildSQLWhere called.
msPostGISBuildSQLSRID called.
msPostGISBuildSQLBox called.
msPostGISLayerWhichShapes query: select encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64') as geom,"oid" from bebouwdekom where the_geom && GeomFromText('POLYGON((107000 435852.25375626,107000 481147.74624374,175000 481147.74624374,175000 435852.25375626,107000 435852.25375626))',find_srid('','bebouwdekom','the_geom'))
msPostGISLayerWhichShapes got 276 records in result.
msPostGISLayerNextShape called.
msPostGISReadShape called.
..
msPostGISLayerFreeItemInfo called.
msPostGISLayerClose called: the_geom from bebouwdekom

Compilation options:

./configure --with-agg=../agg-2.5 --with-freetype --with-proj --with-curl --with-experimental-png --with-geos --with-ogr --with-gdal --with-postgis --with-wfs --with-wmsclient --with-wfsclient --with-fastcgi

$ pg_config|grep VERSION
VERSION = PostgreSQL 8.1.17

Version info:

MapServer version 5.4.2 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP OUTPUT=SVG SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=ICONV SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=FASTCGI SUPPORTS=GEOS SUPPORTS=RGBA_PNG INPUT=EPPL7 INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE

FWTools 2.0.6:
MapServer version 5.1-dev OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP OUTPUT=SWF OUTPUT=SVG SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI SUPPORTS=THREADS SUPPORTS=GEOS INPUT=EPPL7 INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE

Best regards,
Bart