Re: Planning time of Generic plan for a table partitioned into a lot

On Thu, 29 Nov 2018 at 20:40, Kato, Sho <kato-sho(at)jp(dot)fujitsu(dot)com> wrote:
> Sorry for my lack of explanation. I didn't get a generic plan with plan_cache_mode = auto.
> What I am worried about is that if users don't know the flow of PREPARE EXECUTE, query execution seems to be suddenly slow and they will be in trouble.
> Just as you said, generic plan is only made, and a custom plan is chosen.
> But, as the time to make a general plan is added, it becomes slow as a whole.

Like Amit, I also had in mind that you'd never get a generic plan due
to the cost appearing much higher, but of course, the planner must
actually attempt to build a generic plan before it realises that the
cost of it is unfavourable, which would only occur on the 6th
execution, any subsequent executions would realise the generic plan is
no good. I don't quite see any way around that other than ensuring
you have plan_cache_mode as force_custom_plan, but then you're still
going against the manual's recommendations about not having thousands
of partitions. The problem is only made worse in PG11 from PG10
because generating the custom plan has become faster than it
previously was due to the new partition pruning code which might make
it appear we can handle more partitions than we could previously, but
generating a generic plan for that many partitions being slow kinda
proves that's not the case. The fact that we still have the warning
in the manual about not having thousands of partitions makes me not so
worried about this.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services