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

čt 12. 11. 2020 v 2:50 odesílatel torikoshia <torikoshia(at)oss(dot)nttdata(dot)com>
napsal:

> On 2020-09-29 02:39, legrand legrand wrote:
> > Hi Atsushi,
> >
> > +1: Your proposal is a good answer for time based performance analysis
> > (even if parsing durationor blks are not differentiated) .
> >
> > As it makes pgss number of columns wilder, may be an other solution
> > would be to create a pg_stat_statements_xxx view with the same key
> > as pgss (dbid,userid,queryid) and all thoses new counters.
>
> Thanks for your ideas and sorry for my late reply.
>
> It seems creating pg_stat_statements_xxx views both for generic and
> custom plans is better than my PoC patch.
>
> However, I also began to wonder how effective it would be to just
> distinguish between generic and custom plans. Custom plans can
> include all sorts of plans. and thinking cache validation, generic
> plans can also include various plans.
>
> Considering this, I'm starting to feel that it would be better to
> not just keeping whether generic or cutom but the plan itself as
> discussed in the below thread.
>
>
> https://www.postgresql.org/message-id/flat/CAKU4AWq5_jx1Vyai0_Sumgn-Ks0R%2BN80cf%2Bt170%2BzQs8x6%3DHew%40mail.gmail.com#f57e64b8d37697c808e4385009340871
>
>
> Any thoughts?
>

yes, the plan self is very interesting information - and information if
plan was generic or not is interesting too. It is other dimension of query
- maybe there can be rule - for any query store max 100 most slows plans
with all attributes. The next issue is fact so first first 5 execution of
generic plans are not generic in real. This fact should be visible too.

Regards

Pavel

>
> Regards,
>
> --
> Atsushi Torikoshi
>
>
>