[MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

[Rob Dennett]

I recently upgraded our db for our Mapserver from Postgres 11 to Postgres 13 and also had to update Postgis from 2.4 to 3.4, as well as remove the postgis 2.4 raster queries and add the new postgis_raster extension.  Now I have web request which takes over 30 seconds where it used to take about 2.  I made copies of the .map file and question and pointed one at the old db and set the debug level for the map to 2 for both.

These .map files were created by someone who no longer works here and I am not a GIS professional, just a software developer, so I am having trouble understanding the output.

After hitting the web page for both, I see the debug file is twice as large for the new db as it was for the old one.  The .map file has 4 layers, three of which have a status of on, one for polygons, one for lines and one for point and one called "AllSources" (which is off).  For the first 3, they contain

DATA "geom from the_table_in_question"

which is a field of type geometry.  In the logs I see lines like

select "sourceid"::text,"featuretyp"::text,"cartodb_id"::text,"name"::text,"sourcetype"::text,"drawingord"::text,"isnew"::text,ST_AsBinary(("geom"),'NDR') as geom,"cartodb_id"::text from the_table_in_question where "geom" && ST_GeomFromText('POLYGON((-12520996.7293382 2507134.52775378,-12520996.7293382 3754586.82936786,-11273544.4277241 3754586.82936786,-11273544.4277241 2507134.52775378,-12520996.7293382 2507134.52775378))',find_srid('','iswp_sourcefeatures2022','geom')) and ("featuretyp"::text = 'polygon')

There are 8 of these in each output file, but their contents don't match.  I am not sure what's generating them.  I do note that they, along with SELECT geom from ...; statements take roughly equal time whether executed against the old db or the new one.

What is generating these log statements?

Why does what is for all intents and purposes the same .map file produce different output?  I know it's because I changed the db engine and postgis, but if you could be more specific, that would be very helpful.

Thanks,
Rob
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20240202/803f7382/attachment.htm>