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

[Travis Kirstine]

First thought is to check if the table has a spatial index.

https://postgis.net/workshops/postgis-intro/indexing.html

On Fri, 2 Feb 2024 at 18:37, Rob Dennett via MapServer-users <
mapserver-users at lists.osgeo.org> wrote:

> 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
> _______________________________________________
> MapServer-users mailing list
> MapServer-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20240204/e7a9e406/attachment.htm>