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@lab.ntt.co.jp]
> Sent: Friday, November 30, 2018 3:44 PM
> To: David Rowley <david.rowley@2ndquadrant.com>
> Cc: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>; PostgreSQL Hackers
> <pgsql-hackers@lists.postgresql.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@lab.ntt.co.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
>