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 <[email protected]> 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 [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
