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

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(dot)rowley(at)2ndquadrant(dot)com]
> Sent: Thursday, November 29, 2018 7:55 PM
> To: Kato, Sho/加藤 翔 <kato-sho(at)jp(dot)fujitsu(dot)com>
> Cc: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>; PostgreSQL Hackers
> <pgsql-hackers(at)lists(dot)postgresql(dot)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-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