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
