<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" style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hi Paul, thanks for your answer.</div>
<div class="elementToProof" style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I think the issue can be reproduced checking if any (wgs84) geometry is within "my" Finistère geometry (reproduced problem with 2 tables containing unrelated geometries) as long as you check with a big fat table (in my case, ST_Within is slow with 35k geometries,
unusable with 4.8 millions)</div>
<div style="line-height: normal; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
Unfortunately I can't provide the postgis_full_version of the "fast" server (didn't have this issue before system and I didn't save the versions).</div>
<div style="line-height: normal; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
Regarding the index, yes the query plan show index is used, but it's slow just like if there was no index (testing against 4.8 million geometries would take forever with no index). Maybe it's unrelated.</div>
<div style="line-height: normal; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
</div>
<div style="line-height: normal; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
Also, I changed my query to "WHERE a.geometry && d.geometry AND _ST_Within(a.geometry, d.geometry)" and it's working well (just like I expected ST_Within to)</div>
<div style="line-height: normal; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
Let me know if you find anything, if needed I will provide an extract of the db with enough data to test.</div>
<div style="line-height: normal; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
</div>
<div style="line-height: normal; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
Thanks a lot,</div>
<div style="line-height: normal; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
Fabien Vallée</div>
<div id="Signature" class="elementToProof">
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<p><br>
</p>
</div>
<div id="appendonsend"></div>
<div style="font-family: 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>De :</b> Paul Ramsey <pramsey@cleverelephant.ca><br>
<b>Envoyé :</b> vendredi 22 novembre 2024 19:28<br>
<b>À :</b> Fabien Vallée <fabien.vallee@live.fr><br>
<b>Cc :</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br>
<b>Objet :</b> Re: postgis 3.5.0: ST_Within huge performance issue (regression?) with specific geometry (multipolygon)</span>
<div> </div>
</div>
<div>In order to replicate this I’m going to need both sides of the join. Can you post the ocsge.occupation_sol table as well? Just the geometry column and a unique id is enough. Can you get the postgis_full_version from the server that is “fast”?</div>
<div>Why do you say the index is not being used? Both query plans show what I would expect, which is a nested loop with the small table in a seq scan and the large table (ocsge.occupation_sol) as an index scan.</div>
<div>ATB,</div>
<div>P</div>
<div><br>
</div>
<blockquote>
<div>On Nov 22, 2024, at 7:54 AM, Fabien Vallée <fabien.vallee@live.fr> wrote:</div>
<br>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<i>Tested with:</i></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<i>"POSTGIS=""3.5.0 d2c3ca4"" [EXTENSION] PGSQL=""160"" GEOS=""3.12.1-CAPI-1.18.1"" PROJ=""9.4.0 NETWORK_ENABLED=OFF URL_ENDPOINT=<a href="https://cdn.proj.org/" id="OWA53ea985a-3fe1-65cb-9a3e-8d7c363b6b02" class="OWAAutoLink" shash="yk+hPc7rhdEybbm0xJ9rKxqbqO8oXY8fX0elq0+2niSXk7ENeY5aAQ8kF3fmYhE9TWewTt9ZGfJGUevukOKu5hl32L1zlWkpJD3dYCFMKAOF4cJgqgusET4ewPTmqawClyuGg3nHkNFQJIukl18cXyotXsH6LAJ2iwPGrf2ugM0=" originalsrc="https://cdn.proj.org/" data-auth="Verified">https://cdn.proj.org</a> USER_WRITABLE_DIRECTORY=/tmp/proj
DATABASE_PATH=/usr/share/proj/proj.db"" LIBXML=""2.9.14"" LIBJSON=""0.17"" LIBPROTOBUF=""1.4.1"" WAGYU=""0.5.0 (Internal)"" (core procs from ""3.4.2 c19ce56"" need upgrade)"</i></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<i><br>
</i></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
or another server with same database:</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<i><br>
</i></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<i>"POSTGIS=""3.5.0 d2c3ca4"" [EXTENSION] PGSQL=""170"" GEOS=""3.10.2-CAPI-1.16.0"" PROJ=""8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=<a href="https://cdn.proj.org/" id="OWAc73b7e61-d0eb-1508-8ad2-c5acc584acd6" class="OWAAutoLink" shash="JhY5tzF/EuXCqAO1CN0hJrIgbXzogoBahgpDyBYP0+vhEKhj0Jpapr7qZv/zE9AZC5d9z3kpWeiL1teahIwOYRNNNrx1FM15FM1xfcfJ36PW7sU3KqB5+keuV27alYQMO9clr57vlgRdR4q40Nc6KEipMr9S38PWWfdxG1aG6aM=" originalsrc="https://cdn.proj.org/" data-auth="Verified">https://cdn.proj.org</a> USER_WRITABLE_DIRECTORY=/tmp/proj
DATABASE_PATH=/usr/share/proj/proj.db"" (compiled against PROJ 8.10.2) LIBXML=""2.9.13"" LIBJSON=""0.15"" LIBPROTOBUF=""1.3.3"" WAGYU=""0.5.0 (Internal)"""</i></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
Context: departement contains only 101 geometries, but ocsge.occupation_sol is quite big (4860333 entries). BOTH SRID 4326.</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
The geometry i'm searching within is a multipolygon (many islands) (and I don't get same behavior searching within something simpler)</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<image.png></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
I'm doing a simple query:</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<b>select a.geometrie from ocsge.occupation_sol </b></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<b>as a, departement as d</b></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<b>where ST_Within( a.geometrie, d.geometrie)</b></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<b>and d.code_insee = '29'</b></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<b><br>
</b></div>
<div style="text-align: left; text-indent: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div id="x_Signature" class="x_elementToProof" style="font-family:HelveticaNeue;font-size:13px;text-decoration:none">
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">The request was working fine before (postgis 3.4)
<b>as far as I can tell </b>with results within a few seconds max. In that specific case, I am expecting the query</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">to return 0 results but now (postgis 3.5) I never get any response, the request just<b>timeout.</b></div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"> The query is supposed to use a geometry index (gist):</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><image.png></div>
<div style="margin-top: 0px; margin-bottom: 0px;">It looks like the index is not used at all (I tried to re-index both tables, no changes). If I an intersects instead, I get results within 3 seconds:</div>
<div style="margin-top: 0px; margin-bottom: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<b>select a.geometrie from ocsge.occupation_sol </b></div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>as a, departement as d</b></div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>where ST_Intersects(a.geometrie, d.geometrie)</b></div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>and d.code_insee = '29'</b></div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
(returns 310 results)</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
If I do both intersects + within:</div>
<div style="margin-top: 0px; margin-bottom: 0px; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<b>select a.geometrie from ocsge.occupation_sol </b></div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>as a, departement as d</b></div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>where ST_Intersects(a.geometrie, d.geometrie) and ST_Within(a.geometrie, d.geometrie)</b></div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>and d.code_insee = '29'</b></div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
I get 0 results (as expected) but it's very slow (50 secondes) (query plan attached).</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
Testing more, I figured out that <b>_ST_Within</b> is working fine and is much much faster than<b>ST_Within.</b></div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
Testing with same geometry (bloody Finistère) against another table (35k rows), <b>
ST_Within </b>takes 52 seconds while <b>_ST_Within </b>"only" takes 13 seconds (both with 277 results).<br>
ST_Intersects takes 5s with 298 results.</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
I've also figured out that I don't reproduce the issue testing with searching within another (much simplier) geometry.</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
The geometry causing trouble can be downloaded (as geojson, 29MB) here:</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<code><a href="https://send.smart4.io/f.php?h=1G_6egdP&d=1" id="OWAaf53d3ab-2b0f-9350-a327-ab3b7d2bc456" class="x_OWAAutoLink" shash="I6Ps6GTzqKHTk9WAR65yYe42Df3bp1ejUh8cxDURZ/C/WSNg8qLUtJLdmAbFXQBqXcL9SAbulB5qhKZU9ninHcXLuXRT6c+ri9WV9er4Elm2H4h2gmZtSDItjlxtxIrVXea01WPiwCYyCmPeLVvY8c0M0QRkcYKKE+aZMgf3FXM=" originalsrc="https://send.smart4.io/f.php?h=1G_6egdP&d=1" data-auth="Verified">https://send.smart4.io/f.php?h=1G_6egdP&d=1</a></code></div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<code>(from bdtopo </code>database <a href="https://geoservices.ign.fr/bdtopo" id="OWAffe87755-5158-37c0-9d7a-2ca8d95560d0" class="x_OWAAutoLink" shash="g0MFqFrrvgWhurJyZLf/681suurxaH0EWfYLyeHvkMc0taqgxeQfmHp0zbmGHXV3T3VOjL1oVI6km0icxLnl1e1qnwFmeEeib7Ort1fZIMV5upBJfvvSpoqfIXADAlUT0Y4CR5PWH59+23PtVv9R7SSH560NpxVp5yqbtz+y59U=" originalsrc="https://geoservices.ign.fr/bdtopo" data-auth="Verified">
https://geoservices.ign.fr/bdtopo</a>)</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
Please let me know if I'm missing something or if you need anything else to investiguate. Thanks a lot,</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
Fabien Vallée</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
<div style="margin-top: 1em; margin-bottom: 1em; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">
<br>
</div>
</div>
<query_plan.json></blockquote>
<br>
</body>
</html>