Re: Covering Indexes

On Thu, Jul 26, 2012 at 12:17 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> 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.
>
> I think it is going to take several years before we really understand
> how index-only scans play out in the real world, and what factors
> limit their usefulness. This one has come up a few times because it's
> sort of an obvious thing to want to do and we don't have it, but I
> think that there's room for some skepticism about how well it will
> actually work, for reasons that have already been mentioned, and of
> course also because indexing more columns potentially means defeating
> HOT, which I suspect will defeat many otherwise-promising applications
> of index-only scans.

Sure. many will still get to use them though: I'm doing tons of
OLAP/BI lately: wide keys, minimal to no updating, minimal to no RI,
andvery large tables (often clustered and partitioned), and extreme
performance requirements. Covering indexes to me is basically a drop
in feature and COVERING seems to make a lot of sense on paper. (I
absolutely can't wait to get 9.2 on some of our bigger servers here).

merlin