Horton

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

Rafia Sabih <rafia.sabih@enterprisedb.com>

View thread View on postgr.es
From: Rafia Sabih <rafia.sabih@enterprisedb.com>
To: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Robert Haas <robertmhaas@gmail.com>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-07-21T06:24:33Z
Lists: pgsql-hackers 
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/

Commits

  1. Basic partition-wise join functionality.

  2. Assorted preparatory refactoring for partition-wise join.

  3. Teach adjust_appendrel_attrs(_multilevel) to do multiple translations.

  4. Stamp 10beta2.

  5. Eat XIDs more efficiently in recovery TAP test.

  6. Abstract logic to allow for multiple kinds of child rels.

  7. Implement SortSupport for macaddr data type

  8. Attempt to stabilize grouping sets regression test plans.

  9. Teach xlogreader to follow timeline switches

  10. Don't scan partitioned tables.

  11. Fix grammar.

  12. postgres_fdw: Push down FULL JOINs with restriction clauses.

  13. Some preliminary refactoring towards partitionwise join.

  14. contrib/amcheck needs RecentGlobalXmin to be PGDLLIMPORT'ified.

  15. Print test parameters like "foo: 123", and results like "foo = 123".