On Fri, Jul 27, 2012 at 1:27 PM, Merlin Moncure <mmonc...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
