Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables

On Thu, Jul 20, 2017 at 11:46 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2017/07/20 15:05, Ashutosh Bapat wrote:
>> On Wed, Jul 19, 2017 at 9:54 AM, Rafia Sabih
>> <rafia.sabih@enterprisedb.com> wrote:
>>>
>>> Partition information:
>>> Type of partitioning - single column range partition
>>> Tables partitioned - Lineitem and orders
>>>
>>> Lineitem -
>>> Partition key = l_orderkey
>>> No of partitions = 18
>>>
>>> Orders -
>>> Partition key = o_orderkey
>>> No of partitions = 11
>>>
>>
>> The patch set upto 0015 would refuse to join two partitioned relations
>> using a partition-wise join if they have different number of
>> partitions. Next patches implement a more advanced partition matching
>> algorithm only for list partitions. Those next patches would refuse to
>> apply partition-wise join for range partitioned tables. So, I am
>> confused as to how come partition-wise join is being chosen even when
>> the number of partitions differ.
>
> In 21_part_patched.out, I see that lineitem is partitionwise-joined with
> itself.
>
>  >  Append
>
>    ->  Hash Semi Join
>        Hash Cond: (l1.l_orderkey = l2.l_orderkey)
>        Join Filter: (l2.l_suppkey <> l1.l_suppkey)
>        Rows Removed by Join Filter: 395116
>
>        ->  Parallel Seq Scan on lineitem_001 l1
>            Filter: (l_receiptdate > l_commitdate)
>            Rows Removed by Filter: 919654
>
>        ->  Hash
>            Buckets: 8388608  Batches: 1  Memory Usage: 358464kB
>            ->  Seq Scan on lineitem_001 l2
>
Ah, I see now.

We need the same number of partitions in all partitioned tables, for
joins to pick up partition-wise join.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company