Re: pg_plan_advice (now with transparent SQL plan performance overrides - pg_stash_advice)

On Fri, Feb 27, 2026 at 11:46 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Thu, Feb 26, 2026 at 8:55 AM Robert Haas <robertmhaas@gmail.com> wrote:
> Here's v18. [..]
[..]
> Second, I also added a third contrib module called pg_stash_advice.
> This uses the same hook that I previously added for test_plan_advice,
> but unlike that module, this one's not just a test. It lets you set up
> an "advice stash" which is basically a query_id->advice_string hash
> table. If you then set pg_stash_advice.stash_name to the name of your
> advice stash, it will do a lookup into that hash table every time a
> query is planned and, if the query ID is found, it will do the
> planning with the corresponding advice string. What I think is
> particularly cool about this is that it shows that you can really use
> that hook to apply advice on the fly in any way you want. I suspect
> that query ID matching will be suitable for a lot of use cases, but
> you could have a similar module that matches on query text or does
> anything else that you want as long as an advice string pops out at
> the end. It shows that the core pg_plan_advice infrastructure is
> pluggable. So this is both something that I think a lot of people will
> find useful all on its own, and also a design pattern that people can
> copy and adapt.

Hi Robert, I'm glad that you posted this. I've thought this is so important that
I've sligthly added more descriptive subject as this is NEW and easily to miss
out as it was burried in the second paragraph and most folks probably would miss
that this $thread is now includes code for transparent SQL plan
overrides (most known
as baselines or SQL plan management) - it has many names, but I havent come
across "stash" as one of them, so people could miss it too easily.

1. First thought is that I found it quite surprising, we now have 3 modules
and it's might cause confusion and lack of consistency:
- 3 can to be in shared_preload_libraries (pg_stash_advice,
pg_plan_advice, pg_collecti_advice)
- 2 others can use CREATE EXTENSION (pg_stash_advice, pg_collect_advice), but
  "create extension pg_plan_advice;" fails
so maybe they all should behave the same as people (including me) won't read
the docs and just blindly add it here and there and issue CREATE EXTENSION,
but it's going to be hard to remember for which ones (?) So we need more
consistency?

2. Should pgca always duplicate entries like that? (each call gets new entry?)
Shouldn't it just update collection_time for identical calls of
userid/dbid/queryid?

postgres=# select * from pg_get_collected_shared_advice();
 id | userid | dbid |       queryid       |        collection_time
   |                      query                      |
 advice
----+--------+------+---------------------+-------------------------------+-------------------------------------------------+-------------------------------------------
  0 |     10 |    5 | 1069089066624131207 | 2026-03-03
09:47:40.322294+01 | select * from pg_get_collected_shared_advice(); |
NO_GATHER(pg_get_collected_shared_advice)
(1 row)

postgres=# select * from pg_get_collected_shared_advice();
 id | userid | dbid |       queryid       |        collection_time
   |                      query                      |
 advice
----+--------+------+---------------------+-------------------------------+-------------------------------------------------+-------------------------------------------
  0 |     10 |    5 | 1069089066624131207 | 2026-03-03
09:47:40.322294+01 | select * from pg_get_collected_shared_advice(); |
NO_GATHER(pg_get_collected_shared_advice)
  1 |     10 |    5 | 1069089066624131207 | 2026-03-03
09:47:41.983973+01 | select * from pg_get_collected_shared_advice(); |
NO_GATHER(pg_get_collected_shared_advice)
(2 rows)

It floods like that for everything, most visible with couple of independent
starts of pgbench -M prepared. Maybe I'm wrong , but I don't think it worked
like that earlier before refactor?

3. The good news is that I have managed to finally overrride SQL plans
completley transparently using set of those modules for online pgbench runs.
However I found couple of issues.

