Re: Auto Partitioning

Hi,

Gregory Stark wrote:
> However there are also cases such as where you have a=0..99 in one partition
> and a=100..199 in partition two, etc. It could still automatically build
> indexes on (a,b,c) on each partition and somehow note that the unique
> constraint is guaranteed across the whole partitioned table.

Uhm... yes, because 'a' is the partitioning key.

According to my outline for partitioning rule sets, you would have a
split @ a <= 100. Probably another one @ a <= 200, etc... but none the
less, 'a' is the only column needed to decide what partition a row has
to end up in, so 'a' is the only column in the partitioning key.

What I'm saying is, that given your example, it's not easily possible to
have an index on (b,a) even if 'a' is also in the partitioning key. It's
very well possible to emulate a multi-table index on (a,b), though.

Brainstorming about this somewhat more: how about having multiple
columns in the partitioning key, i.e. 'a' and 'b', and the following
rule set (which admittedly is somewhat special):

table sample
|
|
split @ a >= 100
/ \
/ \
split @ b >= 100 part3
/ \
/ \
part1 part2

An index on (a, b) could easily be 'emulated' by having such an index on
all the partitions, but can we have an index on (b, a) like that?
Probably not, because at the first split, we would have to duplicate.
I.e. for an index scan on 'b = 22', we would have to scan the index on
part3 as well as part1.

Thus one can say, that an multi-table index can only easily be
'emulated', if it has the same columns as the partitioning key, in the
same order. For the above example, these ones would be possible:

(a)
(a,b)
(a,b,...)

Yet another thought: the emulation of multi-table indexes, in this case,
is like concatenating the indexes of the partitions in the right order.
Asking for an index scan for 'WHERE a >= 95 AND a <= 105' when having a
split at a >= 100, you would have to start on the index in the left
bucket (with a < 100) and return everything until the end of the index,
then continue on the index in the right bucket (with a >= 100). So you
also have to be able to determine an order, which is easily possible for
splits, but not so simple for modulos (hash partitioning).

For such a modulo node, the executor would have to start multiple index
scans, i.e.:

table sample
|
|
'id' modulo 4
/ | | \
/ | | \
part1 part2 part3 part4

When scanning for a range (i.e. 'WHERE id >= 5 AND id <= 17'), the
planner would have to request an index scan on each of the partition,
joining the results in the right order.

So, why not completely emulate all multi-table index scans? The above
restriction would disappear, if we could teach the planner and executor
how to join multiple index scan results, no?

Questioning the other way around: do we need any sort of multi-table
indexes at all, or isn't it enough to teach the planner and executor how
to intelligently scan through (possibly) multiple indexes to get what is
requested?

Regards

Markus