Thank you Imai-san for testing. Sorry it totally slipped my mind to reply to this email.
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. Thanks, Amit
