<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);">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></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> NAME "PolygonSources"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> CONNECTIONTYPE POSTGIS</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);"> CONNECTION "xxxxxxxxxx"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> TYPE POLYGON</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> STATUS ON</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> DATA "geom FROM
</span><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 16px; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">the_table_in_question</span><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> UTFITEM "cartodb_id"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> UTFDATA "{\"cartodb_id\":\"[cartodb_id]\",\"sourceid\":\"[sourceid]\",\"name\":\"[name]\",\"sourcetype\":\"[sourcetype]\",\"drawingord\":\"[drawingord]\",\"featuretyp\":\"[featuretyp]\",\"isnew\":\"[isnew]\"}"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> TEMPLATE WMSGetFeatureInfo</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> FILTERITEM "featuretyp"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> CLASSITEM "sourceid"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> FILTER "polygon"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> CLASS</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> NAME "polygon"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> EXPRESSION ([sourceid] != 169 AND [sourceid] != 412 AND [sourceid] != 820 AND [sourceid] != 1067)</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> STYLE</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> COLOR "#0B3A71B3"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> OUTLINECOLOR "#AFBFD0B3"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> OUTLINEWIDTH 1.5</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> END</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> END</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> METADATA</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> "wms_title" "Polygon Source Features"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> "wms_include_items" "all"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> "wms_abstract" "Layer of all polygon geometry sources."</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> "wfs_title" "Polygon Source Features"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> "wfs_srs" "EPSG:3857 EPSG:4326"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> "wfs_enable_request" "*"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> "wfs_abstract" "Layer of all polygon geometry sources."</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> "gml_include_items" "all"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> "gml_featureid" "cartodb_id"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> END</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> PROJECTION</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> "init=epsg:3857"</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> END</span></div>
<div><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> PROCESSING "CLOSE_CONNECTION=DEFER"</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);"> END</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> James Gardner <jsg@internode.on.net><br>
<b>Sent:</b> Sunday, February 4, 2024 6:44 PM<br>
<b>To:</b> Rob Dennett <Rob.Dennett@twdb.texas.gov><br>
<b>Cc:</b> Travis Kirstine <traviskirstine@gmail.com>; 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>
<div>
<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 dir="auto">Could you try replacing find_srid with a hard coded srid... I found it had to run find_srid on every tuple...
<div dir="auto">-James Gardner</div>
</div>
<div class="x_gmail_extra"><br>
<div class="x_gmail_quote">On 5 Feb 2024 5:39 am, Rob Dennett via MapServer-users <mapserver-users@lists.osgeo.org> wrote:<br type="attribution">
<blockquote class="x_quote" style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex">
<div dir="ltr">
<div style="font-family:'aptos' ,'aptos_embeddedfont' ,'aptos_msfontservice' ,'calibri' ,'helvetica' ,sans-serif; font-size:12pt; color:rgb(0 ,0 ,0)">
Running </div>
<div style="font-family:'aptos' ,'aptos_embeddedfont' ,'aptos_msfontservice' ,'calibri' ,'helvetica' ,sans-serif; font-size:12pt; color:rgb(0 ,0 ,0)">
<br>
</div>
<div style="font-family:'aptos' ,'aptos_embeddedfont' ,'aptos_msfontservice' ,'calibri' ,'helvetica' ,sans-serif; font-size:12pt; color:rgb(0 ,0 ,0)">
select * from pg_indexes where tablename like '%the_table_in_question%' </div>
<div style="font-family:'aptos' ,'aptos_embeddedfont' ,'aptos_msfontservice' ,'calibri' ,'helvetica' ,sans-serif; font-size:12pt; color:rgb(0 ,0 ,0)">
<br>
</div>
<div style="font-family:'aptos' ,'aptos_embeddedfont' ,'aptos_msfontservice' ,'calibri' ,'helvetica' ,sans-serif; font-size:12pt; color:rgb(0 ,0 ,0)">
yields the same results for both the old and new dbs.</div>
<div style="font-family:'aptos' ,'aptos_embeddedfont' ,'aptos_msfontservice' ,'calibri' ,'helvetica' ,sans-serif; font-size:12pt; color:rgb(0 ,0 ,0)">
<br>
</div>
<div style="font-family:'aptos' ,'aptos_embeddedfont' ,'aptos_msfontservice' ,'calibri' ,'helvetica' ,sans-serif; font-size:12pt; color:rgb(0 ,0 ,0)">
As mentioned, the mapserver debug output is very different from the old and new db are very different, even though the .map files are identical except for the connection string.<br>
<br>
There are 8 select statements in each, but the statements aren't the same and the output from the new db is twice as large, about 1.9MB, as the old one. I compiled the queries each one runs and ran them as a group against both dbs. The old db ran both sets
of queries in about 5 seconds each, whereas the new db ran the new set in about 15 seconds and the old set in about 10 seconds.<br>
<br>
I haven't yet run VACUUM on the new db, and will be doing so I Monday, but I am just as confused as to why the debug output would be so different. Can anyone shed some light on that?</div>
<div style="font-family:'aptos' ,'aptos_embeddedfont' ,'aptos_msfontservice' ,'calibri' ,'helvetica' ,sans-serif; font-size:12pt; color:rgb(0 ,0 ,0)">
<br>
</div>
<div style="font-family:'aptos' ,'aptos_embeddedfont' ,'aptos_msfontservice' ,'calibri' ,'helvetica' ,sans-serif; font-size:12pt; color:rgb(0 ,0 ,0)">
Thanks,<br>
Rob</div>
<div></div>
<hr style="display:inline-block; width:98%">
<div dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> Travis Kirstine <traviskirstine@gmail.com><br>
<b>Sent:</b> Sunday, February 4, 2024 10:18 AM<br>
<b>To:</b> Rob Dennett <Rob.Dennett@twdb.texas.gov><br>
<b>Cc:</b> 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>
<div>
<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 align="center" style="text-align:center; background:#ffffff"><span style="font-size:12pt; font-weight:bold; color:red">External: Beware of links/attachments.
</span></p>
</div>
<br>
<br>
<div>
<div dir="ltr">
<div>First thought is to check if the table has a spatial index.</div>
<div><br>
</div>
<div><a href="https://postgis.net/workshops/postgis-intro/indexing.html">https://postgis.net/workshops/postgis-intro/indexing.html</a></div>
</div>
<br>
<div>
<div dir="ltr">On Fri, 2 Feb 2024 at 18:37, Rob Dennett via MapServer-users <<a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a>> wrote:<br>
</div>
<blockquote style="margin:0px 0px 0px 0.8ex; border-left:1px solid rgb(204 ,204 ,204); padding-left:1ex">
<div>
<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)">I recently upgraded our db for our Mapserver from Postgres 11 to Postgres 13 and also had to update Postgis
from 2.4 to 3.4, as well as remove the postgis 2.4 raster queries and add the new postgis_raster extension. Now I have web request which takes over 30 seconds where it used to take about 2. I made copies of the .map file and question and pointed one at the
old db and set the debug level for the map to 2 for both.</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)">These .map files were created by someone who no longer works here and I am not a GIS professional, just
a software developer, so I am having trouble understanding the output.</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)">After hitting the web page for both, I see the debug file is twice as large for the new db as it was
for the old one. The .map file has 4 layers, three of which have a status of on, one for polygons, one for lines and one for point and one called "AllSources" (which is off). For the first 3, they contain </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)">DATA "geom from the_table_in_question"</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)">which is a field of type geometry. In the logs I see lines like </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)">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><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)">There are 8 of these in each output file, but their contents don't match. I am not sure what's generating
them. I do note that they, along with SELECT geom from ...; statements take roughly equal time whether executed against the old db or the new one.</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)">What is generating these log statements?</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)">Why does what is for all intents and purposes the same .map file produce different output? I know it's
because I changed the db engine and postgis, but if you could be more specific, that would be very helpful.</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,</span></div>
<div><span style="font-family:'aptos' ,'aptos_embeddedfont' ,'aptos_msfontservice' ,'calibri' ,'helvetica' ,sans-serif; font-size:12pt; color:rgb(0 ,0 ,0)">Rob</span></div>
</div>
_______________________________________________<br>
MapServer-users mailing list<br>
<a href="mailto:MapServer-users@lists.osgeo.org">MapServer-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
</div>
</blockquote>
</div>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</body>
</html>