Re: [PROPOSAL] Detecting plan changes with plan_id in pg_stat_activity

Hi,

On Wed, Jun 15, 2022 at 06:45:38PM +0000, Imseih (AWS), Sami wrote:
> Adding a plan_id to pg_stat_activity allows users
> to determine if a plan for a particular statement
> has changed and if the new plan is performing better
> or worse for a particular statement.
> [...]
> Attached is a POC patch that computes the plan_id
> and presents the top-level plan_id in pg_stat_activity.

AFAICS you're proposing to add an identifier for a specific plan, but no way to
know what that plan was? How are users supposed to use the information if they
know something changed but don't know what changed exactly?

> - In the POC, the compute_query_id GUC determines if a
> plan_id is to be computed. Should this be a separate GUC?

Probably, as computing it will likely be quite expensive. Some benchmark on
various workloads would be needed here.

I only had a quick look at the patch, but I see that you have some code to
avoid storing the query text multiple times with different planid. How does it
work exactly, and does it ensure that the query text is only removed once the
last entry that uses it is removed? It seems that you identify a specific
query text by queryid, but that seems wrong as collision can (easily?) happen
in different databases. The real identifier of a query text should be (dbid,
queryid).

Note that this problem already exists, as the query texts are now stored per
(userid, dbid, queryid, istoplevel). Maybe this part could be split in a
different commit as it could already be useful without a planid.