[postgis-devel] ST_DWithin Performance

[Nicklas Avén]

Well, it is not redundant if one of the tables have many rows and the
other just a few.
Then it is 50 % chance that you get the wrong plan without both tests.

/Nicklas

On Sat, 2018-01-06 at 11:10 -0500, Zhihong Zhang wrote:
> 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 booleanAS '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
> > > 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
> 
> _______________________________________________
> 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/3ec960b7/attachment.html>