<div dir="ltr">Test code is here: <a href="https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql">https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql</a></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Nov 8, 2023 at 9:25 AM Cameron McCloud <<a href="mailto:cameron.mccloud@gmail.com">cameron.mccloud@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi,<div><br></div><div>Postgis Version: 3.3.4</div><div>Postgres Version: 14.9</div><div><br></div><div>We have 2 tables, "building" with lat/long and "customer" with a FK to building. There's a 1:1 relationship between the two.</div><div><br></div><div>The test tables we're using have 16K rows each. Our production data has a lot more, but we could reproduce this on a smaller dataset.</div><div><br></div><div>We found some odd behaviour when using ST_DWITHIN in a WHERE clause but only when the "building" table is joined to the "customer" table.</div><div><br></div><div>We also found that converting the result of ST_DWITHIN to an integer (1/0 for true/false) and using the integer in the WHERE is fast.</div><div><br></div><div>-- This query works as expected and takes 60ms returning 6K rows.</div><div>select b.*</div><div>from building b</div><div>where</div><div>  st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000);</div><div>         </div><div>-- This query is orders of magnitude slower - 3000ms, even though joining the two tables without the WHERE takes 30ms</div><div>select b.*</div><div>from building b</div><div>join customer c</div><div><div>where</div><div>  st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000);</div><br></div><div>-- This query converts the result of ST_DWITHIN to an integer. It's fast and takes 80ms</div><div><div>select b.*</div><div>from building b</div><div>join customer c</div><div><div>where</div><div>  case st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000)</div></div><div>    when true then 1</div><div>    else 0</div><div>  end = 1;</div><div><br></div><div>We have no idea why this is the case, but we're curious. In our production scenario using the "case...when" brought a query down from 6 minutes to 6 seconds. We'd love to know why this might be and if there are other scenarios like this that could increase the performance of our Postgis queries.</div><div><br></div><div>Thanks,</div><div><br></div><div>Cam.</div><div><br></div><div><br></div><div><br></div><br></div></div>
</blockquote></div>