Re: Is it useful to record whether plans are generic or custom? - Mailing list pgsql-hackers

From Kyotaro Horiguchi
Subject Re: Is it useful to record whether plans are generic or custom?
Date
Msg-id 20200520.133204.17974397158935788.horikyota.ntt@gmail.com
Whole thread Raw
In response to Re: Is it useful to record whether plans are generic or custom?  (Atsushi Torikoshi <atorik@gmail.com>)
Responses Re: Is it useful to record whether plans are generic or custom?
List pgsql-hackers
At Tue, 19 May 2020 22:56:17 +0900, Atsushi Torikoshi <atorik@gmail.com> wrote in 
> On Sat, May 16, 2020 at 6:01 PM legrand legrand <legrand_legrand@hotmail.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



pgsql-hackers by date:

Previous
From: Jeff Davis
Date:
Subject: Re: Trouble with hashagg spill I/O pattern and costing
Next
From: Kyotaro Horiguchi
Date:
Subject: Re: MultiXact\SLRU buffers configuration