> > 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.