3a. Because query_id each time will be different for every query even
in standard
pgbench mode, I've managed to achieve it only using prepared statements.
Realistically we'll need some way of modular matching not just on query_id OR
query_id should be changed how it being calculcated or maybe by some other means
(argument is: not all users/apps use prepared statements):
- at least some minimal query jumbling, part of me belives that e.g. code from
  62d712ecfd940 / pgss's generate_normalized_query() should be more reusable
  across other extensions, and then why not just use it here?
- maybe it should: ignore uppercase vs lowercase, removal of extra whitespaces
- maybe it should: ignore schema names (in multi-tenant shops)
- maybe it should: remove SQL comments (/*+  xxx */) or newlines (as there are
  client-side libraries that annotate SQL queries by putting comments to locate
  e.g. app source code location/origin)
- maybe we even should have some regexp

I'm not buying argument that it will make something slower, because :
a) this is on-demand loaded module for those who want it
b) it exists purely to avoid way bigger performance problems in the first place

3b. When performing manual testing using PREPARE s1 / EXECUTE s1, the SQL plans
are effective out of the box. Below I'm intentionally downgrading runtime
performance and that works:

postgres=# PREPARE p1 (int) AS UPDATE pgbench_accounts SET abalance =
abalance + $1 WHERE aid = $2;
PREPARE
postgres=# \timing on
Timing is on.
postgres=# EXECUTE p1(42, 42);
UPDATE 1
Time: 8.241 ms

-- making it slow, queryid from explain (verbose), same session:
postgres=# select pg_set_stashed_advice('abc456',
-9041336337128051785, 'SEQ_SCAN(pgbench_accounts)');
 pg_set_stashed_advice
-----------------------

(1 row)

Time: 0.995 ms
postgres=# show pg_stash_advice.stash_name ;
 pg_stash_advice.stash_name
----------------------------
 abc456
(1 row)

Time: 0.366 ms
-- OK, that's effective (up from 8ms)
postgres=# EXECUTE p1(42, 42);
UPDATE 1
Time: 448.415 ms

3c. However for pgbench -M prepared, such online plan alterations are
strangley not effective. Even creating new stash under different name, setting
GUC, and pg_reloading_conf is not effective too for hundreths of seconds of
pgbench.

3d. ... however restarting restarting pgbench helps and the session
changes plan (and thus
performance characteristics). When using manual way of reproducing
this more like:

sess1=# PREPARE p1 (int) AS UPDATE pgbench_accounts SET abalance =
abalance + $1 WHERE aid = $2;
PREPARE
sess1=# EXECUTE p1(42, 42);
UPDATE 1
sess1=# \timing on
Timing is on.
sess1=# EXECUTE p1(42, 42);
UPDATE 1
Time: 8.823 ms

-- now from 2nd session
sess2=# select pg_set_stashed_advice('abc456', -9041336337128051785,
'SEQ_SCAN(pgbench_accounts)');

-- .. it was NOT effective, and still fast, but it should be slow:
sess1=# EXECUTE p1(42, 42);
UPDATE 1
Time: 8.781 ms
postgres=#

However doing it from sess2 sometimes, makes it often faster effective:
postgres=# select pg_create_advice_stash('xyz123');
postgres=# select pg_set_stashed_advice('xyz123',
-9041336337128051785, 'SEQ_SCAN(pgbench_accounts)');
postgres=# alter system set pg_stash_advice.stash_name TO 'xyz123';
postgres=# select pg_reload_conf();

but apparently that was still not solving the pgbench case problem.
So it doesn't seem to be deterministic when the new plan is applied (?)

3e. As this was pretty concerning, I've repeated the pgbench -M prepared
excercise and I've figured out that I could achieve effect that I wanted (
boucing between fast <-> slow immediatley) by injecting call via gdb on that
backend to InvalidateSystemCaches(). Kind of brute-force, but only
then it worked
instantly the pgbench backend started using new "stash" immediatley). Question
should or should not it immediatley effective and have you got any idea why
there is such difference in behaviour between pgbench and psql? Should we
investigate it further? I think, we should?

