> Another possibility is optimizing for the special case of
> indexing on a partitioning key. In this case, index values
> would be very localized to one table, so just storing the
> table info on each index page (or something similar) would work well.
If you have the partitioning key in the index and the partitions don't
overlap, it is better to create separate [unique] indexes on the
subtables.
Building separate indexes per partition is usually preferred because of:
1. performance of dropping a partition
2. smaller index for CE
Only if you need an "order by" without a sort step, that spawns more
than one partition
things usually get ugly. Imho the best solution would be a merge node,
that merges results of
several index accesses to avoid a sort and still use separate indexes.
Such
a merge node could probably also detect the case where accessing
partitions in a certain
order still produces ordered results.
Usually you will only want the "one big unique index" when the
partitioning is not
reflectable in the index keys, and then (also in other db's) such an
index is usually a pain ...
Andreas