I got a report on the PgBouncer repo[1] that running DISCARD ALL was
not sufficient between connection handoffs to force replanning of
stored procedures. Turns out that while DISCARD AL and DISCARD PLAN
reset the plan cache they do not reset the num_custom_plans fields of
the existing PlanSources. So while the generic plan is re-planned
after DISCARD ALL, the decision on whether to choose it or not won't
be changed. See below for a minimally reproducing example:
create table test_mode (a int);
insert into test_mode select 1 from generate_series(1,1000000) union
all select 2;
create index on test_mode (a);
analyze test_mode;
create function test_mode_func(int)
returns integer as $$
declare
v_count integer;
begin
select into v_count count(*) from test_mode where a = $1;
return v_count;
END;
$$ language plpgsql;
\timing on
-- trigger execution 5 times
SELECT test_mode_func(1);
SELECT test_mode_func(1);
SELECT test_mode_func(1);
SELECT test_mode_func(1);
SELECT test_mode_func(1);
DISCARD ALL;
-- slow because of bad plan, even after DISCARD ALL
SELECT test_mode_func(2);
\c
-- fast after re-connect, because of custom plan
SELECT test_mode_func(2);
[1]: https://github.com/pgbouncer/pgbouncer/issues/912#issuecomment-2131250109
