Re: Covering Indexes

On Fri, Jul 27, 2012 at 1:27 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
> 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?

If it did that, then than would make it faster in precisely those
cases were I wouldn't use it in the first place--where there is a less
esoteric alternative that does exactly the same thing. While that is
not something without value, it would seem better (although
potentially more difficult of course) to just make it faster in
general, instead.

I didn't look into the creation, but rather into inserts. During
inserts, it looks like it is doing a look up into the btree twice,
presumably once to maintain it, and once to check for uniqueness. If
there was some way to cache the look-up between those, I think it
would go a long way towards eliminating the performance difference.
Could that be done without losing the generality?

And, does it matter? I would think covering indexes would be deployed
to best effect when your data is not cached in RAM, in which case the
IO cost common to both paths probably overwhelms any extra CPU cost.

Cheers,

Jeff