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

On Thu, Jul 20, 2017 at 2:44 PM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
>
> 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.
>
Oh, I missed this limitation, will modify my setup to have same number
of partitions in the partitioned table with same ranges. So, does this
also mean that a partitioned table will not join with an unpartitioned
table without append of partitions?

-- 
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/