Re: Table Partitioning, Part 1

On Tue, 2005-05-10 at 16:31 +0300, Hannu Krosing wrote:
> On E, 2005-05-09 at 23:30 +0100, Simon Riggs wrote:
> > ISTM fairly straightforward to produce a similar "static" plan along the
> > same lines, using Result nodes to implement Partition Elimination.
> >
> > Append
> > Result
> > SeqScan
> > Result
> > SeqScan
> > Result
> > SeqScan
>
> So you mean another node that looks inside seqscans restrictions and
> then determines if there is any chance that seqscan will return any
> rows ?
>
> Why can't this be done at planning time ? And if it can't be done at
> planning time, then how do you determine which plan is cheapest ?

It can if you have constants. If you have you have bind variables or
Stable functions then it can only be done for certain at execution time,
though can be estimated prior at planning time. This is exactly what is
done currently in the planner for constant eval and simplification.

> > If all partitions in the query had identical indexes on them, then we
> > have another option. In that case, each index could be thought to form
> > part of a larger index ordered initially on the Partitioning Key (PPK).
> > If the first column was actually the PPK, then the set of indexes would
> > be exactly equivalent to a Global Index. We can call this a Pseudo
> > Global Index.
> >
> > The Pseudo Global Index could also be used to enforce uniqueness. If all
> > of the composite indexes were defined unique and the index contained the
> > PPK as one of its columns, this would work.
> >
> > The index enforces
> > uniqueness within each partition and the PPK enforces uniqueness across
> > partitions because the same PPK value cannot be in two partitions.
>
> But only uniqueness of PPK, not any other columns.

No, it would work for *any* set of columns that included the PPK.

> > We can also use the Pseudo Global Index as the object of a multi-index
> > scan node, since the PGI would be ordered on the PPK. The plan node
> > would use the partition boundary definitions to locate the starting
> > partition, then seek within the partition to the correct part of the
> > index then scan sideways, skipping to the next partition as required.
> >
> > This sounds a great idea, but it is exactly the technique I argued
> > against only 4 paragraphs ago for use with SeqScans.
>
> Also this works only for PPK and not other columns.

This would work for any set of columns, as long as the PPK were the
leftmost column in the set.

> > My current thinking is that we need a mix of the two approaches, though
> > I raise all of this here to gain everybody's input...
>
> I'm afraid that hiding the decisions inside the access methods will make
> things harder for the optimiser .

Me too.

> Still there may be cases where smarter access methods make sense as an
> additional feture, though I cant come up with an example right now.

Look at PPUC 2 Join partition elimination, which is the classic Fact to
TimeDimension join.

Hannu,

It's time to follow your thinking for the first implementation:
just implement Constraints as possible gating tests on each inherited
relation. No syntax changes, just some light but complex internals.

Once that works, we can discuss improving it to solve other required use
cases. Until we're at that stage, we should leave further discussion to
one side, now that we have the basic architecture agreed: inheritance.

Best Regards, Simon Riggs