On Thu, 29 Nov 2018 at 20:40, Kato, Sho <kato-...@jp.fujitsu.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
