<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=iso-8859-1">
<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:"Segoe UI";
panose-1:2 11 5 2 4 2 4 2 2 3;}
@font-face
{font-family:"Segoe UI Light";
panose-1:2 11 5 2 4 2 4 2 2 3;}
@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;}
span.Shkpostityyli20
{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 lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">I made a test with a PostGIS on my own laptop. My versions are:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">"POSTGIS=""3.4.1 3.4.1"" [EXTENSION] PGSQL=""160"" GEOS=""3.12.1-CAPI-1.18.1"" SFCGAL=""SFCGAL 1.5.0, CGAL 5.6, BOOST 1.78.0"" PROJ=""8.2.1
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">The polygon layer has 215000 lake polygons. My BBOX select 73 polygons. It takes 66 milliseconds.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">My query and the execution plan are as follows:<br>
<br>
select * from jarvinemo<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">where "geom" &&<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">ST_GeomFromText(<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">'POLYGON (( 314728.6874003611 6936494.124854623, 314728.6874003611 6946067.332484153, 327290.0638853506 6946067.332484153, 327290.0638853506 6936494.124854623,
314728.6874003611 6936494.124854623 ))');<br>
<br>
"Bitmap Heap Scan on jarvinemo (cost=4.58..155.40 rows=39 width=588) (actual time=0.048..0.076 rows=73 loops=1)"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">" Recheck Cond: (geom && '0103000000010000000500000059E1E5BFA23513413F9EFD87EB755A4159E1E5BFA23513419D6B47D5447F5A414E296B41E8F913419D6B47D5447F5A414E296B41E8F913413F9EFD87EB755A4159E1E5BFA23513413F9EFD87EB755A41'::geometry)"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">" Heap Blocks: exact=64"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">" -> Bitmap Index Scan on jarvinemo_geom_geom_idx (cost=0.00..4.58 rows=39 width=0) (actual time=0.039..0.039 rows=73 loops=1)"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">" Index Cond: (geom && '0103000000010000000500000059E1E5BFA23513413F9EFD87EB755A4159E1E5BFA23513419D6B47D5447F5A414E296B41E8F913419D6B47D5447F5A414E296B41E8F913413F9EFD87EB755A4159E1E5BFA23513413F9EFD87EB755A41'::geometry)"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">"Planning Time: 0.142 ms"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">"Execution Time: 0.106 ms"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">I can see that the bbox (POLYGON) in your query is big, something like 1500-2500 kilometres wide, and covers almost the whole EXTENT of your data. That
means that the spatial filter is ineffective because it does not filter out anything. Have a try with a small polygon as a filter and compare the execution plan with the one that my database makes. That helps you to find out if the spatial index kicks in at
some time. Of course, that does not help when the map covers the whole area. Then all the data must be read. But spending 5 seconds (the old db) or 15 seconds (the new db) for that as you have written before feels very slow. I wonder if the slowness comes
from the connection to the database rather than from the database itself. It is some hosted database, cartodb perhaps by some logs that you have showed. Have you made any tests with a local db?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">-Jukka Rahkonen-<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"><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 <mapserver-users-bounces@lists.osgeo.org>
<b>Puolesta </b>Rob Dennett via MapServer-users<br>
<b>Lähetetty:</b> tiistai 6. helmikuuta 2024 21.02<br>
<b>Vastaanottaja:</b> mapserver-users@lists.osgeo.org<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"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">So, I ran explain analyze, and got different results when run against the old and new db. I ran VACUUM on the table (which ran in less than ½ a second) and checked
the query plans again. They're now the same except for the time estimates and against the new db they're still much slower.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">We ran across a StackExchange article about performance degradation after upgrading to postgres 13 and postgis 3.4 (<a href="https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11">https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11</a>)
and they mention a change in the way postgis works, but since these queries are generated by mapserver, I am not sure what I can do on my end. Is there a version of MapServer that's recommended for Postgres 13/PostGIS 3?</span><o:p></o:p></p>
</div>
<div>
<div style="margin-top:12.0pt;margin-bottom:12.0pt;min-width: 424px" id="LPBorder_GTaHR0cHM6Ly9kYmEuc3RhY2tleGNoYW5nZS5jb20vcXVlc3Rpb25zLzMwMDI5Mi93aHktaXMtbXktc3BhdGlhbC1xdWVyeS1zbG93ZXItaW4tcG9zdGdyZXMtMTMtdGhhbi1pbi1wb3N0Z3Jlcy0xMQ..">
<table class="MsoNormalTable" border="1" cellspacing="3" cellpadding="0" width="100%" style="width:100.0%;border:solid #C8C8C8 1.0pt">
<tbody>
<tr>
<td valign="top" style="border:none;padding:9.0pt 27.0pt 9.0pt 9.0pt">
<div style="margin-right:9.0pt;overflow:hidden" id="LPImageContainer891747">
<p class="MsoNormal"><a href="https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11" target="_blank"><span style="text-decoration:none"><img border="0" width="160" height="160" style="width:1.6666in;height:1.6666in" id="_x0000_i1027" src="https://cdn.sstatic.net/Sites/dba/Img/apple-touch-icon@2.png?v=246e2cb2439c"></span></a><o:p></o:p></p>
</div>
</td>
<td width="100%" valign="top" style="width:100.0%;border:none;padding:9.0pt 27.0pt 9.0pt 9.0pt">
<div style="margin-right:6.0pt;margin-bottom:9.0pt" id="LPTitle891747">
<p class="MsoNormal"><span style="font-size:16.0pt;font-family:"Segoe UI Light",sans-serif"><a href="https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11" target="_blank"><span style="text-decoration:none">Why
is my spatial query slower in Postgres 13 than in Postgres 11?</span></a><o:p></o:p></span></p>
</div>
<div style="margin-right:6.0pt;margin-bottom:9.0pt;max-height: 100px;overflow:hidden" id="LPDescription891747">
<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Segoe UI",sans-serif;color:#666666">Postgres versions PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1)
on x86_64-pc-linux-gnu, compiled ...<o:p></o:p></span></p>
</div>
<div id="LPMetadata891747">
<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Segoe UI",sans-serif;color:#A6A6A6">dba.stackexchange.com<o:p></o:p></span></p>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div class="MsoNormal" align="center" style="text-align:center">
<hr size="2" width="98%" align="center">
</div>
<div id="divRplyFwdMsg">
<p class="MsoNormal"><b><span style="color:black">From:</span></b><span style="color:black"> Travis Kirstine <<a href="mailto:traviskirstine@gmail.com">traviskirstine@gmail.com</a>><br>
<b>Sent:</b> Monday, February 5, 2024 10:50 AM<br>
<b>To:</b> Rob Dennett <<a href="mailto:Rob.Dennett@twdb.texas.gov">Rob.Dennett@twdb.texas.gov</a>><br>
<b>Cc:</b> <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 style="border:solid #F43A13 2.25pt;padding:0cm 0cm 0cm 0cm">
<p 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><o:p></o:p></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><o:p> </o:p></p>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">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. </span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">I would try running EXPLAIN ANALYZE on the same query on both versions of Postgres and see what the differences are.</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black">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')</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Aptos",sans-serif;color:black"> </span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</body>
</html>