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

Mon Feb 5 07:34:34 PST 2024

So, looking at this further, this is the bounding box for what's happening is that when pointed at the old db, the .map file is making the calls using these coordinates

-12520996.7293382 2507134.52775378,-12520996.7293382 3754586.82936786,-11273544.4277241 3754586.82936786,-11273544.4277241 2507134.52775378,-12520996.7293382 2507134.52775378

and then again with these

-12518550.744433 2509580.51265891,-12518550.744433 5004485.11588706,-10023646.1412049 5004485.11588706,-10023646.1412049 2509580.51265891,-12518550.744433 2509580.51265891

When pointed at the new db (which does indeed have identical data and structure), the polygons are

-11268652.4579138 2507134.52775378,-11268652.4579138 3754586.82936786,-10021200.1562997 3754586.82936786,-10021200.1562997 2507134.52775378,-11268652.4579138 2507134.52775378

and

-12068252.5062205 2978893.85839647,-12068252.5062205 4369793.96473184,-10212686.4400848 4369793.96473184,-10212686.4400848 2978893.85839647,-12068252.5062205 2978893.85839647

I have no idea where these polygons are coming from, nor do I understand why they are different when pointed at the old vs. new db nor why the queries are called in a different order.  The .map file has a line defining the extent:

EXTENT -11871597.4858696 2978893.85839647 -10409341.4604357 4369793.96473184

but that doesn't match up with these polygons.  The new polygons appear to be a bit smaller than the old ones, if I understand correctly.  Does anyone know why I am seeing what I am seeing?

Thanks,
Rob
________________________________
From: Rahkonen Jukka <jukka.rahkonen at maanmittauslaitos.fi>
Sent: Monday, February 5, 2024 1:25 AM
To: Rob Dennett <Rob.Dennett at twdb.texas.gov>; mapserver-users at lists.osgeo.org <mapserver-users at lists.osgeo.org>
Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

External: Beware of links/attachments.

Hi,

See https://mapserver.org/input/vector/postgis.html, there is an example about how to define the SRID on the DATA line:
DATA "the_geom from the_database using unique gid using srid=4326"

The SRID value to use is the native SRID of the PostGIS table.

What Mapserver does here is that with “&&” operator it selects those features from the table which intersect with the reference geometry and because of that it needs to know the SRID of the table. The reference geometry (POLYGON) is either the BBOX of the request or the EXTENT used in the mapfile, I cannot say for sure with this information. I believe that find_srid is rather fast but it is good to include “using srid=” anyway. Adding “unique” for defining the primary key of the table cannot make any harm either.

You wrote “There are 8 select statements in each, but the statements aren't the same” and then you showed one of the statements. It would help to see also the other statement. But if the tables have the same data and the output from the new db is much larger, and there is no other filter in the SQL query than the &&, then I quess that the reference polygon of the latter case is bigger for some reason.

-Jukka Rahkonen-

Lähettäjä: MapServer-users <mapserver-users-bounces at lists.osgeo.org<mailto:mapserver-users-bounces at lists.osgeo.org>> Puolesta Rob Dennett via MapServer-users
Lähetetty: maanantai 5. helmikuuta 2024 2.54
Vastaanottaja: mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>
Aihe: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

I don't think so.  These queries are generated somehow.  Here's what the PolygonSources layer looks like, and as you can see, the query I am specifying is just "geom from the_table_in_question":

  LAYER

    NAME "PolygonSources"

    CONNECTIONTYPE POSTGIS

    CONNECTION "xxxxxxxxxx"

    TYPE POLYGON

    STATUS ON

    DATA "geom FROM the_table_in_question"

    UTFITEM   "cartodb_id"

    UTFDATA   "{\"cartodb_id\":\"[cartodb_id]\",\"sourceid\":\"[sourceid]\",\"name\":\"[name]\",\"sourcetype\":\"[sourcetype]\",\"drawingord\":\"[drawingord]\",\"featuretyp\":\"[featuretyp]\",\"isnew\":\"[isnew]\"}"

    TEMPLATE WMSGetFeatureInfo

    FILTERITEM "featuretyp"

    CLASSITEM "sourceid"

    FILTER "polygon"

    CLASS

      NAME "polygon"

      EXPRESSION ([sourceid] != 169 AND [sourceid] != 412 AND [sourceid] != 820 AND [sourceid] != 1067)

      STYLE

        COLOR "#0B3A71B3"

        OUTLINECOLOR  "#AFBFD0B3"

        OUTLINEWIDTH    1.5

      END

    END

    METADATA

      "wms_title" "Polygon Source Features"

      "wms_include_items" "all"

      "wms_abstract" "Layer of all polygon geometry sources."

      "wfs_title"         "Polygon Source Features"

      "wfs_srs"           "EPSG:3857 EPSG:4326"

      "wfs_enable_request" "*"

      "wfs_abstract" "Layer of all polygon geometry sources."

      "gml_include_items" "all"

      "gml_featureid"     "cartodb_id"

    END

    PROJECTION

      "init=epsg:3857"

    END

    PROCESSING "CLOSE_CONNECTION=DEFER"

  END

________________________________

From: James Gardner <jsg at internode.on.net<mailto:jsg at internode.on.net>>
Sent: Sunday, February 4, 2024 6:44 PM
To: Rob Dennett <Rob.Dennett at twdb.texas.gov<mailto:Rob.Dennett at twdb.texas.gov>>
Cc: Travis Kirstine <traviskirstine at gmail.com<mailto:traviskirstine at gmail.com>>; mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org> <mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>>
Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

External: Beware of links/attachments.

Could you try replacing find_srid with a hard coded srid... I found it had to run find_srid on every tuple...

-James Gardner

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20240205/3da3b37e/attachment-0001.htm>