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:


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?



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to