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

[Rahkonen Jukka]

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/3cf43588/attachment-0001.htm>