On Fri, Apr 15, 2016 at 5:46 AM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.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
