Horton

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

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>

View thread View on postgr.es
From: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>, Thomas Munro <thomas.munro@enterprisedb.com>, Rafia Sabih <rafia.sabih@enterprisedb.com>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-10-12T02:43:08Z
Lists: pgsql-hackers 
On Wed, Oct 11, 2017 at 7:47 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Oct 9, 2017 at 2:05 AM, Ashutosh Bapat
> <ashutosh.bapat@enterprisedb.com> wrote:
>> On Sat, Oct 7, 2017 at 1:04 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> Committed.  I hope that makes things less red rather than more,
>>> because I'm going to be AFK for a few hours anyway.
>>
>> Here's the last patch, dealing with the dummy relations, rebased. With
>> this fix every join order of a partitioned join can be considered
>> partitioned. (This wasn't the case earlier when dummy relation was
>> involved.). So, we can allocate the child-join RelOptInfo array in
>> build_joinrel_partition_info(), instead of waiting for an appropriate
>> pair to arrive in try_partition_wise_join().
>
> Wouldn't a far more general approach be to allow a partition-wise join
> between a partitioned table and an unpartitioned table, considering
> the result as partitioned?  That seems like it would very often yield
> much better query plans than what we have right now, and also make the
> need for this particular thing go away.
>

You are suggesting that a dummy partitioned table be treated as an
un-partitioned table and apply above suggested optimization. A join
between a partitioned and unpartitioned table is partitioned by the
keys of only partitioned table. An unpartitioned table doesn't have
any keys, so this is fine. But a dummy partitioned table does have
keys. Recording them as keys of the join relation helps when it joins
to other relations. Furthermore a join between partitioned and
unpartitioned table doesn't require any equi-join condition on
partition keys of partitioned table but a join between partitioned
tables is considered to be partitioned by keys on both sides only when
there is an equi-join. So, when implementing a partitioned join
between a partitioned and an unpartitioned table, we will have to make
a special case to record partition keys when the unpartitioned side is
actually a dummy partitioned table. That might be awkward.

Because we don't have dummy children relation in all cases, we already
have some awkwardness like allocating part_rels array only when we
encounter a join order which has all the children. This patch removes
that.

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

Commits

  1. Add test for partitionwise join involving default partition.

  2. Rewrite the code that applies scan/join targets to paths.

  3. Fix code related to partitioning schemes for dropped columns.

  4. Copy information from the relcache instead of pointing to it.

  5. Basic partition-wise join functionality.

  6. Associate partitioning information with each RelOptInfo.

  7. Expand partitioned table RTEs level by level, without flattening.

  8. Set partitioned_rels appropriately when UNION ALL is used.

  9. Remove dedicated B-tree root-split record types.

  10. Assorted preparatory refactoring for partition-wise join.

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

  12. Avoid unnecessary single-child Append nodes.

  13. Revisit handling of UNION ALL subqueries with non-Var output columns.