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

[Cameron McCloud]

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.mccloud at 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 <lr at 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-bounces at lists.osgeo.org> *On Behalf
>> Of *Cameron McCloud via postgis-users
>> *Sent:* Wednesday, November 8, 2023 5:38 AM
>> *To:* postgis-users at lists.osgeo.org
>> *Cc:* Cameron McCloud <cameron.mccloud at 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.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/20231109/0b64e81d/attachment.htm>