Re: Covering Indexes

On Fri, 2012-07-27 at 15:27 -0500, Merlin Moncure wrote:
> The covering index/uniqueness use case adds
> legitimacy to the INDEX clause of exclusion constraints IMNSHO.

Yes, I think it would be worth revisiting the idea.

> 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);

Yes, in my worst-case tests there is about a 2X difference for building
the constraint and about a 30-50% slowdown during INSERT (I thought I
remembered the difference being lower, but I didn't dig up my old test).
That's for an in-memory case, I would expect disk I/O to make the
difference less apparent.

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

We could probably detect that the operator being used is the btree
equality operator, set the unique property of the btree, and avoid the
normal exclusion constraint check. I'm sure there are some details to
work out, but if we start collecting more use cases where people want
the flexibility of exclusion constraints and the speed of UNIQUE, we
should look into it.

Regards,
Jeff Davis