<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body style="overflow-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;">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>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<br id="lineBreakAtBeginningOfMessage"><div><br><blockquote type="cite"><div>On Nov 22, 2024, at 7:54 AM, Fabien Vallée <fabien.vallee@live.fr> wrote:</div><br class="Apple-interchange-newline"><div><meta charset="UTF-8"><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><i>Tested with:</i></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; 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/">https://cdn.proj.org</a><span class="Apple-converted-space"> </span>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 class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><i><br></i></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">or another server with same database:</div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><i><br></i></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; 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/">https://cdn.proj.org</a><span class="Apple-converted-space"> </span>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 class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><br></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><br></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; 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 class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; 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 class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><span id="cid:6c9ebb35-dd6e-47ca-a32e-dbd5808fca3d"><image.png></span></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><br></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><br></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><br></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><br></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">I'm doing a simple query:</div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><br></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>select a.geometrie from ocsge.occupation_sol </b></div><div style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>as a, departement as d</b></div><div style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>where ST_Within( a.geometrie, d.geometrie)</b></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>and d.code_insee = '29'</b></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b><br></b></div><div class="elementToProof" style="font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><br></div><div id="Signature" class="elementToProof" style="caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;"><div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;">The request was working fine before (postgis 3.4)<span class="Apple-converted-space"> </span><b>as far as I can tell<span class="Apple-converted-space"> </span></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;"><span id="cid:df1d1b40-86f6-4b78-9c7b-704102faa68c"><image.png></span></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;"><span style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>select a.geometrie from ocsge.occupation_sol </b></span></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;"><span style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;"><b>select a.geometrie from ocsge.occupation_sol </b></span></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<span class="Apple-converted-space"> </span><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),<span class="Apple-converted-space"> </span><b>ST_Within<span class="Apple-converted-space"> </span></b>takes 52 seconds while<span class="Apple-converted-space"> </span><b>_ST_Within<span class="Apple-converted-space"> </span></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="OWAa6e57bbb-1d08-c9c6-509f-2afe61bd2149" class="OWAAutoLink">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<span class="Apple-converted-space"> </span></code>database<span class="Apple-converted-space"> </span><a href="https://geoservices.ign.fr/bdtopo" id="OWA466fb0fd-074e-0f24-5065-c027ae43db6b" class="OWAAutoLink">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><span id="cid:DD68A04F-9F3D-4055-9E57-60B09B0AB471"><query_plan.json></span></div></blockquote></div><br></div></body></html>