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

On Fri, Apr 28, 2017 at 1:18 AM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> For two-way join this works and is fairly straight-forward. I am
> assuming that A an B are base relations and not joins. But making it
> work for N-way join is the challenge.

I don't think it's much different, is it? Anyway, I'm going to
protest if your algorithm for merging bounds takes any more than
linear time, regardless of what else we decide.

>> Having said that I think we could make this work, I'm starting to
>> agree with you that it will add more complexity than it's worth.
>> Needing to keep track of the type of every partition bound
>> individually seems like a real nuisance, and it's not likely to win
>> very often because, realistically, people should and generally will
>> use the same type for the partitioning column in all of the relevant
>> tables. So I'm going to revise my position and say it's fine to just
>> give up on partitionwise join unless the types match exactly, but I
>> still think we should try to cover the cases where the bounds don't
>> match exactly but only 1:1 or 1:0 or 0:1 mappings are needed (iow,
>> optimizations 1 and 2 from your list of 4). I agree that ganging
>> partitions (optimization 4 from your list) is not something to tackle
>> right now.
>
> Good. I will have a more enjoyable vacation now.

Phew, what a relief. :-)

> Do you still want the patition key type to be out of partition scheme?
> Keeping it there means we match it only once and save it only at a
> single place. Otherwise, it will have to be stored in RelOptInfo of
> the partitioned table and match it for every pair of joining
> relations.

The only reason for removing things from the PartitionScheme was if
they didn't need to be consistent across all tables. Deciding that
the type is one of the things that has to match means deciding it
should be in the PartitionScheme, not the RelOptInfo.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company