<div dir="ltr">The point still stands. With a join, the performance is a lot worse, more so than accounting for the join itself.</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Nov 9, 2023 at 8:54 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">Apologies, must have copied it in wrong when mailing.<div><br></div><div>The ON clause is there in the source code, otherwise that would be invalid SQL.</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Nov 8, 2023 at 6:10 PM Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</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><div lang="EN-US"><div><p class="MsoNormal">First of all is that really your join clause?  You seem to be missing an ON<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">from building b<u></u><u></u></p><p class="MsoNormal">join customer c<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Also please output<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">EXPLAIN <u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">And the <u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">EXPLAIN ANALYZE of each query<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><div style="border-top:none;border-right:none;border-bottom:none;border-left:1.5pt solid blue;padding:0in 0in 0in 4pt"><div><div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0in 0in"><p class="MsoNormal"><b>From:</b> postgis-users <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>> <b>On Behalf Of </b>Cameron McCloud via postgis-users<br><b>Sent:</b> Wednesday, November 8, 2023 5:38 AM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><b>Cc:</b> Cameron McCloud <<a href="mailto:cameron.mccloud@gmail.com" target="_blank">cameron.mccloud@gmail.com</a>><br><b>Subject:</b> Re: [postgis-users] ST_DWithin slow if query is joined to another table, but fast if ST_DWithin cast to an integer<u></u><u></u></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">Test code is here: <a href="https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql" target="_blank">https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql</a><u></u><u></u></p></div><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">On Wed, Nov 8, 2023 at 9:25 AM Cameron McCloud <<a href="mailto:cameron.mccloud@gmail.com" target="_blank">cameron.mccloud@gmail.com</a>> wrote:<u></u><u></u></p></div><blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt solid rgb(204,204,204);padding:0in 0in 0in 6pt;margin-left:4.8pt;margin-right:0in"><div><p class="MsoNormal">Hi,<u></u><u></u></p><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Postgis Version: 3.3.4<u></u><u></u></p></div><div><p class="MsoNormal">Postgres Version: 14.9<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">We have 2 tables, "building" with lat/long and "customer" with a FK to building. There's a 1:1 relationship between the two.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">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.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">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.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">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.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">-- This query works as expected and takes 60ms returning 6K rows.<u></u><u></u></p></div><div><p class="MsoNormal">select b.*<u></u><u></u></p></div><div><p class="MsoNormal">from building b<u></u><u></u></p></div><div><p class="MsoNormal">where<u></u><u></u></p></div><div><p class="MsoNormal">  st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000);<u></u><u></u></p></div><div><p class="MsoNormal">         <u></u><u></u></p></div><div><p class="MsoNormal">-- This query is orders of magnitude slower - 3000ms, even though joining the two tables without the WHERE takes 30ms<u></u><u></u></p></div><div><p class="MsoNormal">select b.*<u></u><u></u></p></div><div><p class="MsoNormal">from building b<u></u><u></u></p></div><div><p class="MsoNormal">join customer c<u></u><u></u></p></div><div><div><p class="MsoNormal">where<u></u><u></u></p></div><div><p class="MsoNormal">  st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000);<u></u><u></u></p></div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">-- This query converts the result of ST_DWITHIN to an integer. It's fast and takes 80ms<u></u><u></u></p></div><div><div><p class="MsoNormal">select b.*<u></u><u></u></p></div><div><p class="MsoNormal">from building b<u></u><u></u></p></div><div><p class="MsoNormal">join customer c<u></u><u></u></p></div><div><div><p class="MsoNormal">where<u></u><u></u></p></div><div><p class="MsoNormal">  case st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000)<u></u><u></u></p></div></div><div><p class="MsoNormal">    when true then 1<u></u><u></u></p></div><div><p class="MsoNormal">    else 0<u></u><u></u></p></div><div><p class="MsoNormal">  end = 1;<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">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.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Thanks,<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Cam.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><p class="MsoNormal"><u></u> <u></u></p></div></div></blockquote></div></div></div></div></div></blockquote></div>
</blockquote></div>