Hi,
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. ;-)
Regards
Markus
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
