Re: Covering Indexes

On Fri, Jul 27, 2012 at 12:24 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Thu, 2012-07-26 at 12:13 -0400, Bruce Momjian wrote:
>> So, do we want a TODO item about adding columns to a unique index that
>> will not be used for uniqueness checks?
>
> -1 from me, at least in its current form.
>
> At it's heart, this is about separating the constraint from the index
> that enforces it -- you'd like the columns to be available for querying
> (for index only scans or otherwise), but not to take part in the
> constraint.
>
> And when you look at it from that perspective, this proposal is and
> extremely limited form. You can't, for example, decide that an existing
> index can be used for a new unique constraint. That's a lot more
> plausible than the use cases mentioned in this thread as far as I can
> see, but this proposal can't do that.
>
> I tried proposing a more general use case when developing exclusion
> constraints:
>
> http://archives.postgresql.org/message-id/1253466074.6983.22.camel@jdavis
>
> (allow user to specify multiple constraints enforced by one existing
> index). But, at least at the time, my proposal didn't pass the
> usefulness test:
>
> http://archives.postgresql.org/pgsql-hackers/2009-09/msg01355.php
>
> even though my proposal was strictly more powerful than this one is.
>
> Also, this proposal extends the weird differences between CREATE UNIQUE
> INDEX and a the declaration of a UNIQUE constraint. For example, if you
> want DEFERRABLE you need to declare the constraint, but if you want to
> use an expression (rather than a simple column reference) you need to
> create the index. This problem does not exist with exclusion
> constraints.
>
> In my opinion, new innovations in unique constraints would be better
> served as part of exclusion constraints, and we should keep unique
> constraints simple. If we make an improvement to UNIQUE, then we will
> want to do similar things for exclusion constraints anyway, so it just
> seems duplicative.

Well, you're right. The exclusion constraint syntax is amazingly
general (if somewhat arcane) and it would be neat to be extended like
that. It already decouples you from physical assumptions on the
index. For example, it creates a two field index for a double field
btree equality exclusion and does a runtime, not equality based
uniqueness check. The covering index/uniqueness use case adds
legitimacy to the INDEX clause of exclusion constraints IMNSHO.

One point of concern though is that (following a bit of testing)
alter table foo add exclude using btree (id with =);
...is always strictly slower for inserts than
alter table foo add primary key(id);

This is probably because it doesn't use the low level btree based
uniqueness check (the index is not declared UNIQUE) -- shouldn't it do
that if it can?

merlin