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

PFA patch which takes care of some of the TODOs mentioned in my
previous mail. The patch is based on the set of patches supporting
declarative partitioning by Amit Langoted posted on 26th August.

>
> TODOs:
>
> 1. Instead of storing partitioning information in RelOptInfo of each of the
> partitioned relations (base and join relations), we can keep a list of
> canonical partition schemes in PlannerInfo. Every RelOptInfo gets a pointer
> to
> the member of list representing the partitioning scheme of corresponding
> relation. RelOptInfo's of all similarly partitioned relations get the same
> pointer thus making it easy to match the partitioning schemes by comparing
> the
> pointers. While we are supporting only exact partition matching scheme now,
> it's possible to extend this method to match compatible partitioning schemes
> by
> maintaining a list of compatible partitioning schemes.
>
> Right now, I have moved some partition related structures from partition.c
> to
> partition.h. These structures are still being reviewed and might change when
> Amit Langote improves his patches. Having canonical partitioning scheme in
> PlannerInfo may not require moving those structures out. So, that code is
> still
> under development. A related change is renaming RangeBound structure in Amit
> Langote's patches to PartitionRangeBound to avoid name conflict with
> rangetypes.h. That change too should vanish once we decide where to keep
> that
> structure and its final name.

Done.

>
> 2. Multi-level partitioned tables: For some reason path created for joining
> partitions are not being picked up as the cheapest paths. I think, we need
> to
> finalize the lower level paths before moving upwards in the partition
> hierarchy. But I am yet to investigate the issue here.
> RelOptInfo::parent_relid
> should point to top parents rather than immediate parents.

Done

>
> 3. Testing: need more tests for testing partition-wise join with foreign
> tables
> as partitions. More tests for parameterized joins for multi-level
> partitioned
> joins.

Needs to be done.

>
> 4. Remove bms_to_char(): I have added this function to print Relids in the
> debugger. I have found it very useful to quickly examine Relids in debugger,
> which otherwise wasn't so easy. If others find it useful too, I can create a
> separate patch to be considered for a separate commit.

I will take care of this after rebasing the patch on the latest
sources and latest set of patches by Amit Langote.

>
> 5. In add_paths_to_append_rel() to find the possible set of outer relations
> for
> generating parameterized paths for a given join. This code needs to be
> adjusted
> to eliminate the parent relations possible set of outer relations for a join
> between child partitions.

Done.

>
> 6. Add support to reparameterize more types of paths for child relations. I
> will add this once we finalize the method to reparameterize a parent path
> for
> child partition.

Will wait for reviewer's opinion.

>
> 7. The patch adds make_joinrel() (name needs to be changed because of its
> similariy with make_join_rel()) to construct an empty RelOptInfo for a join
> between partitions. The function copies code doing the same from
> build_join_rel(). build_join_rel() too can use this function, if we decide
> to
> retain it.

This will be done as a separate cleanup patch.

>
> 8. Few small TODOs related to code reorganization, proper function,
> variable naming etc. are in the patch. pg_indent run.

I have taken care of most of the TODOs. But there are still some TODOs
remaining. I will take care of those in the next version of patches.

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