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

[Rahkonen Jukka via MapServer-users]

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-boun...@lists.osgeo.org<mailto:mapserver-users-boun...@lists.osgeo.org>>
 Puolesta Rob Dennett via MapServer-users
Lähetetty: maanantai 5. helmikuuta 2024 2.54
Vastaanottaja: 
mapserver-users@lists.osgeo.org<mailto:mapserver-users@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 <j...@internode.on.net<mailto:j...@internode.on.net>>
Sent: Sunday, February 4, 2024 6:44 PM
To: Rob Dennett <rob.denn...@twdb.texas.gov<mailto:rob.denn...@twdb.texas.gov>>
Cc: Travis Kirstine 
<traviskirst...@gmail.com<mailto:traviskirst...@gmail.com>>; 
mapserver-users@lists.osgeo.org<mailto:mapserver-users@lists.osgeo.org> 
<mapserver-users@lists.osgeo.org<mailto:mapserver-users@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


_______________________________________________
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users