Re: Add primary key/unique constraint using prefix columns of an index

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I don't object to the feature, but I think it's real-world utility
> will be more limited than we might hope. When covering indexes are
> not in play, someone might choose to index only, say, the primary key.
> And maybe the primary key doesn't change very often, so HOT still
> applies to nearly all updates. But then when they try to make a
> covering index, they're now indexing columns that they *do* update
> pretty regularly. Now the gain from index-only scans is fighting with
> the loss from some updates no longer being HOT. I think in many cases
> losing the benefit of HOT will cost more than the covering index
> gains, at least in 9.2. I hope we'll be able to improve it in future
> releases.

Well, if the table is getting enough update traffic that it matters much
whether your updates are HOT or not, then index-only scans are probably
a dead letter anyhow, because too small a fraction of the pages will be
all-visible. I think the IOS feature is only good for something on
read-mostly tables.

regards, tom lane