Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

On 2019/01/11 11:21, Etsuro Fujita wrote:
> (2019/01/10 21:23), Amit Langote wrote:
>> On Thu, Jan 10, 2019 at 6:49 PM Ashutosh Bapat
>> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>  wrote:
>>> Though this will solve a problem for performance when partition-wise
>>> join is not possible, we still have the same problem when
>>> partition-wise join is possible. And that problem really happens
>>> because our inheritance mechanism requires expression translation from
>>> parent to child everywhere. That consumes memory, eats CPU cycles and
>>> generally downgrades performance of partition related query planning. I
>>> think a better way would be to avoid these translations and use Parent
>>> var to represent a Var of the child being dealt with. That will be a
>>> massive churn on inheritance based planner code, but it will improve
>>> planning time for queries involving thousands of partitions.
>>
>> Yeah, it would be nice going forward to overhaul inheritance planning
>> such that parent-to-child Var translation is not needed, especially
>> where no pruning can occur or many partitions remain even after
>> pruning.
>
> I agree on that point, but I think that's an improvement for a future
> release rather than a fix for the issue reported on this thread.

Agreed. Improving planning performance for large number of partitions
even in the absence of pruning is a good goal to pursue for future
versions, as is being discussed in some other threads [1].

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/0A3221C70F24FB45833433255569204D1FB60AE5%40G01JPEXMBYT05