<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;"><br><div><br><blockquote type="cite"><div>On Nov 8, 2023, at 10:10 AM, Regina Obe via postgis-users <postgis-users@lists.osgeo.org> wrote:</div><br class="Apple-interchange-newline"><div><meta charset="UTF-8"><div class="WordSection1" style="page: WordSection1; 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="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">First of all is that really your join clause? You seem to be missing an ON<o:p></o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">from building b<o:p></o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">join customer c</div></div></div></blockquote><div><br></div><div>That error neatly explains the performance issue, since it results in a complete cross join of all the data I think? and then the filters get applied, but by then the damage is done…</div><div><br></div><div>P</div><br><blockquote type="cite"><div><div class="WordSection1" style="page: WordSection1; 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="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p></o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">Also please output<o:p></o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">EXPLAIN<span class="Apple-converted-space"> </span><o:p></o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">And the<span class="Apple-converted-space"> </span><o:p></o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">EXPLAIN ANALYZE of each query<o:p></o:p></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div><div style="border-width: medium medium medium 1.5pt; border-style: none none none solid; border-color: currentcolor currentcolor currentcolor blue; border-image: none; padding: 0in 0in 0in 4pt;"><div><div style="border-width: 1pt medium medium; border-style: solid none none; border-color: rgb(225, 225, 225) currentcolor currentcolor; border-image: none; padding: 3pt 0in 0in;"><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><b>From:</b><span class="Apple-converted-space"> </span>postgis-users <postgis-users-bounces@lists.osgeo.org><span class="Apple-converted-space"> </span><b>On Behalf Of<span class="Apple-converted-space"> </span></b>Cameron McCloud via postgis-users<br><b>Sent:</b><span class="Apple-converted-space"> </span>Wednesday, November 8, 2023 5:38 AM<br><b>To:</b><span class="Apple-converted-space"> </span>postgis-users@lists.osgeo.org<br><b>Cc:</b><span class="Apple-converted-space"> </span>Cameron McCloud <cameron.mccloud@gmail.com><br><b>Subject:</b><span class="Apple-converted-space"> </span>Re: [postgis-users] ST_DWithin slow if query is joined to another table, but fast if ST_DWithin cast to an integer<o:p></o:p></div></div></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">Test code is here: <a href="https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql" style="color: blue; text-decoration: underline;">https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql</a><o:p></o:p></div></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div><div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">On Wed, Nov 8, 2023 at 9:25 AM Cameron McCloud <<a href="mailto:cameron.mccloud@gmail.com" style="color: blue; text-decoration: underline;">cameron.mccloud@gmail.com</a>> wrote:<o:p></o:p></div></div><blockquote style="border-width: medium medium medium 1pt; border-style: none none none solid; border-color: currentcolor currentcolor currentcolor rgb(204, 204, 204); border-image: none; padding: 0in 0in 0in 6pt; margin-left: 4.8pt; margin-right: 0in;" type="cite"><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">Hi,<o:p></o:p></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">Postgis Version: 3.3.4<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">Postgres Version: 14.9<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">We have 2 tables, "building" with lat/long and "customer" with a FK to building. There's a 1:1 relationship between the two.<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">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.<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">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.<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">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.<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">-- This query works as expected and takes 60ms returning 6K rows.<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">select b.*<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">from building b<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">where<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"> st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000);<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"> <o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">-- This query is orders of magnitude slower - 3000ms, even though joining the two tables without the WHERE takes 30ms<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">select b.*<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">from building b<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">join customer c<o:p></o:p></div></div><div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">where<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"> st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000);<o:p></o:p></div></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">-- This query converts the result of ST_DWITHIN to an integer. It's fast and takes 80ms<o:p></o:p></div></div><div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">select b.*<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">from building b<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">join customer c<o:p></o:p></div></div><div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">where<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"> case st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, st_makepoint(-96.7804060, 33.2471770)::geography, 50000)<o:p></o:p></div></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"> when true then 1<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"> else 0<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"> end = 1;<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">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.<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">Thanks,<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;">Cam.<o:p></o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div><div style="margin: 0in; font-size: 11pt; font-family: Calibri, sans-serif;"><o:p> </o:p></div></div></div></blockquote></div></div></div><span 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; float: none; display: inline !important;">_______________________________________________</span><br 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;"><span 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; float: none; display: inline !important;">postgis-users mailing list</span><br 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;"><span 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; float: none; display: inline !important;">postgis-users@lists.osgeo.org</span><br 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;"><span 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; float: none; display: inline !important;">https://lists.osgeo.org/mailman/listinfo/postgis-users</span></div></blockquote></div><br></body></html>