Detecting plan drift: pg_plan_advice pins plans, nothing watches them

Hi,

PG19 gives us two new tools for plan stability: pg_plan_advice generates
advice for a plan and can force it, and pg_stash_advice stores advice per
query_id and applies it automatically. Both work well, and I have been
using them on a beta19 instance in production use since the cutover.

What I found missing is the third piece: noticing.

Both extensions are deliberate acts -- you decide a plan is good, and you
pin it. Neither answers the operational question that comes after:

Is the plan for this query still the plan I approved?

I would like to know whether the lack of that is intentional, and whether
there is interest in it living closer to core.

Why this is not just monitoring
-------------------------------

A plan regression usually does not fail. The query returns the same rows in
the same order -- it simply stops using the index and starts scanning.
Nothing errors, nothing logs, nothing alerts. Existing tooling notices it
as latency, which means you notice weeks later, if at all.

The case that pushed me into this: a vector similarity search over ~40,000
embeddings backed by a DiskANN index. If the planner stops choosing that
index, results are byte-identical and correctly ordered. It is a sequential
scan now. Correct, silent, and slow.

pg_stat_statements shows the query got slower, but not that the plan shape
changed, and not when or from what to what. auto_explain logs plans but
leaves you diffing text, and EXPLAIN text is a poor comparison unit: it
changes with row estimates even when the plan shape is identical.

That last point turned out to be the important one.

What I built, and the one design decision that mattered
-------------------------------------------------------

I wrote a small SQL-only extension on top of pg_plan_advice (PostgreSQL
License, ~400 lines of plpgsql):

https://github.com/Manuelreyesbravo/pg_plan_guard

plan_guard.capture(name, query_sql) -- approve today's plan
plan_guard.verify() -- re-plan and report drift
plan_guard.drift_log -- append-only: since when, from
what

The decision that made it usable: compare the advice, not the EXPLAIN
output.

Advice from pg_plan_advice describes the shape of the decision -- which
scan on which relation, which join order, which method. It is stable
against changing row counts and costs, so it changes only when the
planner's decision changes. Comparing EXPLAIN text instead produces
constant false drift, which trains people to ignore the alerts -- worse
than no monitoring at all.

In other words, pg_plan_advice's output turns out to be a good canonical
form for a plan, which is arguably a more general capability than "input to
force a plan". That is the part I think might be interesting beyond my use
case.

Measured behavior on beta2: capture a lookup that uses an index, disable
index scans, and verify() reports

expected: INDEX_SCAN(t public.t_id_idx) NO_GATHER(t)
actual: SEQ_SCAN(t) NO_GATHER(t)

restore the GUCs and it returns to ok by itself.

Questions for the list
----------------------

1. Is the absence of drift detection deliberate? I can see an argument that
it belongs in external monitoring. But the canonical form (the advice
string) only exists inside pg_plan_advice, so anything outside has to
shell back into it -- which is exactly what my extension does.

2. Would a function to obtain plan advice as a value be welcome? Today the
only route is parsing EXPLAIN (PLAN_ADVICE) text output. Something like

pg_get_plan_advice(query text) RETURNS text

would make this composable, and would be useful for anyone diffing
plans, not just for my case. This is the smallest change I can identify
with the widest use.

3. Is there interest in comparison living in pg_plan_advice itself -- e.g.
a function that takes a stored advice string and a query and reports
whether today's plan still matches it? The stash already stores advice
per query_id; comparing against what is stashed seems like a natural
neighbour of applying it.

4. Is there prior art or a previous discussion I have missed? I searched
the archives for plan regression detection and plan baselines and mostly
found discussion of forcing plans (hint debates), not of observing them.

I am happy to submit a patch for (2) if there is interest -- it looks
small, and I have a beta19 build with the extensions in production use to
test against. I also have a test environment set up for both master and
REL_18_STABLE from the work on bug #19638.

Thanks,
Manuel Reyes

--
Saludos cordiales,

Manuel Reyes