[postgis-users] ST_DWithin slow if query is joined to another table, but fast if ST_DWithin cast to an integer

[Cameron McCloud]

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.mccloud at 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.
>
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20231108/7170d968/attachment.htm>