> On Nov 8, 2023, at 10:10 AM, Regina Obe via postgis-users > <postgis-users@lists.osgeo.org> wrote: > > First of all is that really your join clause? You seem to be missing an ON > > from building b > join customer c
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… P > > > Also please output > > EXPLAIN > > And the > > EXPLAIN ANALYZE of each query > > From: postgis-users <postgis-users-boun...@lists.osgeo.org> On Behalf Of > Cameron McCloud via postgis-users > Sent: Wednesday, November 8, 2023 5:38 AM > To: postgis-users@lists.osgeo.org > Cc: Cameron McCloud <cameron.mccl...@gmail.com> > Subject: Re: [postgis-users] ST_DWithin slow if query is joined to another > table, but fast if ST_DWithin cast to an integer > > Test code is here: > https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql > > On Wed, Nov 8, 2023 at 9:25 AM Cameron McCloud <cameron.mccl...@gmail.com > <mailto:cameron.mccl...@gmail.com>> wrote: >> Hi, >> >> Postgis Version: 3.3.4 >> Postgres Version: 14.9 >> >> We have 2 tables, "building" with lat/long and "customer" with a FK to >> building. There's a 1:1 relationship between the two. >> >> 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. >> >> 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. >> >> 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. >> >> -- This query works as expected and takes 60ms returning 6K rows. >> select b.* >> from building b >> where >> st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, >> st_makepoint(-96.7804060, 33.2471770)::geography, 50000); >> >> -- This query is orders of magnitude slower - 3000ms, even though joining >> the two tables without the WHERE takes 30ms >> select b.* >> from building b >> join customer c >> where >> st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, >> st_makepoint(-96.7804060, 33.2471770)::geography, 50000); >> >> -- This query converts the result of ST_DWITHIN to an integer. It's fast and >> takes 80ms >> select b.* >> from building b >> join customer c >> where >> case st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, >> st_makepoint(-96.7804060, 33.2471770)::geography, 50000) >> when true then 1 >> else 0 >> end = 1; >> >> 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. >> >> Thanks, >> >> Cam. >> >> >> >> > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
