Re: Covering Indexes

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 06/28/2012 11:37 AM, Jeff Janes wrote:
>> On Thu, Jun 28, 2012 at 5:16 AM, David E. Wheeler<david(at)justatheory(dot)com> wrote:
>>> I'm particularly intrigued by "covering indexes". For example:
>>> CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);

>> I don't see the virtue of this in this case. Since the index is not
>> unique, why not just put the index on (a,b,c,d) and be done with it?

> Presumably the comparison function will be faster the fewer attributes
> it needs to compare.

Well, no, because queries will only be comparing the columns used in
the query. Insertions would likely actually be faster with the extra
columns considered significant, since we've known for a long time that
btree doesn't especially like large numbers of identical index entries.

When this came up a couple weeks ago, the argument that was made for it
was that you could attach non-significant columns to an index that *is*
unique. That might or might not be a wide enough use-case to justify
adding such a horrid kludge.

regards, tom lane