Horton

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

Amit Langote <langote_amit_f8@lab.ntt.co.jp>

View thread View on postgr.es
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Robert Haas <robertmhaas@gmail.com>
Cc: Rafia Sabih <rafia.sabih@enterprisedb.com>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-03-30T10:32:21Z
Lists: pgsql-hackers 
On 2017/03/30 18:35, Ashutosh Bapat wrote:
>> On Wed, Mar 29, 2017 at 8:39 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> I don't think 0011 is likely to be acceptable in current form.  I
>> can't imagine that we just went to the trouble of getting rid of
>> AppendRelInfos for child partitioned rels only to turn around and put
>> them back again.  If you just need the parent-child mappings, you can
>> get that from the PartitionedChildRelInfo list.
>>
> 
> Please refer to my earlier mails on this subject [1], [2]. For
> multi-level partition-wise join, we need RelOptInfo of a partitioned
> table to contain RelOptInfo of its immediate partitions. I have not
> seen any counter arguments not to create RelOptInfos for intermediate
> partitioned tables. We create child RelOptInfos only for entries in
> root->append_rel_list i.e. only for those relations which have an
> AppendRelInfo. Since we are not creating AppendRelInfos for
> partitioned partitions, we do not create RelOptInfos for those. So, to
> me it looks like we have to either have AppendRelInfos for partitioned
> partitions or create RelOptInfos by traversing some other list like
> PartitionedChildRelInfo list. It looks odd to walk
> root->append_rel_list as well as this new list for creating
> RelOptInfos. But for a moment, we assume that we have to walk this
> other list. But then that other list is also lossy. It stores only the
> topmost parent of any of the partitioned partitions and not the
> immediate parent as required to add RelOptInfos of immediate children
> to the RelOptInfo of a parent.

So, because we want to create an Append path for each partitioned table in
a tree separately, we'll need RelOptInfo for each one, which in turn
requires an AppendRelInfo.  Note that we do that only for those
partitioned child RTEs that have inh set to true, so that all the later
stages will treat it as the parent rel to create an Append path for.
There would still be partitioned child RTEs with inh set to false for
which, just like before, no AppendRelInfos and RelOptInfos are created;
they get added as the only member of partitioned_rels in the
PartitionedChildRelInfo of each partitioned table.  Finally, when the
Append path for the root parent is created, its subpaths list will contain
paths of leaf partitions of all levels and its partitioned_rels list
should contain the RT indexes of partitioned tables of all levels.

If we have the following partition tree:

      A
    / | \
   B  C  D
     / \
    E   F

The following RTEs will be created, in that order.  RTEs with inh=true are
shown with suffix _i.  RTEs that get an AppendRelInfo (& a RelOptInfo) are
shown with suffix _a.

A_i_a
A
B_a
C_i_a
C
E_a
F_a
D_a

Thanks,
Amit

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".