Re: Covering Indexes

On Thu, Jul 26, 2012 at 11:13 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Tue, Jul 17, 2012 at 06:00:37PM +0100, Simon Riggs wrote:
>> > Either way the data in "c" and "d" are IN THE INDEX otherwise in neither
>> > case could the data values be returned while strictly querying the index.
>> >
>> > So the question that needs to be asked is what kind of performance increase
>> > can be had during DML (insert/update) statements and whether those gains are
>> > worth pursuing. Since these other engines appear to allow both cases you
>> > should be able to get at least a partial idea of the performance gains
>> > between "index (a,b,c,d)" and "index (a,b) covering (c,d)".
>>
>> There is a use case, already discussed, whereby that is useful for
>> create unique index on foo (a,b) covering (c,d)
>>
>> but there really isn't any functional difference between
>> create index on foo (a,b) covering (c,d)
>>
>> and
>> create index on foo (a,b,c,d)
>>
>> There is a potential performance impact. But as Tom says, that might
>> even be negative if it is actually measurable.
>
> So, do we want a TODO item about adding columns to a unique index that
> will not be used for uniqueness checks?

I think so. The case where you want a wide multiple column primary
key to be extended to cover that one extra commonly grabbed value is
not super common but entirely plausible. With the existing
infrastructure to get the advantages of index covering you'd have to
duplicate the entire index for the extra field which really sucks:
aside from the huge waste of time and space, you force the planner to
play the 'which index do i use?' game.

merlin