Problem with ST_NRings

[Regina Obe]

> > It would be better if you used the fully qualified name.
> > Using search_path in a function crushes query performance.
> >
> >   CREATE FUNCTION my_func(public.geometry)
> >       RETURNS int
> >       AS $$ SELECT public.ST_NRings($1); $$ LANGUAGE sql;
> 
> Hi Regina,
> 
> Thanks for that important warning about the performance aspect. However,
> do you possibly have any real world figures of how much of a difference this
> makes? Just doing an internet search on 'search_path' and 'performance'
> doesn't turn up much of relevant results for something that might be critical. I
> can see how a custom search path loaded with objects might turn a 'search'
> into a heavy operation, but I would expect PostgreSQL to at least cache some
> reference to the main functions and objects of system 'public', 'pg_catalog'
> and 'pg_temp' schema's, but maybe I am just naive here?
> 
> Although I could change the code, having to add 'public' before each PostGIS
> function or class, would make my Python code assembling queries a lot less
> readable. Or is the performance issue you express concerns about mainly
> related to function definitions like in CREATE FUNCTION, which would make
> adjustment much more straightforward, as it would only require modifying the
> function definitions themselves, not all uses in SQL?
> 
> Marco

Only pertains to putting  it in functions variable settings.
As I understand it changes the function then runs in isolation.

I did this earlier on putting it in postgis search path function setting, cause that was faster than schema qualifying
And it made  queries so unbearably slow that I had to roll back that change.
I thought maybe it would only affect index usage, but it even slowed down functions such as ST_Distance.

Tom Lane described it here:

https://www.postgresql.org/message-id/9914.1457628521%40sss.pgh.pa.us


We might want to retry again to see if later versions have changed as it was almost 10 years ago when we tried this.