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