Re: Declarative partitioning

On Fri, Apr 15, 2016 at 10:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Apr 15, 2016 at 5:46 AM, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> > Retaining the partition hierarchy would help to push-down join across
> > partition hierarchy effectively.
>
> -1. You don't get to insert cruft into the final plan for the
> convenience of the optimizer. I think the AppendPath needs to be
> annotated with sufficient information to do whatever query planning
> optimizations we want, and some or all of that may need to carry over
> to the Append plan to allow run-time partition pruning. But I think
> that flattening nests of Appends is a good optimization and we should
> preserve it. If that makes the additional information that any given
> Append needs to carry a bit more complex, so be it.
>
> I also think it's very good that Amit has kept the query planner
> unchanged in this initial patch. Let's leave that work to phase two.
> What I suggest we do when the time comes is invent new nodes
> RangePartitionMap, ListPartitionMap, HashPartitionMap. Each contains
> minimal metadata needed for tuple routing or planner transformation.
> For example, RangePartitionMap can contain an array of partition
> boundaries - represented as Datums - and an array of mappings, each a
> Node *. The associated value can be another PartitionMap object if
> there is subpartitioning in use, or an OID.

range table index instead of OID to make it easy to lookup the

> This can be used both for
> matching up partitions for join pushdown, and also for fast tuple
> routing and runtime partition pruning.
>

I was thinking about join pushdown (in partitioning hierarchy) of multiple
tables which have similar partitioning structure for few upper levels but
the entire partitioning hierarchy does not match. Pushing down the join as
much possible into the partition hierarchy will help. We might end up with
joins between a plain table and Append relation at the leaf level. For such
join push-down it looks like we will have to construct corresponding Append
hierarchy, push the joins down and then (may be) collapse it to just
collect the results of joins at the leaf level. But preparing for that kind
of optimization need not be part of this work.

>
> > 2. The new syntax allows CREATE TABLE to be specified as partition of an
> > already partitioned table. Is it possible to do the same for CREATE
> FOREIGN
> > TABLE? Or that's material for v2? Similarly for ATTACH PARTITION.
>
> +1 for making CREATE FOREIGN TABLE support that also, and in version
> 1. And same for ATTACH PARTITION.
>
>
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company