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 <[email protected]> wrote: > On Fri, Aug 28, 2026 at 4:21 PM Robert Haas <[email protected]> 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 > > > > >
v1-0001-doc-Note-that-plan-advice-does-not-affect-cached-.patch
Description: Binary data
