Skip site navigation (1) Skip section navigation (2)

Re: Covering Indexes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>
Cc: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, "David E(dot) Wheeler" <david(at)justatheory(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Covering Indexes
Date: 2012-06-28 16:07:58
Message-ID: 14261.1340899678@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2012-06-28 16:12:14
Subject: Re: Covering Indexes
Previous:From: Andres FreundDate: 2012-06-28 16:04:25
Subject: Re: [PATCH 08/16] Introduce the ApplyCache module which can reassemble transactions from a stream of interspersed changes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group