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.

## Advertising

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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend