<div dir="ltr"><div>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. </div><div><br></div><div>I would try running EXPLAIN ANALYZE on the same query on both versions of Postgres and see what the differences are.<br></div><div><br></div><div>explain analyze <span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">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')</span>
</div><div> <br></div><div><br></div><div><br></div><div><br></div><div><br> </div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, 5 Feb 2024 at 10:35, Rob Dennett via MapServer-users <<a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div class="msg-4114288958118921749">
<div dir="ltr">
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">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<br>
<br>
-12520996.7293382 2507134.52775378,-12520996.7293382 3754586.82936786,-11273544.4277241 3754586.82936786,-11273544.4277241 2507134.52775378,-12520996.7293382 2507134.52775378</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">and then again with these</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">-12518550.744433 2509580.51265891,-12518550.744433 5004485.11588706,-10023646.1412049
5004485.11588706,-10023646.1412049 2509580.51265891,-12518550.744433 2509580.51265891</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">When pointed at the new db (which does indeed have identical data and structure), the
polygons are</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">-11268652.4579138 2507134.52775378,-11268652.4579138 3754586.82936786,-10021200.1562997
3754586.82936786,-10021200.1562997 2507134.52775378,-11268652.4579138 2507134.52775378</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">and </span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">-12068252.5062205 2978893.85839647,-12068252.5062205 4369793.96473184,-10212686.4400848
4369793.96473184,-10212686.4400848 2978893.85839647,-12068252.5062205 2978893.85839647<br>
<br>
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:</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">EXTENT -11871597.4858696 2978893.85839647 -10409341.4604357 4369793.96473184</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">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?</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><br>
</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">Thanks,<br>
Rob</span></div>
<div id="m_-4114288958118921749appendonsend"></div>
<hr style="display:inline-block;width:98%">
<div id="m_-4114288958118921749divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> Rahkonen Jukka <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>><br>
<b>Sent:</b> Monday, February 5, 2024 1:25 AM<br>
<b>To:</b> Rob Dennett <<a href="mailto:Rob.Dennett@twdb.texas.gov" target="_blank">Rob.Dennett@twdb.texas.gov</a>>; <a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a> <<a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a>><br>
<b>Subject:</b> Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?</font>
<div> </div>
</div>
<div lang="FI" style="overflow-wrap: break-word;">
<div style="background-color:rgb(255,255,255);border-style:solid;border-color:rgb(244,58,19);border-width:2pt;font-size:10pt;line-height:12pt;font-family:"Calibri";color:red">
<p align="center" style="text-align:center;background:rgb(255,255,255)">
<span style="font-size:12pt;font-weight:bold;color:red">External: Beware of links/attachments.
</span></p>
</div>
<br>
<br>
<div>
<div>
<p><span>Hi,</span></p>
<p><span> </span></p>
<p><span lang="EN-US">See </span><span><a href="https://mapserver.org/input/vector/postgis.html" target="_blank"><span lang="EN-US">https://mapserver.org/input/vector/postgis.html</span></a></span><span lang="EN-US">, there is
an example about how to define the SRID on the DATA line:<br>
DATA "the_geom from the_database using unique gid using srid=4326"</span></p>
<p><span lang="EN-US"> </span></p>
<p><span lang="EN-US">The SRID value to use is the native SRID of the PostGIS table.</span></p>
<p style="margin-bottom:12pt"><span lang="EN-US">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.</span></p>
<p><span lang="EN-US">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.</span></p>
<p><span lang="EN-US"> </span></p>
<p><span>-Jukka Rahkonen-</span></p>
<p><span> </span></p>
<div>
<div style="border-width:1pt medium medium;border-style:solid none none;border-color:rgb(225,225,225) currentcolor currentcolor;padding:3pt 0cm 0cm">
<p><b>Lähettäjä:</b> MapServer-users <<a href="mailto:mapserver-users-bounces@lists.osgeo.org" target="_blank">mapserver-users-bounces@lists.osgeo.org</a>>
<b>Puolesta </b>Rob Dennett via MapServer-users<br>
<b>Lähetetty:</b> maanantai 5. helmikuuta 2024 2.54<br>
<b>Vastaanottaja:</b> <a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a><br>
<b>Aihe:</b> Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?</p>
</div>
</div>
<p> </p>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black">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":<br>
<br>
LAYER</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> NAME "PolygonSources"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> CONNECTIONTYPE POSTGIS</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> CONNECTION "xxxxxxxxxx"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> TYPE POLYGON</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> STATUS ON</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> DATA "geom FROM
<span style="background:white">the_table_in_question</span>"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> UTFITEM "cartodb_id"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> UTFDATA "{\"cartodb_id\":\"[cartodb_id]\",\"sourceid\":\"[sourceid]\",\"name\":\"[name]\",\"sourcetype\":\"[sourcetype]\",\"drawingord\":\"[drawingord]\",\"featuretyp\":\"[featuretyp]\",\"isnew\":\"[isnew]\"}"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> TEMPLATE WMSGetFeatureInfo</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> FILTERITEM "featuretyp"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> CLASSITEM "sourceid"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> FILTER "polygon"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> CLASS</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> NAME "polygon"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> EXPRESSION ([sourceid] != 169 AND [sourceid] != 412 AND [sourceid] != 820 AND [sourceid] != 1067)</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> STYLE</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> COLOR "#0B3A71B3"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> OUTLINECOLOR "#AFBFD0B3"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> OUTLINEWIDTH 1.5</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> END</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> END</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> METADATA</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> "wms_title" "Polygon Source Features"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> "wms_include_items" "all"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> "wms_abstract" "Layer of all polygon geometry sources."</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> "wfs_title" "Polygon Source Features"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> "wfs_srs" "EPSG:3857 EPSG:4326"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> "wfs_enable_request" "*"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> "wfs_abstract" "Layer of all polygon geometry sources."</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> "gml_include_items" "all"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> "gml_featureid" "cartodb_id"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> END</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> PROJECTION</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> "init=epsg:3857"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> END</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> PROCESSING "CLOSE_CONNECTION=DEFER"</span></p>
</div>
<div>
<p><span style="font-size:12pt;font-family:"Aptos",sans-serif;color:black"> END</span></p>
</div>
<div align="center" style="text-align:center">
<hr size="1" width="98%" align="center">
</div>
<div id="m_-4114288958118921749x_divRplyFwdMsg">
<p><b><span style="color:black">From:</span></b><span style="color:black"> James Gardner <<a href="mailto:jsg@internode.on.net" target="_blank">jsg@internode.on.net</a>><br>
<b>Sent:</b> Sunday, February 4, 2024 6:44 PM<br>
<b>To:</b> Rob Dennett <<a href="mailto:Rob.Dennett@twdb.texas.gov" target="_blank">Rob.Dennett@twdb.texas.gov</a>><br>
<b>Cc:</b> Travis Kirstine <<a href="mailto:traviskirstine@gmail.com" target="_blank">traviskirstine@gmail.com</a>>;
<a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a> <<a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a>><br>
<b>Subject:</b> Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?</span>
</p>
<div>
<p> </p>
</div>
</div>
<div>
<div style="border:2.25pt solid rgb(244,58,19);padding:0cm">
<p align="center" style="text-align:center;line-height:12pt;background:white">
<b><span style="font-size:12pt;color:red">External: Beware of links/attachments.
</span></b><span style="font-size:10pt;color:red"></span></p>
</div>
<p style="margin-bottom:12pt"> </p>
<div>
<div>
<p>Could you try replacing find_srid with a hard coded srid... I found it had to run find_srid on every tuple...
</p>
<div>
<p>-James Gardner</p>
</div>
</div>
<div>
<p> </p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
_______________________________________________<br>
MapServer-users mailing list<br>
<a href="mailto:MapServer-users@lists.osgeo.org" target="_blank">MapServer-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
</div></blockquote></div>