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

On Tue, May 22, 2012 at 5:26 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>  A bigger problem is that creating such an index turns all
>> of pgbench's write traffic from HOT updates into non-HOT updates,
>> which means this is probably only going to be a win if the write
>> volume is miniscule.
>
> Not sure whether you see that as an argument against the proposal.
> This argument applies to any index. In particular covered indexes are
> specifically encouraged by index only scans, so is not a reason to
> avoid implementing the feature as Jeff describes.

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company