Re: Faster "SET search_path"

On Wed, 2 Aug 2023 at 01:07, Nathan Bossart <nathandbossart(at)gmail(dot)com>
wrote:

> On Mon, Jul 31, 2023 at 10:28:31PM -0700, Jeff Davis wrote:
> > On Sat, 2023-07-29 at 12:44 -0400, Isaac Morland wrote:
> >> Essentially, "just" observe efficiently (somehow) that no change is
> >> needed, and skip changing it?
> >
> > I gave this a try and it speeds things up some more.
> >
> > There might be a surprise factor with an optimization like that,
> > though. If someone becomes accustomed to the function running fast,
> > then changing the search_path in the caller could slow things down a
> > lot and it would be hard for the user to understand what happened.
>
> 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.
>

Speaking as someone who uses a lot of stored procedures, many of which call
one another, I definitely want this optimization.

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? Many people regularly fix performance
problems by making inscrutable adjustments to queries, sometimes after
having accidentally ruined performance by modifying the query. 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.