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

[Cameron McCloud]

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/a87d56ef/attachment.htm>