> > That lets you enforce unique constraints as long as the partition
key
> > is part of the unique constraint.
>
> Is that already sufficient?
yes
> That would alter the ordering of
> the columns in the index, no? I mean:
It produces ordered blocks of append nodes for range queries that span
multiple partitions,
but one unique key can still only be in exactly one of the partitions.
e.g. If you range partition by b, only one partition is applicable
regardless
of the position of b in the index. This is sufficient for a working
unique constraint
with current pg versions.
> 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),
it is not an issue for filtering rows.
And in some cases the sort could still be avoided with some range
proving logic,
if you can bring the append nodes of partitions into an order that
represents
the order by.
(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)
> 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.
No. It may change performance in some situations, but it is not needed
for unique constraints.
Andreas