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

On 23 May 2012 18:13, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.

Seems like very poor logic to me. Why would adding a column to an
index mean that column was heavily updated?

HOT provides a negative effect if you index certain columns.
IOS provides a positive effect if you add columns to an index, and you
have a high percentage of access to now-readonly data.

The area of effect of those two things are not mutually exclusive, not
even close. Or put another way HOT has got nothing at all to do with
covered indexes.

The proposal allows us to have 1 index instead of 2, with obvious
gains in performance. This only has benefit if IOS are effective. If
we believe them to be effective, and I really hope you believe that,
then this will also have benefit to in all the same cases.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services