<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
p.xmsonormal, li.xmsonormal, div.xmsonormal
{mso-style-name:x_msonormal;
margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.Shkpostityyli22
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
mso-ligatures:none;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="FI" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">See </span>
<span style="mso-fareast-language:EN-US"><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="mso-fareast-language: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"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">The SRID value to use is the native SRID of the PostGIS table.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span lang="EN-US" style="mso-fareast-language: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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language: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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="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?<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> NAME "PolygonSources"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> CONNECTIONTYPE POSTGIS</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> CONNECTION "xxxxxxxxxx"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> TYPE POLYGON</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> STATUS ON</span><o:p></o:p></p>
</div>
<div>
<p class="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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> UTFITEM "cartodb_id"</span><o:p></o:p></p>
</div>
<div>
<p class="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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> TEMPLATE WMSGetFeatureInfo</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> FILTERITEM "featuretyp"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> CLASSITEM "sourceid"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> FILTER "polygon"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> CLASS</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> NAME "polygon"</span><o:p></o:p></p>
</div>
<div>
<p class="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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> STYLE</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> COLOR "#0B3A71B3"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> OUTLINECOLOR "#AFBFD0B3"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> OUTLINEWIDTH 1.5</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> END</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> END</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> METADATA</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> "wms_title" "Polygon Source Features"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> "wms_include_items" "all"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> "wms_abstract" "Layer of all polygon geometry sources."</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> "wfs_title" "Polygon Source Features"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> "wfs_srs" "EPSG:3857 EPSG:4326"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> "wfs_enable_request" "*"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> "wfs_abstract" "Layer of all polygon geometry sources."</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> "gml_include_items" "all"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> "gml_featureid" "cartodb_id"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> END</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> PROJECTION</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> "init=epsg:3857"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> END</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> PROCESSING "CLOSE_CONNECTION=DEFER"</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> END</span><o:p></o:p></p>
</div>
<div class="MsoNormal" align="center" style="text-align:center">
<hr size="1" width="98%" align="center">
</div>
<div id="divRplyFwdMsg">
<p class="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>
<o:p></o:p></p>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
</div>
<div>
<div style="border:solid #F43A13 2.25pt;padding:0cm 0cm 0cm 0cm">
<p class="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"><o:p></o:p></span></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">Could you try replacing find_srid with a hard coded srid... I found it had to run find_srid on every tuple...
<o:p></o:p></p>
<div>
<p class="MsoNormal">-James Gardner<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</div>
</div>
</body>
</html>