[postgis-users] Substantial different index use between 9.5 and 9.6 using Postgis

[Regina Obe]

Tracked it down.  It's caused by the cost changes we made in 2.3.  So it affects 9.5 running 2.3 as well.

I've committed one part which would fix your particular problem.

 

The fix, change the cost on ST_SetSRID from 10 to 1 as follows and that should fix this particular issue.  There are other functions commonly used this way that we have to patch.

 

 

CREATE OR REPLACE FUNCTION st_setsrid(

    geometry,

    integer)

  RETURNS geometry AS

'$libdir/postgis-2.3', 'LWGEOM_set_srid'

  LANGUAGE c IMMUTABLE STRICT PARALLEL SAFE

  COST 1;

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Bill Measday
Sent: Saturday, December 03, 2016 3:41 PM
To: postgis-users at lists.osgeo.org
Subject: Re: [postgis-users] Substantial different index use between 9.5 and 9.6 using Postgis

 

Thanks Regina - nice to know it wasn't just my incompetence!.

Good luck tracking down the root cause.


Rgds


Bill

 

 

On 4/12/2016 6:53 AM, Regina Obe wrote:

For reference I have ticketed this as:

 

https://trac.osgeo.org/postgis/ticket/3675

 

 

 

---------- message ----------



Bill,

Never mind. Thanks to Brian Hamlin's nudging on IRC that he sees the issue
too, I was able to replicate it.


I think the reason I missed it in my testing is that it doesn't happen with
the more common cases of:

SELECT e.gid
FROM e1 , e2
WHERE ST_Intersects(e1.geom, e2.geom);

Or where you use constants in ST_MakePoint like your example


But only in case where you have:

SELECT e.gid
FROM e1 , e2
WHERE ST_Intersects(e1.geom, ST_SetSRID(ST_MakePoint(e2.longitude,
e2.latitude),4326)  );


So I'll go ahead and ticket it and try to track down the root cause.  I
suspect it affects all our relation functions.

Thanks for bringing to our attention,

Regina
http://www.postgis.us
http://postgis.net





 






_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
http://lists.osgeo.org/mailman/listinfo/postgis-users

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161203/638507b5/attachment.html>