On Sun, 26 May 2024 at 19:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hm, should it be? That's hard-won knowledge, and I'm not sure there
> is a good reason to believe it's no longer applicable.
Okay, so I looked into this a bit more and there's a similar case here
that's imho very clearly doing something incorrectly: num_custom_plans
of prepared statements is not reset when you change the search_path.
When the search_path is changed, there's no reason to assume that the
previous generic plans have any relevance to the new generic plans,
because the tables that are being accessed might be completely
different. See below for an (imho) obviously incorrect choice of using
the generic plan after changing search_path. Maybe the fix for this
issue should be that if a plan gets invalidated, then num_custom_plans
for the source of that plan should be set to zero too. So to be clear,
that means I now think that DISCARD PLANS should also reset
num_custom_plans (as opposed to what I said before).
create schema a;
create schema b;
create table a.test_mode (a int);
create table b.test_mode (a int);
insert into a.test_mode select 1 from generate_series(1,1000000) union
all select 2;
insert into b.test_mode select 2 from generate_series(1,1000000) union
all select 1;
create index on a.test_mode (a);
create index on b.test_mode (a);
analyze a.test_mode;
analyze b.test_mode;
SET search_path = a;
PREPARE test_mode_func(int) as select count(*) from test_mode where a = $1;
\timing on
-- trigger execution 5 times
EXECUTE test_mode_func(1);
EXECUTE test_mode_func(1);
EXECUTE test_mode_func(1);
EXECUTE test_mode_func(1);
EXECUTE test_mode_func(1);
-- slow because of bad plan, even after changing search_path
SET search_path = b;
EXECUTE test_mode_func(1);
\c
-- fast after re-connect, because of custom plan
SET search_path = a;
PREPARE test_mode_func(int) as select count(*) from test_mode where a = $1;
SET search_path = b;
EXECUTE test_mode_func(1);