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

Robert Haas <robertmhaas@gmail.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