RE: speeding up planning with partitions

Imai-san,

From: Amit Langote [mailto:Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp]
> On 2019/01/09 11:08, Imai, Yoshikazu wrote:
> > I wonder why force_custom_plan is faster than auto after applied the patch.
> >
> > When we use PREPARE-EXECUTE, a generic plan is created and used if its
> cost is
> > cheaper than creating and using a custom plan with plan_cache_mode='auto',
> > while a custom plan is always created and used with
> plan_cache_mode='force_custom_plan'.
> > So one can think the difference in above results is because of creating
> or
> > using a generic plan.
> >
> > I checked how many times a generic plan is created during executing pgbench
> and
> > found a generic plan is created only once and custom plans are created
> at other
> > times with plan_cache_mode='auto'. I also checked the time of creating
> a
> > generic plan, but it didn't take so much(250ms or so with 4096 partitions).
> So
> > the time of creating a generic plan does not affect the performance.
> >
> > Currently, a generic plan is created at sixth time of executing EXECUTE
> query.
> > I changed it to more later (ex. at 400,000th time of executing EXECUTE
> query on
> > master with 4096 partitions, because 7000TPS x 60sec=420,0000
> transactions are
> > run while executing pgbench.), then there are almost no difference between
> auto
> > and force_custom_plan. I think that creation of a generic plan affects
> the time
> > of executing queries which are ordered after creating generic plan.
> >
> > If my assumption is right, we can expect some additional process is
> occurred at
> > executing queries ordered after creating a generic plan, which results
> in auto is
> > slower than force_custom_plan because of additional process. But looking
> at
> > above results, on master with 4096 partitions, auto is faster than
> force_custom_plan.
> > So now I am confused.
> >
> > Do you have any ideas what does affect the performance?
>
> Are you saying that, when using auto mode, all executions of the query
> starting from 7th are slower than the first 5 executions? That is, the
> latency of creating and using a custom plan increases *after* a generic
> plan is created and discarded on the 6th execution of the query? If so,
> that is inexplicable to me.

Isn't CheckCachedPlan() (and AcquireExecutorLocks() therein) called in every EXECUTE after 6th one due to some unknow issue?
Does choose_custom_plan() always return true after 6th EXECUTE?

Regards
Takayuki Tsunakawa