Re: speeding up planning with partitions

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