<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="appendonsend"></div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</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);">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" id="LPlnk">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></div>
<div class="_Entity _EType_OWALinkPreview _EId_OWALinkPreview _EReadonly_1">
<div id="LPBorder_GTaHR0cHM6Ly9kYmEuc3RhY2tleGNoYW5nZS5jb20vcXVlc3Rpb25zLzMwMDI5Mi93aHktaXMtbXktc3BhdGlhbC1xdWVyeS1zbG93ZXItaW4tcG9zdGdyZXMtMTMtdGhhbi1pbi1wb3N0Z3Jlcy0xMQ.." class="LPBorder891747" style="width: 100%; margin-top: 16px; margin-bottom: 16px; position: relative; max-width: 800px; min-width: 424px;">
<table id="LPContainer891747" role="presentation" style="padding: 12px 36px 12px 12px; width: 100%; border-width: 1px; border-style: solid; border-color: rgb(200, 200, 200); border-radius: 2px;">
<tbody>
<tr valign="top" style="border-spacing: 0px;">
<td>
<div id="LPImageContainer891747" style="position: relative; margin-right: 12px; height: 160px; overflow: hidden;">
<a target="_blank" id="LPImageAnchor891747" href="https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11"><img id="LPThumbnailImageId891747" alt="" height="160" style="display: block;" width="160" src="https://cdn.sstatic.net/Sites/dba/Img/apple-touch-icon@2.png?v=246e2cb2439c"></a></div>
</td>
<td style="width: 100%;">
<div id="LPTitle891747" style="font-size: 21px; font-weight: 300; margin-right: 8px; font-family: wf_segoe-ui_light, "Segoe UI Light", "Segoe WP Light", "Segoe UI", "Segoe WP", Tahoma, Arial, sans-serif; margin-bottom: 12px;">
<a target="_blank" id="LPUrlAnchor891747" href="https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11" style="text-decoration: none; color: var(--themePrimary);">Why is my spatial query slower in Postgres
13 than in Postgres 11?</a></div>
<div id="LPDescription891747" style="font-size: 14px; max-height: 100px; color: rgb(102, 102, 102); font-family: wf_segoe-ui_normal, "Segoe UI", "Segoe WP", Tahoma, Arial, sans-serif; margin-bottom: 12px; margin-right: 8px; overflow: hidden;">
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 ...</div>
<div id="LPMetadata891747" style="font-size: 14px; font-weight: 400; color: rgb(166, 166, 166); font-family: wf_segoe-ui_normal, "Segoe UI", "Segoe WP", Tahoma, Arial, sans-serif;">
dba.stackexchange.com</div>
</td>
</tr>
</tbody>
</table>
</div>
</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 class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<hr style="display: inline-block; width: 98%;">
<div id="divRplyFwdMsg" dir="ltr"><span style="font-family: Calibri, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);"><b>From:</b> Travis Kirstine <traviskirstine@gmail.com><br>
<b>Sent:</b> Monday, February 5, 2024 10:50 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?</span>
<div> </div>
</div>
<div style="background-color: rgb(255, 255, 255); border-width: 2pt; border-style: solid; border-color: rgb(244, 58, 19);">
<p align="center" style="line-height: 12pt; background-color: rgb(255, 255, 255);">
<span style="font-family: Calibri; font-size: 12pt; color: red;"><b>External: Beware of links/attachments.</b></span></p>
</div>
<span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br>
<br>
</span>
<div style="direction: ltr;"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">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></div>
<div style="direction: ltr;"><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 style="direction: ltr;"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">I would try running EXPLAIN ANALYZE on the same query on both versions of Postgres
and see what the differences are.</span></div>
<div style="direction: ltr;"><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 style="direction: ltr;"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">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></div>
<div style="direction: ltr;"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </span></div>
<div style="direction: ltr;"><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 style="direction: ltr;"><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 style="direction: ltr;"><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 style="direction: ltr;"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br>
</span></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 style="direction: ltr;"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br>
</span></div>
<blockquote style="margin: 0px 0px 0px 0.8ex; padding-left: 1ex; border-left: 1px solid rgb(204, 204, 204);">
<div style="background-color: rgb(255, 255, 255); border-width: 2pt; border-style: solid; border-color: rgb(244, 58, 19);">
</div>
<div style="padding: 3pt 0cm 0cm; border-width: 1pt medium medium; border-style: solid none none; border-color: rgb(225, 225, 225) currentcolor currentcolor;">
</div>
<div align="center"></div>
<div style="padding: 0cm; border-width: 2.25pt; border-style: solid; border-color: rgb(244, 58, 19);">
</div>
</blockquote>
</body>
</html>