Re: Auto creation of Partitions

On Wed, 2007-03-07 at 10:23 -0500, Luke Lonergan wrote:
> Simon,
>
> On 3/7/07 5:26 AM, "Simon Riggs" <simon(at)2ndquadrant(dot)com> wrote:
>
> > What is really needed is a data structure that allows range partitions
> > to be accessed more efficiently. This could make adding partitions and
> > deciding in which partition a specific value goes an O(logN) operation.
>
> I think we need to re-evaluate the inheritance mechanism for partitioning
> and implement something much closer to the storage layer, similar to Oracle.

Oracle's implementation is fairly close to ours, actually. Each
partition is a segment. SQLServer's is fairly low level.

> Having the constraint exclusion occur in the planner is not flexible enough
> to allow more advanced solutions.

It depends what those advanced solutions are. I definitely want to be
able to do run-time exclusion, push down merge joins and parallelism,
but I also think that being able to index only the first 3 partitions is
a useful feature too.

ISTM that making the Append node responsible for exclusion might be a
way to go with this, but various ways are possible, I'm sure. The trick
is to find one that does everything you need and that will take some
deeper analysis.

However you do it, you'll still need a way of deciding which partitions
to include/exclude that doesn't involve a sequential scan of all
partition constraints. So my comments about a different index structure
are still going to be relevant, wherever that lives/executes.

I'm not doing anything in this area personally for 8.3 though.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com