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

Mon Feb 5 08:50:01 PST 2024

Not sure I understand the issue with the polygons.   The polygon value in
the select statement will change based on the client's view extent, for
example if the users moves the map a new request with a different polygon /
bbox would be issued to mapserver and through to postgres. The EXTENT value
defined in the mapfile defines the extent of the map or layer coverage.

I would try running EXPLAIN ANALYZE on the same query on both versions of
Postgres and see what the differences are.

explain analyze 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')

On Mon, 5 Feb 2024 at 10:35, Rob Dennett via MapServer-users <
mapserver-users at lists.osgeo.org> wrote:

> 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> *Puolesta
> *Rob Dennett via MapServer-users
> *Lähetetty:* maanantai 5. helmikuuta 2024 2.54
> *Vastaanottaja:* 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>
> *Sent:* Sunday, February 4, 2024 6:44 PM
> *To:* Rob Dennett <Rob.Dennett at twdb.texas.gov>
> *Cc:* Travis Kirstine <traviskirstine at gmail.com>;
> 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. *
>
>
>
> 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 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/20240205/7719e9c5/attachment.htm>