Zeugswetter Andreas ADI SD wrote:

CREATE INDEX x ON test(a, b, c);

isn't the same as

CRETAE INDEX x ON test(c, b, a);

That is only a problem if you also want to avoid a sort (e.g. for an
order by),

..or if you want to use that index for 'WHERE a = 5'. The first one is probably helping you, the second isn't.

(an example would be a query "where c=5 and b between 0 and 20"
and two partitions one for 0 <= b < 10 and a second for 10 <= b)

Hm.. in that case, an index on (a, b, c) wouldn't help. An index on (c, b, a) would be just perfect, agreed?

Now, for the partitioning: you simply have to scan two partitions in that case, no matter how you arrange your indexes. And this is where we need some sort of multi-table index scan functionality. (I'm not saying a multi-table index. Such a thing would be too large on disk. That functionality should probably better be realized by using the underlying per-table indexes).

That's why I'd say, the first columns of an index would have to be equal to all of the columns used in the partitioning key.

I correct my own statement somewhat, here: only in that case, a single table index can satisfy your request. For other cases, you'd have to query more than one partition's indexes and mix them correctly to maintain the right order, if required.

No. It may change performance in some situations, but it is not needed
for unique constraints.

Agreed, for unique constraints. But indexes are used for some more things than just unique constraints checking. ;-)



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

Reply via email to