Problem with ST_NRings

[Marco Boeringa]

Op 3-12-2025 om 06:56 schreef 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.

Hi Regina,

Thanks for that insight and also the interesting link you included.

I have resolved my issues per your recommendation, and now included the 
'public' schema reference in all necessary places in the few custom 
functions I have. Seems to work fine now.

Marco