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

On Friday, November 30, 2018 3:44 PM, Amit Langote wrote:
> Yeah, maybe we haven't explained in the documentation where generic plans
> are described that making them for partitioned table is an expensive
> affair.

Will we improve creating the generic plan in the future?
For example, if there is UPDATE / DELETE run-time partition pruning, creating the generic plan will also be faster.

Although it may not be possible with PG 12, I think that it is necessary to improve it in the future.

Regards,
Sho Kato
> -----Original Message-----
> From: Amit Langote [mailto:Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp]
> Sent: Friday, November 30, 2018 3:44 PM
> To: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
> Cc: Kato, Sho/加藤 翔 <kato-sho(at)jp(dot)fujitsu(dot)com>; 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 2018/11/30 14:58, David Rowley wrote:
> > On Fri, 30 Nov 2018 at 15:04, Amit Langote
> > <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> >>
> >> On 2018/11/29 19:54, David Rowley wrote:
> >>> 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,
> >>
> >> Actually, PG 11's pruning improvements don't change plancache.c's
> >> equation of custom plan cost, that is, even if pruning may have
> >> gotten faster it doesn't change the value cached_plan_cost comes up
> with.
> >
> > Unsure why you think I was implying that the plancache code had
> > changed.
>
> Sorry I misinterpreted your sentence "...which might make it appear we
> can handle more partitions than we could previously". I thought you're
> saying that *plancache* now thinks custom plans are better because they're
> sightly faster.
>
> > What I meant was, the faster pruning code means that PG11 appears more
> > capable of handling more partitions than PG10 could handle, but this
> > really only goes as far as custom plans where many partitions get
> > pruned.
>
> Right.
>
> > When no pruning takes place, say, in a generic plan where the
> > partition key is being compared to some parameter, then we've done
> > nothing to improve the performance of planning for that.
>
> Yeah. Even with patches for PG 12, this case will be only slightly faster.
>
> > This may result in someone doing some light testing and thinking PG11
> > can handle a higher number of partitions that we might advise them to
> > use, only to find themselves stumble later when trying to build a
> > generic plan for that number of partitions. It appears to me that
> > this is what's happened in this case.
>
> Yeah, maybe we haven't explained in the documentation where generic plans
> are described that making them for partitioned table is an expensive
> affair. Although, by definition, they are built once for a given query
> and PG 11 with it's execution-time pruning can execute these plans pretty
> quickly, which is an overall improvement. But you'd obviously know that
> much. :)
>
> Thanks,
> Amit
>