On Thursday, November 29, 2018 7:55 PM David Rowley wrote: >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.
Ok, I got it. Regards, Sho Kato > -----Original Message----- > From: David Rowley [mailto:david.row...@2ndquadrant.com] > Sent: Thursday, November 29, 2018 7:55 PM > To: Kato, Sho/加藤 翔 <kato-...@jp.fujitsu.com> > Cc: Amit Langote <langote_amit...@lab.ntt.co.jp>; PostgreSQL Hackers > <pgsql-hackers@lists.postgresql.org> > Subject: Re: Planning time of Generic plan for a table partitioned into > a lot > > 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