The point still stands. With a join, the performance is a lot worse, more so than accounting for the join itself.
On Thu, Nov 9, 2023 at 8:54 AM Cameron McCloud <cameron.mccl...@gmail.com> wrote: > Apologies, must have copied it in wrong when mailing. > > The ON clause is there in the source code, otherwise that would be invalid > SQL. > > On Wed, Nov 8, 2023 at 6:10 PM Regina Obe <l...@pcorp.us> wrote: > >> First of all is that really your join clause? You seem to be missing an >> ON >> >> >> >> from building b >> >> join customer c >> >> >> >> >> >> 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> >> 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