[postgis-devel] ST_DWithin Performance

Zhihong Zhang zhihong at gmail.com
Sat Jan 6 08:10:34 PST 2018


Thanks for the explanation. I understand the reason for the 2nd check now.

All our geometry columns are indexed so the 2nd check is redundant, which causes the performance degradation.

Zhihong

> On Jan 6, 2018, at 11:00 AM, Nicklas Avén <nicklas.aven at jordogskog.no> wrote:
> 
> Hello
> 
> If you get a difference as large as 20% I guess you are not using spatial indexes.
> 
> The reaason for that implementation is that it gives the query planner the possibility to choose if it shall iterate table1 and use the index of table 2 to find overlaps, or if it shall iterate table 2 instead and use the index of table 1 to find overlap.
> 
> The index cannot be used on an expanded geometry (snce the bbox in the index is for the original geometry) so without this double check the planner wouldn't have the choice.
> 
> That would be very bad if only 1 of the tables have an index. It is also a bad thing if the table that gets the sequential scan is the one with millions of rows and the index is used on the table with just a few rows.
> 
> /Nicklas
> 
> 
> On Sat, 2018-01-06 at 10:14 -0500, Zhihong Zhang wrote:
>> ST_DWithin is implemented like this,
>> 
>> CREATE OR REPLACE FUNCTION ST_DWithin(geom1 geometry, geom2 geometry, float8 RETURNS boolean
>> AS 'SELECT $1 OPERATOR(@extschema at .&&) @extschema at .ST_Expand($2,$3) AND $2 OPERATOR(@extschema at .&&) @extschema at .ST_Expand($1,$3) AND @extschema at ._ST_DWithin($1, $2, $3)'
>> LANGUAGE 'sql' IMMUTABLE _PARALLEL;
>> 
>> What’s the purpose of the second bounding box check? 
>> 
>> I did many tests. It’s always faster (about 20%) with one check. So we ended up using our own version of ST_DWithin.
>> 
>> Thanks!
>> 
>> Zhihong
>> 
>> 
>> 
>> 
>> 
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at lists.osgeo.org <mailto:postgis-devel at lists.osgeo.org>
>> https://lists.osgeo.org/mailman/listinfo/postgis-devel <https://lists.osgeo.org/mailman/listinfo/postgis-devel>_______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20180106/925920c1/attachment-0001.html>


More information about the postgis-devel mailing list