Horton

Re: Faster "SET search_path"

Jeff Davis <pgsql@j-davis.com>

View thread View on postgr.es
From: Jeff Davis <pgsql@j-davis.com>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Isaac Morland <isaac.morland@gmail.com>, pgsql-hackers@postgresql.org
Date: 2023-08-02T05:53:11Z
Lists: pgsql-hackers 
On Tue, 2023-08-01 at 22:07 -0700, Nathan Bossart wrote:
> I wonder if this is a good enough reason to _not_ proceed with this
> optimization.  At the moment, I'm on the fence about it.

I was wondering the same thing. It's something that could reasonably be
explained to users; it's not what I'd call "magical", it's just
avoiding an unnecessary SET. But I could certainly see it as a cause of
confusion: "why is this query faster for user foo than user bar?".

Another concern is that the default search_path is "$foo, public" but
the safe search_path is "pg_catalog, pg_temp". So if we automatically
switch to the safe search path for functions in index expressions (as I
think we should do, at least ideally), then they'd be slow by default.
We'd need to start advising people to set their search_path to
"pg_catalog, pg_temp" to speed things up.

I'm not opposed to this optimization, but I'm not sure about it either.

Regards,
	Jeff Davis

Commits

  1. Fix missing invalidations for search_path cache.

  2. Optimize SearchPathCache by saving the last entry.

  3. Optimize check_search_path() by using SearchPathCache.

  4. Be more paranoid about OOM in search_path cache.

  5. Add cache for recomputeNamespacePath().

  6. Transform proconfig for faster execution.