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: Isaac Morland <isaac.morland@gmail.com>, Nathan Bossart <nathandbossart@gmail.com>
Cc: pgsql-hackers@postgresql.org
Date: 2023-08-02T06:06:56Z
Lists: pgsql-hackers 
On Wed, 2023-08-02 at 01:14 -0400, Isaac Morland wrote:
> I don’t think the fact that an optimization might suddenly not work
> in a certain situation is a reason not to optimize. What would our
> query planner look like if we took that approach?

...

> Instead, we should try to find ways of making the performance more
> transparent. We already have some features for this, but maybe more
> can be done.

Exactly; for the planner we have EXPLAIN. We could try to expose GUC
switching costs that way.

Or maybe users can start thinking of search_path as a performance-
related setting to be careful with.

Or we could try harder to optimize the switching costs so that it's not
so bad. I just started and I already saw some nice speedups; with a few
of the easy fixes done, the remaining opportunities should be more
visible in the profiler.

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.