Gregory Stark wrote:
Put another way, multi-table indexes defeat the whole purpose of having
partitioned the table in the first place. If you could have managed a single
massive index then you wouldn't have bothered partitioning.
That depends very much on the implementation of the multi-table index,
as you describe below. I think the major missing part is not *how* such
a meta-index should work - it's easily understandable, that one could
use the per-table indices - but a programming interface, similar to the
current index scan or sequential scan facility, which could return a
table and tuple pointer, no?
However there is a use case that can be handled by a kind of compromise index.
Indexes that have leading columns which restrict all subtrees under that point
to a single partition can be handled by a kind of meta-index. So you have one
index which just points you to the right partition and corresponding index.
That lets you enforce unique constraints as long as the partition key is part
of the unique constraint.
Is that already sufficient? That would alter the ordering of the columns
in the index, no? I mean:
CREATE INDEX x ON test(a, b, c);
isn't the same as
CRETAE INDEX x ON test(c, b, a);
That's why I'd say, the first column of an index would have to be equal
to all of the columns used in the partitioning key.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly