Re: Is it useful to record whether plans are generic or custom?

At Tue, 19 May 2020 22:56:17 +0900, Atsushi Torikoshi <atorik(at)gmail(dot)com> wrote in
> On Sat, May 16, 2020 at 6:01 PM legrand legrand <legrand_legrand(at)hotmail(dot)com>
> wrote:
>
> > > To track executed plan types, I think execution layer hooks
> > > are appropriate.
> > > These hooks, however, take QueryDesc as a param and it does
> > > not include cached plan information.
> >
> > It seems that the same QueryDesc entry is reused when executing
> > a generic plan.
> > For exemple marking queryDesc->plannedstmt->queryId (outside
> > pg_stat_statements) with a pseudo tag during ExecutorStart
> > reappears in later executions with generic plans ...
> >
> > Is this QueryDesc reusable by a custom plan ? If not maybe a solution
> > could be to add a flag in queryDesc->plannedstmt ?
> >
>
> Thanks for your proposal!
>
> I first thought it was a good idea and tried to add a flag to QueryDesc,
> but the comments on QueryDesc say it encapsulates everything that
> the executor needs to execute a query.
>
> Whether a plan is generic or custom is not what executor needs to
> know for running queries, so now I hesitate to do so.
>
> Instead, I'm now considering using a static hash for prepared queries
> (static HTAB *prepared_queries).
>
>
> BTW, I'd also appreciate other opinions about recording the number
> of generic and custom plans on pg_stat_statemtents.

If you/we just want to know how a prepared statement is executed,
couldn't we show that information in pg_prepared_statements view?

=# select * from pg_prepared_statements;
-[ RECORD 1 ]---+----------------------------------------------------
name | stmt1
statement | prepare stmt1 as select * from t where b = $1;
prepare_time | 2020-05-20 12:01:55.733469+09
parameter_types | {text}
from_sql | t
exec_custom | 5 <- existing num_custom_plans
exec_total | 40 <- new member of CachedPlanSource

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center