Horton

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

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Rafia Sabih <rafia.sabih@enterprisedb.com>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-04-28T19:07:02Z
Lists: pgsql-hackers 
On Fri, Apr 28, 2017 at 1:18 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.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

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