Hi,

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.

Yeah.

> 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.

Regards

Markus


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly