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