Imai-san, From: Amit Langote [mailto:langote_amit...@lab.ntt.co.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
