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

Fujita-san,

(sorry about the repeated email, but my previous attempt failed due to
trying to send to the -hackers and -performance lists at the same time, so
trying again after removing -performance)

On 2019/01/08 20:07, Etsuro Fujita wrote:
> (2018/12/07 20:14), Ashutosh Bapat wrote:
>> On Fri, Dec 7, 2018 at 11:13 AM Ashutosh Bapat
>> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com <mailto:ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>> wrote:
>
>>         Robert, Ashutosh, any comments on this?  I'm unfamiliar with the
>>         partitionwise join code.
>
>>     As the comment says it has to do with the equivalence classes being
>>     used during merge append. EC's are used to create pathkeys used for
>>     sorting. Creating a sort node which has column on the nullable side
>>     of an OUTER join will fail if it doesn't find corresponding
>>     equivalence class. You may not notice this if both the partitions
>>     being joined are pruned for some reason. Amit's idea to make
>>     partition-wise join code do this may work, but will add a similar
>>     overhead esp. in N-way partition-wise join once those equivalence
>>     classes are added.
>
>> I looked at the patch. The problem there is that for a given relation,
>> we will add child ec member multiple times, as many times as the number
>> of joins it participates in. We need to avoid that to keep ec_member
>> list length in check.
>
> Amit-san, are you still working on this, perhaps as part of the
> speeding-up-planning-with-partitions patch [1]?

I had tried to continue working on it after PGConf.ASIA last month, but
got distracted by something else.

So, while the patch at [1] can take care of this issue as I also mentioned
upthread, I was trying to come up with a solution that can be back-patched
to PG 11. The patch I posted above is one such solution and as Ashutosh
points out it's perhaps not the best, because it can result in potentially
creating many copies of the same child EC member if we do it in joinrel.c,
as the patch proposes. I will try to respond to the concerns he raised in
the next week if possible.

Thanks,
Amit