4. The familiy of pg_*stash*() functions could return some ::bool result instead
of void. Like true? (it's usage "feeling" that leaves one wondering if
the command
was effective or not, e.g. to get consistency with let's say pg_reload_conf())

5. QQ: will pg_stash_advice persist the stashes one day?

6. Any idea for better name than 'stash' ? :) It's some new term that is for
sure not wildly recognized. Some other used name across industry: plan
stability,
advice freeze, plan freeze, plan force, baselines, plan management, force plan,
force paths, SQL plan optimization profiles, query store (MSSQL).

7. I saw you have written that in docs to be careful about memory use, but
wouldn't be it safer if that maximum memory for pgca (when collecting in shared
with almost infinite limite) would be still subject to like let's say 5% of s_b
(or any other number here)?

8. I'm wondering if we should apply standard PostgreSQL case insensitivity
rule (e.g. like for relations) for those stashes? On one front we ignore case
sensitivty for objects, one another this is GUC so perhaps it is OK (I
feel it is
ok, but I wanted to ask).

9. If IsQueryIdEnabled() is false (even after trying out to use 'auto')
shouldn't this module raise warning when pg_stash_advice.stash_name != NULL?

> [..altered sequence],
[..]
> First I realized that it might be confusing to have the collector
> interface as part of pg_plan_advice, because for most of what
> pg_plan_advice does, you didn't need the extension, but for that part,
> you did. So, I broke that part out into its own extension, now called
> pg_collect_advice, [..]

10. I'm was here mainly for v18-0007 (pg_stash_advice), but this still looks
like some small bug to me (minmax matching in v18-0003):

create table t1  as select * from generate_series(1, 100000) as id;
create unique index t1_pk on t1 (id);
analyze t1;

-- OK "matched"
postgres=# set pg_plan_advice.advice to 'INDEX_ONLY_SCAN(t1@minmax_1
public.t1_pk)';
SET
postgres=# explain (plan_advice, costs off) select max(id) from t1;
                        QUERY PLAN
-----------------------------------------------------------
 Result
   Replaces: MinMaxAggregate
   InitPlan minmax_1
     ->  Limit
           ->  Index Only Scan Backward using t1_pk on t1
                 Index Cond: (id IS NOT NULL)
 Supplied Plan Advice:
   INDEX_ONLY_SCAN(t1@minmax_1 public.t1_pk) /* matched */
 Generated Plan Advice:
   INDEX_ONLY_SCAN(t1@minmax_1 public.t1_pk)
   NO_GATHER(t1@minmax_1)
(11 rows)

Manual SET, wont work and it is failed (which is OK)

postgres=# set pg_plan_advice.advice to 'SEQ_SCAN(t1)';
SET
postgres=# explain (plan_advice, costs off) select max(id) from t1;
                        QUERY PLAN
----------------------------------------------------------
 Result
   Replaces: MinMaxAggregate
   InitPlan minmax_1
     ->  Limit
           ->  Index Only Scan Backward using t1_pk on t1
                 Index Cond: (id IS NOT NULL)
 Supplied Plan Advice:
   SEQ_SCAN(t1) /* matched, failed */
 Generated Plan Advice:
   INDEX_ONLY_SCAN(t1@minmax_1 public.t1_pk)
   NO_GATHER(t1@minmax_1)

However with below SEQ_SCAN is applied/matched, but marked as failed
(so bug?):

postgres=# set pg_plan_advice.advice to 'SEQ_SCAN(t1@minmax_1)';
SET
postgres=# explain (plan_advice, costs off) select max(id) from t1;
                  QUERY PLAN
-----------------------------------------------
 Aggregate
   ->  Seq Scan on t1
 Supplied Plan Advice:
   SEQ_SCAN(t1@minmax_1) /* matched, failed */
 Generated Plan Advice:
   SEQ_SCAN(t1)
   NO_GATHER(t1)

-J.