<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><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 class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof"><br>
</span></div>
<div class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">Thanks,<br>
Rob</span></div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> Rahkonen Jukka <jukka.rahkonen@maanmittauslaitos.fi><br>
<b>Sent:</b> Monday, February 5, 2024 1:25 AM<br>
<b>To:</b> Rob Dennett <Rob.Dennett@twdb.texas.gov>; mapserver-users@lists.osgeo.org <mapserver-users@lists.osgeo.org><br>
<b>Subject:</b> Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?</font>
<div> </div>
</div>
<style>
<!--
@font-face
{font-family:"Cambria Math"}
@font-face
{font-family:Calibri}
@font-face
{font-family:Aptos}
p.x_MsoNormal, li.x_MsoNormal, div.x_MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif}
a:link, span.x_MsoHyperlink
{color:blue;
text-decoration:underline}
p.x_xmsonormal, li.x_xmsonormal, div.x_xmsonormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif}
span.x_Shkpostityyli22
{font-family:"Calibri",sans-serif;
color:windowtext}
.x_MsoChpDefault
{font-size:10.0pt}
@page WordSection1
{margin:70.85pt 2.0cm 70.85pt 2.0cm}
div.x_WordSection1
{}
-->
</style>
<div lang="FI" link="blue" vlink="purple" style="word-wrap:break-word">
<div style="background-color:#ffffff; border-style:solid; border-color:#f43a13; border-width:2pt; font-size:10pt; line-height:12pt; font-family:'Calibri'; color:Red">
<p class="x_MsoNormal" align="center" style="text-align:center; background:#ffffff">
<span style="font-size:12.0pt; font-weight:bold; color:red">External: Beware of links/attachments.
</span></p>
</div>
<br>
<br>
<div>
<div class="x_WordSection1">
<p class="x_MsoNormal"><span style="">Hi,</span></p>
<p class="x_MsoNormal"><span style=""> </span></p>
<p class="x_MsoNormal"><span lang="EN-US" style="">See </span><span style=""><a href="https://mapserver.org/input/vector/postgis.html"><span lang="EN-US">https://mapserver.org/input/vector/postgis.html</span></a></span><span lang="EN-US" style="">, 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 class="x_MsoNormal"><span lang="EN-US" style=""> </span></p>
<p class="x_MsoNormal"><span lang="EN-US" style="">The SRID value to use is the native SRID of the PostGIS table.</span></p>
<p class="x_MsoNormal" style="margin-bottom:12.0pt"><span lang="EN-US" style="">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 class="x_MsoNormal"><span lang="EN-US" style="">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 class="x_MsoNormal"><span lang="EN-US" style=""> </span></p>
<p class="x_MsoNormal"><span style="">-Jukka Rahkonen-</span></p>
<p class="x_MsoNormal"><span style=""> </span></p>
<div>
<div style="border:none; border-top:solid #E1E1E1 1.0pt; padding:3.0pt 0cm 0cm 0cm">
<p class="x_MsoNormal"><b>Lähettäjä:</b> MapServer-users <<a href="mailto:mapserver-users-bounces@lists.osgeo.org">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">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 class="x_MsoNormal"> </p>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; 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 class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> NAME "PolygonSources"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> CONNECTIONTYPE POSTGIS</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> CONNECTION "xxxxxxxxxx"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> TYPE POLYGON</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> STATUS ON</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> DATA "geom FROM
<span style="background:white">the_table_in_question</span>"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> UTFITEM "cartodb_id"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; 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 class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> TEMPLATE WMSGetFeatureInfo</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> FILTERITEM "featuretyp"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> CLASSITEM "sourceid"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> FILTER "polygon"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> CLASS</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> NAME "polygon"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> EXPRESSION ([sourceid] != 169 AND [sourceid] != 412 AND [sourceid] != 820 AND [sourceid] != 1067)</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> STYLE</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> COLOR "#0B3A71B3"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> OUTLINECOLOR "#AFBFD0B3"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> OUTLINEWIDTH 1.5</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> END</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> END</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> METADATA</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> "wms_title" "Polygon Source Features"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> "wms_include_items" "all"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> "wms_abstract" "Layer of all polygon geometry sources."</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> "wfs_title" "Polygon Source Features"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> "wfs_srs" "EPSG:3857 EPSG:4326"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> "wfs_enable_request" "*"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> "wfs_abstract" "Layer of all polygon geometry sources."</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> "gml_include_items" "all"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> "gml_featureid" "cartodb_id"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> END</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> PROJECTION</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> "init=epsg:3857"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> END</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> PROCESSING "CLOSE_CONNECTION=DEFER"</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="font-size:12.0pt; font-family:"Aptos",sans-serif; color:black"> END</span></p>
</div>
<div class="x_MsoNormal" align="center" style="text-align:center">
<hr size="1" width="98%" align="center">
</div>
<div id="x_divRplyFwdMsg">
<p class="x_MsoNormal"><b><span style="color:black">From:</span></b><span style="color:black"> James Gardner <<a href="mailto:jsg@internode.on.net">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">Rob.Dennett@twdb.texas.gov</a>><br>
<b>Cc:</b> Travis Kirstine <<a href="mailto:traviskirstine@gmail.com">traviskirstine@gmail.com</a>>;
<a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a> <<a href="mailto:mapserver-users@lists.osgeo.org">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 class="x_MsoNormal"> </p>
</div>
</div>
<div>
<div style="border:solid #F43A13 2.25pt; padding:0cm 0cm 0cm 0cm">
<p class="x_xmsonormal" align="center" style="text-align:center; line-height:12.0pt; background:white">
<b><span style="font-size:12.0pt; color:red">External: Beware of links/attachments.
</span></b><span style="font-size:10.0pt; color:red"></span></p>
</div>
<p class="x_MsoNormal" style="margin-bottom:12.0pt"> </p>
<div>
<div>
<p class="x_MsoNormal">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 class="x_MsoNormal">-James Gardner</p>
</div>
</div>
<div>
<p class="x_MsoNormal"> </p>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>