On Sat, 29 Jul 2023 at 11:59, Jeff Davis <pg...@j-davis.com> wrote: Unfortunately, adding a "SET search_path" clause to functions slows > them down. The attached patches close the performance gap > substantially. > > Changes: > > 0001: Transform the settings in proconfig into a List for faster > processing. This is simple and speeds up any proconfig setting. > > 0002: Introduce CheckIdentifierString(), which is a faster version of > SplitIdentifierString() that only validates, and can be used in > check_search_path(). > > 0003: Cache of previous search_path settings. The key is the raw > namespace_search_path string and the role OID, and it caches the > computed OID list. Changes to the search_path setting or the role can > retrieve the cached OID list as long as nothing else invalidates the > cache (changes to the temp schema or a syscache invalidation of > pg_namespace or pg_role). >
I'm glad to see this work. Something related to consider, not sure if this is helpful: can the case of the caller's search_path happening to be the same as the SET search_path setting be optimized? Essentially, "just" observe efficiently (somehow) that no change is needed, and skip changing it? I ask because substantially all my functions are written using "SET search_path FROM CURRENT", and then many of them call each other. As a result, in my use I would say that the common case is a function being called by another function, where both have the same search_path setting. So ideally, the search_path would not be changed at all when entering and exiting the callee.
