Re: pg_*_advice: tsv load failure, etc.

Hi,

On Mon, Aug 31, 2026 Jakub Wartak wrote:
> Perhaps adding one statement to the docs would be good enough to cover
> it for now, like: "In order to change the already cached plans, issue
> ANALYZE on relevant tables."

Attached is a doc patch for item 5. I understand that Robert and Noah
agreed to leave the behavior as-is. This patch only documents it.

I did not use the ANALYZE wording, because ANALYZE does not reliably
invalidate cached plans. vac_update_relstats() writes the pg_class
tuple only if a value changed, so an ANALYZE that leaves the pg_class
statistics unchanged sends no relcache invalidation:

LOAD 'pg_plan_advice';
CREATE TABLE t (a int PRIMARY KEY);
INSERT INTO t SELECT generate_series(1, 10000);
VACUUM ANALYZE t;
SET plan_cache_mode = force_generic_plan;
PREPARE q AS SELECT * FROM t WHERE a = $1;
EXPLAIN (COSTS OFF) EXECUTE q(1); -- Index Only Scan
SET pg_plan_advice.advice = 'SEQ_SCAN(t)';
EXPLAIN (COSTS OFF) EXECUTE q(1); -- still Index Only Scan
ANALYZE t;
EXPLAIN (COSTS OFF) EXECUTE q(1); -- still Index Only Scan
INSERT INTO t SELECT generate_series(10001, 12000);
ANALYZE t;
EXPLAIN (COSTS OFF) EXECUTE q(1); -- Seq Scan

The same happens when another session changes an advice stash.

The patch adds a paragraph to the Limitations section of pg_plan_advice
and a short pointer in pg_stash_advice. It says that advice is consulted
only at plan time, mentions DISCARD PLANS for the current session, and
refers to PREPARE for when other sessions re-plan.

Best regards,
Dongpo Liu

On Sunday, October 4th, 2026 at 2:25 PM, Jakub Wartak <jakub(dot)wartak(at)enterprisedb(dot)com> wrote:

> On Fri, Aug 28, 2026 at 4:21 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> >
> >
> > > ### 5. pg_plan_advice.advice / stash advice changes are silently ignored by an already-cached generic plan
> >
> > This is a planner control feature; it does not affect behavior other
> > than at plan time. I don't see that as a bug. That said, I think it
> > would be perfectly valid for someone to try to figure a way for advice
> > stash changes to invalidate plans, but I suspect that will require
> > significant new infrastructure. Plan invalidation is generally tied to
> > catalog modifications, and here we would instead want to tie it to a
> > plan ID. We could do that by adding a custom invalidation type to
> > sinval.h just for the use of pg_stash_advice, but would be a pretty
> > serious piece of core infrastructure for an as-yet-unproven contrib
> > module to use to solve a problem which (for all we know now) may have
> > little practical impact.
>
> FWIW, this was already discussed back in the day I think in [1]. I wouldn't
> remeber it otherwise if not the mailing list, but apparently ANALYZE on the
> table affected was enough to trigger the change [2]. Perhaps adding one
> statement to the docs would be good enough to cover it for now, like: "In
> order to change the already cached plans, issue ANALYZE on relevant tables."
> (because apparently without it it now confused 2 people and 1 AI ;))
>
> -J.
>
> [1] - https://www.postgresql.org/message-id/CA%2BTgmoYO0qtqz%2BV7S4q0e_dLhLrrsMxA51t5wks_y8Skv6cdRQ%40mail.gmail.com
> [2] - https://www.postgresql.org/message-id/CA%2BTgmob8O4TbZVr2zoqm5m-Zp6fj-8iBh%3D0u-xfiy5Xr5MNFCQ%40mail.gmail.com
>
>
>
>
>