Subject: Re: A problem about partitionwise join
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
 Richard Guo <guofenglinux@gmail.com>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>,
 Pg Hackers <pgsql-hackers@lists.postgresql.org>,
 Richard Guo <riguo@pivotal.io>, ashutosh.bapat@enterprisedb.com
References: 
 <CAN_9JTzo_2F5dKLqXVtDX5V6dwqB0Xk+ihstpKEt3a1LT6X78A@mail.gmail.com>
 <CAMbWs4-zhLVU-uH42_N_v-LQF+VTGruVadQr6A_amhjmugB9-g@mail.gmail.com>
 <160467632488.7362.12712548134124442813.pgcf@coridan.postgresql.org>
 <CAMbWs49wRS5CkrjEOZxCVLO+Vm5YE_XHrYh8FCQbyvW-OUoMCg@mail.gmail.com>
 <CAExHW5vx89LbqphcusNR3AJ+kihWXDo0gv7eJVu6+nsafwow-Q@mail.gmail.com>
From: David Steele <david@pgmasters.net>
Message-ID: <65519d8f-4e8d-3e79-cce7-bb267ca4cba9@pgmasters.net>
Date: Tue, 9 Mar 2021 11:22:32 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.16; rv:78.0)
 Gecko/20100101 Thunderbird/78.7.1
MIME-Version: 1.0
In-Reply-To: 
 <CAExHW5vx89LbqphcusNR3AJ+kihWXDo0gv7eJVu6+nsafwow-Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/65519d8f-4e8d-3e79-cce7-bb267ca4cba9%40pgmasters.net>
Precedence: bulk

On 11/27/20 7:05 AM, Ashutosh Bapat wrote:
> On Tue, Nov 10, 2020 at 2:43 PM Richard Guo <guofenglinux@gmail.com> wrote:
>>
>> To recap, the problem we are fixing here is when generating join clauses
>> from equivalence classes, we only select the joinclause with the 'best
>> score', or the first joinclause with a score of 3. This may cause us to
>> miss some joinclause on partition keys and thus fail to generate
>> partitionwise join.
>>
>> The initial idea for the fix is to create all the RestrictInfos from ECs
>> in order to check whether there exist equi-join conditions involving
>> pairs of matching partition keys of the relations being joined for all
>> partition keys. And then Tom proposed a much better idea which leverages
>> function exprs_known_equal() to tell whether the partkeys can be found
>> in the same eclass, which is the current implementation in the latest
>> patch.
> 
> In the example you gave earlier, the equi join on partition key was
> there but it was replaced by individual constant assignment clauses.
> So if we keep the original restrictclause in there with a new flag
> indicating that it's redundant, have_partkey_equi_join will still be
> able to use it without much change. Depending upon where all we need
> to use avoid restrictclauses with the redundant flag, this might be an
> easier approach. However, with Tom's idea partition-wise join may be
> used even when there is no equi-join between partition keys but there
> are clauses like pk = const for all tables involved and const is the
> same for all such tables.
> 
> In the spirit of small improvement made to the performance of
> have_partkey_equi_join(), pk_has_clause should be renamed as
> pk_known_equal and pks_known_equal as num_equal_pks.
> 
> The loop traversing the partition keys at a given position, may be
> optimized further if we pass lists to exprs_known_equal() which in
> turns checks whether one expression from each list is member of a
> given EC. This will avoid traversing all equivalence classes for each
> partition key expression, which can be a huge improvement when there
> are many ECs. But I think if one of the partition key expression at a
> given position is member of an equivalence class all the other
> partition key expressions at that position should be part of that
> equivalence class since there should be an equi-join between those. So
> the loop in loop may not be required to start with.

Richard, any thoughts on Ashutosh's comments?

Regards,
-- 
-David
david@pgmasters.net