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

[Bill Measday]

This looks odd. Here is what pgAdmin tells me if is the current 
definition of st_setsrid (cost of 1.0)


CREATE OR REPLACE FUNCTION public.st_setsrid(
     geog geography,
     srid integer)
     RETURNS geography
     LANGUAGE 'c'
     COST 1.0
     IMMUTABLE NOT LEAKPROOF STRICT
AS '$libdir/postgis-2.3', 'LWGEOM_set_srid'
;

However using you new definition still seems to have fixed the issue.


Anyway,


thanks


Bill


On 4/12/2016 10:19 AM, Regina Obe wrote:
>
> 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
>
>
>
> _______________________________________________
> postgis-users mailing list
> 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/20161204/76221338/attachment.html>